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

Commita46a701

Browse files
committed
Add options to control whether VACUUM runs vac_update_datfrozenxid.
VACUUM normally ends by running vac_update_datfrozenxid(), whichrequires a scan of pg_class. Therefore, if one attempts to vacuum adatabase one table at a time --- as vacuumdb has done since v12 ---we will spend O(N^2) time in vac_update_datfrozenxid(). That causesserious performance problems in databases with tens of thousands oftables, and indeed the effect is measurable with only a few hundred.To add insult to injury, only one process can runvac_update_datfrozenxid at the same time per DB, so this behaviorlargely defeats vacuumdb's -j option.Hence, invent options SKIP_DATABASE_STATS and ONLY_DATABASE_STATSto allow applications to postpone vac_update_datfrozenxid() until theend of a series of VACUUM requests, and teach vacuumdb to use them.Per bug #17717 from Gunnar L. Sadly, this answer doesn't seemlike something we'd consider back-patching, so the performanceproblem will remain in v12-v15.Tom Lane and Nathan BossartDiscussion:https://postgr.es/m/17717-6c50eb1c7d23a886@postgresql.org
1 parentcd4b233 commita46a701

File tree

10 files changed

+147
-21
lines changed

10 files changed

+147
-21
lines changed

‎doc/src/sgml/ref/vacuum.sgml‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,8 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
3636
PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
3737
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
3838
PARALLEL <replaceable class="parameter">integer</replaceable>
39+
SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
40+
ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
3941

4042
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
4143

@@ -295,6 +297,41 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
295297
</listitem>
296298
</varlistentry>
297299

300+
<varlistentry>
301+
<term><literal>SKIP_DATABASE_STATS</literal></term>
302+
<listitem>
303+
<para>
304+
Specifies that <command>VACUUM</command> should skip updating the
305+
database-wide statistics about oldest unfrozen XIDs. Normally
306+
<command>VACUUM</command> will update these statistics once at the
307+
end of the command. However, this can take awhile in a database
308+
with a very large number of tables, and it will accomplish nothing
309+
unless the table that had contained the oldest unfrozen XID was
310+
among those vacuumed. Moreover, if multiple <command>VACUUM</command>
311+
commands are issued in parallel, only one of them can update the
312+
database-wide statistics at a time. Therefore, if an application
313+
intends to issue a series of many <command>VACUUM</command>
314+
commands, it can be helpful to set this option in all but the last
315+
such command; or set it in all the commands and separately
316+
issue <literal>VACUUM (ONLY_DATABASE_STATS)</literal> afterwards.
317+
</para>
318+
</listitem>
319+
</varlistentry>
320+
321+
<varlistentry>
322+
<term><literal>ONLY_DATABASE_STATS</literal></term>
323+
<listitem>
324+
<para>
325+
Specifies that <command>VACUUM</command> should do nothing except
326+
update the database-wide statistics about oldest unfrozen XIDs.
327+
When this option is specified,
328+
the <replaceable class="parameter">table_and_columns</replaceable>
329+
list must be empty, and no other option may be enabled
330+
except <literal>VERBOSE</literal>.
331+
</para>
332+
</listitem>
333+
</varlistentry>
334+
298335
<varlistentry>
299336
<term><replaceable class="parameter">boolean</replaceable></term>
300337
<listitem>

‎src/backend/commands/vacuum.c‎

Lines changed: 37 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -114,6 +114,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
114114
boolfull= false;
115115
booldisable_page_skipping= false;
116116
boolprocess_toast= true;
117+
boolskip_database_stats= false;
118+
boolonly_database_stats= false;
117119
ListCell*lc;
118120

