Re: pg_autovacuum seems to be a neat freak and cleans way

On Tue, 2004-05-18 at 22:16, Bruce Momjian wrote:
> Brian Hirt wrote:
> > I've having a strange issue with pg_autovacuum. I have a table with
> > about 4 million rows in 20,000 pages. autovacuum likes to vacuum
> > and/or analyze it every 45 minutes or so, but it probably doesn't have
> > more that a few hundred rows changed every few hours. when i run
> > autovacuum with -d3 it says
> >
> > [2004-05-18 07:04:26 PM] table name:
> > basement_nightly."public"."search_words4"
> > [2004-05-18 07:04:26 PM] relid: 396238832; relisshared: 0
> > [2004-05-18 07:04:26 PM] reltuples: 4; relpages: 20013
> > [2004-05-18 07:04:26 PM] curr_analyze_count: 0; cur_delete_count:
> > 0
> > [2004-05-18 07:04:26 PM] ins_at_last_analyze: 0;
> > del_at_last_vacuum: 0
> > [2004-05-18 07:04:26 PM] insert_threshold: 504;
> > delete_threshold 1008
> >
> > reltuples: 4 seems wrong. I would expect a table with 4m rows and 20k
> > pages to have more than 4 tuples. I think this is why the insert
> > threshhold is all messed up -- which is why it gets analyzed way too
> > frequently.

reltuples: 4 is wrong. This is a known bug that has been reported
several times lately. It is already fixed in CVS. If you want you can
grab pg_autovacuum.c and .h from CVS and recompile, that will fix it.

> > this happens with other big tables too. the autovacuum is from 7.4.2,
> > some information is below.
>
> Oh, 7.4.2. I know we have some known bug and are waiting on a patch for
> it.
>
> Matthew, we need those fixes for pg_autovacuum soon.

As I said above this problem is already fixed, unfortunately it was
fixed just after the 7.4.2 release so it's been sitting in CVS for a
while.

The only outstanding bug I need to fix that I'm aware of is the temp
table issue. Everything else I'm planning on is development work for
7.5, mostly integrating it into the backend directly.

If anyone is aware of some other bug that I'm not thinking about please
let me know.

Thanks,

Matthew