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

Cristian Gafton <gafton@rpath.com> writes:
> We are churning through a bunch of short-lived temp tables.

I think that's probably the root of the problem ...

> Since I 
> reported the problem, the pgstat file is now sitting at 85M, yet the 
> pg_stat* tables barely have any entries in them:

>              count(*)
> pg_stats        298
> pg_statistic        298
> pg_stat_all_indexes    76
> pg_stat_all_tables    76
> pg_statio_all_tables    56
> pg_statio_all_indexes    76

Those views are joins against pg_class, so only tables that have live
pg_class rows can possibly show up there.  You could try remembering the
OIDs of some temp tables and probing the underlying pg_stat_get_xxx()
functions to see if there are stats-table entries for them.

(Pokes around in the code...)  I think the problem here is that the only
active mechanism for flushing dead stats-table entries is
pgstat_vacuum_tabstat(), which is invoked by a VACUUM command or an
autovacuum.  Once-a-day VACUUM isn't gonna cut it for you under those
circumstances.  What you might do is just issue a VACUUM on some
otherwise-uninteresting small table, once an hour or however often you
need to keep the stats file bloat to a reasonable level.

There is a pgstat_drop_relation() function to tell the stats collector
to drop a single table entry, but it's not being called from anyplace.
We probably ought to try a bit harder to make that work.  The problem
is described here:

2007-07-08 18:23  tgl
* src/: backend/postmaster/pgstat.c, backend/storage/smgr/smgr.c,include/pgstat.h (REL8_1_STABLE),
backend/postmaster/pgstat.c,backend/storage/smgr/smgr.c,include/pgstat.h (REL8_2_STABLE),backend/postmaster/pgstat.c,
backend/storage/smgr/smgr.c,include/pgstat.h:Remove the pgstat_drop_relation() call fromsmgr_internal_unlink(), because
wedon't know at that point whichrelation OID to tell pgstat to forget.    The code was passing therelfilenode, which is
incorrect,and could possibly cause someother relation's stats to be zeroed out.  While we could try toclean this up, it
seemsmuch simpler and more reliable to let thenext invocation of pgstat_vacuum_tabstat() fix things; which indeedis how
itworked before I introduced the buggy code into 8.1.3 andlater :-(.  Problem noticed by Itagaki Takahiro, fix is
persubsequentdiscussion.
 
        regards, tom lane