Tuning / performance questions

From: Bryan Montgomery <monty(at)english(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Tuning / performance questions
Date: 2012-11-06 13:04:06
Message-ID: CAPTJ3=fJioT0E7t9oQmZ3qvV2+-4jK++trvfM9b12hwwbmbw2A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello experts! (and other like me).
**

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(at)%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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2012-11-06 13:45:09 Re: Hot Standby Not So Hot Anymore
Previous Message Magnus Hagander 2012-11-06 12:08:40 Re: Problem with streaming replication over SSL