Re: tuning auto vacuum for highly active tables

Scott Marlowe escribió:
> On Tue, Mar 23, 2010 at 5:28 PM, Bhella Paramjeet-PFCW67
> <PBhella(at)motorola(dot)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.