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:
  
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to