119121
/* index_cleanup and truncate values unspecified for now */
@@ -200,6 +202,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
200202
params.nworkers=nworkers;
201203
}
202204
}
205+
elseif (strcmp(opt->defname,"skip_database_stats")==0)
206+
skip_database_stats=defGetBoolean(opt);
207+
elseif (strcmp(opt->defname,"only_database_stats")==0)
208+
only_database_stats=defGetBoolean(opt);
203209
else
204210
ereport(ERROR,
205211
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -216,7 +222,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
216222
(freeze ?VACOPT_FREEZE :0) |
217223
(full ?VACOPT_FULL :0) |
218224
(disable_page_skipping ?VACOPT_DISABLE_PAGE_SKIPPING :0) |
219-
(process_toast ?VACOPT_PROCESS_TOAST :0);
225+
(process_toast ?VACOPT_PROCESS_TOAST :0) |
226+
(skip_database_stats ?VACOPT_SKIP_DATABASE_STATS :0) |
227+
(only_database_stats ?VACOPT_ONLY_DATABASE_STATS :0);
220228

221229
/* sanity checks on options */
222230
Assert(params.options& (VACOPT_VACUUM |VACOPT_ANALYZE));
@@ -349,6 +357,24 @@ vacuum(List *relations, VacuumParams *params,
349357
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
350358
errmsg("PROCESS_TOAST required with VACUUM FULL")));
351359

360+
/* sanity check for ONLY_DATABASE_STATS */
361+
if (params->options&VACOPT_ONLY_DATABASE_STATS)
362+
{
363+
Assert(params->options&VACOPT_VACUUM);
364+
if (relations!=NIL)
365+
ereport(ERROR,
366+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
367+
errmsg("ONLY_DATABASE_STATS cannot be specified with a list of tables")));
368+
/* don't require people to turn off PROCESS_TOAST explicitly */
369+
if (params->options& ~(VACOPT_VACUUM |
370+
VACOPT_VERBOSE |
371+
VACOPT_PROCESS_TOAST |
372+
VACOPT_ONLY_DATABASE_STATS))
373+
ereport(ERROR,
374+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
375+
errmsg("ONLY_DATABASE_STATS cannot be specified with other VACUUM options")));
376+
}
377+
352378
/*
353379
* Create special memory context for cross-transaction storage.
354380
*
@@ -376,7 +402,12 @@ vacuum(List *relations, VacuumParams *params,
376402
* Build list of relation(s) to process, putting any new data in
377403
* vac_context for safekeeping.
378404
*/
379-
if (relations!=NIL)
405+
if (params->options&VACOPT_ONLY_DATABASE_STATS)
406+
{
407+
/* We don't process any tables in this case */
408+
Assert(relations==NIL);
409+
}
410+
elseif (relations!=NIL)
380411
{
381412
List*newrels=NIL;
382413
ListCell*lc;
@@ -528,11 +559,11 @@ vacuum(List *relations, VacuumParams *params,
528559
StartTransactionCommand();
529560
}
530561

531-
if ((params->options&VACOPT_VACUUM)&& !IsAutoVacuumWorkerProcess())
562+
if ((params->options&VACOPT_VACUUM)&&
563+
!(params->options&VACOPT_SKIP_DATABASE_STATS))
532564
{
533565
/*
534566
* Update pg_database.datfrozenxid, and truncate pg_xact if possible.
535-
* (autovacuum.c does this for itself.)
536567
*/
537568
vac_update_datfrozenxid();
538569
}
@@ -560,13 +591,14 @@ vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple,
560591

561592
Assert((options& (VACOPT_VACUUM |VACOPT_ANALYZE))!=0);
562593

563-
/*
594+
/*----------
564595
* A role has privileges to vacuum or analyze the relation if any of the
565596
* following are true:
566597
* - the role is a superuser
567598
* - the role owns the relation
568599
* - the role owns the current database and the relation is not shared
569600
* - the role has been granted the MAINTAIN privilege on the relation
601+
*----------
570602
*/
571603
if (object_ownercheck(RelationRelationId,relid,GetUserId())||
572604
(object_ownercheck(DatabaseRelationId,MyDatabaseId,GetUserId())&& !reltuple->relisshared)||

‎src/backend/postmaster/autovacuum.c‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2854,8 +2854,13 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
28542854
tab->at_relid=relid;
28552855
tab->at_sharedrel=classForm->relisshared;
28562856

2857-
/* Note that this skips toast relations */
2858-
tab->at_params.options= (dovacuum ?VACOPT_VACUUM :0) |
2857+
/*
2858+
* Select VACUUM options. Note we don't say VACOPT_PROCESS_TOAST, so
2859+
* that vacuum() skips toast relations. Also note we tell vacuum() to
2860+
* skip vac_update_datfrozenxid(); we'll do that separately.
2861+
*/
2862+
tab->at_params.options=
2863+
(dovacuum ? (VACOPT_VACUUM |VACOPT_SKIP_DATABASE_STATS) :0) |
28592864
(doanalyze ?VACOPT_ANALYZE :0) |
28602865
(!wraparound ?VACOPT_SKIP_LOCKED :0);
28612866

‎src/bin/psql/tab-complete.c‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4598,8 +4598,9 @@ psql_completion(const char *text, int start, int end)
45984598
COMPLETE_WITH("FULL","FREEZE","ANALYZE","VERBOSE",
45994599
"DISABLE_PAGE_SKIPPING","SKIP_LOCKED",
46004600
"INDEX_CLEANUP","PROCESS_TOAST",
4601-
"TRUNCATE","PARALLEL");
4602-
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE"))
4601+
"TRUNCATE","PARALLEL","SKIP_DATABASE_STATS",
4602+
"ONLY_DATABASE_STATS");
4603+
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
46034604
COMPLETE_WITH("ON","OFF");
46044605
elseif (TailMatches("INDEX_CLEANUP"))
46054606
COMPLETE_WITH("AUTO","ON","OFF");

‎src/bin/scripts/t/100_vacuumdb.pl‎

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -22,27 +22,27 @@
2222
'SQL VACUUM run');
2323
$node->issues_sql_like(
2424
['vacuumdb','-f','postgres' ],
25-
qr/statement: VACUUM\(FULL\).*;/,
25+
qr/statement: VACUUM\(SKIP_DATABASE_STATS,FULL\).*;/,
2626
'vacuumdb -f');
2727
$node->issues_sql_like(
2828
['vacuumdb','-F','postgres' ],
29-
qr/statement: VACUUM\(FREEZE\).*;/,
29+
qr/statement: VACUUM\(SKIP_DATABASE_STATS,FREEZE\).*;/,
3030
'vacuumdb -F');
3131
$node->issues_sql_like(
3232
['vacuumdb','-zj2','postgres' ],
33-
qr/statement: VACUUM\(ANALYZE\).*;/,
33+
qr/statement: VACUUM\(SKIP_DATABASE_STATS,ANALYZE\).*;/,
3434
'vacuumdb -zj2');
3535
$node->issues_sql_like(
3636
['vacuumdb','-Z','postgres' ],
3737
qr/statement: ANALYZE.*;/,
3838
'vacuumdb -Z');
3939
$node->issues_sql_like(
4040
['vacuumdb','--disable-page-skipping','postgres' ],
41-
qr/statement: VACUUM\(DISABLE_PAGE_SKIPPING\).*;/,
41+
qr/statement: VACUUM\(DISABLE_PAGE_SKIPPING, SKIP_DATABASE_STATS\).*;/,
4242
'vacuumdb --disable-page-skipping');
4343
$node->issues_sql_like(
4444
['vacuumdb','--skip-locked','postgres' ],
45-
qr/statement: VACUUM\(SKIP_LOCKED\).*;/,
45+
qr/statement: VACUUM\(SKIP_DATABASE_STATS,SKIP_LOCKED\).*;/,
4646
'vacuumdb --skip-locked');
4747
$node->issues_sql_like(
4848
['vacuumdb','--skip-locked','--analyze-only','postgres' ],
@@ -53,32 +53,32 @@
5353
'--analyze-only and --disable-page-skipping specified together');
5454
$node->issues_sql_like(
5555
['vacuumdb','--no-index-cleanup','postgres' ],
56-
qr/statement: VACUUM\(INDEX_CLEANUP FALSE\).*;/,
56+
qr/statement: VACUUM\(INDEX_CLEANUP FALSE, SKIP_DATABASE_STATS\).*;/,
5757
'vacuumdb --no-index-cleanup');
5858
$node->command_fails(
5959
['vacuumdb','--analyze-only','--no-index-cleanup','postgres' ],
6060
'--analyze-only and --no-index-cleanup specified together');
6161
$node->issues_sql_like(
6262
['vacuumdb','--no-truncate','postgres' ],
63-
qr/statement: VACUUM\(TRUNCATE FALSE\).*;/,
63+
qr/statement: VACUUM\(TRUNCATE FALSE, SKIP_DATABASE_STATS\).*;/,
6464
'vacuumdb --no-truncate');
6565
$node->command_fails(
6666
['vacuumdb','--analyze-only','--no-truncate','postgres' ],
6767
'--analyze-only and --no-truncate specified together');
6868
$node->issues_sql_like(
6969
['vacuumdb','--no-process-toast','postgres' ],
70-
qr/statement: VACUUM\(PROCESS_TOAST FALSE\).*;/,
70+
qr/statement: VACUUM\(PROCESS_TOAST FALSE, SKIP_DATABASE_STATS\).*;/,
7171
'vacuumdb --no-process-toast');
7272
$node->command_fails(
7373
['vacuumdb','--analyze-only','--no-process-toast','postgres' ],
7474
'--analyze-only and --no-process-toast specified together');
7575
$node->issues_sql_like(
7676
['vacuumdb','-P', 2,'postgres' ],
77-
qr/statement: VACUUM\(PARALLEL 2\).*;/,
77+
qr/statement: VACUUM\(SKIP_DATABASE_STATS,PARALLEL 2\).*;/,
7878
'vacuumdb -P 2');
7979
$node->issues_sql_like(
8080
['vacuumdb','-P', 0,'postgres' ],
81-
qr/statement: VACUUM\(PARALLEL 0\).*;/,
81+
qr/statement: VACUUM\(SKIP_DATABASE_STATS,PARALLEL 0\).*;/,
8282
'vacuumdb -P 0');
8383
$node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
8484
'vacuumdb with connection string');
@@ -119,7 +119,7 @@
119119
'negative parallel degree');
120120
$node->issues_sql_like(
121121
['vacuumdb','--analyze','--table','vactable(a, b)','postgres' ],
122-
qr/statement: VACUUM\(ANALYZE\) public.vactable\(a, b\);/,
122+
qr/statement: VACUUM\(SKIP_DATABASE_STATS,ANALYZE\) public.vactable\(a, b\);/,
123123
'vacuumdb --analyze with complete column list');
124124
$node->issues_sql_like(
125125
['vacuumdb','--analyze-only','--table','vactable(b)','postgres' ],
@@ -150,7 +150,7 @@
150150
'vacuumdb --table --min-xid-age');
151151
$node->issues_sql_like(
152152
['vacuumdb','--schema','"Foo"','postgres' ],
153-
qr/VACUUM "Foo".bar/,
153+
qr/VACUUM\(SKIP_DATABASE_STATS\)"Foo".bar/,
154154
'vacuumdb --schema');
155155
$node->issues_sql_like(
156156
['vacuumdb','--exclude-schema','"Foo"','postgres' ],

‎src/bin/scripts/vacuumdb.c‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@ typedef struct vacuumingOptions
4444
boolforce_index_cleanup;
4545
booldo_truncate;
4646
boolprocess_toast;
47+
boolskip_database_stats;
4748
}vacuumingOptions;
4849

4950
/* object filter options */
@@ -533,6 +534,9 @@ vacuum_one_database(ConnParams *cparams,
533534
pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
534535
"--parallel","13");
535536

537+
/* skip_database_stats is used automatically if server supports it */
538+
vacopts->skip_database_stats= (PQserverVersion(conn) >=160000);
539+
536540
if (!quiet)
537541
{
538542
if (stage!=ANALYZE_NO_STAGE)
@@ -790,7 +794,29 @@ vacuum_one_database(ConnParams *cparams,
790794
}while (cell!=NULL);
791795

792796
if (!ParallelSlotsWaitCompletion(sa))
797+
{
793798
failed= true;
799+
gotofinish;
800+
}
801+
802+
/* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
803+
if (vacopts->skip_database_stats&&stage==ANALYZE_NO_STAGE)
804+
{
805+
constchar*cmd="VACUUM (ONLY_DATABASE_STATS);";
806+
ParallelSlot*free_slot=ParallelSlotsGetIdle(sa,NULL);
807+
808+
if (!free_slot)
809+
{
810+
failed= true;
811+
gotofinish;
812+
}
813+
814+
ParallelSlotSetHandler(free_slot,TableCommandResultHandler,NULL);
815+
run_vacuum_command(free_slot->connection,cmd,echo,NULL);
816+
817+
if (!ParallelSlotsWaitCompletion(sa))
818+
failed= true;
819+
}
794820

795821
finish:
796822
ParallelSlotsTerminate(sa);
@@ -957,6 +983,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
957983
appendPQExpBuffer(sql,"%sPROCESS_TOAST FALSE",sep);
958984
sep=comma;
959985
}
986+
if (vacopts->skip_database_stats)
987+
{
988+
/* SKIP_DATABASE_STATS is supported since v16 */
989+
Assert(serverVersion >=160000);
990+
appendPQExpBuffer(sql,"%sSKIP_DATABASE_STATS",sep);
991+
sep=comma;
992+
}
960993
if (vacopts->skip_locked)
961994
{
962995
/* SKIP_LOCKED is supported since v12 */

‎src/fe_utils/parallel_slot.c‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -475,6 +475,9 @@ ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
475475
continue;
476476
if (!consumeQueryResult(&sa->slots[i]))
477477
return false;
478+
/* Mark connection as idle */
479+
sa->slots[i].inUse= false;
480+
ParallelSlotClearHandler(&sa->slots[i]);
478481
}
479482

