Hackers,
I have prepared some docs for autovacuum. I attach the patch. Comments
on grammar, style etc. are welcome.
I will not apply it yet because it conflicts with Neil's runtime-config
restructuring, so I'll wait until he applies his patch.
--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
www.google.com: interfaz de línea de comando para la web.
Index: catalogs.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.113
diff -c -r2.113 catalogs.sgml
*** catalogs.sgml 8 Sep 2005 20:07:41 -0000 2.113
--- catalogs.sgml 12 Sep 2005 13:56:35 -0000
***************
*** 1137,1145 ****
<primary>pg_autovacuum</primary>
</indexterm>
<para>
The catalog <structname>pg_autovacuum</structname> stores optional
! per-relation configuration parameters for <quote>autovacuum</>.
If there is an entry here for a particular relation, the given
parameters will be used for autovacuuming that table. If no entry
is present, the system-wide defaults will be used.
--- 1137,1150 ----
<primary>pg_autovacuum</primary>
</indexterm>
+ <indexterm zone="catalog-pg-autovacuum">
+ <primary>autovacuum</primary>
+ <secondary>table-specific configuration</secondary>
+ </indexterm>
+
<para>
The catalog <structname>pg_autovacuum</structname> stores optional
! per-relation configuration parameters for <xref linkend="autovacuum"
endterm="autovacuum-title">.
If there is an entry here for a particular relation, the given
parameters will be used for autovacuuming that table. If no entry
is present, the system-wide defaults will be used.
Index: maintenance.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/maintenance.sgml,v
retrieving revision 1.45
diff -c -r1.45 maintenance.sgml
*** maintenance.sgml 22 Jun 2005 21:14:28 -0000 1.45
--- maintenance.sgml 12 Sep 2005 14:10:17 -0000
***************
*** 99,104 ****
--- 99,110 ----
<xref linkend="runtime-config-resource-vacuum-cost">.
</para>
+ <para>
+ An automated mechanism for performing the necessary <command>VACUUM</>
+ operations has been added in <productname>PostgreSQL</productname> 8.1.
+ See <xref linkend="autovacuum" endterm="autovacuum-title">.
+ </para>
+
<sect2 id="vacuum-for-space-recovery">
<title>Recovering disk space</title>
***************
*** 177,189 ****
the program <filename>vacuumdb</> may be helpful.
</para>
- <tip>
- <para>
- The <filename>contrib/pg_autovacuum</> program can be useful for
- automating high-frequency vacuuming operations.
- </para>
- </tip>
-
<para>
<command>VACUUM FULL</> is recommended for cases where you know
you have deleted the majority of rows in a table, so that the
--- 183,188 ----
***************
*** 456,461 ****
--- 455,551 ----
</para>
</warning>
</sect2>
+
+ <sect2 id="autovacuum">
+ <title id="autovacuum-title">The Auto-Vacuum Daemon</title>
+
+ <indexterm>
+ <primary>autovacuum</primary>
+ <secondary>general information</secondary>
+ </indexterm>
+ <para>
+ Beginning in <productname>PostgreSQL </productname> 8.1, there is a
+ separate optional server process called the <firstterm>autovacuum
+ daemon</firstterm>, whose purpose is to automate the issuance of
+ <command>VACUUM</command> and <command>ANALYZE </command> commands.
+ When enabled, the autovacuum daemon runs periodically and checks for
+ tables that have had a large number of inserted, updated or deleted
+ tuples. These checks use the row-level statistics collection facility;
+ therefore, the autovacuum daemon cannot be used unless <xref
+ linkend="guc-stats-start-collector"> and <xref
+ linkend="guc-stats-row-level"> are set <literal>true</literal>. Also,
it's
+ important to allow a slot for the autovacuum process when choosing the
+ value of <xref linkend="guc-superuser-reserved-connections">.
+ </para>
+
+ <para>
+ The autovacuum daemon, when enabled, runs every <xref
+ linkend="guc-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 <command>VACUUM</command> call, or <command>VACUUM
+ FREEZE</command> if it's a template database, and then terminates. If
+ no database fulfills this criterion, the one that was least recently
+ processed by autovacuum itself is chosen. In this mode, each table in
+ the database is checked for new and obsolete tuples, according to the
+ applicable autovacuum parameters. If a <link
linkend="catalog-pg-autovacuum">
+ <structname>pg_autovacuum</structname></link> tuple is found for this
+ table, these settings are applied; otherwise the global values in
+ <filename>postgresql.conf</filename> are used. See <xref
linkend="runtime-config-autovacuum">
+ for more details on the global settings. Note that the autovacuum
+ daemon can be instructed to skip particular tables, by setting the
+ <structname>pg_autovacuum</>.<structfield>enabled</> field to
<literal>false</literal>.
+ </para>
+
+ <para>
+ For each table, two conditions are used to determine which operation to
+ apply. If the number of obsolete tuples since the last
+ <command>VACUUM</command> exceeds the <quote>vacuum threshold</quote>, the
+ table is vacuumed and analyzed. The vacuum threshold is defined as:
+ <programlisting>
+ vacuum threshold = vacuum base threshold + vacuum scale factor * number of
tuples
+ </programlisting>
+ where the vacuum base threshold is
+
<structname>pg_autovacuum</structname>.<structfield>vac_base_thresh</structfield>,
+ the vacuum scale factor is
+
<structname>pg_autovacuum</structname>.<structfield>vac_scale_factor</structfield>
+ and the number of tuples is
+ <structname>pg_class</structname>.<structfield>reltuples</structfield>.
+ The number of obsolete tuples is taken from the statistics
+ collector, which is a semi-accurate count updated by each
+ <command>UPDATE</command> and <command>DELETE</command> operation. (It
+ is only semi-accurate because some information may be lost under heavy
+ load.) For analyze, a similar condition is used: the threshold,
calculated
+ by an equivalent equation to that above, is compared to the number of
+ new tuples, that is, those created by the <command>INSERT</command> and
+ <command>COPY</command> commands.
+ </para>
+
+ <para>
+ Note that if any of the values in <structname>pg_autovacuum</structname>
+ is set to a negative number, or if a tuple is not present at all in
+ <structname>pg_autovacuum</structname> for any particular table, the
+ equivalent value from <filename>postgresql.conf</filename> is used.
+ </para>
+
+ <para>
+ Additionally to the base threshold values and scale factors, there are
+ two parameters that can be set for each table in
<structname>pg_autovacuum</structname>:
+ the vacuum cost delay
+
(<structname>pg_autovacuum</structname>.<structfield>vac_cost_delay</structfield>)
+ and the vacuum cost limit
+
(<structname>pg_autovacuum</structname>.<structfield>vac_cost_limit</structfield>).
+ They are used to set table-specific values for the
+ <xref linkend="runtime-config-resource-vacuum-cost"
endterm="runtime-config-resource-vacuum-cost-title">
+ feature. The above note about negative values also applies here, but
+ also note that if the <filename>postgresql.conf</filename> variables
+ <varname>autovacuum_vacuum_cost_limit</varname> and
+ <varname>autovacuum_vacuum_cost_delay</varname> are also set to negative
+ values, the <varname>vacuum_cost_limit</varname> and
+ <varname>vacuum_cost_delay</varname> values will be used instead.
+ </para>
+
+ </sect2>
</sect1>
Index: runtime.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.352
diff -c -r1.352 runtime.sgml
*** runtime.sgml 30 Aug 2005 15:48:28 -0000 1.352
--- runtime.sgml 12 Sep 2005 13:55:25 -0000
***************
*** 3344,3361 ****
<sect2 id="runtime-config-autovacuum">
<title>Automatic Vacuuming</title>
<para>
! Beginning in <productname>PostgreSQL</> 8.1, there is an optional server
! process called the <firstterm>autovacuum daemon</>, whose purpose is
! to automate the issuance of periodic <command>VACUUM</> and
! <command>ANALYZE</> commands. When enabled, the autovacuum daemon
! runs periodically and checks for tables that have had a large number
! of updated or deleted tuples. This check uses the row-level statistics
! collection facility; therefore, the autovacuum daemon cannot be used
! unless <xref linkend="guc-stats-start-collector"> and
! <xref linkend="guc-stats-row-level"> are set TRUE. Also, it's
! important to allow a slot for the autovacuum process when choosing
! the value of <xref linkend="guc-superuser-reserved-connections">.
</para>
<variablelist>
--- 3344,3358 ----
<sect2 id="runtime-config-autovacuum">
<title>Automatic Vacuuming</title>
+ <indexterm>
+ <primary>autovacuum</primary>
+ <secondary>global configuration parameters</secondary>
+ </indexterm>
+
<para>
! These settings control the default behavior for the
<firstterm>autovacuum
! daemon</firstterm>. Please refer to <xref linkend="autovacuum"> for
! more information.
</para>
<variablelist>
***************
*** 3470,3477 ****
<command>VACUUM</> operations. If -1 is specified (which is the
default), the regular
<xref linkend="guc-vacuum-cost-delay"> value will be used.
- This setting can be overridden for individual tables by entries in
- <structname>pg_autovacuum</>.
</para>
</listitem>
</varlistentry>
--- 3467,3472 ----
***************
*** 3487,3494 ****
<command>VACUUM</> operations. If -1 is specified (which is the
default), the regular
<xref linkend="guc-vacuum-cost-limit"> value will be used.
- This setting can be overridden for individual tables by entries in
- <structname>pg_autovacuum</>.
</para>
</listitem>
</varlistentry>
--- 3482,3487 ----
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
