We have a system, that due to recent events is getting a lot heavier use. The application makes extensive use of functions. These functions would typically run sub-second but now can take several seconds.
I'm wondering what general ways there are to monitor and improve performance? We look at pgadmin's server status but that only sees the function being run. Additionally, is there a good way to 'explain' a function? Or do you have to execute the function steps individually and explain those?
The server is running on suse 11.4 with 8 vcpu and 32Gb ram on a virtual machine.
Running pg_version returns 'PostgreSQL 9.1.0 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.5.1 20101208 [gcc-4_5-branch revision 167585], 64-bit' and select pg_size_pretty(pg_database_size('nrgdb')); returns 63 GB.
The server typically has up to 500 connections with a max of 750 connections.
Below are the non-default values of our configuration file.
Any thoughts on what we should look at?
Thanks,
Bryan
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 750 # (change requires restart)
superuser_reserved_connections = 9 # (change requires restart)
shared_buffers = 8192MB # min 128kB or max_connections*16kB
temp_buffers = 64MB # min 800kB
max_prepared_transactions = 250 # can be 0 or more
work_mem = 512MB # min 64kB
maintenance_work_mem = 1GB # min 1MB
fsync = off # turns forced synchronization on or off
full_page_writes = off # recover from partial page writes
wal_buffers = 16MB # min 32kB
commit_delay = 1000 # range 0-100000, in microseconds
commit_siblings = 5 # range 1-1000
checkpoint_segments = 50 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 5min # range 30s-1h
checkpoint_warning = 1min # 0 is off
effective_cache_size = 16GB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on
Log_directory = '/var/log/postgres' # Directory where log files are written
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 20MB # Automatic rotation of logfiles will
log_min_messages = info # Values, in order of decreasing detail:
log_min_duration_statement = 10000 # -1 is disabled, 0 logs all statements
log_line_prefix = '%t %p %u@%h: ' # Special values for Pgsi
log_statement = 'none' # none, ddl, mod, all
log_duration = off
autovacuum = on # enable autovacuum subprocess?
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
