Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc47fc6d

Browse files
committed
Add options to pg_autovacuum to support setting the cost-based-vacuum
parameters differently from their default values. Matthew T. O'Connor
1 parent18b5835 commitc47fc6d

File tree

3 files changed

+185
-62
lines changed

3 files changed

+185
-62
lines changed

‎contrib/pg_autovacuum/README.pg_autovacuum

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -133,6 +133,17 @@ pg_autovacuum has the following optional arguments:
133133
-p port: port used for connection.
134134
-h help: list of command line options.
135135

136+
The following 5 autovacuum command line options correspond to the various
137+
cost-based vacuum settings. If not given, then the cluster default values
138+
will be used.
139+
140+
-cvacuum_cost_delay
141+
-Cvacuum_cost_page_hit
142+
-mvacuum_cost_page_miss
143+
-nvacuum_cost_page_dirty
144+
-Nvacuum_cost_limit
145+
146+
136147
Numerous arguments have default values defined in pg_autovacuum.h. At
137148
the time of writing they are:
138149

‎contrib/pg_autovacuum/pg_autovacuum.c

Lines changed: 163 additions & 61 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* Revisions by Christopher B. Browne, Liberty RMS
55
* Win32 Service code added by Dave Page
66
*
7-
* $PostgreSQL: pgsql/contrib/pg_autovacuum/pg_autovacuum.c,v 1.23 2004/10/25 02:14:59 tgl Exp $
7+
* $PostgreSQL: pgsql/contrib/pg_autovacuum/pg_autovacuum.c,v 1.24 2004/11/17 16:54:15 tgl Exp $
88
*/
99

1010
#include"postgres_fe.h"
@@ -530,7 +530,7 @@ print_table_info(tbl_info * tbl)
530530
{
531531
sprintf(logbuffer," table name: %s.%s",tbl->dbi->dbname,tbl->table_name);
532532
log_entry(logbuffer,LVL_INFO);
533-
sprintf(logbuffer," relid: %u; relisshared: %i",tbl->relid,tbl->relisshared);
533+
sprintf(logbuffer," relid: %u; relisshared: %d",tbl->relid,tbl->relisshared);
534534
log_entry(logbuffer,LVL_INFO);
535535
sprintf(logbuffer," reltuples: %f; relpages: %u",tbl->reltuples,tbl->relpages);
536536
log_entry(logbuffer,LVL_INFO);
@@ -905,6 +905,7 @@ db_connect(db_info * dbi)
905905
PQfinish(db_conn);
906906
db_conn=NULL;
907907
}
908+
908909
returndb_conn;
909910
}/* end of db_connect() */
910911

@@ -973,6 +974,75 @@ send_query(const char *query, db_info * dbi)
973974
returnres;
974975
}/* End of send_query() */
975976

977+
/*
978+
* Perform either a vacuum or a vacuum analyze
979+
*/
980+
staticvoid
981+
perform_maintenance_command(db_info*dbi,tbl_info*tbl,intoperation)
982+
{
983+
charbuf[256];
984+
985+
/*
986+
* Set the vacuum_cost variables if supplied on command line
987+
*/
988+
if (args->av_vacuum_cost_delay!=-1)
989+
{
990+
snprintf(buf,sizeof(buf),"set vacuum_cost_delay = %d",
991+
args->av_vacuum_cost_delay);
992+
send_query(buf,dbi);
993+
}
994+
if (args->av_vacuum_cost_page_hit!=-1)
995+
{
996+
snprintf(buf,sizeof(buf),"set vacuum_cost_page_hit = %d",
997+
args->av_vacuum_cost_page_hit);
998+
send_query(buf,dbi);
999+
}
1000+
if (args->av_vacuum_cost_page_miss!=-1)
1001+
{
1002+
snprintf(buf,sizeof(buf),"set vacuum_cost_page_miss = %d",
1003+
args->av_vacuum_cost_page_miss);
1004+
send_query(buf,dbi);
1005+
}
1006+
if (args->av_vacuum_cost_page_dirty!=-1)
1007+
{
1008+
snprintf(buf,sizeof(buf),"set vacuum_cost_page_dirty = %d",
1009+
args->av_vacuum_cost_page_dirty);
1010+
send_query(buf,dbi);
1011+
}
1012+
if (args->av_vacuum_cost_limit!=-1)
1013+
{
1014+
snprintf(buf,sizeof(buf),"set vacuum_cost_limit = %d",
1015+
args->av_vacuum_cost_limit);
1016+
send_query(buf,dbi);
1017+
}
1018+
1019+
/*
1020+
* if ((relisshared = t and database != template1) or
1021+
* if operation = ANALYZE_ONLY)
1022+
* then only do an analyze
1023+
*/
1024+
if ((tbl->relisshared>0&&strcmp("template1",dbi->dbname)!=0)||
1025+
(operation==ANALYZE_ONLY))
1026+
snprintf(buf,sizeof(buf),"ANALYZE %s",tbl->table_name);
1027+
elseif (operation==VACUUM_ANALYZE)
1028+
snprintf(buf,sizeof(buf),"VACUUM ANALYZE %s",tbl->table_name);
1029+
else
1030+
return;
1031+
1032+
if (args->debug >=1)
1033+
{
1034+
sprintf(logbuffer,"Performing: %s",buf);
1035+
log_entry(logbuffer,LVL_DEBUG);
1036+
fflush(LOGOUTPUT);
1037+
}
1038+
1039+
send_query(buf,dbi);
1040+
1041+
update_table_thresholds(dbi,tbl,operation);
1042+
1043+
if (args->debug >=2)
1044+
print_table_info(tbl);
1045+
}
9761046

