Skip site navigation (1) Skip section navigation (2)

Sudden Query slowdown on our Postgresql Server

From: Sebastian Melchior <webmaster(at)mailz(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Sudden Query slowdown on our Postgresql Server
Date: 2012-03-22 22:52:49
Message-ID: 03615FD7-20B3-49AB-AC5E-484544D03933@mailz.de (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

we are currently seeing some strange performance issues related to our Postgresql Database. Our Setup currently contains:
- 1 Master with 32GB Ram and 6 x 100GB SSDs in RAID10 and 2 Quad Core Intel Processors (this one has a failover Box, the data volume is shared via DRBD)
- 2 Slaves with 16GB Ram and 6 x 100GB SAS Disks in RAID 10 and 1 Quad Core Processor connected via streaming replication

We currently use Postgresql 9.0.6 from Debian Squeeze Backports with a 2.6.39-bpo.2 Backports Squeeze Kernel. 
All Servers use Pgbouncer as a connection Pooler, which is installed on each box. 
In times of higher usage, we see some strange issues on our Master box, the connections start stacking up in an "<idle in transaction>" state, and the Queries get slower and slower when using the Master Server. We traced the Application which is connected via a private LAN, and could not find any hangups that could cause these states in the Database. During this time, the load of the Master goes up a bit, but the CPU Usage and IOwait is still quite low at around a Load of 5-8. The usual Load is around 1 - 1.5. 

19:14:28.654 4838 LOG Stats: 3156 req/s, in 1157187 b/s, out 1457656 b/s,query 6119 us
19:15:28.655 4838 LOG Stats: 3247 req/s, in 1159833 b/s, out 1421552 b/s,query 5025 us
19:16:28.660 4838 LOG Stats: 3045 req/s, in 1096349 b/s, out 1377927 b/s,query 3713 us
19:17:28.680 4838 LOG Stats: 2779 req/s, in 1030783 b/s, out 1343547 b/s,query 11977 us
19:18:28.688 4838 LOG Stats: 1723 req/s, in 664282 b/s, out 789989 b/s,query 67144 us
19:19:28.665 4838 LOG Stats: 1371 req/s, in 472587 b/s, out 622347 b/s,query 48417 us
19:20:28.668 4838 LOG Stats: 2161 req/s, in 748727 b/s, out 995794 b/s,query 2794 us


As you can see in the pgbouncer logs, the query exec time shoots up. 
We took a close look at locking issues during that time, but we don't see any excessive amount of locking during that time.
The issue suddenly popped up, we had times of higher usage before and the Postgresql DB handled it without any problems. We also did not recently change anything in this setup. We also did take a look at the Slow Queries Log during that time. This did now show anything unusual during the time of the slowdown. 

Does anyone have any idea what could cause this issue or how we can further debug it?

Thanks for your Input!

Sebastian


Responses

pgsql-performance by date

Next:From: Mark KirkwoodDate: 2012-03-23 00:08:53
Subject: Re: Write workload is causing severe slowdown in Production
Previous:From: Merlin MoncureDate: 2012-03-22 16:18:43
Subject: Re: Write workload is causing severe slowdown in Production

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group