pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSudden Query slowdown on our Postgresql Server
9+ messages / 5 participants
[nested] [flat]
* Sudden Query slowdown on our Postgresql Server
@ 2012-03-22 22:52 Sebastian Melchior <webmaster@mailz.de>
0 siblings, 1 reply; 9+ messages in thread
From: Sebastian Melchior @ 2012-03-22 22:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-03-23 00:47 Stephen Frost <sfrost@snowman.net>
parent: Sebastian Melchior <webmaster@mailz.de>
0 siblings, 1 reply; 9+ messages in thread
From: Stephen Frost @ 2012-03-23 00:47 UTC (permalink / raw)
To: Sebastian Melchior <webmaster@mailz.de>; +Cc: pgsql-performance
* Sebastian Melchior (webmaster@mailz.de) wrote:
> Does anyone have any idea what could cause this issue or how we can further debug it?
Are you logging checkpoints? If not, you should, if so, then see if
they correllate to the time of the slowdown..?
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-03-23 04:37 Sebastian Melchior <webmaster@mailz.de>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 9+ messages in thread
From: Sebastian Melchior @ 2012-03-23 04:37 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-performance
Hi,
yeah we log those, those times do not match the times of the slowdown at all. Seems to be unrelated.
Sebastian
On 23.03.2012, at 01:47, Stephen Frost wrote:
> * Sebastian Melchior (webmaster@mailz.de) wrote:
>> Does anyone have any idea what could cause this issue or how we can further debug it?
>
> Are you logging checkpoints? If not, you should, if so, then see if
> they correllate to the time of the slowdown..?
>
> Thanks,
>
> Stephen
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-03-23 04:48 Scott Marlowe <scott.marlowe@gmail.com>
parent: Sebastian Melchior <webmaster@mailz.de>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2012-03-23 04:48 UTC (permalink / raw)
To: Sebastian Melchior <webmaster@mailz.de>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-performance
I'd suggest the handy troubleshooting tools sar, iostat, vmstat and iotop
On Thu, Mar 22, 2012 at 10:37 PM, Sebastian Melchior <webmaster@mailz.de> wrote:
> Hi,
>
> yeah we log those, those times do not match the times of the slowdown at all. Seems to be unrelated.
>
> Sebastian
>
>
> On 23.03.2012, at 01:47, Stephen Frost wrote:
>
>> * Sebastian Melchior (webmaster@mailz.de) wrote:
>>> Does anyone have any idea what could cause this issue or how we can further debug it?
>>
>> Are you logging checkpoints? If not, you should, if so, then see if
>> they correllate to the time of the slowdown..?
>>
>> Thanks,
>>
>> Stephen
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-03-23 04:53 Sebastian Melchior <webmaster@mailz.de>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Sebastian Melchior @ 2012-03-23 04:53 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
Hi,
we already used iostat and iotop during times of the slowdown, there is no sudden drop in I/O workload in the times of the slowdown. Also the iowait does not spike and stays as before.
So i do not think that this is I/O related. As the disks are SSDs there also still is some "head room" left.
Sebastian
On 23.03.2012, at 05:48, Scott Marlowe wrote:
> I'd suggest the handy troubleshooting tools sar, iostat, vmstat and iotop
>
> On Thu, Mar 22, 2012 at 10:37 PM, Sebastian Melchior <webmaster@mailz.de> wrote:
>> Hi,
>>
>> yeah we log those, those times do not match the times of the slowdown at all. Seems to be unrelated.
>>
>> Sebastian
>>
>>
>> On 23.03.2012, at 01:47, Stephen Frost wrote:
>>
>>> * Sebastian Melchior (webmaster@mailz.de) wrote:
>>>> Does anyone have any idea what could cause this issue or how we can further debug it?
>>>
>>> Are you logging checkpoints? If not, you should, if so, then see if
>>> they correllate to the time of the slowdown..?
>>>
>>> Thanks,
>>>
>>> Stephen
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-03-23 05:19 Scott Marlowe <scott.marlowe@gmail.com>
parent: Sebastian Melchior <webmaster@mailz.de>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2012-03-23 05:19 UTC (permalink / raw)
To: Sebastian Melchior <webmaster@mailz.de>; +Cc: pgsql-performance
What does vmstat say about things like context switches / interrupts per second?
On Thu, Mar 22, 2012 at 10:53 PM, Sebastian Melchior <webmaster@mailz.de> wrote:
> Hi,
>
> we already used iostat and iotop during times of the slowdown, there is no sudden drop in I/O workload in the times of the slowdown. Also the iowait does not spike and stays as before.
> So i do not think that this is I/O related. As the disks are SSDs there also still is some "head room" left.
>
> Sebastian
>
> On 23.03.2012, at 05:48, Scott Marlowe wrote:
>
>> I'd suggest the handy troubleshooting tools sar, iostat, vmstat and iotop
>>
>> On Thu, Mar 22, 2012 at 10:37 PM, Sebastian Melchior <webmaster@mailz.de> wrote:
>>> Hi,
>>>
>>> yeah we log those, those times do not match the times of the slowdown at all. Seems to be unrelated.
>>>
>>> Sebastian
>>>
>>>
>>> On 23.03.2012, at 01:47, Stephen Frost wrote:
>>>
>>>> * Sebastian Melchior (webmaster@mailz.de) wrote:
>>>>> Does anyone have any idea what could cause this issue or how we can further debug it?
>>>>
>>>> Are you logging checkpoints? If not, you should, if so, then see if
>>>> they correllate to the time of the slowdown..?
>>>>
>>>> Thanks,
>>>>
>>>> Stephen
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>>
>> --
>> To understand recursion, one must first understand recursion.
>
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-03-23 07:10 Yeb Havinga <yebhavinga@gmail.com>
parent: Sebastian Melchior <webmaster@mailz.de>
1 sibling, 1 reply; 9+ messages in thread
From: Yeb Havinga @ 2012-03-23 07:10 UTC (permalink / raw)
To: Sebastian Melchior <webmaster@mailz.de>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
On 2012-03-23 05:53, Sebastian Melchior wrote:
> Hi,
>
> we already used iostat and iotop during times of the slowdown, there is no sudden drop in I/O workload in the times of the slowdown. Also the iowait does not spike and stays as before.
> So i do not think that this is I/O related. As the disks are SSDs there also still is some "head room" left.
I've seen a ssd completely lock up for a dozen seconds or so after
giving it a smartctl command to trim a section of the disk. I'm not sure
if that was the vertex 2 pro disk I was testing or the intel 710, but
enough reason for us to not mount filesystems with -o discard.
regards,
Yeb
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-03-23 07:52 Sebastian Melchior <webmaster@mailz.de>
parent: Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Sebastian Melchior @ 2012-03-23 07:52 UTC (permalink / raw)
To: pgsql-performance
Hi,
unfortunately we cannot directly control the TRIM (i am not sure it even occurs) because the SSDs are behind an LSI MegaSAS 9260 Controller which does not allow access via smart. Even if some kind of TRIM command is the problem, shouldn't the iowait go up in this case?
Sebastian
On 23.03.2012, at 08:10, Yeb Havinga wrote:
> On 2012-03-23 05:53, Sebastian Melchior wrote:
>> Hi,
>>
>> we already used iostat and iotop during times of the slowdown, there is no sudden drop in I/O workload in the times of the slowdown. Also the iowait does not spike and stays as before.
>> So i do not think that this is I/O related. As the disks are SSDs there also still is some "head room" left.
>
> I've seen a ssd completely lock up for a dozen seconds or so after giving it a smartctl command to trim a section of the disk. I'm not sure if that was the vertex 2 pro disk I was testing or the intel 710, but enough reason for us to not mount filesystems with -o discard.
>
> regards,
> Yeb
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sudden Query slowdown on our Postgresql Server
@ 2012-05-08 16:17 Robert Haas <robertmhaas@gmail.com>
parent: Sebastian Melchior <webmaster@mailz.de>
0 siblings, 0 replies; 9+ messages in thread
From: Robert Haas @ 2012-05-08 16:17 UTC (permalink / raw)
To: Sebastian Melchior <webmaster@mailz.de>; +Cc: pgsql-performance
On Fri, Mar 23, 2012 at 3:52 AM, Sebastian Melchior <webmaster@mailz.de> wrote:
> unfortunately we cannot directly control the TRIM (i am not sure it even occurs) because the SSDs are behind an LSI MegaSAS 9260 Controller which does not allow access via smart. Even if some kind of TRIM command is the problem, shouldn't the iowait go up in this case?
Based on my recent benchmarking experiences, maybe not. Suppose
backend A takes a lock and then blocks on an I/O. Then, all the other
backends block waiting on the lock. So maybe one backend is stuck in
I/O-wait, but on a multi-processor system the percentages are averaged
across all CPUs, so it doesn't really look like there's much I/O-wait.
If you have 'perf' available, I've found the following quite helpful:
perf record -e cs -g -a sleep 30
perf report -g
Then you can look at the report and find out what's causing PostgreSQL
to context-switch out - i.e. block - and therefore find out what lock
and call path is contended. LWLocks don't show up in pg_locks, so you
can't troubleshoot this sort of contention that way.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2012-05-08 16:17 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-22 22:52 Sudden Query slowdown on our Postgresql Server Sebastian Melchior <webmaster@mailz.de>
2012-03-23 00:47 ` Stephen Frost <sfrost@snowman.net>
2012-03-23 04:37 ` Sebastian Melchior <webmaster@mailz.de>
2012-03-23 04:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-03-23 04:53 ` Sebastian Melchior <webmaster@mailz.de>
2012-03-23 05:19 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-03-23 07:10 ` Yeb Havinga <yebhavinga@gmail.com>
2012-03-23 07:52 ` Sebastian Melchior <webmaster@mailz.de>
2012-05-08 16:17 ` Robert Haas <robertmhaas@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