pg_autovacuum vacuum cost variables patch - Mailing list pgsql-patches

Hello, as per some discussion on the lists a few days ago, I am
submitting this patch against the CVS version of pg_autovacuum.

This patch adds 5 new command line options to pg_autovacuum that
directly correspond to the 5 new vacuum cost GUC variables in 8.0.   I
have done some simple testing and this patch works Ok on my FC3 box.

Two questions:
1) It is my understanding that these new GUC vars only effect vacuum.
That is they do NOT have any effect on an analyze command right? (I ask
since I'm only setting the vars before I issue a vacuum command)
2) Does anyone have any better suggestions for the letters used for the
new pg_autovacuum command line args?  Nothing obvious came to mind, so I
just basically picked at random resulting in this:
-c    vacuum_cost_delay
-C  vacuum_cost_page_hit
-m    vacuum_cost_page_miss
-n    vacuum_cost_page_dirty
-N    vacuum_cost_limit
Any better ideas?


 Please review and if deemed accecptable, please apply to CVS HEAD.

Thanks,

Matthew O'Connor

*** ./pg_autovacuum.c.orig    2004-10-26 00:00:00.000000000 -0400
--- ./pg_autovacuum.c    2004-10-26 01:15:40.000000000 -0400
***************
*** 973,978 ****
--- 973,1054 ----
      return res;
  }    /* End of send_query() */

+ static void perform_vacuum_analyze(db_info * dbi, tbl_info * tbl)
+ {
+     char        buf[256];
+     /*
+     * if relisshared = t and database
+     * != template1 then only do an
+     * analyze
+     */
+     if (tbl->relisshared > 0 && strcmp("template1", dbi->dbname))
+         snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
+     else
+     {
+         /*
+          * Since we are actually doing a vacuum only in this case, then lets
+          * go ahead and set the vacuum_cost variables
+          */
+         if(args->av_vacuum_cost_delay != -1)
+         {
+             snprintf(buf, sizeof(buf), "set vacuum_cost_delay = %i", args->av_vacuum_cost_delay);
+             send_query(buf, dbi);
+         }
+         if(args->av_vacuum_cost_page_hit != -1)
+         {
+             snprintf(buf, sizeof(buf), "set vacuum_cost_delay = %i", args->av_vacuum_cost_page_hit);
+             send_query(buf, dbi);
+         }
+         if(args->av_vacuum_cost_page_miss != -1)
+         {
+             snprintf(buf, sizeof(buf), "set vacuum_cost_page_miss = %i", args->av_vacuum_cost_page_miss);
+             send_query(buf, dbi);
+         }
+         if(args->av_vacuum_cost_page_dirty != -1)
+         {
+             snprintf(buf, sizeof(buf), "set vacuum_cost_page_dirty = %i", args->av_vacuum_cost_page_dirty);
+             send_query(buf, dbi);
+         }
+         if(args->av_vacuum_cost_limit != -1)
+         {
+             snprintf(buf, sizeof(buf), "set vacuum_cost_limit = %i", args->av_vacuum_cost_limit);
+             send_query(buf, dbi);
+         }
+
+         /* Set buf to the actual vaccum command */
+         snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);
+     }
+
+     if (args->debug >= 1)
+     {
+         sprintf(logbuffer, "Performing: %s", buf);
+         log_entry(logbuffer, LVL_DEBUG);
+         fflush(LOGOUTPUT);
+     }
+
+     send_query(buf, dbi);
+     update_table_thresholds(dbi, tbl, VACUUM_ANALYZE);
+     if (args->debug >= 2)
+         print_table_info(tbl);
+
+ }
+
+ static void perform_analyze(db_info * dbi, tbl_info * tbl)
+ {
+     char        buf[256];
+     snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
+     if (args->debug >= 1)
+     {
+         sprintf(logbuffer, "Performing: %s", buf);
+         log_entry(logbuffer, LVL_DEBUG);
+         fflush(LOGOUTPUT);
+     }
+     send_query(buf, dbi);
+     update_table_thresholds(dbi, tbl, ANALYZE_ONLY);
+     if (args->debug >= 2)
+         print_table_info(tbl);
+ }
+

  static void
  free_cmd_args(void)
***************
*** 1015,1027 ****
      args->port = 0;

      /*
       * Fixme: Should add some sanity checking such as positive integer
       * values etc
       */
  #ifndef WIN32
!     while ((c = getopt(argc, argv, "s:S:v:V:a:A:d:U:P:H:L:p:hD")) != -1)
  #else
!     while ((c = getopt(argc, argv, "s:S:v:V:a:A:d:U:P:H:L:p:hIRN:W:")) != -1)
  #endif
      {
          switch (c)
--- 1091,1112 ----
      args->port = 0;

      /*
+      * Cost-Based Vacuum Delay Settings for pg_autovacuum
+      */
+     args->av_vacuum_cost_delay = -1;
+     args->av_vacuum_cost_page_hit = -1;
+     args->av_vacuum_cost_page_miss = -1;
+     args->av_vacuum_cost_page_dirty = -1;
+     args->av_vacuum_cost_limit = -1;
+
+     /*
       * Fixme: Should add some sanity checking such as positive integer
       * values etc
       */
  #ifndef WIN32
!     while ((c = getopt(argc, argv, "s:S:v:V:a:A:d:U:P:H:L:p:hD:c:C:m:n:N:")) != -1)
  #else
!     while ((c = getopt(argc, argv, "s:S:v:V:a:A:d:U:P:H:L:p:hIRN:W:c:C:m:n:N:")) != -1)
  #endif
      {
          switch (c)
***************
*** 1044,1049 ****
--- 1129,1149 ----
              case 'A':
                  args->analyze_scaling_factor = atof(optarg);
                  break;
+             case 'c':
+                 args->av_vacuum_cost_delay = atoi(optarg);
+                 break;
+             case 'C':
+                 args->av_vacuum_cost_page_hit = atoi(optarg);
+                 break;
+             case 'm':
+                 args->av_vacuum_cost_page_miss = atoi(optarg);
+                 break;
+             case 'n':
+                 args->av_vacuum_cost_page_dirty = atoi(optarg);
+                 break;
+             case 'N':
+                 args->av_vacuum_cost_limit = atoi(optarg);
+                 break;
  #ifndef WIN32
              case 'D':
                  args->daemonize++;
***************
*** 1142,1147 ****
--- 1242,1253 ----

      fprintf(stderr, "   [-L] logfile (default=none)\n");

+     fprintf(stderr, "   [-c] vacuum_cost_delay (default=none)\n");
+     fprintf(stderr, "   [-C] vacuum_cost_page_hit (default=none)\n");
+     fprintf(stderr, "   [-m] vacuum_cost_page_miss (default=none)\n");
+     fprintf(stderr, "   [-n] vacuum_cost_page_dirty (default=none)\n");
+     fprintf(stderr, "   [-N] vacuum_cost_limit (default=none)\n");
+
      fprintf(stderr, "   [-U] username (libpq default)\n");
      fprintf(stderr, "   [-P] password (libpq default)\n");
      fprintf(stderr, "   [-H] host (libpq default)\n");
***************
*** 1191,1196 ****
--- 1297,1329 ----
      log_entry(logbuffer, LVL_INFO);
      sprintf(logbuffer, "  args->analyze_scaling_factor=%f", args->analyze_scaling_factor);
      log_entry(logbuffer, LVL_INFO);
+
+     if (args->av_vacuum_cost_delay != -1)
+         sprintf(logbuffer, "  args->av_vacuum_cost_delay=%i", args->av_vacuum_cost_delay);
+     else
+         sprintf(logbuffer, "  args->av_vacuum_cost_delay=(default)");
+     log_entry(logbuffer, LVL_INFO);
+     if(args->av_vacuum_cost_page_hit != -1)
+         sprintf(logbuffer, "  args->av_vacuum_cost_page_hit=%i", args->av_vacuum_cost_page_hit);
+     else
+         sprintf(logbuffer, "  args->av_vacuum_cost_page_hit=(default)");
+     log_entry(logbuffer, LVL_INFO);
+     if(args->av_vacuum_cost_page_miss != -1)
+         sprintf(logbuffer, "  args->av_vacuum_cost_page_miss=%i", args->av_vacuum_cost_page_miss);
+     else
+         sprintf(logbuffer, "  args->av_vacuum_cost_page_miss=(default)");
+     log_entry(logbuffer, LVL_INFO);
+     if(args->av_vacuum_cost_page_dirty != -1)
+         sprintf(logbuffer, "  args->av_vacuum_cost_page_dirty=%i", args->av_vacuum_cost_page_dirty);
+     else
+         sprintf(logbuffer, "  args->av_vacuum_cost_page_dirty=(default)");
+     log_entry(logbuffer, LVL_INFO);
+     if(args->av_vacuum_cost_limit != -1)
+         sprintf(logbuffer, "  args->av_vacuum_cost_limit=%i", args->av_vacuum_cost_limit);
+     else
+         sprintf(logbuffer, "  args->av_vacuum_cost_limit=(default)");
+     log_entry(logbuffer, LVL_INFO);
+
      sprintf(logbuffer, "  args->debug=%i", args->debug);
      log_entry(logbuffer, LVL_INFO);

***************
*** 1366,1372 ****
  int
  VacuumLoop(int argc, char **argv)
  {
-     char        buf[256];
      int            j = 0,
                  loops = 0;

--- 1499,1504 ----
***************
*** 1516,1556 ****
                                       * analyze
                                       */
                                      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);
!                                             log_entry(logbuffer, LVL_DEBUG);
!                                             fflush(LOGOUTPUT);
!                                         }
!                                         send_query(buf, dbs);
!                                         update_table_thresholds(dbs, tbl, VACUUM_ANALYZE);
!                                         if (args->debug >= 2)
!                                             print_table_info(tbl);
!                                     }
                                      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);
