Re: What could lock up pg_stat_activity

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