Re: autovacuum strategy / parameters

On Apr 22, 2:55 pm, robertmh(dot)(dot)(dot)(at)gmail(dot)com (Robert Haas) wrote:
> On Wed, Apr 21, 2010 at 11:06 AM, Rick <richard(dot)bran(dot)(dot)(dot)(at)ca(dot)com> wrote:
> > I have a DB with small and large tables that can go up to 15G.
> > For performance benefits, it appears that analyze has much less cost
> > than vacuum, but the same benefits?
>
> Err, no.  ANALYZE gathers statistics for the query planner; VACUUM
> clears out old, dead tuples so that space can be reused by the
> database system.
>
> > I can’t find any clear recommendations for frequencies and am
> > considering these parameters:
>
> > Autovacuum_vacuum_threshold = 50000
> > Autovacuum_analyze_threshold = 10000
> > Autovacuum_vacuum_scale_factor = 0.01
> > Autovacuum_analyze_scale_factor = 0.005
>
> > This appears it will result in table analyzes occurring around 10,000
> > to 85,000 dead tuples and vacuum occuring around 50,000 to 200,000,
> > depending on the table sizes.
>
> > Can anyone comment on whether this is the right strategy and targets
> > to use?
>
> I'm not that familiar with tuning these parameters but increasing the
> default thesholds by a thousand-fold doesn't seem like a good idea.
> Small tables will never get vacuumed or analyzed at all.
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance

The problem is with the autovacuum formula:

In a loop, autovacuum checks to see if number of dead tuples >
((number of live tuples * autovacuum_vacuum_scale_factor) +
autovacuum_vacuum_threshold), and if
so, it runs VACUUM. If not, it sleeps. It works the same way for
ANALYZE.

So, in a large table, the scale_factor is the dominant term. In a
small
table, the threshold is the dominant term. But both are taken into
account.

The default values are set for small tables; it is not being run for
large tables.
The question boils down to exactly what is the max number of dead
tuples that should be allowed to accumulate before running analyze?
Since vacuum just recovers space, that doesn't seem to be nearly as
critical for performance?

-Rick