pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jnasby@pervasive.com>
To: Chris Hoover <revoohc@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Figuring autovacuum settings
Date: Wed, 17 May 2006 16:30:34 -0500
Message-ID: <20060517213033.GA42612@pervasive.com> (raw)
In-Reply-To: <1d219a6f0605170853j3d7c38a1n3ebb2305a12d915e@mail.gmail.com>
References: <1d219a6f0605170853j3d7c38a1n3ebb2305a12d915e@mail.gmail.com>

On Wed, May 17, 2006 at 11:53:44AM -0400, Chris Hoover wrote:
> Ok, I'm still working on the possibility of turning autovacuum on.
> 
> Question, I have a tables that are multi GB.  When I run the threshold
> calculations using "normal" values, it seems that my large important tables
> would almost never be vacuumed.  Here is an example, in my largest database,
> my most important table has 1,855,970 tuples in it.  However, this is a
> table with 3 years of data.  It has data loaded into it every day and is key
> to the majority of our queries.  In the past 16 hours or so I have had the
> following activity (via pg_stat_user_indexes) - 2960 tuples inserted and
> 40389 tuples updated, and no tuples deleted.
> 
> If I understand the threshold calculations, then I need to run with
> autovacuum_vacuum_scale_factor set to between .005 and .001.  Does this seem
> to be to small?

Well, you have to ask yourself if there's much reason to vacuum a table
when well less than 1% of it has been updated (meaning well less than 1%
will be dead tuples). If you're really paranoid about keeping dead space
to a minimum, and can tolerate running vacuum so frequently, then sure,
set the thresholds that low. Otherwise, I suspect you're being too
agressive here.

Typically, I cut the thresholds in half, so a vacuum would happen when
roughly 20% of your table is dead space. I'll also crontab vacuums on
small tables that need to stay small (ie: queue tables). Though in your
case, that would equate to up to 20GB of dead space, which is a bit
much. So you might want to run closer to 10%, or maybe just run smaller
numbers on your larger tables.

Of course you also have to take into consideration what your maintenance
windows are. It may make the most sense to disable autovacuum on your
big tables and just vacuum them every night or so.

In a nutshell, autovacuum is meant to cover 'normal' use cases; most
people arent running 100+ GB databases, so you're going to have to spend
more time administrating something like that.

> Here is the math:
> 
> vacuum threshold = vacuum base threshold + vacuum scale factor * number of
> tuples
> 
> vacuum_threshold = 200 + (.001*1,855,970)
> vacuum_threshold = 2,056
> 
> vacuum_threshold = 200 + (.005*1,855,970)
> vacuum_threshold = 9,480
> 
> It would seem with the numbers given that I should set it somewhere in this
> range.  Do you agree?
> 
> If you don't agree, where would you recomment setting them.
> 
> This is my largest database and is over 110GB.  It has tables with
> 305,702,000 reltuples down to about 500,000 reltuples (for important
> tables).
> 
> I'm just not sure how to properly set the numbers.
> 
> My servers have databases in the folowing ranges:
> Server 1 66 GB to 100 MB
> Server 2 117 GB to 1.70 GB.
> Server 3 95 GB to 4 GB
> Server 4 83 GB to 1.2 GB
> 
> While the sizes differ, the ratios should be the same since they are
> identical databases.
> 
> Any suggestions about recommendations for the autovacuum values?  Also,
> should I set the autovacuum just at the cluster level, or should I also set
> some of the larger tables with table specific values.
> 
> Thanks,
> 
> Chris

-- 
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



view thread (2+ messages)

Message-ID: <20060517213033.GA42612@pervasive.com>
Permalink:  ../20060517213033.GA42612@pervasive.com/
Also on:    postgresql.org/message-id/20060517213033.GA42612@pervasive.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: jnasby@pervasive.com, revoohc@gmail.com
  Subject: Re: Figuring autovacuum settings
  In-Reply-To: <20060517213033.GA42612@pervasive.com>
  References: <1d219a6f0605170853j3d7c38a1n3ebb2305a12d915e@mail.gmail.com> <20060517213033.GA42612@pervasive.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox