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
