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