From: Robin Iddon <robin@edesix.com>
To: pgsql-admin@postgresql.org
Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
Date: Thu, 23 Feb 2006 18:31:54 +0000
Message-ID: <43FDFF9A.90603@edesix.com> (raw)
In-Reply-To: <43FDF046.2050609@zeut.net>
References: <43FD7312.5080701@edesix.com>
<43FDF046.2050609@zeut.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: robin@edesix.com
Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
In-Reply-To: <43FDFF9A.90603@edesix.com>
References: <43FD7312.5080701@edesix.com> <43FDF046.2050609@zeut.net> <43FDFF9A.90603@edesix.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox