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: