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

Cristian Gafton <gafton@rpath.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

/*
 * dumpstat --- simple standalone program to read and analyze a PG stats
 * file.  Based on pgstat_read_statsfile() from 8.1 sources.
 *
 * Currently works with either 8.0 or 8.1 formats depending on which
 * headers it is compiled against.
 */
#include "postgres.h"

#include "pgstat.h"


int
main(int argc, char **argv)
{
    PgStat_StatDBEntry dbbuf;
    PgStat_StatTabEntry tabbuf;
    PgStat_StatBeEntry beentry;
    FILE       *fpin;
    int32        format_id;
    int            maxbackends = 0;
    int            havebackends = 0;
    int            havedbs = 0;
    int            havetabs = 0;

    /*
     * Try to open the status file. If it doesn't exist, the backends simply
     * return zero for anything and the collector simply starts from scratch
     * with empty counters.
     */
    if ((fpin = fopen(argv[1], "rb")) == NULL)
    {
        perror(argv[1]);
        return 1;
    }

    /*
     * Verify it's of the expected format.
     */
#ifdef PGSTAT_FILE_FORMAT_ID
    if (fread(&format_id, 1, sizeof(format_id), fpin) != sizeof(format_id)
        || format_id != PGSTAT_FILE_FORMAT_ID)
    {
        fprintf(stderr, "corrupted pgstat.stat file\n");
        goto done;
    }
#endif

    /*
     * We found an existing collector stats file. Read it and put all the
     * hashtable entries into place.
     */
    for (;;)
    {
        switch (fgetc(fpin))
        {
                /*
                 * 'D'    A PgStat_StatDBEntry struct describing a database
                 * follows. Subsequently, zero to many 'T' entries will follow
                 * until a 'd' is encountered.
                 */
            case 'D':
                if (fread(&dbbuf, 1, sizeof(dbbuf), fpin) != sizeof(dbbuf))
                {
                    fprintf(stderr, "corrupted pgstat.stat file\n");
                    goto done;
                }
                havedbs++;
                break;

                /*
                 * 'd'    End of this database.
                 */
            case 'd':
                break;

                /*
                 * 'T'    A PgStat_StatTabEntry follows.
                 */
            case 'T':
                if (fread(&tabbuf, 1, sizeof(tabbuf), fpin) != sizeof(tabbuf))
                {
                    fprintf(stderr, "corrupted pgstat.stat file\n");
                    goto done;
                }
                havetabs++;
                break;

                /*
                 * 'M'    The maximum number of backends to expect follows.
                 */
            case 'M':
                if (fread(&maxbackends, 1, sizeof(maxbackends), fpin) !=
                    sizeof(maxbackends))
                {
                    fprintf(stderr, "corrupted pgstat.stat file\n");
                    goto done;
                }
                if (maxbackends == 0)
                    goto done;
                break;

                /*
                 * 'B'    A PgStat_StatBeEntry follows.
                 */
            case 'B':
                /*
                 * Read it directly into the table.
                 */
                if (fread(&beentry, 1, sizeof(PgStat_StatBeEntry), fpin) !=
                    sizeof(PgStat_StatBeEntry))
                {
                    fprintf(stderr, "corrupted pgstat.stat file\n");
                    goto done;
                }

                havebackends++;
                break;

                /*
                 * 'E'    The EOF marker of a complete stats file.
                 */
            case 'E':
                goto done;

            default:
                fprintf(stderr, "corrupted pgstat.stat file at %ld\n",
                        ftell(fpin) - 1);
                goto done;
        }
    }

done:
    fclose(fpin);

    printf("found %d backends of %d (%ld bytes)\n", havebackends, maxbackends,
           havebackends * (long) sizeof(PgStat_StatBeEntry));
    printf("%d databases (%ld bytes)\n", havedbs,
           havedbs * (long) sizeof(PgStat_StatDBEntry));
    printf("%d tables (%ld bytes)\n", havetabs,
           havetabs * (long) sizeof(PgStat_StatTabEntry));

    return 0;
}