From 29094193f526bf90671d71b59a2e007aad1fcae5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 14 Jul 2005 05:13:45 +0000 Subject: 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. --- doc/src/sgml/catalogs.sgml | 105 ++++++++++++++++++++++++++++++++++++- doc/src/sgml/runtime.sgml | 125 ++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 227 insertions(+), 3 deletions(-) (limited to 'doc/src') 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 @@ @@ -88,6 +88,11 @@ authorization identifier membership relationships + + pg_autovacuum + per-relation autovacuum configuration parameters + + pg_cast casts (data type conversions) @@ -1102,6 +1107,104 @@ + + <structname>pg_autovacuum</structname> + + + pg_autovacuum + + + + The catalog pg_autovacuum stores optional + per-relation configuration parameters for 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. + + + + <structname>pg_autovacuum</> Columns + + + + + Name + Type + References + Description + + + + + + vacrelid + oid + pg_class.oid + The table this entry is for + + + + enabled + bool + + If false, this table is never autovacuumed + + + + vac_base_thresh + integer + + Minimum number of modified tuples before vacuum + + + + vac_scale_factor + float4 + + Multiplier for reltuples to add to + vac_base_thresh + + + + anl_base_thresh + integer + + Minimum number of modified tuples before analyze + + + + anl_scale_factor + float4 + + Multiplier for reltuples to add to + anl_base_thresh + + + +
+ + + The autovacuum daemon will initiate a VACUUM operation + on a particular table when the number of updated or deleted tuples + exceeds vac_base_thresh plus + vac_scale_factor times the number of + live tuples currently estimated to be in the relation. + Similarly, it will initiate an ANALYZE operation + when the number of inserted, updated or deleted tuples + exceeds anl_base_thresh plus + anl_scale_factor times the number of + live tuples currently estimated to be in the relation. + + + + Any of the numerical fields can contain -1 (or indeed + any negative value) to indicate that the system-wide default should + be used for this particular value. + + + + + <structname>pg_cast</structname> 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 @@ @@ -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 on. This option can only - be set at server start. + be set at server start. @@ -3182,6 +3182,127 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows + + Automatic Vacuuming + + + Beginning in PostgreSQL 8.1, there is an optional server + process called the autovacuum daemon, whose purpose is + to automate the issuance of periodic VACUUM and + 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 and + are set TRUE. Also, it's + important to allow a slot for the autovacuum process when choosing + the value of . + + + + + + autovacuum (boolean) + + autovacuum configuration parameter + + + + 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 + postgresql.conf file. + + + + + + autovacuum_naptime (integer) + + autovacuum_naptime configuration parameter + + + + 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 only be set at server start or in the + postgresql.conf file. + + + + + + autovacuum_vacuum_threshold (integer) + + autovacuum_vacuum_threshold configuration parameter + + + + Specifies the minimum number of updated or deleted tuples needed + to trigger a VACUUM in any one table. + The default is 1000. + This option can only be set at server start or in the + postgresql.conf file. + + + + + + autovacuum_analyze_threshold (integer) + + autovacuum_analyze_threshold configuration parameter + + + + Specifies the minimum number of inserted, updated or deleted tuples + needed to trigger an ANALYZE in any one table. + The default is 500. + This option can only be set at server start or in the + postgresql.conf file. + + + + + + autovacuum_vacuum_scale_factor (floating point) + + autovacuum_vacuum_scale_factor configuration parameter + + + + Specifies a fraction of the table size to add to + autovacuum_vacuum_threshold + when deciding whether to trigger a VACUUM. + The default is 0.4. + This option can only be set at server start or in the + postgresql.conf file. + + + + + + autovacuum_analyze_scale_factor (floating point) + + autovacuum_analyze_scale_factor configuration parameter + + + + Specifies a fraction of the table size to add to + autovacuum_analyze_threshold + when deciding whether to trigger an ANALYZE. + The default is 0.2. + This option can only be set at server start or in the + postgresql.conf file. + + + + + + + Client Connection Defaults -- cgit v1.2.3