From PBhella@Motorola.com Tue Mar  23 21:01:58 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 74F59632A9B;
	Tue, 23 Mar 2010 18:01:58 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024)
	with ESMTP id 91358-01; Tue, 23 Mar 2010 21:01:47 +0000 (UTC)
X-Greylist: delayed 00:06:39.010378 by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail55.messagelabs.com (mail55.messagelabs.com
 [216.82.241.163])
	by mail.postgresql.org (Postfix) with ESMTP id B53C2632844;
	Tue, 23 Mar 2010 18:01:47 -0300 (ADT)
X-VirusChecked: Checked
X-Env-Sender: PBhella@Motorola.com
X-Msg-Ref: server-12.tower-55.messagelabs.com!1269377705!98031954!1
X-StarScan-Version: 6.2.4; banners=-,-,-
X-Originating-IP: [129.188.136.8]
Received: (qmail 20488 invoked from network); 23 Mar 2010 20:55:06 -0000
Received: from motgate8.mot.com (HELO motgate8.mot.com) (129.188.136.8)
	by server-12.tower-55.messagelabs.com with DHE-RSA-AES256-SHA encrypted
	SMTP; 23 Mar 2010 20:55:06 -0000
Received: from il06exr03.mot.com (il06exr03.mot.com [129.188.137.133])
	by motgate8.mot.com (8.14.3/8.14.3) with ESMTP id o2NKt5LW007503;
	Tue, 23 Mar 2010 13:55:05 -0700 (MST)
Received: from il06vts03.mot.com (il06vts03.mot.com [129.188.137.143])
	by il06exr03.mot.com (8.13.1/Vontu) with SMTP id o2NKt5Nt028433;
	Tue, 23 Mar 2010 15:55:05 -0500 (CDT)
Received: from ct11exm61.ds.mot.com (CT11EXM61.am.mot.com [10.177.8.45])
	by il06exr03.mot.com (8.13.1/8.13.0) with ESMTP id o2NKt4ff028428;
	Tue, 23 Mar 2010 15:55:04 -0500 (CDT)
X-MimeOLE: Produced By Microsoft Exchange V6.5
x-cr-hashedpuzzle: AS4E B2SF C2Pn DaWW FjJr GlUN Jgtx JnDK J0is LccA Mx3g OKin
	Oehf PlNY Q8Ka RpwT; 2;
	cABnAHMAcQBsAC0AYQBkAG0AaQBuAEAAcABvAHMAdABnAHIAZQBzAHEAbAAuAG8AcgBnADsAcABnAHMAcQBsAC0AcABlAHIAZgBvAHIAbQBhAG4AYwBlAEAAcABvAHMAdABnAHIAZQBzAHEAbAAuAG8AcgBnAA==;
	Sosha1_v1; 7; {6C40AF48-734C-4BE5-AEF4-1FC341F5624B};
	cABiAGgAZQBsAGwAYQBAAG0AbwB0AG8AcgBvAGwAYQAuAGMAbwBtAA==;
	Tue, 23 Mar 2010 20:54:37 GMT;
	dAB1AG4AaQBuAGcAIABhAHUAdABvACAAdgBhAGMAdQB1AG0AIABmAG8AcgAgAGgAaQBnAGgAbAB5ACAAYQBjAHQAaQB2AGUAIAB0AGEAYgBsAGUAcwA=
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01CACACB.10923EDC"
x-cr-puzzleid: {6C40AF48-734C-4BE5-AEF4-1FC341F5624B}
Content-class: urn:content-classes:message
Subject: tuning auto vacuum for highly active tables
Date: Tue, 23 Mar 2010 16:54:37 -0400
Message-ID: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: tuning auto vacuum for highly active tables
thread-index: AcrKywycNhHtceGqTKi6R0ltJKD1qA==
From: "Bhella Paramjeet-PFCW67" <PBhella@Motorola.com>
To: <pgsql-admin@postgresql.org>, <pgsql-performance@postgresql.org>
X-CFilter-Loop: Reflected
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5
	tests=BAYES_20=-0.74, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/173
X-Sequence-Number: 33972

This is a multi-part message in MIME format.

------_=_NextPart_001_01CACACB.10923EDC
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi All,

=20

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  how
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.    Any help will be highly appreciated.

=20

Thanks

Paramjeet Kaur


