Re: Another small bug (pg_autovacuum)

matthew@zeut.net ("Matthew T. O'Connor") writes:
> OK, well as we wait on the fix for the stats system, let me submit my
> patch for pg_autovacuum.  This patch assumes that the stats system will
> be fixed so that all inserts, updates and deletes performed on shared
> tables reguardless of what database those commands were executed from,
> will show up in the stats shown in each database.

I had to make a further change to this to take quotes off the 'last
ANALYZE' in order for it to not overquote the relation name, so
there's a _little_ work left to get it to play well.

I have deployed it onto several boxes that should be doing some
vacuuming over the weekend, and it is now certainly hitting pg_
tables.

I would like to present a CVS-oriented patch; unfortunately, I had to
change the indentation patterns when editing some of it :-(.  The
following _may_ be good; not sure...

? pg_autovacuum
Index: README.pg_autovacuum
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/README.pg_autovacuum,v
retrieving revision 1.2
diff -c -b -r1.2 README.pg_autovacuum
*** README.pg_autovacuum    12 Jun 2003 01:36:44 -0000    1.2
--- README.pg_autovacuum    12 Sep 2003 22:45:37 -0000
***************
*** 2,67 **** --------------------  pg_autovacuum is a libpq client program that monitors all the
! databases associated with a postgresql server.  It uses the stats collector to monitor insert, update and delete
activity.  
 
! When a table exceeds its insert or delete threshold (more detail
! on thresholds below) then that table will be  vacuumed or analyzed.  
! 
! This allows postgresql to keep the fsm and table statistics up to
! date, and eliminates the need to schedule periodic vacuums.  The primary benefit of pg_autovacuum is that the FSM and
table
! statistic information are updated as needed.  When a table is actively
! changing, pg_autovacuum will perform the necessary vacuums and
! analyzes, whereas if a table remains static, no cycles will be wasted
! performing unnecessary vacuums/analyzes.  A secondary benefit of pg_autovacuum is that it ensures that a
! database wide vacuum is performed prior to xid wraparound.  This is an important, if rare, problem, as failing to do
socan result in major
 
! data loss.
!   KNOWN ISSUES: -------------
- pg_autovacuum has been tested under Redhat Linux (by me) and Solaris (by
- Christopher B. Browne) and all known bugs have been resolved.  Please report
- any problems to the hackers list.
- 
- pg_autovacuum does not get started automatically by either the postmaster or
- by pg_ctl.  Along the sames lines, when the postmaster exits no one tells
- pg_autovacuum.  The result is that at the start of the next loop,
- pg_autovacuum fails to connect to the server and exits.  Any time  it fails
- to connect pg_autovacuum exits.
- 
- pg_autovacuum requires that the stats system be enabled and reporting row
- level stats.  The overhead of the stats system has been shown to be
- significant under certain workloads.  For instance a tight loop of queries
- performing "select 1" was nearly 30% slower with stats enabled.  However,
- in practice with more realistic workloads, the stats system overhead is
- usually nominal.   INSTALL: -------- 
! As of postgresql v7.4 pg_autovacuum is included in the main source tree
! under contrib.  Therefore you just make && make install (similar to most other
! contrib modules) and it will be installed for you.
! 
! If you are using an earlier version of postgresql just uncompress the tar.gz
! into the contrib directory and modify the contrib/Makefile to include the pg_autovacuum
! directory.  pg_autovacuum will then be made as part of the standard
! postgresql install. 
! make sure that the folowing are set in postgresql.conf    stats_start_collector = true   stats_row_level = true 
! start up the postmaster, then execute the pg_autovacuum executable.   Command line arguments:
-----------------------
--- 2,98 ---- --------------------  pg_autovacuum is a libpq client program that monitors all the
! databases associated with a PostgreSQL server.  It uses the statistics collector to monitor insert, update and delete
activity.
 
! When a table exceeds a insert or delete threshold (for more detail on
! thresholds, see "Vacuum and Analyze" below) then that table will be
! vacuumed and/or analyzed.
! 
! This allows PostgreSQL to keep the FSM (Free Space Map) and table
! statistics up to date, and eliminates the need to schedule periodic
! vacuums.  The primary benefit of pg_autovacuum is that the FSM and table
! statistic information are updated more nearly as frequently as needed.
! When a table is actively changing, pg_autovacuum will perform the
! VACUUMs and ANALYZEs that such a table needs, whereas if a table
! remains static, no cycles will be wasted performing this
! unnecessarily.  A secondary benefit of pg_autovacuum is that it ensures that a
! database wide vacuum is performed prior to XID wraparound.  This is an important, if rare, problem, as failing to do
socan result in major
 
! data loss.  (See the section in the _Administrator's Guide_ entitled
! "Preventing transaction ID wraparound failures" for more details.)  KNOWN ISSUES: ------------- 
+ pg_autovacuum has been tested under Redhat Linux (by me) and Debian
+ GNU/Linux and Solaris (by Christopher B. Browne) and all known bugs
+ have been resolved.  Please report any problems to the hackers list.
+ 
+ pg_autovacuum requires that the statistics system be enabled and
+ reporting row level stats.  The overhead of the stats system has been
+ shown to be significant costly under certain workloads.  For instance,
+ a tight loop of queries performing "select 1" was found to run nearly
+ 30% slower when stats were enabled.  However, in practice, with more
+ realistic workloads, the stats system overhead is usually nominal.
+ 
+ pg_autovacuum does not get started automatically by either the
+ postmaster or by pg_ctl.  Similarly, when the postmaster exits, no one
+ tells pg_autovacuum.  The result of that is that at the start of the
+ next loop, pg_autovacuum will fail to connect to the server and
+ exit().  Any time it fails to connect pg_autovacuum exit()s.
+ 
+ While pg_autovacuum can manage vacuums for as many databases as you
+ may have tied to a particular PostgreSQL postmaster, it can only
+ connect to a single PostgreSQL postmaster.  Thus, if you have multiple
+ postmasters on a particular host, you will need multiple pg_autovacuum
+ instances, and they have no way, at present, to coordinate between one
+ another to ensure that they do not concurrently vacuum big tables.
+ 
+ TODO:
+ -----
+ 
+ At present, there are no sample scripts to automatically start up
+ pg_autovacuum along with the database.  It would be desirable to have
+ a SysV script to start up pg_autovacuum after PostgreSQL has been
+ started.
+ 
+ Some users have expressed interest in making pg_autovacuum more
+ configurable so that certain tables known to be inactive could be
+ excluded from being vacuumed.  It would probably make sense to
+ introduce this sort of functionality by providing arguments to specify
+ the database and schema in which to find a configuration table.  INSTALL: -------- 
! As of postgresql v7.4 pg_autovacuum is included in the main source
! tree under contrib.  Therefore you merely need to "make && make
! install" (similar to most other contrib modules) and it will be
! installed for you.
! 
! If you are using an earlier version of PostgreSQL, uncompress the
! tar.gz file into the contrib directory and modify the contrib/Makefile
! to include the pg_autovacuum directory.  pg_autovacuum will then be
! built as part of the standard postgresql install. 
! make sure that the following are set in postgresql.conf:    stats_start_collector = true   stats_row_level = true 
! Start up the postmaster, then execute the pg_autovacuum executable.
! 
! If you have a script that automatically starts up the PostgreSQL
! instance, you might add in, after that, something similar to the
! following: 
+   sleep 10    # To give the database some time to start up
+   $PGBINS/pg_autovacuum -D -s $SBASE -S $SSCALE ... [other arguments]  Command line arguments:
-----------------------
***************
*** 69,75 **** pg_autovacuum has the following optional arguments:  -d debug: 0 silent, 1 basic info, 2 more debug
info, etc...
 
! -D dameonize: Detach from tty and run in background. -s sleep base value: see "Sleeping" below. -S sleep scaling
factor:see "Sleeping" below. -v vacuum base threshold: see Vacuum and Analyze.
 
--- 100,106 ---- pg_autovacuum has the following optional arguments:  -d debug: 0 silent, 1 basic info, 2 more debug
info, etc...
 
! -D daemonize: Detach from tty and run in background. -s sleep base value: see "Sleeping" below. -S sleep scaling
factor:see "Sleeping" below. -v vacuum base threshold: see Vacuum and Analyze.
 
***************
*** 80,97 **** -U username: Username pg_autovacuum will use to connect with, if not    specified the current username
isused. -P password: Password pg_autovacuum will use to connect with.
 
! -H host: host name or IP to connect too. -p port: port used for connection. -h help: list of command line options. 
! All arguments have default values defined in pg_autovacuum.h.  At the
! time of writing they are:  -d 1 -v 1000 -V 2   
! -a 500 (half of -v is not specified)
! -A 1   (half of -v is not specified) -s 300 (5 minutes) -S 2 
--- 111,128 ---- -U username: Username pg_autovacuum will use to connect with, if not    specified the current username
isused. -P password: Password pg_autovacuum will use to connect with.
 
! -H host: host name or IP to connect to. -p port: port used for connection. -h help: list of command line options. 
! Numerous arguments have default values defined in pg_autovacuum.h.  At
! the time of writing they are:  -d 1 -v 1000 -V 2   
! -a 500 (half of -v if not specified)
! -A 1   (half of -v if not specified) -s 300 (5 minutes) -S 2 
***************
*** 99,111 **** Vacuum and Analyze: ------------------- 
! pg_autovacuum performs either a vacuum analyze or just analyze depending
! on the quantity and type of table activity (insert, update, or delete):  - If the number of (inserts + updates +
deletes)> AnalyzeThreshold, then   only an analyze is performed. 
 
! - If the number of (deletes + updates ) > VacuumThreshold, then a   vacuum analyze is performed.  deleteThreshold is
equalto: 
 
--- 130,143 ---- Vacuum and Analyze: ------------------- 
! pg_autovacuum performs either a VACUUM ANALYZE or just ANALYZE
! depending on the mixture of table activity (insert, update, or
! delete):  - If the number of (inserts + updates + deletes) > AnalyzeThreshold, then   only an analyze is performed. 
! - If the number of (deletes + updates) > VacuumThreshold, then a   vacuum analyze is performed.  deleteThreshold is
equalto: 
 
***************
*** 115,140 ****     analyze_base_value + (analyze_scaling_factor * "number of tuples in the table")  The
AnalyzeThresholddefaults to half of the VacuumThreshold since it
 
! represents a much less expensive operation (approx 5%-10% of vacuum), and
! running it more often should not substantially degrade system performance.  Sleeping: ---------  pg_autovacuum sleeps
fora while after it is done checking all the databases.  It does this in order to limit the amount of system
 
! resources it consumes.  This also allows the system administrator to configure pg_autovacuum to be more or less
aggressive. Reducing the sleep time will cause pg_autovacuum to respond more quickly to changes, whether they be
databaseaddition/removal, table addition/removal, or just normal table activity. 
 
! On the other hand, setting pg_autovaccum to sleep values to agressivly
! (for too short a period of time) can have a negative effect on server
! performance.  If a table gets vacuumed 5 times during the course of a
! large update, this is likely to take much longer than if the table was
! vacuumed only once, at the end.  The total time it sleeps is equal to: 
--- 147,173 ----     analyze_base_value + (analyze_scaling_factor * "number of tuples in the table")  The
AnalyzeThresholddefaults to half of the VacuumThreshold since it
 
! represents a much less expensive operation (approx 5%-10% of vacuum),
! and running ANALYZE more often should not substantially degrade system
! performance.  Sleeping: ---------  pg_autovacuum sleeps for a while after it is done checking all the databases.  It
doesthis in order to limit the amount of system
 
! resources it consumes.  This allows the system administrator to configure pg_autovacuum to be more or less
aggressive. Reducing the sleep time will cause pg_autovacuum to respond more quickly to changes, whether they be
databaseaddition/removal, table addition/removal, or just normal table activity. 
 
! On the other hand, setting pg_autovacuum to sleep values too
! aggressively (to too short periods of time) can have a negative effect
! on server performance.  For instance, if a table gets vacuumed 5 times
! during the course of a large set of updates, this is likely to take a
! lot more work than if the table was vacuumed just once, at the end.  The total time it sleeps is equal to: 
***************
*** 142,156 ****   loop"  Note that timing measurements are made in seconds; specifying
! "pg_vacuum -s 1" means pg_autovacuum could poll the database upto 60 times
! minute.  In a system with large tables where vacuums may run for several
! minutes, longer times between vacuums are likely to be appropriate.  What pg_autovacuum monitors:
----------------------------
 
! pg_autovacuum dynamically generates a list of all databases and tables that
! exist on the server.  It will dynamically add and remove databases and
! tables that are removed from the database server while pg_autovacuum is
! running.  Overhead is fairly small per object.  For example: 10 databases
! with 10 tables each appears to less than 10k of memory on my Linux box.
--- 175,191 ----   loop"  Note that timing measurements are made in seconds; specifying
! "pg_vacuum -s 1" means pg_autovacuum could poll the database up to 60
! times minute.  In a system with large tables where vacuums may run for
! several minutes, rather longer times between vacuums are likely to be
! appropriate.  What pg_autovacuum monitors: ---------------------------- 
! pg_autovacuum dynamically generates a list of all databases and tables
! that exist on the server.  It will dynamically add and remove
! databases and tables that are removed from the database server while
! pg_autovacuum is running.  Overhead is fairly small per object.  For
! example: 10 databases with 10 tables each appears to less than 10k of
! memory on my Linux box.
Index: pg_autovacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.c,v
retrieving revision 1.5
diff -c -b -r1.5 pg_autovacuum.c
*** pg_autovacuum.c    11 Sep 2003 19:01:18 -0000    1.5
--- pg_autovacuum.c    12 Sep 2003 22:45:37 -0000
***************
*** 118,123 ****
--- 118,129 ----     new_tbl->reltuples = atoi(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
new_tbl->relpages= atoi(PQgetvalue(res, row, PQfnumber(res, "relpages"))); 
 
+     log_entry(PQgetvalue(res, row, PQfnumber(res, "relisshared")));
+     if (strcmp("t", PQgetvalue(res, row, PQfnumber(res, "relisshared"))))
+       new_tbl->relisshared = 0;
+     else
+       new_tbl->relisshared = 1;
+      new_tbl->analyze_threshold =         args->analyze_base_threshold + args->analyze_scaling_factor *
new_tbl->reltuples;    new_tbl->vacuum_threshold =
 
***************
*** 213,219 ****          * both remove tables from the list that no longer exist and add          * tables to the list
thatare new          */
 
!         res = send_query(query_table_stats(dbi), dbi);         t = PQntuples(res);          /*
--- 219,225 ----          * both remove tables from the list that no longer exist and add          * tables to the list
thatare new          */
 
!         res = send_query((char *) TABLE_STATS_QUERY, dbi);         t = PQntuples(res);          /*
***************
*** 353,359 **** {     sprintf(logbuffer, "  table name:     %s.%s", tbl->dbi->dbname, tbl->table_name);
log_entry(logbuffer);
!     sprintf(logbuffer, "     relfilenode: %i", tbl->relfilenode);     log_entry(logbuffer);     sprintf(logbuffer, "
  reltuples: %i;  relpages: %i", tbl->reltuples, tbl->relpages);     log_entry(logbuffer);
 
--- 359,365 ---- {     sprintf(logbuffer, "  table name:     %s.%s", tbl->dbi->dbname, tbl->table_name);
log_entry(logbuffer);
!     sprintf(logbuffer, "     relfilenode: %i;   relisshared: %i", tbl->relfilenode, tbl->relisshared);
log_entry(logbuffer);    sprintf(logbuffer, "     reltuples: %i;  relpages: %i", tbl->reltuples, tbl->relpages);
log_entry(logbuffer);
***************
*** 688,706 ****  /* End of DB List Management Function */ 
! /* Begninning of misc Functions */
! 
! 
! char *
! query_table_stats(db_info * dbi)
! {
!     if (!strcmp(dbi->dbname, "template1"))        /* Use template1 to
!                                                  * monitor the system
!                                                  * tables */
!         return (char *) TABLE_STATS_ALL;
!     else
!         return (char *) TABLE_STATS_USER;
! }  /* Perhaps add some test to this function to make sure that the stats we need are available */ PGconn *
--- 694,700 ----  /* End of DB List Management Function */ 
! /* Beginning of misc Functions */  /* Perhaps add some test to this function to make sure that the stats we need are
available*/ PGconn *
 
***************
*** 753,758 ****
--- 747,755 ----     if (NULL == dbi->conn)         return NULL; 
+     if (args->debug >= 4)
+       log_entry(query);
+      res = PQexec(dbi->conn, query);      if (!res)
***************
*** 964,970 ****     int            j = 0,                 loops = 0; 
! /*    int numInserts, numDeletes, */     int            sleep_secs;     Dllist       *db_list;     Dlelem
*db_elem,
--- 961,967 ----   int            j = 0,     loops = 0; 
!   /*    int numInserts, numDeletes, */   int            sleep_secs;   Dllist       *db_list;   Dlelem
*db_elem,
***************
*** 1055,1061 ****                  if (0 == xid_wraparound_check(dbs));                 {
!                     res = send_query(query_table_stats(dbs), dbs);        /* Get an updated
                                              * snapshot of this dbs
                     * table stats */                     for (j = 0; j < PQntuples(res); j++)
 
--- 1052,1058 ----            if (0 == xid_wraparound_check(dbs));           {
!         res = send_query(TABLE_STATS_QUERY, dbs);        /* Get an updated                                      *
snapshotof this dbs                                      * table stats */         for (j = 0; j < PQntuples(res); j++)
 
***************
*** 1087,1093 ****                                  */                                 if ((tbl->curr_vacuum_count -
tbl->CountAtLastVacuum)>= tbl->vacuum_threshold)                                 {
 
!                                     snprintf(buf, sizeof(buf), "VACUUM ANALYZE \"%s\"", tbl->table_name);
                       if (args->debug >= 1)                                     {
  sprintf(logbuffer, "Performing: %s", buf);
 
--- 1084,1094 ----                  */                 if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >=
tbl->vacuum_threshold)                  {
 
!                 /* if relisshared = t and database != template1 then only do an analyze */
!                 if((tbl->relisshared > 0) && (strcmp("template1",dbs->dbname)))
!                   snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
!                 else    
!                   snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);                 if (args->debug
>=1)                   {                     sprintf(logbuffer, "Performing: %s", buf);
 
***************
*** 1101,1107 ****                                 }                                 else if ((tbl->curr_analyze_count
-tbl->CountAtLastAnalyze) >= tbl->analyze_threshold)                                 {
 
!                                     snprintf(buf, sizeof(buf), "ANALYZE \"%s\"", tbl->table_name);
                if (args->debug >= 1)                                     {
sprintf(logbuffer,"Performing: %s", buf);
 
--- 1102,1108 ----                   }                 else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >=
tbl->analyze_threshold)                  {
 
!                 snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);                 if (args->debug >= 1)
            {                     sprintf(logbuffer, "Performing: %s", buf);
 
Index: pg_autovacuum.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.h,v
retrieving revision 1.6
diff -c -b -r1.6 pg_autovacuum.h
*** pg_autovacuum.h    7 Aug 2003 21:11:57 -0000    1.6
--- pg_autovacuum.h    12 Sep 2003 22:45:37 -0000
***************
*** 34,41 **** #define VACUUM_ANALYZE        0 #define ANALYZE_ONLY        1 
! #define TABLE_STATS_ALL        "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_delfrom
pg_classa, pg_stat_all_tables b where a.relfilenode=b.relid"
 
! #define TABLE_STATS_USER    "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_delfrom
pg_classa, pg_stat_user_tables b where a.relfilenode=b.relid" #define FRONTEND #define PAGES_QUERY "select
relfilenode,reltuples,relpagesfrom pg_class where relfilenode=%i" #define FROZENOID_QUERY "select oid,age(datfrozenxid)
frompg_database where datname = 'template1'"
 
--- 34,41 ---- #define VACUUM_ANALYZE        0 #define ANALYZE_ONLY        1 
! #define TABLE_STATS_QUERY    "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del
frompg_class a, pg_stat_all_tables b where a.relfilenode=b.relid and a.relkind = 'r'"
 
!  #define FRONTEND #define PAGES_QUERY "select relfilenode,reltuples,relpages from pg_class where relfilenode=%i"
#defineFROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
 
***************
*** 86,91 ****
--- 86,92 ----                *table_name;     int            relfilenode,                 reltuples,
+                 relisshared,                 relpages;     long        analyze_threshold,
vacuum_threshold;
***************
*** 132,138 **** static PGconn *db_connect(db_info * dbi); static void db_disconnect(db_info * dbi); static PGresult
*send_query(constchar *query, db_info * dbi);
 
- static char *query_table_stats(db_info * dbi);  /* Other Generally needed Functions */ static void daemonize(void);
--- 133,138 ----

-- 
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)