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

Commitc92c3d5

Browse files
committed
vacuumdb: Add option --analyze-in-stages
Add vacuumdb option --analyze-in-stages which runs ANALYZE three timeswith different configuration settings, adopting the logic from theanalyze_new_cluster.sh script that pg_upgrade generates. That way,users of pg_dump/pg_restore can also use that functionality.Change pg_upgrade to create the script so that it calls vacuumdb insteadof implementing the logic itself.
1 parent0d3b2b8 commitc92c3d5

File tree

4 files changed

+94
-70
lines changed

4 files changed

+94
-70
lines changed

‎contrib/pg_upgrade/check.c

Lines changed: 4 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -565,62 +565,12 @@ create_script_for_cluster_analyze(char **analyze_script_file_name)
565565
"--analyze-only" :"--analyze",ECHO_QUOTE);
566566
fprintf(script,"echo%s\n\n",ECHO_BLANK);
567567

568-
#ifndefWIN32
569-
fprintf(script,"sleep 2\n");
570-
fprintf(script,"PGOPTIONS='-c default_statistics_target=1 -c vacuum_cost_delay=0'\n");
571-
/* only need to export once */
572-
fprintf(script,"export PGOPTIONS\n");
573-
#else
574-
fprintf(script,"REM simulate sleep 2\n");
575-
fprintf(script,"PING 1.1.1.1 -n 1 -w 2000 > nul\n");
576-
fprintf(script,"SET PGOPTIONS=-c default_statistics_target=1 -c vacuum_cost_delay=0\n");
577-
#endif
578-
579-
fprintf(script,"echo %sGenerating minimal optimizer statistics (1 target)%s\n",
580-
ECHO_QUOTE,ECHO_QUOTE);
581-
fprintf(script,"echo %s--------------------------------------------------%s\n",
582-
ECHO_QUOTE,ECHO_QUOTE);
583-
fprintf(script,"\"%s/vacuumdb\" %s--all --analyze-only\n",
584-
new_cluster.bindir,user_specification);
585-
fprintf(script,"echo%s\n",ECHO_BLANK);
586-
fprintf(script,"echo %sThe server is now available with minimal optimizer statistics.%s\n",
587-
ECHO_QUOTE,ECHO_QUOTE);
588-
fprintf(script,"echo %sQuery performance will be optimal once this script completes.%s\n",
589-
ECHO_QUOTE,ECHO_QUOTE);
590-
fprintf(script,"echo%s\n\n",ECHO_BLANK);
591-
592-
#ifndefWIN32
593-
fprintf(script,"sleep 2\n");
594-
fprintf(script,"PGOPTIONS='-c default_statistics_target=10'\n");
595-
#else
596-
fprintf(script,"REM simulate sleep\n");
597-
fprintf(script,"PING 1.1.1.1 -n 1 -w 2000 > nul\n");
598-
fprintf(script,"SET PGOPTIONS=-c default_statistics_target=10\n");
599-
#endif
600-
601-
fprintf(script,"echo %sGenerating medium optimizer statistics (10 targets)%s\n",
602-
ECHO_QUOTE,ECHO_QUOTE);
603-
fprintf(script,"echo %s---------------------------------------------------%s\n",
604-
ECHO_QUOTE,ECHO_QUOTE);
605-
fprintf(script,"\"%s/vacuumdb\" %s--all --analyze-only\n",
568+
fprintf(script,"\"%s/vacuumdb\" %s--all --analyze-in-stages\n",
606569
new_cluster.bindir,user_specification);
607-
fprintf(script,"echo%s\n\n",ECHO_BLANK);
608-
609-
#ifndefWIN32
610-
fprintf(script,"unset PGOPTIONS\n");
611-
#else
612-
fprintf(script,"SET PGOPTIONS\n");
613-
#endif
614-
615-
fprintf(script,"echo %sGenerating default (full) optimizer statistics (100 targets?)%s\n",
616-
ECHO_QUOTE,ECHO_QUOTE);
617-
fprintf(script,"echo %s-------------------------------------------------------------%s\n",
618-
ECHO_QUOTE,ECHO_QUOTE);
619-
fprintf(script,"\"%s/vacuumdb\" %s--all %s\n",new_cluster.bindir,
620-
user_specification,
621570
/* Did we copy the free space files? */
622-
(GET_MAJOR_VERSION(old_cluster.major_version) >=804) ?
623-
"--analyze-only" :"--analyze");
571+
if (GET_MAJOR_VERSION(old_cluster.major_version)<804)
572+
fprintf(script,"\"%s/vacuumdb\" %s--all\n",new_cluster.bindir,
573+
user_specification);
624574

625575
fprintf(script,"echo%s\n\n",ECHO_BLANK);
626576
fprintf(script,"echo %sDone%s\n",

‎doc/src/sgml/ref/vacuumdb.sgml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -203,6 +203,26 @@ PostgreSQL documentation
203203
</listitem>
204204
</varlistentry>
205205

206+
<varlistentry>
207+
<term><option>--analyze-in-stages</option></term>
208+
<listitem>
209+
<para>
210+
Only calculate statistics for use by the optimizer (no vacuum),
211+
like <option>--analyze-only</option>. Run several (currently three)
212+
stages of analyze with different configuration settings, to produce
213+
usable statistics faster.
214+
</para>
215+
216+
<para>
217+
This option is useful to analyze a database that was newly populated
218+
from a restored dump or by <command>pg_upgrade</command>. This option
219+
will try to create some statistics as fast as possible, to make the
220+
database usable, and then produce full statistics in the subsequent
221+
stages.
222+
</para>
223+
</listitem>
224+
</varlistentry>
225+
206226
<varlistentry>
207227
<term><option>-?</></term>
208228
<term><option>--help</></term>
Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
use strict;
2+
use warnings;
3+
use TestLib;
4+
use Test::Moretests=> 1;
5+
6+
my$tempdir = tempdir;
7+
start_test_server$tempdir;
8+
9+
issues_sql_like(['vacuumdb','--analyze-in-stages','postgres'],
10+
qr/.*statement:\SET\default_statistics_target=1;\SET\vacuum_cost_delay=0;
11+
.*statement:\ANALYZE.*
12+
.*statement:\SET\default_statistics_target=10;\RESET\vacuum_cost_delay;
13+
.*statement:\ANALYZE.*
14+
.*statement:\RESET\default_statistics_target;
15+
.*statement:\ANALYZE/sx,
16+
'analyze three times');

‎src/bin/scripts/vacuumdb.c

Lines changed: 54 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -16,12 +16,12 @@
1616

1717

1818
staticvoidvacuum_one_database(constchar*dbname,boolfull,boolverbose,
19-
booland_analyze,boolanalyze_only,boolfreeze,
19+
booland_analyze,boolanalyze_only,boolanalyze_in_stages,boolfreeze,
2020
constchar*table,constchar*host,constchar*port,
2121
constchar*username,enumtrivalueprompt_password,
2222
constchar*progname,boolecho);
2323
staticvoidvacuum_all_databases(boolfull,boolverbose,booland_analyze,
24-
boolanalyze_only,boolfreeze,
24+
boolanalyze_only,boolanalyze_in_stages,boolfreeze,
2525
constchar*maintenance_db,
2626
constchar*host,constchar*port,
2727
constchar*username,enumtrivalueprompt_password,
@@ -50,6 +50,7 @@ main(int argc, char *argv[])
5050
{"full",no_argument,NULL,'f'},
5151
{"verbose",no_argument,NULL,'v'},
5252
{"maintenance-db",required_argument,NULL,2},
53+
{"analyze-in-stages",no_argument,NULL,3},
5354
{NULL,0,NULL,0}
5455
};
5556

@@ -67,6 +68,7 @@ main(int argc, char *argv[])
6768
boolquiet= false;
6869
booland_analyze= false;
6970
boolanalyze_only= false;
71+
boolanalyze_in_stages= false;
7072
boolfreeze= false;
7173
boolalldb= false;
7274
boolfull= false;
@@ -130,6 +132,9 @@ main(int argc, char *argv[])
130132
case2:
131133
maintenance_db=pg_strdup(optarg);
132134
break;
135+
case3:
136+
analyze_in_stages=analyze_only= true;
137+
break;
133138
default:
134139
fprintf(stderr,_("Try \"%s --help\" for more information.\n"),progname);
135140
exit(1);
@@ -189,7 +194,7 @@ main(int argc, char *argv[])
189194
exit(1);
190195
}
191196

192-
vacuum_all_databases(full,verbose,and_analyze,analyze_only,freeze,
197+
vacuum_all_databases(full,verbose,and_analyze,analyze_only,analyze_in_stages,freeze,
193198
maintenance_db,host,port,username,
194199
prompt_password,progname,echo,quiet);
195200
}
@@ -212,15 +217,15 @@ main(int argc, char *argv[])
212217
for (cell=tables.head;cell;cell=cell->next)
213218
{
214219
vacuum_one_database(dbname,full,verbose,and_analyze,
215-
analyze_only,
220+
analyze_only,analyze_in_stages,
216221
freeze,cell->val,
217222
host,port,username,prompt_password,
218223
progname,echo);
219224
}
220225
}
221226
else
222227
vacuum_one_database(dbname,full,verbose,and_analyze,
223-
analyze_only,
228+
analyze_only,analyze_in_stages,
224229
freeze,NULL,
225230
host,port,username,prompt_password,
226231
progname,echo);
@@ -230,9 +235,26 @@ main(int argc, char *argv[])
230235
}
231236

