Date: Wed, 24 Mar 2010 00:20:23 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Bhella Paramjeet-PFCW67 <PBhella@motorola.com>, pgsql-admin@postgresql.org
Subject: Re: tuning auto vacuum for highly active tables
Message-ID: <20100324032023.GE3240@alvh.no-ip.org>
References: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
	<dcc563d11003231441m6ec903d1k2370a38e52c2bba4@mail.gmail.com>
	<C84662912D280D4AB5C50100A100F0C50574C7DD@ct11exm61.ds.mot.com>
	<dcc563d11003231659q6d639343sb2e3b106ad5b164d@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <dcc563d11003231659q6d639343sb2e3b106ad5b164d@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-06-14)

Scott Marlowe escribió:
> On Tue, Mar 23, 2010 at 5:28 PM, Bhella Paramjeet-PFCW67
> <PBhella@motorola.com> wrote:
> > Hi Scott,
> >
> > Thanks for replying.
> > Can you explain what you mean by increase the number of threads or how I can increase the number of threads? I just have 2 tables that are very active. I am using postgres version 8.2.7 and 3510 storagetek array with 10 disks in raid 1+0.
> 
> Sure, if you psql into your db and do:
> 
> show autovac
> 
> and hit tab a couple times you'll see a list of all these
> configuration options.  The one we're looking for is
> autovacuum_max_workers.  Setting this to something higher will allow
> that many threads to run at once.  While 6 or 8 threads at 5 or 10
> milliseconds delay is ok on a bigger RAID array, it'll kill the perf
> of a machine with a pair of disks in a RAID-1.  As you drop the
> cost_delay, you can no longer run as many threads without starving
> your machine of IO.  It's a good idea to keep track of how many vacuum
> threads you're usually running and how long they run for
> (pg_stat_activity can shed some light there).

Hmm, keep in mind that having more workers means that each one of them
increments its cost_delay so that the total is roughly what you
configured.

Also, keep in mind that max_workers is a new setting in 8.3.  Since the
OP is running 8.2, he can only get one "worker".  Presumable he needs to
disable autovac for those two very active tables and setup a cron job to
process them in their own schedule.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.