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

Commitfaec281

Browse files
committed
In pg_upgrade, create a script to incrementally generate more accurate
optimizer statistics so the cluster can be made available sooner.
1 parentd431848 commitfaec281

File tree

3 files changed

+144
-10
lines changed

3 files changed

+144
-10
lines changed

‎contrib/pg_upgrade/check.c‎

Lines changed: 136 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,11 @@ static void check_for_reg_data_type_usage(ClusterInfo *cluster);
2323
staticvoidcheck_for_support_lib(ClusterInfo*cluster);
2424
staticvoidget_bin_version(ClusterInfo*cluster);
2525

26+
#ifndefWIN32
27+
#defineECHO_QUOTE"'"
28+
#else
29+
#defineECHO_QUOTE""
30+
#endif
2631

2732
void
2833
output_check_banner(bool*live_check)
@@ -193,21 +198,20 @@ issue_warnings(char *sequence_script_file_name)
193198

194199

195200
void
196-
output_completion_banner(char*deletion_script_file_name)
201+
output_completion_banner(char*analyze_script_file_name,
202+
char*deletion_script_file_name)
197203
{
198204
/* Did we copy the free space files? */
199205
if (GET_MAJOR_VERSION(old_cluster.major_version) >=804)
200206
pg_log(PG_REPORT,
201-
"Optimizer statistics are not transferred by pg_upgrade so\n"
202-
"consider running:\n"
203-
" vacuumdb --all --analyze-only\n"
204-
"on the newly-upgraded cluster.\n\n");
207+
"Optimizer statistics are not transferred by pg_upgrade so,\n"
208+
"once you start the new server, consider running:\n"
209+
" %s\n\n",analyze_script_file_name);
205210
else
206211
pg_log(PG_REPORT,
207212
"Optimizer statistics and free space information are not transferred\n"
208-
"by pg_upgrade so consider running:\n"
209-
" vacuumdb --all --analyze\n"
210-
"on the newly-upgraded cluster.\n\n");
213+
"by pg_upgrade so, once you start the new server, consider running:\n"
214+
" %s\n\n",analyze_script_file_name);
211215

212216
pg_log(PG_REPORT,
213217
"Running this script will delete the old cluster's data files:\n"
@@ -379,6 +383,130 @@ check_new_cluster_is_empty(void)
379383
}
380384

381385

386+
/*
387+
* create_script_for_cluster_analyze()
388+
*
389+
*This incrementally generates better optimizer statistics
390+
*/
391+
void
392+
create_script_for_cluster_analyze(char**analyze_script_file_name)
393+
{
394+
FILE*script=NULL;
395+
396+
*analyze_script_file_name=pg_malloc(MAXPGPATH);
397+
398+
prep_status("Creating script to analyze new cluster");
399+
400+
snprintf(*analyze_script_file_name,MAXPGPATH,"analyze_new_cluster.%s",
401+
SCRIPT_EXT);
402+
403+
if ((script=fopen_priv(*analyze_script_file_name,"w"))==NULL)
404+
pg_log(PG_FATAL,"Could not open file \"%s\": %s\n",
405+
*analyze_script_file_name,getErrorText(errno));
406+
407+
#ifndefWIN32
408+
/* add shebang header */
409+
fprintf(script,"#!/bin/sh\n\n");
410+
#endif
411+
412+
fprintf(script,"echo %sThis script will generate minimal optimizer statistics rapidly%s\n",
413+
ECHO_QUOTE,ECHO_QUOTE);
414+
fprintf(script,"echo %sso your system is usable, and then gather statistics twice more%s\n",
415+
ECHO_QUOTE,ECHO_QUOTE);
416+
fprintf(script,"echo %swith increasing accuracy. When it is done, your system will%s\n",
417+
ECHO_QUOTE,ECHO_QUOTE);
418+
fprintf(script,"echo %shave the default level of optimizer statistics.%s\n",
419+
ECHO_QUOTE,ECHO_QUOTE);
420+
fprintf(script,"echo\n\n");
421+
422+
fprintf(script,"echo %sIf you have used ALTER TABLE to modify the statistics target for%s\n",
423+
ECHO_QUOTE,ECHO_QUOTE);
424+
fprintf(script,"echo %sany tables, you might want to remove them and restore them after%s\n",
425+
ECHO_QUOTE,ECHO_QUOTE);
426+
fprintf(script,"echo %srunning this script because they will delay fast statistics generation.%s\n",
427+
ECHO_QUOTE,ECHO_QUOTE);
428+
fprintf(script,"echo\n\n");
429+
430+
fprintf(script,"echo %sIf you would like default statistics as quickly as possible, cancel%s\n",
431+
ECHO_QUOTE,ECHO_QUOTE);
432+
fprintf(script,"echo %sthis script and run:%s\n",
433+
ECHO_QUOTE,ECHO_QUOTE);
434+
fprintf(script,"echo %s vacuumdb --all %s%s\n",ECHO_QUOTE,
435+
/* Did we copy the free space files? */
436+
(GET_MAJOR_VERSION(old_cluster.major_version) >=804) ?
437+
"--analyze-only" :"--analyze",ECHO_QUOTE);
438+
fprintf(script,"echo\n\n");
439+
440+
#ifndefWIN32
441+
fprintf(script,"sleep 2\n");
442+
fprintf(script,"PGOPTIONS='-c default_statistics_target=1 -c vacuum_cost_delay=0'\n");
443+
/* only need to export once */
444+
fprintf(script,"export PGOPTIONS\n");
445+
#else
446+
fprintf(script,"REM simulate sleep 2\n");
447+
fprintf(script,"PING 1.1.1.1 -n 1 -w 2000 > nul\n");
448+
fprintf(script,"SET PGOPTIONS=-c default_statistics_target=1 -c vacuum_cost_delay=0\n");
449+
#endif
450+
451+
fprintf(script,"echo %sGenerating minimal optimizer statistics (1 target)%s\n",
452+
ECHO_QUOTE,ECHO_QUOTE);
453+
fprintf(script,"echo %s--------------------------------------------------%s\n",
454+
ECHO_QUOTE,ECHO_QUOTE);
455+
fprintf(script,"vacuumdb --all --analyze-only\n");
456+
fprintf(script,"echo\n");
457+
fprintf(script,"echo %sThe server is now available with minimal optimizer statistics.%s\n",
458+
ECHO_QUOTE,ECHO_QUOTE);
459+
fprintf(script,"echo %sQuery performance will be optimal once this script completes.%s\n",
460+
ECHO_QUOTE,ECHO_QUOTE);
461+
fprintf(script,"echo\n\n");
462+
463+
#ifndefWIN32
464+
fprintf(script,"sleep 2\n");
465+
fprintf(script,"PGOPTIONS='-c default_statistics_target=10'\n");
466+
#else
467+
fprintf(script,"REM simulate sleep\n");
468+
fprintf(script,"PING 1.1.1.1 -n 1 -w 2000 > nul\n");
469+
fprintf(script,"SET PGOPTIONS=-c default_statistics_target=10\n");
470+
#endif
471+
472+
fprintf(script,"echo %sGenerating medium optimizer statistics (10 targets)%s\n",
473+
ECHO_QUOTE,ECHO_QUOTE);
474+
fprintf(script,"echo %s---------------------------------------------------%s\n",
475+
ECHO_QUOTE,ECHO_QUOTE);
476+
fprintf(script,"vacuumdb --all --analyze-only\n");
477+
fprintf(script,"echo\n\n");
478+
479+
#ifndefWIN32
480+
fprintf(script,"unset PGOPTIONS\n");
481+
#else
482+
fprintf(script,"SET PGOPTIONS\n");
483+
#endif
484+
485+
fprintf(script,"echo %sGenerating default (full) optimizer statistics (100 targets?)%s\n",
486+
ECHO_QUOTE,ECHO_QUOTE);
487+
fprintf(script,"echo %s-------------------------------------------------------------%s\n",
488+
ECHO_QUOTE,ECHO_QUOTE);
489+
fprintf(script,"vacuumdb --all %s\n",
490+
/* Did we copy the free space files? */
491+
(GET_MAJOR_VERSION(old_cluster.major_version) >=804) ?
492+
"--analyze-only" :"--analyze");
493+
494+
fprintf(script,"echo\n\n");
495+
fprintf(script,"echo %sDone%s\n",
496+
ECHO_QUOTE,ECHO_QUOTE);
497+
498+
fclose(script);
499+
500+
#ifndefWIN32
501+
if (chmod(*analyze_script_file_name,S_IRWXU)!=0)
502+
pg_log(PG_FATAL,"Could not add execute permission to file \"%s\": %s\n",
503+
*analyze_script_file_name,getErrorText(errno));
504+
#endif
505+
506+
check_ok();
507+
}
508+
509+
382510
/*
383511
* create_script_for_old_cluster_deletion()
384512
*

‎contrib/pg_upgrade/pg_upgrade.c‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,7 @@ int
6767
main(intargc,char**argv)
6868
{
6969
char*sequence_script_file_name=NULL;
70+
char*analyze_script_file_name=NULL;
7071
char*deletion_script_file_name=NULL;
7172
boollive_check= false;
7273

@@ -142,15 +143,18 @@ main(int argc, char **argv)
142143
new_cluster.pgdata,UTILITY_LOG_FILE);
143144
check_ok();
144145

146+
create_script_for_cluster_analyze(&analyze_script_file_name);
145147
create_script_for_old_cluster_deletion(&deletion_script_file_name);
146148

147149
issue_warnings(sequence_script_file_name);
148150

149151
pg_log(PG_REPORT,"\nUpgrade Complete\n");
150152
pg_log(PG_REPORT,"----------------\n");
151153

152-
output_completion_banner(deletion_script_file_name);
154+
output_completion_banner(analyze_script_file_name,
155+
deletion_script_file_name);
153156

157+
pg_free(analyze_script_file_name);
154158
pg_free(deletion_script_file_name);
155159
pg_free(sequence_script_file_name);
156160

‎contrib/pg_upgrade/pg_upgrade.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -283,10 +283,12 @@ void check_old_cluster(bool live_check,
283283
voidcheck_new_cluster(void);
284284
voidreport_clusters_compatible(void);
285285
voidissue_warnings(char*sequence_script_file_name);
286-
voidoutput_completion_banner(char*deletion_script_file_name);
286+
voidoutput_completion_banner(char*analyze_script_file_name,
287+
char*deletion_script_file_name);
287288
voidcheck_cluster_versions(void);
288289
voidcheck_cluster_compatibility(boollive_check);
289290
voidcreate_script_for_old_cluster_deletion(char**deletion_script_file_name);
291+
voidcreate_script_for_cluster_analyze(char**analyze_script_file_name);
290292

291293

292294
/* controldata.c */

0 commit comments

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