9771047
staticvoid
9781048
free_cmd_args(void)
@@ -1014,14 +1084,23 @@ get_cmd_args(int argc, char *argv[])
10141084
args->logfile=0;
10151085
args->port=0;
10161086

1087+
/*
1088+
* Cost-Based Vacuum Delay Settings for pg_autovacuum
1089+
*/
1090+
args->av_vacuum_cost_delay=-1;
1091+
args->av_vacuum_cost_page_hit=-1;
1092+
args->av_vacuum_cost_page_miss=-1;
1093+
args->av_vacuum_cost_page_dirty=-1;
1094+
args->av_vacuum_cost_limit=-1;
1095+
10171096
/*
10181097
* Fixme: Should add some sanity checking such as positive integer
10191098
* values etc
10201099
*/
10211100
#ifndefWIN32
1022-
while ((c=getopt(argc,argv,"s:S:v:V:a:A:d:U:P:H:L:p:hD"))!=-1)
1101+
while ((c=getopt(argc,argv,"s:S:v:V:a:A:d:U:P:H:L:p:hD:c:C:m:n:N:"))!=-1)
10231102
#else
1024-
while ((c=getopt(argc,argv,"s:S:v:V:a:A:d:U:P:H:L:p:hIRN:W:"))!=-1)
1103+
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)
10251104
#endif
10261105
{
10271106
switch (c)
@@ -1044,6 +1123,21 @@ get_cmd_args(int argc, char *argv[])
10441123
case'A':
10451124
args->analyze_scaling_factor=atof(optarg);
10461125
break;
1126+
case'c':
1127+
args->av_vacuum_cost_delay=atoi(optarg);
1128+
break;
1129+
case'C':
1130+
args->av_vacuum_cost_page_hit=atoi(optarg);
1131+
break;
1132+
case'm':
1133+
args->av_vacuum_cost_page_miss=atoi(optarg);
1134+
break;
1135+
case'n':
1136+
args->av_vacuum_cost_page_dirty=atoi(optarg);
1137+
break;
1138+
case'N':
1139+
args->av_vacuum_cost_limit=atoi(optarg);
1140+
break;
10471141
#ifndefWIN32
10481142
case'D':
10491143
args->daemonize++;
@@ -1124,24 +1218,30 @@ usage(void)
11241218
fprintf(stderr," [-W] Password to run service with (only useful when installing as a Windows service)\n");
11251219
#endif
11261220
i=AUTOVACUUM_DEBUG;
1127-
fprintf(stderr," [-d] debug (debug level=0,1,2,3; default=%i)\n",i);
1221+
fprintf(stderr," [-d] debug (debug level=0,1,2,3; default=%d)\n",i);
11281222

11291223
i=SLEEPBASEVALUE;
1130-
fprintf(stderr," [-s] sleep base value (default=%i)\n",i);
1224+
fprintf(stderr," [-s] sleep base value (default=%d)\n",i);
11311225
f=SLEEPSCALINGFACTOR;
11321226
fprintf(stderr," [-S] sleep scaling factor (default=%f)\n",f);
11331227

11341228
i=VACBASETHRESHOLD;
1135-
fprintf(stderr," [-v] vacuum base threshold (default=%i)\n",i);
1229+
fprintf(stderr," [-v] vacuum base threshold (default=%d)\n",i);
11361230
f=VACSCALINGFACTOR;
11371231
fprintf(stderr," [-V] vacuum scaling factor (default=%f)\n",f);
11381232
i=i /2;
1139-
fprintf(stderr," [-a] analyze base threshold (default=%i)\n",i);
1233+
fprintf(stderr," [-a] analyze base threshold (default=%d)\n",i);
11401234
f=f /2;
11411235
fprintf(stderr," [-A] analyze scaling factor (default=%f)\n",f);
11421236

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

1239+
fprintf(stderr," [-c] vacuum_cost_delay (default=none)\n");
1240+
fprintf(stderr," [-C] vacuum_cost_page_hit (default=none)\n");
1241+
fprintf(stderr," [-m] vacuum_cost_page_miss (default=none)\n");
1242+
fprintf(stderr," [-n] vacuum_cost_page_dirty (default=none)\n");
1243+
fprintf(stderr," [-N] vacuum_cost_limit (default=none)\n");
1244+
11451245
fprintf(stderr," [-U] username (libpq default)\n");
11461246
fprintf(stderr," [-P] password (libpq default)\n");
11471247
fprintf(stderr," [-H] host (libpq default)\n");
@@ -1166,32 +1266,59 @@ print_cmd_args(void)
11661266
sprintf(logbuffer," args->logfile=%s", (args->logfile) ?args->logfile :"(null)");
11671267
log_entry(logbuffer,LVL_INFO);
11681268
#ifndefWIN32
1169-
sprintf(logbuffer," args->daemonize=%i",args->daemonize);
1269+
sprintf(logbuffer," args->daemonize=%d",args->daemonize);
11701270
log_entry(logbuffer,LVL_INFO);
11711271
#else
1172-
sprintf(logbuffer," args->install_as_service=%i",args->install_as_service);
1272+
sprintf(logbuffer," args->install_as_service=%d",args->install_as_service);
11731273
log_entry(logbuffer,LVL_INFO);
1174-
sprintf(logbuffer," args->remove_as_service=%i",args->remove_as_service);
1274+
sprintf(logbuffer," args->remove_as_service=%d",args->remove_as_service);
11751275
log_entry(logbuffer,LVL_INFO);
11761276
sprintf(logbuffer," args->service_user=%s", (args->service_user) ?args->service_user :"(null)");
11771277
log_entry(logbuffer,LVL_INFO);
11781278
sprintf(logbuffer," args->service_password=%s", (args->service_password) ?args->service_password :"(null)");
11791279
log_entry(logbuffer,LVL_INFO);
11801280
#endif
11811281

1182-
sprintf(logbuffer," args->sleep_base_value=%i",args->sleep_base_value);
1282+
sprintf(logbuffer," args->sleep_base_value=%d",args->sleep_base_value);
11831283
log_entry(logbuffer,LVL_INFO);
11841284
sprintf(logbuffer," args->sleep_scaling_factor=%f",args->sleep_scaling_factor);
11851285
log_entry(logbuffer,LVL_INFO);
1186-
sprintf(logbuffer," args->vacuum_base_threshold=%i",args->vacuum_base_threshold);
1286+
sprintf(logbuffer," args->vacuum_base_threshold=%d",args->vacuum_base_threshold);
11871287
log_entry(logbuffer,LVL_INFO);
11881288
sprintf(logbuffer," args->vacuum_scaling_factor=%f",args->vacuum_scaling_factor);
11891289
log_entry(logbuffer,LVL_INFO);
1190-
sprintf(logbuffer," args->analyze_base_threshold=%i",args->analyze_base_threshold);
1290+
sprintf(logbuffer," args->analyze_base_threshold=%d",args->analyze_base_threshold);
11911291
log_entry(logbuffer,LVL_INFO);
11921292
sprintf(logbuffer," args->analyze_scaling_factor=%f",args->analyze_scaling_factor);
11931293
log_entry(logbuffer,LVL_INFO);
1194-
sprintf(logbuffer," args->debug=%i",args->debug);
1294+
1295+
if (args->av_vacuum_cost_delay!=-1)
1296+
sprintf(logbuffer," args->av_vacuum_cost_delay=%d",args->av_vacuum_cost_delay);
1297+
else
1298+
sprintf(logbuffer," args->av_vacuum_cost_delay=(default)");
1299+
log_entry(logbuffer,LVL_INFO);
1300+
if (args->av_vacuum_cost_page_hit!=-1)
1301+
sprintf(logbuffer," args->av_vacuum_cost_page_hit=%d",args->av_vacuum_cost_page_hit);
1302+
else
1303+
sprintf(logbuffer," args->av_vacuum_cost_page_hit=(default)");
1304+
log_entry(logbuffer,LVL_INFO);
1305+
if (args->av_vacuum_cost_page_miss!=-1)
1306+
sprintf(logbuffer," args->av_vacuum_cost_page_miss=%d",args->av_vacuum_cost_page_miss);
1307+
else
1308+
sprintf(logbuffer," args->av_vacuum_cost_page_miss=(default)");
1309+
log_entry(logbuffer,LVL_INFO);
1310+
if (args->av_vacuum_cost_page_dirty!=-1)
1311+
sprintf(logbuffer," args->av_vacuum_cost_page_dirty=%d",args->av_vacuum_cost_page_dirty);
1312+
else
1313+
sprintf(logbuffer," args->av_vacuum_cost_page_dirty=(default)");
1314+
log_entry(logbuffer,LVL_INFO);
1315+
if (args->av_vacuum_cost_limit!=-1)
1316+
sprintf(logbuffer," args->av_vacuum_cost_limit=%d",args->av_vacuum_cost_limit);
1317+
else
1318+
sprintf(logbuffer," args->av_vacuum_cost_limit=(default)");
1319+
log_entry(logbuffer,LVL_INFO);
1320+
1321+
sprintf(logbuffer," args->debug=%d",args->debug);
11951322
log_entry(logbuffer,LVL_INFO);
11961323

11971324
fflush(LOGOUTPUT);
@@ -1285,20 +1412,30 @@ InstallService()
12851412
if (args->logfile)
12861413
sprintf(szCommand,"%s -L %s",szCommand,args->logfile);
12871414
if (args->sleep_base_value!= (int)SLEEPBASEVALUE)
1288-
sprintf(szCommand,"%s -s %i",szCommand,args->sleep_base_value);
1415+
sprintf(szCommand,"%s -s %d",szCommand,args->sleep_base_value);
12891416
if (args->sleep_scaling_factor!= (float)SLEEPSCALINGFACTOR)
12901417
sprintf(szCommand,"%s -S %f",szCommand,args->sleep_scaling_factor);
12911418
if (args->vacuum_base_threshold!= (int)VACBASETHRESHOLD)
1292-
sprintf(szCommand,"%s -v %i",szCommand,args->vacuum_base_threshold);
1419+
sprintf(szCommand,"%s -v %d",szCommand,args->vacuum_base_threshold);
12931420
if (args->vacuum_scaling_factor!= (float)VACSCALINGFACTOR)
12941421
sprintf(szCommand,"%s -V %f",szCommand,args->vacuum_scaling_factor);
12951422
if (args->analyze_base_threshold!= (int) (VACBASETHRESHOLD /2))
1296-
sprintf(szCommand,"%s -a %i",szCommand,args->analyze_base_threshold);
1423+
sprintf(szCommand,"%s -a %d",szCommand,args->analyze_base_threshold);
12971424
if (args->analyze_scaling_factor!= (float) (VACSCALINGFACTOR /2))
12981425
sprintf(szCommand,"%s -A %f",szCommand,args->analyze_scaling_factor);
12991426
if (args->debug!= (int)AUTOVACUUM_DEBUG)
1300-
sprintf(szCommand,"%s -d %i",szCommand,args->debug);
1301-
1427+
sprintf(szCommand,"%s -d %d",szCommand,args->debug);
1428+
if (args->av_vacuum_cost_delay!=-1)
1429+
sprintf(szCommand,"%s -d %d",szCommand,args->av_vacuum_cost_delay);
1430+
if (args->av_vacuum_cost_page_hit!=-1)
1431+
sprintf(szCommand,"%s -d %d",szCommand,args->av_vacuum_cost_page_hit);
1432+
if (args->av_vacuum_cost_page_miss!=-1)
1433+
sprintf(szCommand,"%s -d %d",szCommand,args->av_vacuum_cost_page_miss);
1434+
if (args->av_vacuum_cost_page_dirty!=-1)
1435+
sprintf(szCommand,"%s -d %d",szCommand,args->av_vacuum_cost_page_dirty);
1436+
if (args->av_vacuum_cost_limit!=-1)
1437+
sprintf(szCommand,"%s -d %d",szCommand,args->av_vacuum_cost_limit);
1438+
13021439
/* And write the new value */
13031440
if (RegSetValueEx(hk,"ImagePath",0,REG_EXPAND_SZ, (LPBYTE)szCommand, (DWORD)strlen(szCommand)+1))
13041441
return-4;
@@ -1366,7 +1503,6 @@ static
13661503
int
13671504
VacuumLoop(intargc,char**argv)
13681505
{
1369-
charbuf[256];
13701506
intj=0,
13711507
loops=0;
13721508

@@ -1516,45 +1652,11 @@ VacuumLoop(int argc, char **argv)
15161652
* analyze
15171653
*/
15181654
if (tbl->curr_vacuum_count-tbl->CountAtLastVacuum >=tbl->vacuum_threshold)
1519-
{
1520-
/*
1521-
* if relisshared = t and database
1522-
* != template1 then only do an
1523-
* analyze
1524-
*/
1525-
if (tbl->relisshared>0&&strcmp("template1",dbs->dbname))
1526-
snprintf(buf,sizeof(buf),"ANALYZE %s",tbl->table_name);
1527-
else
1528-
snprintf(buf,sizeof(buf),"VACUUM ANALYZE %s",tbl->table_name);
1529-
if (args->debug >=1)
1530-
{
1531-
sprintf(logbuffer,"Performing: %s",buf);
1532-
log_entry(logbuffer,LVL_DEBUG);
1533-
fflush(LOGOUTPUT);
1534-
}
1535-
send_query(buf,dbs);
1536-
update_table_thresholds(dbs,tbl,VACUUM_ANALYZE);
1537-
if (args->debug >=2)
1538-
print_table_info(tbl);
1539-
}
1655+
perform_maintenance_command(dbs,tbl,VACUUM_ANALYZE);
15401656
elseif (tbl->curr_analyze_count-tbl->CountAtLastAnalyze >=tbl->analyze_threshold)
1541-
{
1542-
snprintf(buf,sizeof(buf),"ANALYZE %s",tbl->table_name);
1543-
if (args->debug >=1)
1544-
{
1545-
sprintf(logbuffer,"Performing: %s",buf);
1546-
log_entry(logbuffer,LVL_DEBUG);
1547-
fflush(LOGOUTPUT);
1548-
}
1549-
send_query(buf,dbs);
1550-
update_table_thresholds(dbs,tbl,ANALYZE_ONLY);
1551-
if (args->debug >=2)
1552-
print_table_info(tbl);
1553-
}
1554-
1555-
break;/* once we have found a
1556-
* match, no need to keep
1557-
* checking. */
1657+
perform_maintenance_command(dbs,tbl,ANALYZE_ONLY);
1658+
1659+
break;/* We found a match, no need to keep looping. */
15581660
}
15591661

15601662
/*
@@ -1566,7 +1668,7 @@ VacuumLoop(int argc, char **argv)
15661668
}/* end for table while loop */
15671669
}/* end for j loop (tuples in PGresult) */
15681670
}/* end if (res != NULL) */
1569-
}/* close of if(xid_wraparound_check()) */
1671+
}/* close of if(xid_wraparound_check()) */
15701672
/* Done working on this db, Clean up, then advance cur_db */
15711673
PQclear(res);
15721674
res=NULL;
@@ -1585,7 +1687,7 @@ VacuumLoop(int argc, char **argv)
15851687
if (args->debug >=2)
15861688
{
15871689
sprintf(logbuffer,
1588-
"%i All DBs checked in: %.0f usec, will sleep for %i secs.",
1690+
"%d All DBs checked in: %.0f usec, will sleep for %d secs.",
15891691
loops,diff,sleep_secs);
15901692
log_entry(logbuffer,LVL_DEBUG);
15911693
fflush(LOGOUTPUT);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp