Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 336FEEC32B9 for ; Thu, 12 Apr 2012 15:41:45 -0300 (ADT) Received: from 74-93-229-125-colorado.hfc.comcastbusiness.net ([74.93.229.125] helo=mail.consistentstate.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SIOxj-0002DI-Lv for pgsql-performance@postgresql.org; Thu, 12 Apr 2012 18:41:44 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.consistentstate.com (Postfix) with ESMTP id F27D5309A76 for ; Thu, 12 Apr 2012 12:41:30 -0600 (MDT) X-Virus-Scanned: amavisd-new at consistentstate.com Received: from mail.consistentstate.com ([127.0.0.1]) by localhost (mail.consistentstate.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id aNxNBRUR2Fq4 for ; Thu, 12 Apr 2012 12:41:30 -0600 (MDT) Received: from [192.168.1.100] (unknown [192.168.1.100]) by mail.consistentstate.com (Postfix) with ESMTPSA id 353D7309694 for ; Thu, 12 Apr 2012 12:41:30 -0600 (MDT) Message-ID: <4F8721C4.8090300@consistentstate.com> Date: Thu, 12 Apr 2012 12:41:08 -0600 From: Brian Fehrle User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.3) Gecko/20120314 Thunderbird/10.0.3 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Random performance hit, unknown cause. Content-Type: multipart/alternative; boundary="------------010300030107070502060007" X-Pg-Spam-Score: -0.9 (/) X-Archive-Number: 201204/88 X-Sequence-Number: 46734 This is a multi-part message in MIME format. --------------010300030107070502060007 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------010300030107070502060007 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------010300030107070502060007--