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

Cristian Gafton <gafton(at)rpath(dot)com> writes:
> I just ran a vacuumdb -a on the box - the pgstat file is still >90MB in
> size. If vacuum is supposed to clean up the cruft from pgstat, then I
> don't know if we're looking at the right cruft - I kind of expected the
> pgstat file to go down in size and the I/O storm to subside, but that is
> not happening after vacuum.

Hmph ... I did a simple test here involving creating a lot of temp
tables, and indeed it made the stats file bigger, but the size went
right down again after vacuuming. Is it possible that the vacuumdb
failed to connect to the particular database in which the temp tables
are coming and going?

> I will try to instrument the application to record the oids of the temp
> tables it creates and investigate from that angle, but in the meantime is
> there any way to reset the stats collector altogether? Could this be a
> corrupt stat file that gets read and written right back on every loop
> without any sort of validation?

There's stats_reset_on_server_start (sp?), and I think 8.2 also has a
stats-reset function. But what might be more interesting is to pull the
file-reading function out of pgstat.c and dump out the stats file in
readable form to see what the heck is in there. (If you decide to try
resetting the stats, I'd suggest saving a copy of the stats file first
for possible analysis later.) I have the beginnings of such a program
laying about, which I'll attach --- note that it was last used for 8.1
and might require some tweaks for 8.2, and that you'd need to flesh it
out a lot if you want details about individual entries instead of just
a count.

regards, tom lane