pg_autovacuum vacuum cost variables patch v2 - Mailing list pgsql-patches

Ok, here is an updated version of the patch I submitted last night.
This patch now sets the appropriate vacuum cost variables for both
vacuum commands and analyze commands.   In addition I have added the new
vacuum cost options to the win32 InstallService function.

Please give it another look and apply if deemed acceptable.

Mathew T. O'Connor


*** ./pg_autovacuum.c.orig    2004-10-26 00:00:00.000000000 -0400
--- ./pg_autovacuum.c    2004-10-26 23:51:01.453522827 -0400
***************
*** 905,910 ****
--- 905,911 ----
          PQfinish(db_conn);
          db_conn = NULL;
      }
+
      return db_conn;
  }    /* end of db_connect() */

***************
*** 973,978 ****
--- 974,1044 ----
      return res;
  }    /* End of send_query() */

+ /*
+  * Perform either a vacuum or a vacuum analyze
+  */
+ static void perform_maintenance_command(db_info * dbi, tbl_info * tbl, int operation)
+ {
+     char        buf[256];
+
+     /*
+      * 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_page_hit = %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);
+     }
+
+     /*
+      * if ((relisshared = t and database != template1) or
+      * if operation = ANALYZE_ONLY)
+      * then only do an analyze
+      */
+     if ((tbl->relisshared > 0 && strcmp("template1", dbi->dbname)) || (operation == ANALYZE_ONLY))
+         snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
+     else if(operation == VACUUM_ANALYZE)
+         snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);
+     else
+         return 1;
+
+     if (args->debug >= 1)
+     {
+         sprintf(logbuffer, "Performing: %s", buf);
+         log_entry(logbuffer, LVL_DEBUG);
+         fflush(LOGOUTPUT);
+     }
+
+     send_query(buf, dbi);
+     if(operation == VACUUM_ANALYZE)
+         update_table_thresholds(dbi, tbl, VACUUM_ANALYZE);
+     else if(operation == VACUUM_ANALYZE)
+         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)
--- 1081,1102 ----
      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 ****
--- 1119,1139 ----
              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 ****
--- 1232,1243 ----

      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 ****
--- 1287,1319 ----
      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);

***************
*** 1298,1304 ****
          sprintf(szCommand, "%s -A %f", szCommand, args->analyze_scaling_factor);
      if (args->debug != (int) AUTOVACUUM_DEBUG)
          sprintf(szCommand, "%s -d %i", szCommand, args->debug);
!
      /* And write the new value */
      if (RegSetValueEx(hk, "ImagePath", 0, REG_EXPAND_SZ, (LPBYTE) szCommand, (DWORD) strlen(szCommand) + 1))
          return -4;
--- 1421,1437 ----
          sprintf(szCommand, "%s -A %f", szCommand, args->analyze_scaling_factor);
      if (args->debug != (int) AUTOVACUUM_DEBUG)
          sprintf(szCommand, "%s -d %i", szCommand, args->debug);
!     if (args->av_vacuum_cost_delay != -1)
!         sprintf(szCommand, "%s -d %i", szCommand, args->av_vacuum_cost_delay);
!     if(args->av_vacuum_cost_page_hit != -1)
!         sprintf(szCommand, "%s -d %i", szCommand, args->av_vacuum_cost_page_hit);
!     if(args->av_vacuum_cost_page_miss != -1)
!         sprintf(szCommand, "%s -d %i", szCommand, args->av_vacuum_cost_page_miss);
!     if(args->av_vacuum_cost_page_dirty != -1)
!         sprintf(szCommand, "%s -d %i", szCommand, args->av_vacuum_cost_page_dirty);
!     if(args->av_vacuum_cost_limit != -1)
!         sprintf(szCommand, "%s -d %i", szCommand, args->av_vacuum_cost_limit);
!
      /* And write the new value */
      if (RegSetValueEx(hk, "ImagePath", 0, REG_EXPAND_SZ, (LPBYTE) szCommand, (DWORD) strlen(szCommand) + 1))
          return -4;
***************
*** 1366,1372 ****
  int
  VacuumLoop(int argc, char **argv)
  {
-     char        buf[256];
      int            j = 0,
                  loops = 0;

--- 1499,1504 ----
***************
*** 1516,1560 ****
                                       * 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
!                                                  * checking. */
                                  }

                                  /*
--- 1648,1658 ----
                                       * analyze
                                       */
                                      if (tbl->curr_vacuum_count - tbl->CountAtLastVacuum >= tbl->vacuum_threshold)
!                                         perform_maintenance_command(dbs, tbl, VACUUM_ANALYZE);
                                      else if (tbl->curr_analyze_count - tbl->CountAtLastAnalyze >=
tbl->analyze_threshold)
!                                         perform_maintenance_command(dbs, tbl, ANALYZE_ONLY);
!
!                                     break;        /* We found a match, no need to keep looping. */
                                  }

                                  /*
*** ./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 23:44:10.862610900 -0400
***************
*** 133,138 ****
--- 133,148 ----
  -p port: port used for connection.
  -h help: list of command line options.

+ The following 5 autovacuum command line options correspond to the new cost
+ based vacuum settings.  If left unset, then the cluster default values will
+ be remain 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: