From: Stefano Nichele <stefano.nichele@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: understanding postgres issues/bottlenecks
Date: Wed, 07 Jan 2009 20:05:43 +0100
Message-ID: <4964FD07.2030702@gmail.com> (raw)
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
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: <4964FD07.2030702@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