From: Stefano Nichele <stefano.nichele@gmail.com>
To: pgsql-performance@postgresql.org
Subject: understanding postgres issues/bottlenecks
Date: Tue, 06 Jan 2009 17:50:49 +0100
Message-ID: <49638BE9.2030701@gmail.com> (raw)
Hi list,
I would like to ask your help in order to understand if my postgresql
server (ver. 8.2.9) is well configured.
It's a quad-proc system (32 bit) with a 6 disk 1+0 RAID array and 2
separate disks for the OS and write-ahead logs with 4GB of RAM.
I don't know what is the best info to help me and so I start with some
vmstat information:
> vmstat -n 30
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy
id wa st
0 23 84 129968 25060 3247860 0 0 78 50 0 2 17 5
33 45 0
1 24 84 124204 25136 3257100 0 0 3037 1154 3359 7253 25
7 6 62 0
2 30 84 124704 25136 3256344 0 0 3004 1269 3553 7906 33
9 7 51 0
0 25 84 125784 24956 3253344 0 0 3357 773 3163 6454 17 4
10 68 0
0 2 84 125744 25236 3258996 0 0 3186 567 3125 6425 24 6
21 50 0
3 7 84 124948 25500 3260088 0 0 1829 535 2706 4625 18 3
54 25 0
5 0 84 124976 25624 3259112 0 0 2067 647 3050 6163 26 6
41 27 0
0 7 84 123836 25644 3260760 0 0 2239 1065 3289 8654 27 7
38 28 0
These are gathered loadavg info for the same period:
29.57 29.53 33.52 1/231 12641
29.54 29.63 33.31 1/226 12678
24.43 28.45 32.69 1/223 12696
12.31 24.17 30.95 4/223 12706
At the moment as average there are about 120/150 connections and this is
my postgresql.conf file
listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 400 # (change requires restart)
ssl = off # (change requires restart)
password_encryption = on
shared_buffers = 32MB # min 128kB or max_connections*16kB
max_prepared_transactions = 0 # can be 0 or more
work_mem = 1MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
max_fsm_pages = 204800 # min max_fsm_relations*16, 6
bytes each
fsync = on # turns forced synchronization
on or off
full_page_writes = on # recover from partial page writes
wal_buffers = 8MB # min 32kB
checkpoint_segments = 56 # in logfile segments, min 1,
16MB each
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
cpu_tuple_cost = 0.003 # same scale as above
cpu_index_tuple_cost = 0.001 # same scale as above
cpu_operator_cost = 0.0005 # same scale as above
effective_cache_size = 3GB
geqo_threshold = 14
log_destination = 'stderr' # Valid values are combinations of
redirect_stderr = on # Enable capturing of stderr
into log
log_directory = 'pg_log' # Directory where log files are
written
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
log_truncate_on_rotation = on # If on, any existing log file
of the same
log_rotation_age = 1d # Automatic rotation of logfiles
will
log_rotation_size = 0 # Automatic rotation of logfiles
will
log_min_duration_statement = -1 # -1 is disabled, 0 logs all
statements
log_statement = 'none' # none, ddl, mod, all
autovacuum = on # enable autovacuum subprocess?
stats_start_collector = on # must be 'on' for autovacuum
stats_row_level = on # must be 'on' for autovacuum
statement_timeout = 150000
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
deadlock_timeout = 5s
escape_string_warning = off
standard_conforming_strings = on
Cheers and thanks a lot in advance.
Let me know if other info is useful.
Ste
--
Stefano Nichele
Funambol Chief Architect
Funambol :: Open Source Mobile'We' for the Mass Market :: http://www.funambol.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stefano.nichele@gmail.com
Subject: Re: understanding postgres issues/bottlenecks
In-Reply-To: <49638BE9.2030701@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox