Here is another patch for autovacuum:

- vacuum_cost_delay and vacuum_cost_limit can be set per table, as well
  as globally with autovacuum_vacuum_cost_{limit,delay}

- pgstat is reset if recovery is required

- pgstat reset at postmaster start is disabled by default

- Xid-wraparound VACUUM is now FULL without ANALYZE


Note that because of the cost parameters, I changed the vacuum call in a
per-table call instead of passing a list of Oids.  This could be changed
by having two separate lists, one which uses the default values and
other for the rest, but it hardly seems worth the trouble.

(This patch requires catversion bump.)

On Sun, Jul 31, 2005 at 03:15:35PM -0400, Tom Lane wrote:

> BTW, it strikes me that there is one serious error in the current
> autovac logic: it does VACUUM ANALYZE rather than merely VACUUM
> when doing XID-wrap protection.  This means that it actively introduces
> unfrozen tuples into template databases, which is A Bad Move.  We
> should just VACUUM, instead.

True.  Changed in the attached patch.


I think this completes our expectations for 8.1, doesn't it?  Now we
only need the documentation.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.109
diff -c -r2.109 catalogs.sgml
*** doc/src/sgml/catalogs.sgml  26 Jul 2005 16:38:25 -0000      2.109
--- doc/src/sgml/catalogs.sgml  31 Jul 2005 20:19:20 -0000
***************
*** 1187,1192 ****
--- 1187,1206 ----
        <entry>Multiplier for reltuples to add to
         <structfield>anl_base_thresh</></entry>
       </row>
+ 
+        <row>
+         <entry><structfield>vac_cost_delay</structfield></entry>
+         <entry><type>integer</type></entry>
+         <entry></entry>
+         <entry>Custom <variable>vacuum_cost_delay</> parameter</entry>
+        </row>
+ 
+        <row>
+         <entry><structfield>vac_cost_limit</structfield></entry>
+         <entry><type>integer</type></entry>
+         <entry></entry>
+         <entry>Custom <variable>vacuum_cost_limit</> parameter</entry>
+        </row>
      </tbody>
     </tgroup>
    </table>
***************
*** 1207,1213 ****
    <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>
--- 1221,1231 ----
    <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.  Observe that the
!    <structfield>vac_cost_delay</> variable inherits its default value from the
!    <varname>autovacuum_vacuum_cost_delay</> configuration parameter,
!    or from <varname>vacuum_cost_delay</> if the former is set to a negative
!    value.  The same applies to <structfield>vac_cost_limit</>.
    </para>
  
   </sect1>
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.341
diff -c -r1.341 runtime.sgml
*** doc/src/sgml/runtime.sgml   30 Jul 2005 17:15:35 -0000      1.341
--- doc/src/sgml/runtime.sgml   31 Jul 2005 23:22:11 -0000
***************
*** 3399,3404 ****
--- 3399,3436 ----
        </listitem>
       </varlistentry>
  
+      <varlistentry id="guc-autovacuum-vacuum-cost-delay" 
xreflabel="autovacuum_vacuum_cost_delay">
+       <term><varname>autovacuum_vacuum_cost_delay</varname> 
(<type>integer</type>)</term>
+       <indexterm>
+        <primary><varname>autovacuum_vacuum_cost_delay</> configuration 
parameter</primary>
+       </indexterm>
+       <listitem>
+        <para>
+         Specifies the default value that will be applied to each
+         <command>VACUUM</> operation, for tables that do not have
+         a default value set in <structname>pg_autovacuum</>.  If a
+         negative value is specified (like the default value of -1),
+         the <varname>vacuum_cost_delay</> value will be applied instead.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry id="guc-autovacuum-cost-limit" 
xreflabel="autovacuum_vacuum_cost_limit">
+       <term><varname>autovacuum_vacuum_cost_limit</varname> 
(<type>integer</type>)</term>
+       <indexterm>
+        <primary><varname>autovacuum_vacuum_cost_limit</> configuration 
parameter</primary>
+       </indexterm>
+       <listitem>
+        <para>
+         Specifies the default value that will be applied to each
+         <command>VACUUM</> operation, for tables that do not have
+         a default value set in <structname>pg_autovacuum</>.  If a 
+         negative value is specified (like the default value of -1),
+         the <varname>vacuum_cost_limit</> value will be applied instead.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
      </variablelist>
     </sect2>
  
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.214
diff -c -r1.214 xlog.c
*** src/backend/access/transam/xlog.c   30 Jul 2005 14:15:44 -0000      1.214
--- src/backend/access/transam/xlog.c   31 Jul 2005 21:36:45 -0000
***************
*** 33,38 ****
--- 33,39 ----
  #include "catalog/catversion.h"
  #include "catalog/pg_control.h"
  #include "miscadmin.h"
+ #include "pgstat.h"
  #include "postmaster/bgwriter.h"
  #include "storage/bufpage.h"
  #include "storage/fd.h"
***************
*** 48,54 ****
  
  
  /*
!  *    Becauase O_DIRECT bypasses the kernel buffers, and because we never
   *    read those buffers except during crash recovery, it is a win to use
   *    it in all cases where we sync on each write().  We could allow O_DIRECT
   *    with fsync(), but because skipping the kernel buffer forces writes out
--- 49,55 ----
  
  
  /*
!  *    Because O_DIRECT bypasses the kernel buffers, and because we never
   *    read those buffers except during crash recovery, it is a win to use
   *    it in all cases where we sync on each write().  We could allow O_DIRECT
   *    with fsync(), but because skipping the kernel buffer forces writes out
***************
*** 4544,4549 ****
--- 4545,4555 ----
                        } while (record != NULL && recoveryContinue);
  
                        /*
+                        * Reset pgstat data, because it may be invalid after 
recovery.
+                        */
+                       pgstat_reset_all();
+ 
+                       /*
                         * end of main redo apply loop
                         */
  
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.2
diff -c -r1.2 autovacuum.c
*** src/backend/postmaster/autovacuum.c 29 Jul 2005 19:30:04 -0000      1.2
--- src/backend/postmaster/autovacuum.c 31 Jul 2005 22:45:53 -0000
***************
*** 57,62 ****
--- 57,65 ----
  int                   autovacuum_anl_thresh;
  double                autovacuum_anl_scale;
  
+ int                   autovacuum_vac_cost_delay;
+ int                   autovacuum_vac_cost_limit;
+ 
  /* Flag to tell if we are in the autovacuum daemon process */
  static bool am_autovacuum = false;
  
***************
*** 74,79 ****
--- 77,89 ----
        int32                   age;
  } autovac_dbase;
  
+ /* struct to keep track of tables to vacuum */
+ typedef struct autovac_table
+ {
+       Oid                             relid;
+       int                             vacuum_cost_delay;
+       int                             vacuum_cost_limit;
+ } autovac_table;
  
  #ifdef EXEC_BACKEND
  static pid_t autovac_forkexec(void);
***************
*** 84,90 ****
  static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
                         Form_pg_class classForm, Form_pg_autovacuum avForm,
                         List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool dovacuum);
  
  
  /*
--- 94,101 ----
  static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
                         Form_pg_class classForm, Form_pg_autovacuum avForm,
                         List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool doanalyze,
!                                                                         bool 
dovacuum, bool full);
  
  
  /*
***************
*** 469,476 ****
  
        if (whole_db)
        {
!               elog(DEBUG2, "autovacuum: VACUUM ANALYZE whole database");
!               autovacuum_do_vac_analyze(NIL, true);
        }
        else
        {
--- 480,487 ----
  
        if (whole_db)
        {
!               elog(DEBUG2, "autovacuum: VACUUM FULL whole database");
!               autovacuum_do_vac_analyze(NIL, false, true, true);
        }
        else
        {
***************
*** 545,559 ****
                 */
  
                if (analyze_tables)
!                       autovacuum_do_vac_analyze(analyze_tables, false);
  
                CHECK_FOR_INTERRUPTS();
  
-               /* get back to proper context */
-               MemoryContextSwitchTo(AutovacMemCxt);
- 
                if (vacuum_tables)
!                       autovacuum_do_vac_analyze(vacuum_tables, true);
        }
  
        /* Finally close out the last transaction. */
--- 556,585 ----
                 */
  
                if (analyze_tables)
!                       autovacuum_do_vac_analyze(analyze_tables, true, false, 
false);
  
                CHECK_FOR_INTERRUPTS();
  
                if (vacuum_tables)
!               {
!                       ListCell *cell;
! 
! 
!                       foreach(cell, vacuum_tables)
!                       {
!                               autovac_table *tab = lfirst(cell);
! 
!                               /* get back to proper context */
!                               MemoryContextSwitchTo(AutovacMemCxt);
! 
!                               /* Set the cost vacuum parameters for this 
table */
!                               VacuumCostDelay = tab->vacuum_cost_delay;
!                               VacuumCostLimit = tab->vacuum_cost_limit;
! 
!                               
autovacuum_do_vac_analyze(list_make1_oid(tab->relid), true,
!                                                                               
  true, true);
!                       }
!               }
        }
  
        /* Finally close out the last transaction. */
***************
*** 606,611 ****
--- 632,640 ----
        /* number of vacuum (resp. analyze) tuples at this time */
        float4                  vactuples,
                                        anltuples;
+       /* cost-based vacuum delay parameters */
+       int                             vac_cost_limit;
+       int                             vac_cost_delay;
  
        /* User disabled it in pg_autovacuum? */
        if (avForm && !avForm->enabled)
***************
*** 645,650 ****
--- 674,687 ----
                        autovacuum_anl_scale : avForm->anl_scale_factor;
                anl_base_thresh = (avForm->anl_base_thresh < 0) ?
                        autovacuum_anl_thresh : avForm->anl_base_thresh;
+ 
+               vac_cost_limit = (avForm->vac_cost_limit < 0) ?
+                       (autovacuum_vac_cost_limit < 0) ? VacuumCostLimit :
+                       autovacuum_vac_cost_limit : avForm->vac_cost_limit;
+ 
+               vac_cost_delay = (avForm->vac_cost_delay < 0) ?
+                       (autovacuum_vac_cost_delay < 0) ? VacuumCostDelay :
+                       autovacuum_vac_cost_delay : avForm->vac_cost_delay;
        }
        else
        {
***************
*** 653,658 ****
--- 690,701 ----
  
                anl_scale_factor = autovacuum_anl_scale;
                anl_base_thresh = autovacuum_anl_thresh;
+ 
+               vac_cost_limit = (autovacuum_vac_cost_limit < 0) ?
+                       VacuumCostLimit : autovacuum_vac_cost_limit;
+ 
+               vac_cost_delay = (autovacuum_vac_cost_delay < 0) ?
+                       VacuumCostDelay : autovacuum_vac_cost_delay;
        }
  
        vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
