autovacuum problems

[Steve Crawford]

I've been running pg_autovacuum for a week or so, now and while I like
it I'm having trouble trusting it.

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 &

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.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Matthew T. O'Connor]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match