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

Re: Large pgstat.stat file causes I/O storm

From: Cristian Gafton <gafton(at)rpath(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Large pgstat.stat file causes I/O storm
Date: 2008-01-29 19:06:12
Message-ID: Pine.LNX.4.64.0801291354071.19796@alienpad.rpath.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, 29 Jan 2008, Tom Lane wrote:

>> On Tue, 29 Jan 2008, Cristian Gafton wrote:
>>> I have a ~150GB sized server, containing two databases that are active in
>>> mostly read mode. I have noticed lately that the global/pgstat.stat file is
>>> somewhere around 1MB freshly after a restart, but at some point it baloons to
>>> 74MB in size for no apparent reason, after a few hours of uptime. Needless to
>>> say, having the stats collector dump 74MB of stuff on disk on its every loop
>>> takes a big bite of the I/O capabilities of this box.
>
>> Of course, leaving out the most important thing - this is postgresql 8.2.6
>> on x86_64
>
> Hmm ... do you have autovacuum enabled?  If not, what's the vacuuming
> policy on that box?  I'm wondering if this is triggered by something
> deciding to vacuum or analyze a bunch of otherwise-unused tables, and
> thereby causing stats entries to be created for those tables.

Autovacuum is disabled, since the database is mostly read only. There is a 
"vacuumdb -a -z" running nightly on the box. However, the application that 
queries it does a lot of work with temporary tables - would those bloat 
the stats at all?

> You could investigate by comparing the contents of the stats views
> before and after the file balloons.  I would expect to see a lot more
> rows, and the key is exactly what non-null activity is recorded in
> the extra rows.

Any one of the stats views in particular? Currently all of the stats_*
flags are set to "on".

Thanks,

Cristian
-- 
Cristian Gafton
rPath, Inc.


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-01-29 19:09:13
Subject: Re: [PATCHES] Proposed patch: synchronized_scanning GUCvariable
Previous:From: Tom LaneDate: 2008-01-29 18:40:02
Subject: Re: Large pgstat.stat file causes I/O storm

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