Skip site navigation (1) Skip section navigation (2)

Re: autovacuum strategy / parameters

From: Rick <richard(dot)branton(at)ca(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: autovacuum strategy / parameters
Date: 2010-04-22 20:42:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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:

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

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

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?


In response to


pgsql-performance by date

Next:From: Eliot GableDate: 2010-04-22 20:57:08
Subject: Re: Replacing Cursors with Temporary Tables
Previous:From: Robert HaasDate: 2010-04-22 18:55:18
Subject: Re: autovacuum strategy / parameters

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group