DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=qqfMTwnpqNFGwi53myyzTe1X98D6LpQxt/78yXoV3WFNFOqeXDe1pRXpNs3ouBpVN6
	6Igro3G2h+U27TwnqOIE84XHlm7WHBygwgbvaj8kR+ehe35j2sHd8+ZUJp6mJ5d7qApc
	THrd7n3yhq9QVp3bE9Hmv5fIvDvrXZYKd0Cf0=
MIME-Version: 1.0
In-Reply-To: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
References: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
Date: Tue, 23 Mar 2010 15:41:48 -0600
Message-ID: <dcc563d11003231441m6ec903d1k2370a38e52c2bba4@mail.gmail.com>
Subject: Re: tuning auto vacuum for highly active tables
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Bhella Paramjeet-PFCW67 <PBhella@motorola.com>
Cc: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 23, 2010 at 2:54 PM, Bhella Paramjeet-PFCW67
<PBhella@motorola.com> wrote:
> Hi All,
>
>
>
> We have a postgres database in which couple of tables get bloated due to
> heavy inserts and deletes. Auto vacuum is running. My question is =A0how =
can I
> make auto vacuum more aggressive? I am thinking of enabling
> autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit parameters.
> Can anyone suggest how to calculate the appropriate values for these
> parameters and if there are any side effects of enabling these parameters=
.
> =A0Any help will be highly appreciated.

OK, autovacuum runs x number of threads, and these threads can have
their IO impact limited by cost delay and cost limit.

Your first choice is based a lot on your db needs.  If you have a lot
of large tables that all need to be vacuumed a lot, then you might
want to first increase the number of threads before making any of them
more aggressive.  Then you might want to make the vacuums more
aggressive by lower cost_delay down from 20 to 10 or 5 or so
milliseconds.

On our servers we run 6 threads with a cost_delay of 3 or 4
milliseconds, and autovacuum keeps up without getting in the way.  We
have a decent DAS array, so we can handle a lot of vacuum threads
running at once before they become an issue.

The smaller your disk set, the less you can throw vacuum at it and not
expect it to mess up the rest of the app.  It's all a trade off, but
if you don't have more than a disk or two to throw at your db don't
expect vacuum to keep up with really heavy activity without impacting
your system's performance.