------_=_NextPart_001_01CACACB.10923EDC
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>Hi All,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>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 &nbsp;how 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.&nbsp;&nbsp; &nbsp;Any help will be highly
appreciated.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Thanks<o:p></o:p></p>

<p class=3DMsoNormal>Paramjeet Kaur<o:p></o:p></p>

</div>

</body>

</html>

------_=_NextPart_001_01CACACB.10923EDC--



From speckner@activedg.com Tue Mar  23 21:47:32 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 32736632B03
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 18:47:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 50703-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 21:47:16 +0000 (UTC)
X-Greylist: delayed 00:11:32.949733 by SQLgrey-1.7.6
Received: from mail1.activedatatech.net (mail1.activedatatech.net
	[216.154.205.166])
	by mail.postgresql.org (Postfix) with ESMTP id 9E77363271B
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 18:47:21 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by mail1.activedatatech.net (Postfix) with ESMTP id BAB9616E30F
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 17:35:46 -0400 (EDT)
Received: from mail1.activedatatech.net ([192.168.3.224])
	by localhost (mail1.activedatatech.net [127.0.0.1]) (amavisd-new,
	port 10024) with ESMTP id 02743-04 for <pgsql-admin@postgresql.org>;
	Tue, 23 Mar 2010 17:35:46 -0400 (EDT)
Received: from [192.168.2.130] (dfb.livedatagroup.com [64.139.144.2])
	by mail1.activedatatech.net (Postfix) with ESMTP id BA6E516E30C
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 17:35:45 -0400 (EDT)
Message-ID: <4BA93431.7050509@activedg.com>
Date: Tue, 23 Mar 2010 17:35:45 -0400
From: Szu-Ching Peckner <speckner@activedg.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20081227)
MIME-Version: 1.0
CC: pgsql-admin@postgresql.org
Subject: Re: tuning auto vacuum for highly active tables
References: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
In-Reply-To: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: amavisd-new at activedatatech.net
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.307 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, MISSING_HEADERS=1.292
X-Spam-Level: 
X-Archive-Number: 201003/175
X-Sequence-Number: 33974

we ran into the same problem, had big table, played with vacuum cost and
delay, but can't shrink too much because of heavy insert and delete.
we ended up with using slony for upgrade, also have data copy from fresh
because of inital replication to shrink our large table, with minimum
controlled downtime.

Bhella Paramjeet-PFCW67 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
>  how 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.    Any help will be highly appreciated.
>
>  
>
> Thanks
>
> Paramjeet Kaur
>




From speckner@activedg.com Tue Mar  23 21:57:32 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 8B0AA632B03
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 18:57:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 98656-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 21:57:21 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail1.activedatatech.net (mail1.activedatatech.net
	[216.154.205.166])
	by mail.postgresql.org (Postfix) with ESMTP id 324B0632817
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 18:57:21 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by mail1.activedatatech.net (Postfix) with ESMTP id 0AF8916D396;
	Tue, 23 Mar 2010 17:37:58 -0400 (EDT)
Received: from mail1.activedatatech.net ([192.168.3.224])
	by localhost (mail1.activedatatech.net [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id 02807-09; Tue, 23 Mar 2010 17:37:57 -0400 (EDT)
Received: from [192.168.2.130] (dfb.livedatagroup.com [64.139.144.2])
	by mail1.activedatatech.net (Postfix) with ESMTP id 2B38916B978;
	Tue, 23 Mar 2010 17:37:57 -0400 (EDT)
Message-ID: <4BA934B4.8090208@activedg.com>
Date: Tue, 23 Mar 2010 17:37:56 -0400
From: Szu-Ching Peckner <speckner@activedg.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20081227)
MIME-Version: 1.0
To: Bhella Paramjeet-PFCW67 <PBhella@Motorola.com>
CC: pgsql-admin@postgresql.org
Subject: Re: tuning auto vacuum for highly active tables
References: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
In-Reply-To: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: amavisd-new at activedatatech.net
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/176
X-Sequence-Number: 33975

we ran into the same problem, had big table, played with vacuum cost and
delay, but can't shrink too much because of heavy insert and delete.
we ended up with using slony for upgrade, also have data copy from fresh
because of inital replication to shrink our large table, with minimum
controlled downtime.



Bhella Paramjeet-PFCW67 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
>  how 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.    Any help will be highly appreciated.
>
>  
>
> Thanks
>
> Paramjeet Kaur
>




From scott.marlowe@gmail.com Tue Mar  23 21:42:01 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id BEF23632817;
	Tue, 23 Mar 2010 18:42:01 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024)
	with ESMTP id 96559-06; Tue, 23 Mar 2010 21:41:51 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f184.google.com (mail-yw0-f184.google.com
	[209.85.211.184])
	by mail.postgresql.org (Postfix) with ESMTP id 1C3BE63271B;
	Tue, 23 Mar 2010 18:41:50 -0300 (ADT)
