X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 1B6869FA15C
	for <pgsql-admin-postgresql.org@localhost.postgresql.org>;
	Tue, 16 May 2006 14:43:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 17488-09
	for <pgsql-admin-postgresql.org@localhost.postgresql.org>;
	Tue, 16 May 2006 14:43:20 -0300 (ADT)
X-Greylist: delayed 00:09:37.649724 by SQLgrey-
Received: from rwcrmhc14.comcast.net (rwcrmhc14.comcast.net [216.148.227.154])
	by postgresql.org (Postfix) with ESMTP id 2CCF39FA29C
	for <pgsql-admin@postgresql.org>; Tue, 16 May 2006 14:43:19 -0300 (ADT)
Received: from spectre (c-66-30-214-31.hsd1.ma.comcast.net[66.30.214.31])
	by comcast.net (rwcrmhc14) with SMTP
	id <20060516173338m1400km9lre>; Tue, 16 May 2006 17:33:39 +0000
From: "Ian Westmacott" <ianw@intellivid.com>
To: "Chris Hoover" <revoohc@gmail.com>,
	<pgsql-admin@postgresql.org>
Subject: Re: Autovacuum Question
Date: Tue, 16 May 2006 13:27:09 -0400
Message-ID: <002d01c6790d$f5da54a0$1fd61e42@hsd1.ma.comcast.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002E_01C678EC.6EC8B4A0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2377.0
In-Reply-To: <1d219a6f0605161027y190c9ba2y781b048ea5c647d9@mail.gmail.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Importance: Normal
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200605/222
X-Sequence-Number: 21754

This is a multi-part message in MIME format.

------=_NextPart_000_002E_01C678EC.6EC8B4A0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I just happened to be reading this page from the 8.1 docs:

"The autovacuum daemon, when enabled, runs every autovacuum_naptime seconds
and determines which database to process. Any database which is close to
transaction ID wraparound is immediately processed. In this case, autovacuum
issues a database-wide VACUUM call, or VACUUM FREEZE if it's a template
database, and then terminates. If no database fulfills this criterion, the
one that was least recently processed by autovacuum is chosen. In this case
each table in the selected database is checked, and individual VACUUM or
ANALYZE commands are issued as needed."


  -----Original Message-----
  From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Chris Hoover
  Sent: Tuesday, May 16, 2006 1:27 PM
  To: pgsql-admin@postgresql.org
  Subject: [ADMIN] Autovacuum Question


  PostgreSQL 8.1.3

  Question on autovacuum.

  autovacuum_naptime (integer)
   Specifies the delay between activity rounds for the autovacuum
subprocess. In each round the subprocess examines one database and issues
VACUUM and ANALYZE commands as needed for tables in that database. The delay
is measured in seconds, and the default is 60. This option can be set at
server start or in the postgresql.conf file.

  Does this mean that each run of autovacuum will vacuum/analyze all tables
that need work in the database that autovacuum is looking at?

  The reason I'm asking is that I have ~172 non system tables in each
database, so if autovacuum only does one table per run, I don't think it
will ever get caught up (since there are almost 35k tables in total).

  Also, with over 200 databases, does this mean that a database will only
get checked once about every 3.5 hours, or does autovacuum run on the
database that needs the vacuuming the most?

  Thanks,

  Chris

------=_NextPart_000_002E_01C678EC.6EC8B4A0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1528" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D270002517-16052006>I just happened to be reading this =
page from=20
the 8.1 docs:</SPAN></DIV>
<DIV><SPAN class=3D270002517-16052006></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D270002517-16052006></SPAN><SPAN=20
class=3D270002517-16052006>"</SPAN>The autovacuum daemon, when enabled, =
runs=20
every&nbsp;<SPAN class=3D270002517-16052006>autovacuum_naptime</SPAN> =
seconds and=20
determines which database to process. Any database which is close to =
transaction=20
ID wraparound is immediately processed. In this case, autovacuum issues =
a=20
database-wide <TT class=3DCOMMAND>VACUUM</TT> call, or <TT =
class=3DCOMMAND>VACUUM=20
FREEZE</TT> if it's a template database, and then terminates. If no =
database=20
fulfills this criterion, the one that was least recently processed by =
autovacuum=20
is chosen. In this case each table in the selected database is checked, =
and=20
individual <TT class=3DCOMMAND>VACUUM</TT> or <TT =
class=3DCOMMAND>ANALYZE</TT>=20
commands are issued as needed.<SPAN =
class=3D270002517-16052006>"</SPAN></DIV>
<DIV><SPAN class=3D270002517-16052006></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D270002517-16052006><FONT face=3DArial=20
color=3D#0000ff></FONT></SPAN>&nbsp;</DIV>
<BLOCKQUOTE>
  <DIV class=3DOutlookMessageHeader><FONT size=3D2>-----Original=20
  Message-----<BR><B>From:</B> pgsql-admin-owner@postgresql.org=20
  [mailto:pgsql-admin-owner@postgresql.org]<B>On Behalf Of</B> Chris=20
  Hoover<BR><B>Sent:</B> Tuesday, May 16, 2006 1:27 PM<BR><B>To:</B>=20
  pgsql-admin@postgresql.org<BR><B>Subject:</B> [ADMIN] Autovacuum=20
  Question<BR><BR></FONT></DIV>PostgreSQL 8.1.3<BR><BR>Question on=20
  autovacuum.<BR><BR>autovacuum_naptime (integer)<BR>&nbsp;Specifies the =
delay=20
  between activity rounds for the autovacuum subprocess. In each round =
the=20
  subprocess examines one database and issues VACUUM and ANALYZE =
commands as=20
  needed for tables in that database. The delay is measured in seconds, =
and the=20
  default is 60. This option can be set at server start or in the=20
  postgresql.conf file.<BR><BR>Does this mean that each run of =
autovacuum will=20
  vacuum/analyze all tables that need work in the database that =
autovacuum is=20
  looking at?&nbsp; <BR><BR>The reason I'm asking is that I have ~172 =
non system=20
  tables in each database, so if autovacuum only does one table per run, =
I don't=20
  think it will ever get caught up (since there are almost 35k tables in =
total).=20
  <BR><BR>Also, with over 200 databases, does this mean that a database =
will=20
  only get checked once about every 3.5 hours, or does autovacuum run on =
the=20
  database that needs the vacuuming the =
most?<BR><BR>Thanks,<BR><BR>Chris=20
<BR></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_002E_01C678EC.6EC8B4A0--