diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2005-07-14 05:13:45 +0000 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2005-07-14 05:13:45 +0000 |
commit | 29094193f526bf90671d71b59a2e007aad1fcae5 (patch) | |
tree | 51e632c843ab9c7eedbd0b01f4de5b27c202443e /doc/src | |
parent | f2bf2d2dc5cef3f5b9cf50493490fa9931f982b2 (diff) | |
download | postgresql-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.sgml | 105 | ||||
-rw-r--r-- | doc/src/sgml/runtime.sgml | 125 |
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> |