Re: 8.0.3 pg_autovacuum doesn't clear out stats table? - Mailing list pgsql-admin

Hi Matt,

Thanks for the reply.


Matthew T. O'Connor wrote:

> What do you mean by stats table entries?  Are you saying there is
> bloat in a specific table, or just the whole database?
>
The table in question is pg_statistic.  It's pages as reported by
pg_class.relpages grow without bound until I perform a vacuum full.

I do a "vacuum full" and it drops in size to next to nothing.

I have a standby database server configured in the same way, with the
same data set, but almost completely idle; I did the vacuum full on it
~1-2 months back when I started to look into this issue; since then it
has accumulated 215K relpages (which is, I think, around 1.7G!)  in the
pg_statistic table.

Chasing through pg_database.oid and pg_class.relfilenode I compute that
pg_statistic should be stored in $PGDATA/base/17230/16408.  Here is what
du reports for those files:

1049600 17230/16408
1049600 17230/16408.1
1049600 17230/16408.2
15912   17230/16408.3

Which is a total of just over 3G (this implies to me that the relpages
count in the pg_class table is out of date).

Looking at another (tiny) database that is definitely only used for
testing and I know hasn't been used in months it's pg_statistic has also
grown to 1.1G on disk.

> Second, I think you need to make autovacuum more aggressive.  I think
> most people find that the vacuum scaling factor should be more like
> 0.3 or so.  With the settings you have a 1000 tuple table would have
> to see 3000 invalidated tuples before it does a vacuum that is
> probably much more slack space than you want.
>
Valuable advice, but as it happens we delete next to nothing and I think
the disk space in use by the database is reasonable for the data stored.

> Third: Are you seeing in the autovacuum log that autovacuum is
> actually trying to vacuum tables?  Turn up the debugging to -d2 that
> should give you some more info as to why autovac is (or is not) doing
> what it's doing.
>
I will turn it on and take a look.  I am guessing it will tell me that
for whatever reason it isn't actually doing anything to the pg_catalog
tables ...

> Fourth: there will always be some slack space.  Under the best of
> circumstances, a database running with autovac will be somewhat bigger
> than the database right after a VACUUM FULL, probably not 2.5G, but some.
>
Understood - I am happy for there to be 50% slack - I just don't
understand why pg_statistic grows without bound and I am concerned that
the size of the stats table might be causing me some performance issues
(this is fear rather than fact).

Thanks again,

Robin