***************
*** 671,679 ****
        /* Determine if this table needs vacuum or analyze. */
        if (vactuples > vacthresh)
        {
                elog(DEBUG2, "will VACUUM ANALYZE %s",
                         RelationGetRelationName(rel));
!               *vacuum_tables = lappend_oid(*vacuum_tables, relid);
        }
        else if (anltuples > anlthresh)
        {
--- 714,730 ----
        /* Determine if this table needs vacuum or analyze. */
        if (vactuples > vacthresh)
        {
+               autovac_table *tab = (autovac_table *)
+                       palloc(sizeof(autovac_table));
+ 
                elog(DEBUG2, "will VACUUM ANALYZE %s",
                         RelationGetRelationName(rel));
! 
!               tab->relid = relid;
!               tab->vacuum_cost_limit = vac_cost_limit;
!               tab->vacuum_cost_delay = vac_cost_delay;
! 
!               *vacuum_tables = lappend(*vacuum_tables, tab);
        }
        else if (anltuples > anlthresh)
        {
***************
*** 691,702 ****
  
  /*
   * autovacuum_do_vac_analyze
!  *            Vacuum or analyze a list of tables; or all tables if relids = 
NIL
   *
   * We must be in AutovacMemCxt when this routine is called.
   */
  static void
! autovacuum_do_vac_analyze(List *relids, bool dovacuum)
  {
        VacuumStmt              *vacstmt = makeNode(VacuumStmt);
  
--- 742,754 ----
  
  /*
   * autovacuum_do_vac_analyze
!  *            Vacuum and/or analyze a list of tables; or all tables if relids 
= NIL
   *
   * We must be in AutovacMemCxt when this routine is called.
   */
  static void
! autovacuum_do_vac_analyze(List *relids, bool doanalyze, bool dovacuum,
!                                                 bool full)
  {
        VacuumStmt              *vacstmt = makeNode(VacuumStmt);
  
***************
*** 709,722 ****
  
        /* Set up command parameters */
        vacstmt->vacuum = dovacuum;
!       vacstmt->full = false;
!       vacstmt->analyze = true;
        vacstmt->freeze = false;
        vacstmt->verbose = false;
        vacstmt->relation = NULL;       /* all tables, or not used if relids != 
NIL */
        vacstmt->va_cols = NIL;
  
        vacuum(vacstmt, relids);
  }
  
  /*
--- 761,776 ----
  
        /* Set up command parameters */
        vacstmt->vacuum = dovacuum;
!       vacstmt->full = full;
!       vacstmt->analyze = doanalyze;
        vacstmt->freeze = false;
        vacstmt->verbose = false;
        vacstmt->relation = NULL;       /* all tables, or not used if relids != 
NIL */
        vacstmt->va_cols = NIL;
  
        vacuum(vacstmt, relids);
+ 
+       pfree(vacstmt);
  }
  
  /*
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.102
diff -c -r1.102 pgstat.c
*** src/backend/postmaster/pgstat.c     29 Jul 2005 19:30:04 -0000      1.102
--- src/backend/postmaster/pgstat.c     31 Jul 2005 21:36:07 -0000
***************
*** 99,105 ****
   * ----------
   */
  bool          pgstat_collect_startcollector = true;
! bool          pgstat_collect_resetonpmstart = true;
  bool          pgstat_collect_querystring = false;
  bool          pgstat_collect_tuplelevel = false;
  bool          pgstat_collect_blocklevel = false;
--- 99,105 ----
   * ----------
   */
  bool          pgstat_collect_startcollector = true;
! bool          pgstat_collect_resetonpmstart = false;
  bool          pgstat_collect_querystring = false;
  bool          pgstat_collect_tuplelevel = false;
  bool          pgstat_collect_blocklevel = false;
***************
*** 236,242 ****
         * statistics on postmaster start, simply remove the stats file.
         */
        if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
!               unlink(PGSTAT_STAT_FILENAME);
  
        /*
         * Nothing else required if collector will not get started
--- 236,242 ----
         * statistics on postmaster start, simply remove the stats file.
         */
        if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
!               pgstat_reset_all();
  
        /*
         * Nothing else required if collector will not get started
***************
*** 455,460 ****
--- 455,472 ----
        pgstat_collect_blocklevel = false;
  }
  
+ /*
+  * pgstat_reset_all() -
+  *
+  * Remove the stats file.  This is used on server start if the 
+  * stats_reset_on_server_start feature is enabled, or if WAL
+  * recovery is needed after a crash.
+  */
+ void
+ pgstat_reset_all(void)
+ {
+       unlink(PGSTAT_STAT_FILENAME);
+ }
  
  #ifdef EXEC_BACKEND
  
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.280
diff -c -r1.280 guc.c
*** src/backend/utils/misc/guc.c        30 Jul 2005 15:17:20 -0000      1.280
--- src/backend/utils/misc/guc.c        31 Jul 2005 21:15:36 -0000
***************
*** 672,678 ****
                        NULL
                },
                &pgstat_collect_resetonpmstart,
!               true, NULL, NULL
        },
        {
                {"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
--- 672,678 ----
                        NULL
                },
                &pgstat_collect_resetonpmstart,
!               false, NULL, NULL
        },
        {
                {"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
***************
*** 1161,1166 ****
--- 1161,1184 ----
        },
  
        {
+               {"autovacuum_vacuum_cost_delay", PGC_USERSET, AUTOVACUUM,
+                       gettext_noop("Vacuum cost delay in milliseconds, for 
autovacuum."),
+                       NULL
+               },
+               &autovacuum_vac_cost_delay,
+               -1, -1, 1000, NULL, NULL
+       },
+ 
+       {
+               {"autovacuum_vacuum_cost_limit", PGC_USERSET, AUTOVACUUM,
+                       gettext_noop("Vacuum cost amount available before 
napping, for autovacuum."),
+                       NULL
+               },
+               &autovacuum_vac_cost_limit,
+               -1, -1, 10000, NULL, NULL
+       },
+ 
+       {
                {"max_files_per_process", PGC_POSTMASTER, RESOURCES_KERNEL,
                        gettext_noop("Sets the maximum number of simultaneously 
open files for each server process."),
                        NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: 
/home/alvherre/cvs/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.155
diff -c -r1.155 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample       30 Jul 2005 15:17:20 
-0000      1.155
--- src/backend/utils/misc/postgresql.conf.sample       31 Jul 2005 21:16:43 
-0000
***************
*** 287,293 ****
  #stats_command_string = off
  #stats_block_level = off
  #stats_row_level = off
! #stats_reset_on_server_start = on
  
  
  #---------------------------------------------------------------------------
--- 287,293 ----
  #stats_command_string = off
  #stats_block_level = off
  #stats_row_level = off
! #stats_reset_on_server_start = off
  
  
  #---------------------------------------------------------------------------
***************
*** 300,305 ****
--- 300,309 ----
  #autovacuum_analyze_threshold = 500   # min # of tuple updates before analyze
  #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum
  #autovacuum_analyze_scale_factor = 0.2        # fraction of rel size before 
analyze
+ #autovacuum_vacuum_cost_delay = -1  # default vacuum cost delay for autovac
+                                     # negative means use vacuum_cost_delay
+ #autovacuum_vacuum_cost_limit = -1  # default vacuum cost limit for autovac
+                                     # negative means use vacuum_cost_limit
  
  
  #---------------------------------------------------------------------------
Index: src/include/pgstat.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/pgstat.h,v
retrieving revision 1.34
diff -c -r1.34 pgstat.h
*** src/include/pgstat.h        29 Jul 2005 19:30:09 -0000      1.34
--- src/include/pgstat.h        31 Jul 2005 21:32:59 -0000
***************
*** 367,372 ****
--- 367,373 ----
  extern void pgstat_init(void);
  extern int    pgstat_start(void);
  extern void pgstat_beterm(int pid);
+ extern void pgstat_reset_all(void);
  
  #ifdef EXEC_BACKEND
  extern void PgstatBufferMain(int argc, char *argv[]);
Index: src/include/catalog/pg_autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/pg_autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 pg_autovacuum.h
*** src/include/catalog/pg_autovacuum.h 14 Jul 2005 05:13:42 -0000      1.1
--- src/include/catalog/pg_autovacuum.h 31 Jul 2005 23:26:27 -0000
***************
*** 34,39 ****
--- 34,41 ----
        float4          vac_scale_factor;       /* reltuples scaling factor */
        int4            anl_base_thresh;        /* base threshold value */
        float4          anl_scale_factor;       /* reltuples scaling factor */
+       int4            vac_cost_delay;         /* vacuum cost-based delay */
+       int4            vac_cost_limit;         /* vacuum cost limit */
  } FormData_pg_autovacuum;
  
  /* ----------------
***************
*** 47,59 ****
   *            compiler constants for pg_autovacuum
   * ----------------
   */
! #define Natts_pg_autovacuum                                                   
6
  #define Anum_pg_autovacuum_vacrelid                                   1
  #define Anum_pg_autovacuum_enabled                                    2
  #define Anum_pg_autovacuum_vac_base_thresh                    3
  #define Anum_pg_autovacuum_vac_scale_factor                   4
  #define Anum_pg_autovacuum_anl_base_thresh                    5
  #define Anum_pg_autovacuum_anl_scale_factor                   6
  
  /* There are no preloaded tuples in pg_autovacuum.h */
  
--- 49,63 ----
   *            compiler constants for pg_autovacuum
   * ----------------
   */
! #define Natts_pg_autovacuum                                                   
8
  #define Anum_pg_autovacuum_vacrelid                                   1
  #define Anum_pg_autovacuum_enabled                                    2
  #define Anum_pg_autovacuum_vac_base_thresh                    3
  #define Anum_pg_autovacuum_vac_scale_factor                   4
  #define Anum_pg_autovacuum_anl_base_thresh                    5
  #define Anum_pg_autovacuum_anl_scale_factor                   6
+ #define Anum_pg_autovacuum_vac_cost_delay                     7
+ #define Anum_pg_autovacuum_vac_cost_limit                     8
  
  /* There are no preloaded tuples in pg_autovacuum.h */
  
Index: src/include/postmaster/autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/postmaster/autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 autovacuum.h
*** src/include/postmaster/autovacuum.h 14 Jul 2005 05:13:43 -0000      1.1
--- src/include/postmaster/autovacuum.h 31 Jul 2005 17:32:12 -0000
***************
*** 21,26 ****
--- 21,28 ----
  extern double autovacuum_vac_scale;
  extern int            autovacuum_anl_thresh;
  extern double autovacuum_anl_scale;
+ extern int            autovacuum_vac_cost_delay;
+ extern int            autovacuum_vac_cost_limit;
  
  /* Status inquiry functions */
  extern bool AutoVacuumingActive(void);
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to