Received: by ywh14 with SMTP id 14so1026093ywh.15
	for <multiple recipients>; Tue, 23 Mar 2010 14:41:49 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=V6JlsbaZ/71UZnO/u9mJf2PZIvY/liA1AKBFwgfBnuc=;
	b=pYlaAVlthZkAzxJK898hQkUFYFNw2KMJXiXRQ7g7ae728zZog5eK1lD/y94c/oyKNf
	3NL+O9fIpr0K/9tF6X7OTfGPbnA91nvbVX1/jFNwRJBfVrl4Gp0o5uu1jNqhm/rC4iXk
	XwfF6tMdAzZIAdAF9oPJUZANt1SxN0+j51RlU=
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
Received: by 10.150.170.11 with SMTP id s11mr8422670ybe.241.1269380508761;
	Tue, 23 Mar 2010 14:41:48 -0700 (PDT)
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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/174
X-Sequence-Number: 33973

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.



From PBhella@Motorola.com Tue Mar  23 23:28:52 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id BB809632824
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 20:28:52 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 62905-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 23:28:35 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail119.messagelabs.com (mail119.messagelabs.com
	[216.82.241.195])
	by mail.postgresql.org (Postfix) with ESMTP id D91AF632DF2
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 20:28:41 -0300 (ADT)
X-VirusChecked: Checked
X-Env-Sender: PBhella@Motorola.com
X-Msg-Ref: server-2.tower-119.messagelabs.com!1269386919!38995201!1
X-StarScan-Version: 6.2.4; banners=-,-,-
X-Originating-IP: [136.182.1.13]
Received: (qmail 30249 invoked from network); 23 Mar 2010 23:28:39 -0000
Received: from motgate3.mot.com (HELO motgate3.mot.com) (136.182.1.13)
	by server-2.tower-119.messagelabs.com with DHE-RSA-AES256-SHA encrypted
	SMTP; 23 Mar 2010 23:28:39 -0000
Received: from il27exr04.cig.mot.com ([10.17.196.73])
	by motgate3.mot.com (8.14.3/8.14.3) with ESMTP id o2NNSTtD026504
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 16:28:39 -0700 (MST)
Received: from az10vts04.mot.com (il27vts04.cig.mot.com [10.17.196.88])
	by il27exr04.cig.mot.com (8.13.1/Vontu) with SMTP id o2NNSS1i012987
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 18:28:28 -0500 (CDT)
Received: from ct11exm61.ds.mot.com (CT11EXM61.am.mot.com [10.177.8.45])
	by il27exr04.cig.mot.com (8.13.1/8.13.0) with ESMTP id o2NNSSQD012984
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 18:28:28 -0500 (CDT)
X-MimeOLE: Produced By Microsoft Exchange V6.5
x-cr-hashedpuzzle: +Es= AyVz A9qL BG09 CUqE Ep1Q GEkp Ggj8 ISMt KIK+ LqC/ OyPC
	PJpI TKN7 UgMB VB3r; 2;
	cABnAHMAcQBsAC0AYQBkAG0AaQBuAEAAcABvAHMAdABnAHIAZQBzAHEAbAAuAG8AcgBnADsAcwBjAG8AdAB0AC4AbQBhAHIAbABvAHcAZQBAAGcAbQBhAGkAbAAuAGMAbwBtAA==;
	Sosha1_v1; 7; {0C83E063-0E77-498F-8F7C-87C9B4783312};
	cABiAGgAZQBsAGwAYQBAAG0AbwB0AG8AcgBvAGwAYQAuAGMAbwBtAA==;
	Tue, 23 Mar 2010 23:28:03 GMT;
	UgBFADoAIABbAEEARABNAEkATgBdACAAdAB1AG4AaQBuAGcAIABhAHUAdABvACAAdgBhAGMAdQB1AG0AIABmAG8AcgAgAGgAaQBnAGgAbAB5ACAAYQBjAHQAaQB2AGUAIAB0AGEAYgBsAGUAcwA=
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
x-cr-puzzleid: {0C83E063-0E77-498F-8F7C-87C9B4783312}
Content-class: urn:content-classes:message
Subject: Re: tuning auto vacuum for highly active tables
Date: Tue, 23 Mar 2010 19:28:03 -0400
Message-ID: <C84662912D280D4AB5C50100A100F0C50574C7DD@ct11exm61.ds.mot.com>
In-Reply-To: <dcc563d11003231441m6ec903d1k2370a38e52c2bba4@mail.gmail.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [ADMIN] tuning auto vacuum for highly active tables
thread-index: AcrK0aal/LncW4nxTIWY54sqHEmi1AADY4HQ
References: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
	<dcc563d11003231441m6ec903d1k2370a38e52c2bba4@mail.gmail.com>
From: "Bhella Paramjeet-PFCW67" <PBhella@Motorola.com>
To: "Scott Marlowe" <scott.marlowe@gmail.com>
Cc: <pgsql-admin@postgresql.org>
X-CFilter-Loop: Reflected
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/177
X-Sequence-Number: 33976

Hi Scott,

Thanks for replying.=20
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.=20

Thanks
Paramjeet Kaur

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]=20
Sent: Tuesday, March 23, 2010 2:42 PM
To: Bhella Paramjeet-PFCW67
Cc: pgsql-admin@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [ADMIN] tuning auto vacuum for highly active tables

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.



From scott.marlowe@gmail.com Tue Mar  23 23:59:25 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3C3A4632919
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 20:59:25 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 20461-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 23 Mar 2010 23:59:14 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-gw0-f46.google.com (mail-gw0-f46.google.com
 [74.125.83.46])
	by mail.postgresql.org (Postfix) with ESMTP id 647AB632476
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 20:59:12 -0300 (ADT)
Received: by gwaa18 with SMTP id a18so1723462gwa.19
	for <pgsql-admin@postgresql.org>; Tue, 23 Mar 2010 16:59:10 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=bcWcutpCxIhrDTKTWAGzkcbJzXVlFO389uh4BCIM8qg=;
	b=I3w1xi/wsrJpmjJeJnqMDt+pwWWctOCtgmg2GEULydaMmMyWKrfmi6M0uCp0n9VrEE
	/Vqj/pobZWvUR9ayKIdDqvhqpWDzEBDeS1YhRjv11n0DaYV4vQmeQT6W8U6q6bfVZ7W8
	Lxi3RU0uk1wS+pndcqP/CbT6Pv8biOHsXJ9/o=
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=aJxOd63a+MGkpDXsSrnWbsmK7WGRlpxtRkoVbOEn6K0X1EsLMIcC/EYfJfoFPd5zkU
	rAlZybI5K4y+6NJmyw8MSEokkHv6l/MNs2hNgRGE+bj2w8b2Zyhf44RgVPAyi/Nx+oDz
	v1U0vsi6VJinQMsSrY1hoL6/c8Oq/R1M6WpIo=
MIME-Version: 1.0
Received: by 10.151.87.3 with SMTP id p3mr1596184ybl.333.1269388750731; Tue,
	23 Mar 2010 16:59:10 -0700 (PDT)
In-Reply-To: <C84662912D280D4AB5C50100A100F0C50574C7DD@ct11exm61.ds.mot.com>
References: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
	<dcc563d11003231441m6ec903d1k2370a38e52c2bba4@mail.gmail.com>
	<C84662912D280D4AB5C50100A100F0C50574C7DD@ct11exm61.ds.mot.com>
Date: Tue, 23 Mar 2010 17:59:10 -0600
Message-ID: <dcc563d11003231659q6d639343sb2e3b106ad5b164d@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
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/178
X-Sequence-Number: 33977

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 acti=
ve. I am using postgres version 8.2.7 and 3510 storagetek array with 10 dis=
ks 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).

What you're trying to do is get enough threads running so any large
tables that take a long time (half hour or more maybe) to vacuum don't
get in the way of all the other tables getting vacuumed too.  If
you've got 2 really big tables and the rest aren't so big, then three
threads is likely plenty.  If you've got 40 really big tables that
take a long time to get vacuumed, then you might need more than just 3
threads.

Use iostat -x 10 /dev/sdx

to monitor the db arrays to see how much IO you're utilizing with x
autovac threads running, then increase it and keep an eye on it, all
while running under a fairly steady production load.  If going from 3
to 4 threads makes the average utilization go up by 5% then you have
an idea how much each thread is costing you.

Changing cost_delay OR cost_limit will directly affect how much IO is
getting thrown to autovacuum.  Lower values of cost_delay and higher
values of cost_limit will cost you more in terms of autovacuum daemon
using up IO.  You really don't want it using up too much of your IO.
I shoot for something in the 10% range max,  maybe a bit more.

Then you want to keep an eye on table bloat to see if vacuum is
keeping up.  If it's falling behind vacuum verbose as superuser will
give you an idea.


>
> Thanks
> Paramjeet Kaur
>
> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Tuesday, March 23, 2010 2:42 PM
> To: Bhella Paramjeet-PFCW67
> Cc: pgsql-admin@postgresql.org; pgsql-performance@postgresql.org
> Subject: Re: [ADMIN] tuning auto vacuum for highly active tables
>
> 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 parameter=
s.
>> =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. =A0If 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. =A0Then 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. =A0We
> 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. =A0It'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.
>



--=20
When fascism comes to America, it will be intolerance sold as diversity.



From alvherre@commandprompt.com Wed Mar  24 03:20:38 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id A90C7632354
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Mar 2010 00:20:38 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 58946-07 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Mar 2010 03:20:27 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from spinlock.commandprompt.com (host-215.commandprompt.net
	[207.173.203.215])
	by mail.postgresql.org (Postfix) with ESMTP id A807A63236B
	for <pgsql-admin@postgresql.org>; Wed, 24 Mar 2010 00:20:26 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by spinlock.commandprompt.com (Postfix) with ESMTP id AF3BD348037;
	Tue, 23 Mar 2010 20:20:25 -0700 (PDT)
Received: from spinlock.commandprompt.com ([127.0.0.1])
	by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id GROn6ybOgvxl; Tue, 23 Mar 2010 20:20:25 -0700 (PDT)
Received: from perhan.alvh.no-ip.org (190-95-29-43.bk17-dsl.surnet.cl
	[190.95.29.43])
	by spinlock.commandprompt.com (Postfix) with ESMTPSA id 4592B348036;
	Tue, 23 Mar 2010 20:20:25 -0700 (PDT)
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id 246C86E4AA; Wed, 24 Mar 2010 00:20:23 -0300 (CLST)
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)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/179
X-Sequence-Number: 33978

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.



From alvherre@commandprompt.com Wed Mar  24 03:23:11 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E6D5E6338E4
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Mar 2010 00:23:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 59388-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Mar 2010 03:23:01 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from spinlock.commandprompt.com (host-215.commandprompt.net
	[207.173.203.215])
	by mail.postgresql.org (Postfix) with ESMTP id F148C63379A
	for <pgsql-admin@postgresql.org>; Wed, 24 Mar 2010 00:23:00 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by spinlock.commandprompt.com (Postfix) with ESMTP id 245DB348037;
	Tue, 23 Mar 2010 20:23:00 -0700 (PDT)
Received: from spinlock.commandprompt.com ([127.0.0.1])
	by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id 61PBUQLGuR+U; Tue, 23 Mar 2010 20:23:00 -0700 (PDT)
Received: from perhan.alvh.no-ip.org (190-95-29-43.bk17-dsl.surnet.cl
	[190.95.29.43])
	by spinlock.commandprompt.com (Postfix) with ESMTPSA id B1BA2348036;
	Tue, 23 Mar 2010 20:22:59 -0700 (PDT)
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id A21B96E4AA; Wed, 24 Mar 2010 00:22:57 -0300 (CLST)
Date: Wed, 24 Mar 2010 00:22:57 -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: <20100324032257.GF3240@alvh.no-ip.org>
References: <C84662912D280D4AB5C50100A100F0C50574C753@ct11exm61.ds.mot.com>
	<dcc563d11003231441m6ec903d1k2370a38e52c2bba4@mail.gmail.com>
	<C84662912D280D4AB5C50100A100F0C50574C7DD@ct11exm61.ds.mot.com>
	<dcc563d11003231659q6d639343sb2e3b106ad5b164d@mail.gmail.com>
	<20100324032023.GE3240@alvh.no-ip.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20100324032023.GE3240@alvh.no-ip.org>
User-Agent: Mutt/1.5.20 (2009-06-14)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/180
X-Sequence-Number: 33979

Alvaro Herrera escribió:

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

Err, sorry, "she", not "he".

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