232237

238+
staticvoid
239+
run_vacuum_command(PGconn*conn,constchar*sql,boolecho,constchar*dbname,constchar*table,constchar*progname)
240+
{
241+
if (!executeMaintenanceCommand(conn,sql,echo))
242+
{
243+
if (table)
244+
fprintf(stderr,_("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
245+
progname,table,dbname,PQerrorMessage(conn));
246+
else
247+
fprintf(stderr,_("%s: vacuuming of database \"%s\" failed: %s"),
248+
progname,dbname,PQerrorMessage(conn));
249+
PQfinish(conn);
250+
exit(1);
251+
}
252+
}
253+
254+
233255
staticvoid
234256
vacuum_one_database(constchar*dbname,boolfull,boolverbose,booland_analyze,
235-
boolanalyze_only,boolfreeze,constchar*table,
257+
boolanalyze_only,boolanalyze_in_stages,boolfreeze,constchar*table,
236258
constchar*host,constchar*port,
237259
constchar*username,enumtrivalueprompt_password,
238260
constchar*progname,boolecho)
@@ -300,25 +322,38 @@ vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyz
300322
appendPQExpBuffer(&sql," %s",table);
301323
appendPQExpBufferStr(&sql,";");
302324

303-
if (!executeMaintenanceCommand(conn,sql.data,echo))
325+
if (analyze_in_stages)
304326
{
305-
if (table)
306-
fprintf(stderr,_("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
307-
progname,table,dbname,PQerrorMessage(conn));
308-
else
309-
fprintf(stderr,_("%s: vacuuming of database \"%s\" failed: %s"),
310-
progname,dbname,PQerrorMessage(conn));
311-
PQfinish(conn);
312-
exit(1);
327+
constchar*stage_commands[]= {
328+
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
329+
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
330+
"RESET default_statistics_target;"
331+
};
332+
constchar*stage_messages[]= {
333+
gettext_noop("Generating minimal optimizer statistics (1 target)"),
334+
gettext_noop("Generating medium optimizer statistics (10 targets)"),
335+
gettext_noop("Generating default (full) optimizer statistics")
336+
};
337+
inti;
338+
339+
for (i=0;i<3;i++)
340+
{
341+
puts(gettext(stage_messages[i]));
342+
executeCommand(conn,stage_commands[i],progname,echo);
343+
run_vacuum_command(conn,sql.data,echo,dbname,table,progname);
344+
}
313345
}
346+
else
347+
run_vacuum_command(conn,sql.data,echo,dbname,NULL,progname);
348+
314349
PQfinish(conn);
315350
termPQExpBuffer(&sql);
316351
}
317352

318353

319354
staticvoid
320355
vacuum_all_databases(boolfull,boolverbose,booland_analyze,boolanalyze_only,
321-
boolfreeze,constchar*maintenance_db,
356+
boolanalyze_in_stages,boolfreeze,constchar*maintenance_db,
322357
constchar*host,constchar*port,
323358
constchar*username,enumtrivalueprompt_password,
324359
constchar*progname,boolecho,boolquiet)
@@ -343,6 +378,7 @@ vacuum_all_databases(bool full, bool verbose, bool and_analyze, bool analyze_onl
343378
}
344379

345380
vacuum_one_database(dbname,full,verbose,and_analyze,analyze_only,
381+
analyze_in_stages,
346382
freeze,NULL,host,port,username,prompt_password,
347383
progname,echo);
348384
}
@@ -369,6 +405,8 @@ help(const char *progname)
369405
printf(_(" -V, --version output version information, then exit\n"));
370406
printf(_(" -z, --analyze update optimizer statistics\n"));
371407
printf(_(" -Z, --analyze-only only update optimizer statistics\n"));
408+
printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
409+
" stages for faster results\n"));
372410
printf(_(" -?, --help show this help, then exit\n"));
373411
printf(_("\nConnection options:\n"));
374412
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));

0 commit comments

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