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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings