diff options
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/config.sgml | 29 | ||||
| -rw-r--r-- | doc/src/sgml/maintenance.sgml | 107 |
3 files changed, 123 insertions, 22 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c813ba98ded..445f359f221 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.113 2005/09/08 20:07:41 tgl Exp $ + $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.114 2005/09/13 01:51:18 alvherre Exp $ --> <chapter id="catalogs"> @@ -1137,9 +1137,14 @@ <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 <quote>autovacuum</>. + 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. diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index dea655b67e7..4d65dd6b805 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.15 2005/09/12 23:09:05 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.16 2005/09/13 01:51:18 alvherre Exp $ --> <chapter Id="runtime-config"> <title>Run-time Configuration</title> @@ -2897,18 +2897,15 @@ SELECT * FROM parent WHERE key = 2400; <sect1 id="runtime-config-autovacuum"> <title>Automatic Vacuuming</title> + <indexterm> + <primary>autovacuum</primary> + <secondary>global configuration parameters</secondary> + </indexterm> + <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">. + These settings control the default behavior for the <firstterm>autovacuum + daemon</firstterm>. Please refer to <xref linkend="autovacuum"> for + more information. </para> <variablelist> @@ -2958,6 +2955,8 @@ SELECT * FROM parent WHERE key = 2400; The default is 1000. This option can only be set at server start or in the <filename>postgresql.conf</filename> file. + This setting can be overridden for individual tables by entries in + <structname>pg_autovacuum</>. </para> </listitem> </varlistentry> @@ -2974,6 +2973,8 @@ SELECT * FROM parent WHERE key = 2400; The default is 500. This option can only be set at server start or in the <filename>postgresql.conf</filename> file. + This setting can be overridden for individual tables by entries in + <structname>pg_autovacuum</>. </para> </listitem> </varlistentry> @@ -2991,6 +2992,8 @@ SELECT * FROM parent WHERE key = 2400; The default is 0.4. This option can only be set at server start or in the <filename>postgresql.conf</filename> file. + This setting can be overridden for individual tables by entries in + <structname>pg_autovacuum</>. </para> </listitem> </varlistentry> @@ -3008,6 +3011,8 @@ SELECT * FROM parent WHERE key = 2400; The default is 0.2. This option can only be set at server start or in the <filename>postgresql.conf</filename> file. + This setting can be overridden for individual tables by entries in + <structname>pg_autovacuum</>. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index b9ce518554b..d4b3f51d557 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.45 2005/06/22 21:14:28 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.46 2005/09/13 01:51:18 alvherre Exp $ --> <chapter id="maintenance"> @@ -99,6 +99,12 @@ $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.45 2005/06/22 21:14:28 tgl <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">. + </para> + <sect2 id="vacuum-for-space-recovery"> <title>Recovering disk space</title> @@ -177,13 +183,6 @@ $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.45 2005/06/22 21:14:28 tgl 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 @@ -456,6 +455,98 @@ HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb". </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. + </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> + Besides the base threshold values and scale factors, there are three + 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. + The other parameter, <structname>pg_autovacuum</>.<structfield>enabled</>, + can be used to instruct the autovacuum daemon to skip any particular table + by setting it to <literal>false</literal>. + </para> + + </sect2> </sect1> |