480483
return true;

‎src/include/commands/vacuum.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,8 @@ typedef struct VacAttrStats
188188
#defineVACOPT_SKIP_LOCKED 0x20/* skip if cannot get lock */
189189
#defineVACOPT_PROCESS_TOAST 0x40/* process the TOAST table, if any */
190190
#defineVACOPT_DISABLE_PAGE_SKIPPING 0x80/* don't skip any pages */
191+
#defineVACOPT_SKIP_DATABASE_STATS 0x100/* skip vac_update_datfrozenxid() */
192+
#defineVACOPT_ONLY_DATABASE_STATS 0x200/* only vac_update_datfrozenxid() */
191193

192194
/*
193195
* Values used by index_cleanup and truncate params.

‎src/test/regress/expected/vacuum.out‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -282,6 +282,12 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
282282
VACUUM (PROCESS_TOAST FALSE) vactst;
283283
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
284284
ERROR: PROCESS_TOAST required with VACUUM FULL
285+
-- SKIP_DATABASE_STATS option
286+
VACUUM (SKIP_DATABASE_STATS) vactst;
287+
-- ONLY_DATABASE_STATS option
288+
VACUUM (ONLY_DATABASE_STATS);
289+
VACUUM (ONLY_DATABASE_STATS) vactst; -- error
290+
ERROR: ONLY_DATABASE_STATS cannot be specified with a list of tables
285291
DROP TABLE vaccluster;
286292
DROP TABLE vactst;
287293
DROP TABLE vacparted;

‎src/test/regress/sql/vacuum.sql‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -237,6 +237,13 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
237237
VACUUM (PROCESS_TOAST FALSE) vactst;
238238
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
239239

240+
-- SKIP_DATABASE_STATS option
241+
VACUUM (SKIP_DATABASE_STATS) vactst;
242+
243+
-- ONLY_DATABASE_STATS option
244+
VACUUM (ONLY_DATABASE_STATS);
245+
VACUUM (ONLY_DATABASE_STATS) vactst;-- error
246+
240247
DROPTABLE vaccluster;
241248
DROPTABLE vactst;
242249
DROPTABLE vacparted;

0 commit comments

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