pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedunderstanding postgres issues/bottlenecks
10+ messages / 6 participants
[nested] [flat]
* understanding postgres issues/bottlenecks
@ 2009-01-06 16:50 Stefano Nichele <stefano.nichele@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Stefano Nichele @ 2009-01-06 16:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-06 17:16 Merlin Moncure <mmoncure@gmail.com>
parent: Stefano Nichele <stefano.nichele@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Merlin Moncure @ 2009-01-06 17:16 UTC (permalink / raw)
To: Stefano Nichele <stefano.nichele@gmail.com>; +Cc: pgsql-performance
On Tue, Jan 6, 2009 at 11:50 AM, Stefano Nichele
<stefano.nichele@gmail.com> wrote:
> 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
clearly i/o bound. can you throw an iostat to give some more detailed info?
also,
need o/s version, etc
disk info (speed, raid controller, etc)
my guess is your disk system is underpowered for transaction load you
are giving.
how many tps is the database doing?
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-06 18:45 Scott Marlowe <scott.marlowe@gmail.com>
parent: Stefano Nichele <stefano.nichele@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Scott Marlowe @ 2009-01-06 18:45 UTC (permalink / raw)
To: Stefano Nichele <stefano.nichele@gmail.com>; +Cc: pgsql-performance
I concur with Merlin you're I/O bound.
Adding to his post, what RAID controller are you running, does it have
cache, does the cache have battery backup, is the cache set to write
back or write through?
Also, what do you get for this (need contrib module pgbench installed)
pgbench -i -s 100
pgbench -c 50 -n 10000
? Specifically transactions per second?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-06 19:02 Stefano Nichele <stefano.nichele@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Stefano Nichele @ 2009-01-06 19:02 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
Thanks for your help. I'll give you the info you asked as soon as I'll
have it (i have also to install iostat but I don't have enough privilege
to do that).
BTW, why did you said I/O bound ? Which are the parameters that
highlight that ? Sorry for my ignorance....
ste
Merlin Moncure wrote:
> On Tue, Jan 6, 2009 at 11:50 AM, Stefano Nichele
> <stefano.nichele@gmail.com> wrote:
>
>> 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
>>
>
> clearly i/o bound. can you throw an iostat to give some more detailed info?
> also,
> need o/s version, etc
> disk info (speed, raid controller, etc)
> my guess is your disk system is underpowered for transaction load you
> are giving.
> how many tps is the database doing?
>
> merlin
>
>
--
Stefano Nichele
Funambol Chief Architect
Funambol :: Open Source Mobile'We' for the Mass Market :: http://www.funambol.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-06 20:50 Scott Marlowe <scott.marlowe@gmail.com>
parent: Stefano Nichele <stefano.nichele@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Scott Marlowe @ 2009-01-06 20:50 UTC (permalink / raw)
To: Stefano Nichele <stefano.nichele@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, Jan 6, 2009 at 12:02 PM, Stefano Nichele
<stefano.nichele@gmail.com> wrote:
> Thanks for your help. I'll give you the info you asked as soon as I'll have
> it (i have also to install iostat but I don't have enough privilege to do
> that).
>
> BTW, why did you said I/O bound ? Which are the parameters that highlight
> that ? Sorry for my ignorance....
In this output, the second to the last column is wa, which stands for
wait, the third to last is id, which is CPU idle.
r b swpd free buff cache si so bi bo in cs us sy id wa st
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
So here the first line reports
25% user
7% system
6% idle
62% wait
So it's mostly waiting.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-06 23:33 David Rees <drees76@gmail.com>
parent: Stefano Nichele <stefano.nichele@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: David Rees @ 2009-01-06 23:33 UTC (permalink / raw)
To: Stefano Nichele <stefano.nichele@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, Jan 6, 2009 at 11:02 AM, Stefano Nichele
<stefano.nichele@gmail.com> wrote:
> BTW, why did you said I/O bound ? Which are the parameters that highlight
> that ? Sorry for my ignorance....
In addition to the percentage of time spent in wait as Scott said, you
can also see the number of processes which are blocked (b column on
the left). Those are the processes which would like to run but are
waiting for IO to complete.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-07 04:17 M. Edward (Ed) Borasky <znmeb@cesmail.net>
parent: David Rees <drees76@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: M. Edward (Ed) Borasky @ 2009-01-07 04:17 UTC (permalink / raw)
To: pgsql-performance
David Rees wrote:
> On Tue, Jan 6, 2009 at 11:02 AM, Stefano Nichele
> <stefano.nichele@gmail.com> wrote:
>> BTW, why did you said I/O bound ? Which are the parameters that highlight
>> that ? Sorry for my ignorance....
>
> In addition to the percentage of time spent in wait as Scott said, you
> can also see the number of processes which are blocked (b column on
> the left). Those are the processes which would like to run but are
> waiting for IO to complete.
>
>>From your earlier vmstat output, you can see that often quite a large
> number of processes were waiting on IO - 20-30 of them. This is
> confirmed by your load average. Since you only have 6 spindles, during
> those periods, request service latency was likely very high during
> those periods.
>
> Anyway, I would start with significantly increasing the amount of
> memory you have allocated to shared_buffers.
>
> 32MB is _way_ too low. For a dedicated PostgreSQL machine with 4GB
> RAM, 1GB going to be a lot closer to optimal, with the optimal setting
> probably somewhere between 500MB and 2GB.
>
> And please post the rest of the information that the others have asked for...
>
> -Dave
>
A couple of notes on "iostat":
1. The package it lives in is called "sysstat". Most Linux distros do
*not* install "sysstat" by default. Somebody should beat up on them
about that. :)
2. It does not require admin privileges to install or execute if you
want to have your own personal copy. You will, however, need the Gnu C
compiler, "make", and some other things that also may not be installed.
In any event, you can build "sysstat" from source in your home directory
if you can't get the sysadmin to install it globally.
3. Once you do get it installed, the command line to run it that gives
the most information is
$ iostat -cdmtx <sampling interval> <number of samples>
That will give you CPU utilization, throughputs in megabytes, time
stamps and extended statistics like device utilizations, queue lengths
and average service times. And if you're running 2.6.25 or later, it
should give you all the statistics for all partitions, not just devices!
Somewhere buried in one of my open source projects is a Perl script that
will parse the "iostat" output into a CSV file. By the way, if you need
to, you can be fairly aggressive with small sampling intervals. "iostat"
is very efficient, and chances are, you're I/O bound anyhow so your
processors will have plenty of bandwidth to format numbers out of /proc.
:) I normally run with samples every ten seconds, but you can probably
go even smaller if you need to.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-07 09:31 Simon Waters <simonw@zynet.net>
parent: M. Edward (Ed) Borasky <znmeb@cesmail.net>
0 siblings, 1 reply; 10+ messages in thread
From: Simon Waters @ 2009-01-07 09:31 UTC (permalink / raw)
To: pgsql-performance
On Wednesday 07 January 2009 04:17:10 M. Edward (Ed) Borasky wrote:
>
> 1. The package it lives in is called "sysstat". Most Linux distros do
> *not* install "sysstat" by default. Somebody should beat up on them
> about that. :)
Hehe, although sysstat and friends did have issues on Linux for a long time.
Nothing worse than misleading stats, so I suspect it lost a lot of friends
back then. It is a lot better these days when most of the "Unix" software
targets Linux first, and other kernels second.
Aside from all the advice here about system tuning, as a system admin I'd also
ask is the box doing the job you need? And are you looking at the Postgres
log (with logging of slow queries) to see that queries perform in a sensible
time? I'd assume with the current performance figure there is an issue
somewhere, but I've been to places where it was as simple as adding one
index, or even modifying an index so it does what the application developer
intended instead of what they ask for ;)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-07 15:35 M. Edward (Ed) Borasky <znmeb@cesmail.net>
parent: Simon Waters <simonw@zynet.net>
0 siblings, 0 replies; 10+ messages in thread
From: M. Edward (Ed) Borasky @ 2009-01-07 15:35 UTC (permalink / raw)
To: pgsql-performance
Simon Waters wrote:
> On Wednesday 07 January 2009 04:17:10 M. Edward (Ed) Borasky wrote:
>> 1. The package it lives in is called "sysstat". Most Linux distros do
>> *not* install "sysstat" by default. Somebody should beat up on them
>> about that. :)
>
> Hehe, although sysstat and friends did have issues on Linux for a long time.
> Nothing worse than misleading stats, so I suspect it lost a lot of friends
> back then. It is a lot better these days when most of the "Unix" software
> targets Linux first, and other kernels second.
I'm unfortunately familiar with that "episode", which turned out to be
bugs in the Red Hat Linux kernels / drivers. I don't remember the exact
versions, although there was at least one of them shipped at one time as
part of RHEL 3. I may be wrong, but I don't think Sebastien ever had to
change any of *his* code in "sysstat" -- I think he had to wait for Red
Hat. :)
>
> Aside from all the advice here about system tuning, as a system admin I'd also
> ask is the box doing the job you need? And are you looking at the Postgres
> log (with logging of slow queries) to see that queries perform in a sensible
> time? I'd assume with the current performance figure there is an issue
> somewhere, but I've been to places where it was as simple as adding one
> index, or even modifying an index so it does what the application developer
> intended instead of what they ask for ;)
>
Yeah ... the issues of application response time and throughput, cost of
adding hardware to the box(es) vs. cost of changing the application,
changes in demand for the server over time, etc., are what make capacity
planning "fun". Squeezing the last megabyte per second out of a RAID
array is the easy stuff. :)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: understanding postgres issues/bottlenecks
@ 2009-01-07 19:05 Stefano Nichele <stefano.nichele@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Stefano Nichele @ 2009-01-07 19:05 UTC (permalink / raw)
To: pgsql-performance
Ok, here some information:
OS: Centos 5.x (Linux 2.6.18-53.1.21.el5 #1 SMP Tue May 20 09:34:18 EDT
2008 i686 i686 i386 GNU/Linux)
RAID: it's a hardware RAID controller
The disks are 9600rpm SATA drives
(6 disk 1+0 RAID array and 2 separate disks for the OS).
About iostat (on sdb I have pg_xlog, on sdc I have data)
> iostat -k
Linux 2.6.18-53.1.21.el5 (*******) 01/07/2009
avg-cpu: %user %nice %system %iowait %steal %idle
17.27 0.00 5.13 45.08 0.00 32.52
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 30.42 38.50 170.48 182600516 808546589
sdb 46.16 0.23 52.10 1096693 247075617
sdc 269.26 351.51 451.00 1667112043 2138954833
> iostat -x -k -d 2 5
Linux 2.6.18-53.1.21.el5 (*******) 01/07/2009
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await svctm %util
sda 0.17 12.68 0.47 29.95 38.51 170.51
13.74 0.03 0.86 0.19 0.57
sdb 0.01 80.11 0.05 46.11 0.23 52.01
2.26 0.01 0.22 0.22 1.01
sdc 7.50 64.57 222.55 46.69 350.91 450.98
5.96 0.57 2.05 3.13 84.41
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
sdb 0.00 196.00 1.00 117.00 4.00 1252.00
21.29 0.02 0.19 0.19 2.30
sdc 1.50 66.00 277.00 66.50 3100.00 832.00
22.89 50.84 242.30 2.91 100.10
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
sdb 0.00 264.50 0.00 176.50 0.00 1764.00
19.99 0.04 0.21 0.21 3.70
sdc 3.50 108.50 291.50 76.00 3228.00 752.00
21.66 89.42 239.39 2.72 100.05
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 4.98 0.00 1.00 0.00 23.88
48.00 0.00 0.00 0.00 0.00
sdb 0.00 23.88 0.00 9.45 0.00 133.33
28.21 0.00 0.21 0.21 0.20
sdc 1.00 105.97 274.13 53.73 3297.51 612.94
23.85 67.99 184.58 3.04 99.55
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
sdb 0.00 79.00 0.00 46.00 0.00 500.00
21.74 0.01 0.25 0.25 1.15
sdc 2.50 141.00 294.00 43.50 3482.00 528.00
23.76 51.33 170.46 2.96 100.05
vmstat in the same time:
> vmstat 2
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy
id wa st
0 27 80 126380 27304 3253016 0 0 98 55 0 1 17 5
33 45 0
0 26 80 124516 27300 3255456 0 0 3438 1724 2745 4011 11
2 8 78 0
1 25 80 124148 27276 3252548 0 0 3262 2806 3572 7007 33
11 3 53 0
1 28 80 128272 27244 3248516 0 0 2816 1006 2926 5624 12 3
12 73 0
I will run pgbench in the next days.
>
> Aside from all the advice here about system tuning, as a system admin I'd also
> ask is the box doing the job you need? And are you looking at the Postgres
> log (with logging of slow queries) to see that queries perform in a sensible
> time? I'd assume with the current performance figure there is an issue
> somewhere, but I've been to places where it was as simple as adding one
> index, or even modifying an index so it does what the application developer
> intended instead of what they ask for ;)
>
I already checked postgres log and resolved index/slow queries issues.
Actually I have queries that sometime are really fast, and sometime go
in timeout.
But all the required indexes are there. For sure, there are space to
improve performances also in that way, but I would like also to
investigate issue from other point of views (in order to understand also
how to monitor the server).
Cheers and thanks a lot.
ste
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2009-01-07 19:05 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-01-06 16:50 understanding postgres issues/bottlenecks Stefano Nichele <stefano.nichele@gmail.com>
2009-01-06 17:16 ` Merlin Moncure <mmoncure@gmail.com>
2009-01-06 19:02 ` Stefano Nichele <stefano.nichele@gmail.com>
2009-01-06 20:50 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-01-06 23:33 ` David Rees <drees76@gmail.com>
2009-01-07 04:17 ` M. Edward (Ed) Borasky <znmeb@cesmail.net>
2009-01-07 09:31 ` Simon Waters <simonw@zynet.net>
2009-01-07 15:35 ` M. Edward (Ed) Borasky <znmeb@cesmail.net>
2009-01-06 18:45 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-01-07 19:05 Re: understanding postgres issues/bottlenecks Stefano Nichele <stefano.nichele@gmail.com>
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