Re: pg_autovacuum UPDATE_INTERVAL cmd arg - Mailing list pgsql-patches

Patch applied.  Thanks.

I had to adjust the patch, so I attached it.

---------------------------------------------------------------------------


Thomas F.O'Connell wrote:
> 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
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: contrib/pg_autovacuum/README.pg_autovacuum
===================================================================
RCS file: /cvsroot/pgsql/contrib/pg_autovacuum/README.pg_autovacuum,v
retrieving revision 1.13
diff -c -c -r1.13 README.pg_autovacuum
*** contrib/pg_autovacuum/README.pg_autovacuum    30 Mar 2005 05:05:01 -0000    1.13
--- contrib/pg_autovacuum/README.pg_autovacuum    19 Apr 2005 03:30:38 -0000
***************
*** 123,128 ****
--- 123,132 ----
  -V vacuum scaling factor: see "Vacuum and Analyze" below.
  -a analyze base threshold: see "Vacuum and Analyze" below.
  -A analyze scaling factor: see "Vacuum and Analyze" below.
+ -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.
***************
*** 157,162 ****
--- 161,167 ----
  -A 1   (half of -V if not specified)
  -s 300 (5 minutes)
  -S 2
+ -i 2

  The following arguments are used on Windows only:

Index: contrib/pg_autovacuum/pg_autovacuum.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/pg_autovacuum/pg_autovacuum.c,v
retrieving revision 1.30
diff -c -c -r1.30 pg_autovacuum.c
*** contrib/pg_autovacuum/pg_autovacuum.c    3 Apr 2005 00:01:51 -0000    1.30
--- contrib/pg_autovacuum/pg_autovacuum.c    19 Apr 2005 03:30:39 -0000
***************
*** 1103,1108 ****
--- 1103,1109 ----
      args->analyze_base_threshold = -1;
      args->analyze_scaling_factor = -1;
      args->debug = AUTOVACUUM_DEBUG;
+     args->update_interval = UPDATE_INTERVAL;
  #ifndef WIN32
      args->daemonize = 0;
  #else
***************
*** 1157,1162 ****
--- 1158,1166 ----
              case 'A':
                  args->analyze_scaling_factor = atof(optarg);
                  break;
+             case 'i':
+                 args->update_interval = atoi(optarg);
+                 break;
              case 'c':
                  args->av_vacuum_cost_delay = atoi(optarg);
                  break;
***************
*** 1341,1346 ****
--- 1345,1352 ----
      log_entry(logbuffer, LVL_INFO);
      sprintf(logbuffer, "  args->analyze_scaling_factor=%f", args->analyze_scaling_factor);
      log_entry(logbuffer, LVL_INFO);
+     sprintf(logbuffer, "  args->update_interval=%i", args->update_interval);
+     log_entry(logbuffer, LVL_INFO);

      if (args->av_vacuum_cost_delay != -1)
          sprintf(logbuffer, "  args->av_vacuum_cost_delay=%d", args->av_vacuum_cost_delay);
***************
*** 1646,1653 ****
              }
          }

!         if (loops % UPDATE_INTERVAL == 0)        /* Update the list if it's
!                                                  * time */
              update_db_list(db_list);    /* Add and remove databases from
                                           * the list */

--- 1652,1659 ----
              }
          }

!         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 */

***************
*** 1661,1668 ****

              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 */

--- 1667,1674 ----

              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 */

Index: contrib/pg_autovacuum/pg_autovacuum.h
===================================================================
RCS file: /cvsroot/pgsql/contrib/pg_autovacuum/pg_autovacuum.h,v
retrieving revision 1.14
diff -c -c -r1.14 pg_autovacuum.h
*** contrib/pg_autovacuum/pg_autovacuum.h    2 Dec 2004 22:48:10 -0000    1.14
--- contrib/pg_autovacuum/pg_autovacuum.h    19 Apr 2005 03:30:39 -0000
***************
*** 44,49 ****
--- 44,50 ----
  {
      int            vacuum_base_threshold,
                  analyze_base_threshold,
+                 update_interval,
                  sleep_base_value,
                  debug,