!                                             log_entry(logbuffer, LVL_DEBUG);
!                                             fflush(LOGOUTPUT);
!                                         }
!                                         send_query(buf, dbs);
!                                         update_table_thresholds(dbs, tbl, ANALYZE_ONLY);
!                                         if (args->debug >= 2)
!                                             print_table_info(tbl);
!                                     }

                                      break;        /* once we have found a
                                                   * match, no need to keep
--- 1648,1656 ----
                                       * analyze
                                       */
                                      if (tbl->curr_vacuum_count - tbl->CountAtLastVacuum >= tbl->vacuum_threshold)
!                                         perform_vacuum_analyze(dbs, tbl);
                                      else if (tbl->curr_analyze_count - tbl->CountAtLastAnalyze >=
tbl->analyze_threshold)
!                                         perform_analyze(dbs, tbl);

                                      break;        /* once we have found a
                                                   * match, no need to keep
*** ./pg_autovacuum.h.orig    2004-10-26 00:00:00.000000000 -0400
--- ./pg_autovacuum.h    2004-10-26 00:10:08.000000000 -0400
***************
*** 46,51 ****
--- 46,61 ----
                  analyze_base_threshold,
                  sleep_base_value,
                  debug,
+
+                 /*
+                  * Cost-Based Vacuum Delay Settings for pg_autovacuum
+                  */
+                 av_vacuum_cost_delay,
+                 av_vacuum_cost_page_hit,
+                 av_vacuum_cost_page_miss,
+                 av_vacuum_cost_page_dirty,
+                 av_vacuum_cost_limit,
+
  #ifndef WIN32
                  daemonize;
  #else
***************
*** 64,69 ****
--- 74,80 ----
                 *host,
                 *logfile,
                 *port;
+
  } cmd_args;

  /*
*** ./README.pg_autovacuum.orig    2004-10-26 00:10:38.000000000 -0400
--- ./README.pg_autovacuum    2004-10-26 00:17:56.000000000 -0400
***************
*** 133,138 ****
--- 133,148 ----
  -p port: port used for connection.
  -h help: list of command line options.

+ The following 5 options tell autovacuum to set specific values for the cost
+ based vacuum settings.  If left unset, then the cluster default values will be
+ in effect.
+ -c    vacuum_cost_delay
+ -C  vacuum_cost_page_hit
+ -m    vacuum_cost_page_miss
+ -n    vacuum_cost_page_dirty
+ -N    vacuum_cost_limit
+
+
  Numerous arguments have default values defined in pg_autovacuum.h.  At
  the time of writing they are: