Re: 8.0.3 pg_autovacuum doesn't clear out stats table? - Mailing list pgsql-admin

From Jim C. Nasby
Subject Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
Date
Msg-id 20060225230128.GO82012@pervasive.com
Whole thread Raw
In response to Re: 8.0.3 pg_autovacuum doesn't clear out stats table?  (Robin Iddon <robin@edesix.com>)
List pgsql-admin
On Sat, Feb 25, 2006 at 08:15:15AM +0000, Robin Iddon wrote:
> From README.pg_autovacuum (and I think 22.1.4 of the 8.1.x document
> says the same thing):
>
> - If the number of (deletes + updates) > VacuumThreshold, then a
>  vacuum analyze is performed.
>
> VacuumThreshold is equal to:
>    vacuum_base_value + (vacuum_scaling_factor * "number of tuples in
> the table")

By "number of tuples in the table", I'm pretty sure they mean visible
tuples; ie: the row count. In any case, I've seen pg_autovacuum work
with my own eyes, so as long as it stays running long enough there's no
reason it shouldn't end up vacuuming pg_statistics.

> In general deletes is 0 for the pg_statistic table so for the default
> values we're looking at
>
> n_tup_upd >= 1000 + (2.0 * reltuples)
>
> to trigger a vacuum.
>
> We know for each increment of n_tup_upd, reltuples will receive the same
> increment.  Thus if the above expression isn't true for any non-zero
> starting values of n_tup_upd and reltuples, it won't ever be true:
>
> If:
> n_tup_upd < 1000 + (2.0 * reltuples)
> Then:
> n_tup_upd+X < 1000 + (2.0 * (reltuples+X))
>
> I see that in 8.1.x this has been resolved by defaulting the scale to
> 0.4.  Rightly or wrongly I have set my scale to 0.3.

You should probably drop the threshold to something closer to 200, too.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-admin by date:

Previous
From: Robin Iddon
Date:
Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
Next
From: "sandhya"
Date:
Subject: Reg: Multiplequeries pls help me