The following patch should allow UPDATE_INTERVAL to be specified on the command line. We find this useful because we frequently deal with thousands of tables in an environment where neither the databases nor the tables are updated frequently. This helps allow us to cut down on the overhead of updating the list for every other primary loop of pg_autovacuum.

I chose -i as the command-line argument and documented it briefly in the README.

The patch was applied to the 7.4.7 version of pg_autovacuum in contrib.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

diff -c postgresql-7.4.7/contrib/pg_autovacuum/README.pg_autovacuum 
postgresql-7.4.7-patched/contrib/pg_autovacuum/README.pg_autovacuum
*** postgresql-7.4.7/contrib/pg_autovacuum/README.pg_autovacuum Sat Nov  8 
21:17:37 2003
--- postgresql-7.4.7-patched/contrib/pg_autovacuum/README.pg_autovacuum Mon Mar 
 7 18:16:29 2005
***************
*** 114,119 ****
--- 114,123 ----
  -V vacuum scaling factor: see Vacuum and Analyze.
  -a analyze base threshold: see Vacuum and Analyze.
  -A analyze scaling factor: see Vacuum and Analyze.
+ -i update interval: how often (in terms of iterations of the primary loop
+    over the database list) to update the database list. The default is 2,
+    which means the list will be updated before every other pass through
+    the database list.
  -L log file: Name of file to which output is submitted, otherwise STDERR
  -U username: Username pg_autovacuum will use to connect with, if not
     specified the current username is used.
***************
*** 132,137 ****
--- 136,142 ----
  -A 1   (half of -v if not specified)
  -s 300 (5 minutes)
  -S 2
+ -i 2
  
  
  Vacuum and Analyze:
diff -c postgresql-7.4.7/contrib/pg_autovacuum/pg_autovacuum.c 
postgresql-7.4.7-patched/contrib/pg_autovacuum/pg_autovacuum.c
*** postgresql-7.4.7/contrib/pg_autovacuum/pg_autovacuum.c      Wed May 26 
13:48:36 2004
--- postgresql-7.4.7-patched/contrib/pg_autovacuum/pg_autovacuum.c      Mon Mar 
 7 18:17:47 2005
***************
*** 836,841 ****
--- 836,842 ----
        args->analyze_base_threshold = -1;
        args->analyze_scaling_factor = -1;
        args->debug = AUTOVACUUM_DEBUG;
+       args->update_interval = UPDATE_INTERVAL;
        args->daemonize = 0;
        args->user = 0;
        args->password = 0;
***************
*** 890,895 ****
--- 891,899 ----
                        case 'p':
                                args->port = optarg;
                                break;
+                       case 'i':
+                               args->update_interval = atoi(optarg);
+                               break;
                        case 'h':
                                usage();
                                exit(0);
***************
*** 984,989 ****
--- 988,995 ----
        log_entry(logbuffer);
        sprintf(logbuffer, "  args->debug=%i", args->debug);
        log_entry(logbuffer);
+       sprintf(logbuffer, "  args->update_interval=%i", args->update_interval);
+       log_entry(logbuffer);
  
        fflush(LOGOUTPUT);
  }
***************
*** 1065,1071 ****
                        }
                }
  
!               if (loops % UPDATE_INTERVAL == 0)               /* Update the 
list if it's
                                                                                
                 * time */
                        update_db_list(db_list);        /* Add and remove 
databases from
                                                                                
 * the list */
--- 1071,1077 ----
                        }
                }
  
!               if (loops % args->update_interval == 0)         /* Update the 
list if it's
                                                                                
                 * time */
                        update_db_list(db_list);        /* Add and remove 
databases from
                                                                                
 * the list */
***************
*** 1080,1086 ****
  
                        if (dbs->conn != NULL)
                        {
!                               if (loops % UPDATE_INTERVAL == 0)               
/* Update the list if
                                                                                
                                 * it's time */
                                        update_table_list(dbs);         /* Add 
and remove tables
                                                                                
                 * from the list */
--- 1086,1092 ----
  
                        if (dbs->conn != NULL)
                        {
!                               if (loops % args->update_interval == 0)         
/* Update the list if
                                                                                
                                 * it's time */
                                        update_table_list(dbs);         /* Add 
and remove tables
                                                                                
                 * from the list */
diff -c postgresql-7.4.7/contrib/pg_autovacuum/pg_autovacuum.h 
postgresql-7.4.7-patched/contrib/pg_autovacuum/pg_autovacuum.h
*** postgresql-7.4.7/contrib/pg_autovacuum/pg_autovacuum.h      Wed May 26 
13:48:37 2004
--- postgresql-7.4.7-patched/contrib/pg_autovacuum/pg_autovacuum.h      Mon Mar 
 7 17:57:13 2005
***************
*** 51,56 ****
--- 51,57 ----
                                analyze_base_threshold,
                                sleep_base_value,
                                debug,
+                               update_interval,
                                daemonize;
        float           vacuum_scaling_factor,
                                analyze_scaling_factor,
***************
*** 66,72 ****
  /* define cmd_args as global so we can get to them everywhere */
  cmd_args   *args;
  
! /* Might need to add a time value for last time the whold database was 
vacuumed.
        I think we need to guarantee this happens approx every 1Million TX's  */
  struct dbinfo
  {
--- 67,73 ----
  /* define cmd_args as global so we can get to them everywhere */
  cmd_args   *args;
  
! /* Might need to add a time value for last time the whole database was 
vacuumed.
        I think we need to guarantee this happens approx every 1Million TX's  */
  struct dbinfo
  {
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to