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

Re: What could lock up pg_stat_activity

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Marcus Engene <mengpg2(at)engene(dot)se>
Cc: PGSQL-Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: What could lock up pg_stat_activity
Date: 2011-10-14 16:19:24
Message-ID: 4E98610C.5080804@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 10/14/2011 08:07 AM, Marcus Engene wrote:
> Hi,
>
> I have a site with reasonable # of page loads. I log slow selects so I 
> can get panic later. Today I had a 12s period where things stood still.
>
> As an overload precaution, for each page I...
> select count(*) as nbr from pg_stat_activity where current_query <> 
> '<IDLE>'
> ...to see that not too much is torturing the db. But (roughly) 1/3 of 
> the entries was this test above. What could possibly make a count() on 
> pg_stat_activity take 12s? Please speculate wildly.
>
> Setup:
> Contemporary Debian
> 72GB
> 4x1TB RAID6
> 2x100GB RAID1 SSD
> pgbouncer
> private local network
> pg 9.0.4
>
> Thanks,
> Marcus
>
>

A bit more info is in order. Is the database handling lots of writes or 
mostly simple reads? How much of the database is typically touched? How 
big is the database? What data is on the spinning media vs. the SSD? Do 
you have battery-backed write-cache? How big is it?

There are lots of situations with large RAM sizes where the OS allows so 
much data to be cached that when a flush to disk occurs, everything 
halts till the data is written.

If you don't have Greg Smith's "PostgreSQL 9.0 High Performance", go buy it.

You may find this recent thread interesting, not for the solution but 
for the discussion:
http://postgresql.1045698.n5.nabble.com/Adding-more-memory-hugh-cpu-load-td4888181.html#a4888530

Cheers,
Steve


In response to

Responses

pgsql-novice by date

Next:From: Hany ABOU-GHOURYDate: 2011-10-16 05:44:22
Subject: Re: Partitioning in PostgrSQL 9.04
Previous:From: Marcus EngeneDate: 2011-10-14 15:07:07
Subject: What could lock up pg_stat_activity

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