Re: autovacuum problems

On Mon, 2004-08-02 at 18:56, Steve Crawford wrote:
> I have several small (~0-50 record) working tables that have high
> insert/update/delete activity. When I first set up pg_autovacuum they
> were being vacuumed every ~1-4 hours during the day and less often at
> night. Most of the time the tuple count was reasonable.
>
> Now these tables haven't been vacuumed for a couple of days although
> the activity on them hasn't changed.
>
> What has changed is that pg_autovacuum reports that the tuple count is
> (actually, was at last vacuum) over 12,000 where it is actually more
> like 16 so the next vacuum won't happen for quite a while. 12,000+ is
> an unreasonable tuple count - it would represent several week's
> activity happening at once.
>
> This has happened to at least two working tables. I'm currently
> running it as:
> pg_autovacuum -d 2 >> autovacuum.log 2>&1 &

My first guess is that autovacuum is being misled by a bad reltuples
value in pg_class, I know that the sampling method used by ANALYZE to
estimate the tuple count has been improved in CVS, so it's possible that
pg_autovacuum performed an analyze which set reltuples too high.

> I have also encountered another oddity. If I run pg_autovacuum with
> all defaults then it appears to analyze every table in every database
> every pass.
>
> Any ideas? My searches have turned up nothing useful. Version is
> 7.4.1.

There were several bug fixes for pg_autovacuum in both 7.4.2 and 7.4.3
so I would suggest upgrading to 7.4.3 or least pulling pg_autovacuum.c
and .h from 7.4 CVS branch and compile it for yourself, which ever is
easier. If you still have this problem after upgrading to 7.4.3 please
let me know.

Matthew