From: Brian Fehrle <brianf@consistentstate.com>
To: pgsql-performance@postgresql.org
Subject: Random performance hit, unknown cause.
Date: Thu, 12 Apr 2012 12:41:08 -0600
Message-ID: <4F8721C4.8090300@consistentstate.com> (raw)
Hi all,
OS: Linux 64 bit 2.6.32
PostgreSQL 9.0.5 installed from Ubuntu packages.
8 CPU cores
64 GB system memory
Database cluster is on raid 10 direct attached drive, using a HP p800
controller card.
I have a system that has been having occasional performance hits, where
the load on the system skyrockets, all queries take longer to execute
and a hot standby slave I have set up via streaming replication starts
to get behind. I'm having trouble pinpointing where the exact issue is.
This morning, during our nightly backup process (where we grab a copy of
the data directory), we started having this same issue. The main thing
that I see in all of these is a high disk wait on the system. When we
are performing 'well', the %wa from top is usually around 30%, and our
load is around 12 - 15. This morning we saw a load 21 - 23, and an %wa
jumping between 60% and 75%.
The top process pretty much at all times is the WAL Sender Process, is
this normal?
From what I can tell, my access patterns on the database has not
changed, same average number of inserts, updates, deletes, and had
nothing on the system changed in any way. No abnormal autovacuum
processes that aren't normally already running.
So what things can I do to track down what an issue is? Currently the
system has returned to a 'good' state, and performance looks great. But
I would like to know how to prevent this, as well as be able to grab
good stats if it does happen again in the future.
Has anyone had any issues with the HP p800 controller card in a postgres
environment? Is there anything that can help us maximise the performance
to disk in this case, as it seems to be one of our major bottlenecks? I
do plan on moving the pg_xlog to a separate drive down the road, the
cluster is extremely active so that will help out a ton.
some IO stats:
$ iostat -d -x 5 3
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
dev1 1.99 75.24 651.06 438.04 41668.57 8848.18
46.38 0.60 3.68 0.70 76.36
dev2 0.00 0.00 653.05 513.43 41668.57 8848.18
43.31 2.18 4.78 0.65 76.35
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
dev1 0.00 35.20 676.20 292.00 35105.60 5688.00
42.13 67.76 70.73 1.03 100.00
dev2 0.00 0.00 671.80 295.40 35273.60 4843.20
41.48 73.41 76.62 1.03 100.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
dev1 1.20 40.80 865.40 424.80 51355.20 8231.00
46.18 37.87 29.22 0.77 99.80
dev2 0.00 0.00 867.40 465.60 51041.60 8231.00
44.47 38.28 28.58 0.75 99.80
Thanks in advance,
Brian F
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: brianf@consistentstate.com
Subject: Re: Random performance hit, unknown cause.
In-Reply-To: <4F8721C4.8090300@consistentstate.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