aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2005-07-14 05:13:45 +0000
committerTom Lane <tgl@sss.pgh.pa.us>2005-07-14 05:13:45 +0000
commit29094193f526bf90671d71b59a2e007aad1fcae5 (patch)
tree51e632c843ab9c7eedbd0b01f4de5b27c202443e /doc/src
parentf2bf2d2dc5cef3f5b9cf50493490fa9931f982b2 (diff)
downloadpostgresql-29094193f526bf90671d71b59a2e007aad1fcae5.tar.gz
postgresql-29094193f526bf90671d71b59a2e007aad1fcae5.zip
Integrate autovacuum functionality into the backend. There's still a
few loose ends to be dealt with, but it seems to work. Alvaro Herrera, based on the contrib code by Matthew O'Connor.
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/catalogs.sgml105
-rw-r--r--doc/src/sgml/runtime.sgml125
2 files changed, 227 insertions, 3 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 560a6b3f35f..fa0abca950c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
<!--
Documentation of the system catalogs, directed toward PostgreSQL developers
- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.107 2005/07/07 20:39:56 tgl Exp $
+ $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.108 2005/07/14 05:13:38 tgl Exp $
-->
<chapter id="catalogs">
@@ -89,6 +89,11 @@
</row>
<row>
+ <entry><link linkend="catalog-pg-autovacuum"><structname>pg_autovacuum</structname></link></entry>
+ <entry>per-relation autovacuum configuration parameters</entry>
+ </row>
+
+ <row>
<entry><link linkend="catalog-pg-cast"><structname>pg_cast</structname></link></entry>
<entry>casts (data type conversions)</entry>
</row>
@@ -1102,6 +1107,104 @@
</sect1>
+ <sect1 id="catalog-pg-autovacuum">
+ <title><structname>pg_autovacuum</structname></title>
+
+ <indexterm zone="catalog-pg-autovacuum">
+ <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.
+ </para>
+
+ <table>
+ <title><structname>pg_autovacuum</> Columns</title>
+
+ <tgroup cols=4>
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>vacrelid</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
+ <entry>The table this entry is for</entry>
+ </row>
+
+ <row>
+ <entry><structfield>enabled</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>If false, this table is never autovacuumed</entry>
+ </row>
+
+ <row>
+ <entry><structfield>vac_base_thresh</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Minimum number of modified tuples before vacuum</entry>
+ </row>
+
+ <row>
+ <entry><structfield>vac_scale_factor</structfield></entry>
+ <entry><type>float4</type></entry>
+ <entry></entry>
+ <entry>Multiplier for reltuples to add to
+ <structfield>vac_base_thresh</></entry>
+ </row>
+
+ <row>
+ <entry><structfield>anl_base_thresh</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Minimum number of modified tuples before analyze</entry>
+ </row>
+
+ <row>
+ <entry><structfield>anl_scale_factor</structfield></entry>
+ <entry><type>float4</type></entry>
+ <entry></entry>
+ <entry>Multiplier for reltuples to add to
+ <structfield>anl_base_thresh</></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The autovacuum daemon will initiate a <command>VACUUM</> operation
+ on a particular table when the number of updated or deleted tuples
+ exceeds <structfield>vac_base_thresh</structfield> plus
+ <structfield>vac_scale_factor</structfield> times the number of
+ live tuples currently estimated to be in the relation.
+ Similarly, it will initiate an <command>ANALYZE</> operation
+ when the number of inserted, updated or deleted tuples
+ exceeds <structfield>anl_base_thresh</structfield> plus
+ <structfield>anl_scale_factor</structfield> times the number of
+ live tuples currently estimated to be in the relation.
+ </para>
+
+ <para>
+ Any of the numerical fields can contain <literal>-1</> (or indeed
+ any negative value) to indicate that the system-wide default should
+ be used for this particular value.
+ </para>
+
+ </sect1>
+
+
<sect1 id="catalog-pg-cast">
<title><structname>pg_cast</structname></title>
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 1aed577b087..4cae3fa894c 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.337 2005/07/06 14:45:12 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.338 2005/07/14 05:13:38 tgl Exp $
-->
<chapter Id="runtime">
@@ -3173,7 +3173,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
If on, collected statistics are zeroed out whenever the server
is restarted. If off, statistics are accumulated across server
restarts. The default is <literal>on</>. This option can only
- be set at server start.
+ be set at server start.
</para>
</listitem>
</varlistentry>
@@ -3182,6 +3182,127 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
</sect3>
</sect2>
+ <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>
+
+ <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
+ <term><varname>autovacuum</varname> (<type>boolean</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Controls whether the server should start the
+ autovacuum subprocess. This is off by default.
+ This option can only be set at server start or in the
+ <filename>postgresql.conf</filename> file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
+ <term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_naptime</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the delay between activity rounds for the autovacuum
+ subprocess. In each round the subprocess examines one database
+ and issues <command>VACUUM</> and <command>ANALYZE</> commands
+ as needed for tables in that database. The delay is measured
+ in seconds, and the default is 60.
+ This option can only be set at server start or in the
+ <filename>postgresql.conf</filename> file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
+ <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_threshold</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the minimum number of updated or deleted tuples needed
+ to trigger a <command>VACUUM</> in any one table.
+ The default is 1000.
+ This option can only be set at server start or in the
+ <filename>postgresql.conf</filename> file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
+ <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_analyze_threshold</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the minimum number of inserted, updated or deleted tuples
+ needed to trigger an <command>ANALYZE</> in any one table.
+ The default is 500.
+ This option can only be set at server start or in the
+ <filename>postgresql.conf</filename> file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
+ <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_scale_factor</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</>.
+ The default is 0.4.
+ This option can only be set at server start or in the
+ <filename>postgresql.conf</filename> file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
+ <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_analyze_scale_factor</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_analyze_threshold</varname>
+ when deciding whether to trigger an <command>ANALYZE</>.
+ The default is 0.2.
+ This option can only be set at server start or in the
+ <filename>postgresql.conf</filename> file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
+
<sect2 id="runtime-config-client">
<title>Client Connection Defaults</title>