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

Commit30a6ed0

Browse files
committed
Track per-relation cumulative time spent in [auto]vacuum and [auto]analyze
This commit adds four fields to the statistics of relations, aggregatingthe amount of time spent for each operation on a relation:- total_vacuum_time, for manual vacuum.- total_autovacuum_time, for vacuum done by the autovacuum daemon.- total_analyze_time, for manual analyze.- total_autoanalyze_time, for analyze done by the autovacuum daemon.This gives users the option to derive the average time spent for theseoperations with the help of the related "count" fields.Bump catalog version (for the catalog changes) and PGSTAT_FILE_FORMAT_ID(for the additions in PgStat_StatTabEntry).Author: Sami ImseihReviewed-by: Bertrand Drouvot, Michael PaquierDiscussion:https://postgr.es/m/CAA5RZ0uVOGBYmPEeGF2d1B_67tgNjKx_bKDuL+oUftuoz+=Y1g@mail.gmail.com
1 parent5afaba6 commit30a6ed0

File tree

10 files changed

+141
-19
lines changed

10 files changed

+141
-19
lines changed

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4053,6 +4053,44 @@ description | Waiting for a newly initialized WAL file to reach durable storage
40534053
daemon
40544054
</para></entry>
40554055
</row>
4056+
4057+
<row>
4058+
<entry role="catalog_table_entry"><para role="column_definition">
4059+
<structfield>total_vacuum_time</structfield> <type>double precision</type>
4060+
</para>
4061+
<para>
4062+
Total time this table has been manually vacuumed, in milliseconds
4063+
</para></entry>
4064+
</row>
4065+
4066+
<row>
4067+
<entry role="catalog_table_entry"><para role="column_definition">
4068+
<structfield>total_autovacuum_time</structfield> <type>double precision</type>
4069+
</para>
4070+
<para>
4071+
Total time this table has been vacuumed by the autovacuum daemon,
4072+
in milliseconds
4073+
</para></entry>
4074+
</row>
4075+
4076+
<row>
4077+
<entry role="catalog_table_entry"><para role="column_definition">
4078+
<structfield>total_analyze_time</structfield> <type>double precision</type>
4079+
</para>
4080+
<para>
4081+
Total time this table has been manually analyzed, in milliseconds
4082+
</para></entry>
4083+
</row>
4084+
4085+
<row>
4086+
<entry role="catalog_table_entry"><para role="column_definition">
4087+
<structfield>total_autoanalyze_time</structfield> <type>double precision</type>
4088+
</para>
4089+
<para>
4090+
Total time this table has been analyzed by the autovacuum daemon,
4091+
in milliseconds
4092+
</para></entry>
4093+
</row>
40564094
</tbody>
40574095
</tgroup>
40584096
</table>

‎src/backend/access/heap/vacuumlazy.c‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -386,14 +386,16 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
386386
if (instrument)
387387
{
388388
pg_rusage_init(&ru0);
389-
starttime=GetCurrentTimestamp();
390389
if (track_io_timing)
391390
{
392391
startreadtime=pgStatBlockReadTime;
393392
startwritetime=pgStatBlockWriteTime;
394393
}
395394
}
396395

396+
/* Used for instrumentation and stats report */
397+
starttime=GetCurrentTimestamp();
398+
397399
pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
398400
RelationGetRelid(rel));
399401

@@ -659,7 +661,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
659661
rel->rd_rel->relisshared,
660662
Max(vacrel->new_live_tuples,0),
661663
vacrel->recently_dead_tuples+
662-
vacrel->missed_dead_tuples);
664+
vacrel->missed_dead_tuples,
665+
starttime);
663666
pgstat_progress_end_command();
664667

665668
if (instrument)

‎src/backend/catalog/system_views.sql‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -696,7 +696,11 @@ CREATE VIEW pg_stat_all_tables AS
696696
pg_stat_get_vacuum_count(C.oid)AS vacuum_count,
697697
pg_stat_get_autovacuum_count(C.oid)AS autovacuum_count,
698698
pg_stat_get_analyze_count(C.oid)AS analyze_count,
699-
pg_stat_get_autoanalyze_count(C.oid)AS autoanalyze_count
699+
pg_stat_get_autoanalyze_count(C.oid)AS autoanalyze_count,
700+
pg_stat_get_total_vacuum_time(C.oid)AS total_vacuum_time,
701+
pg_stat_get_total_autovacuum_time(C.oid)AS total_autovacuum_time,
702+
pg_stat_get_total_analyze_time(C.oid)AS total_analyze_time,
703+
pg_stat_get_total_autoanalyze_time(C.oid)AS total_autoanalyze_time
700704
FROM pg_class CLEFT JOIN
701705
pg_index IONC.oid=I.indrelid
702706
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)

‎src/backend/commands/analyze.c‎

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -344,8 +344,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
344344
RestrictSearchPath();
345345

346346
/*
347-
*measure elapsed time if called with verbose or if autovacuum logging
348-
*requires it
347+
*When verbose or autovacuum logging is used, initialize a resource usage
348+
*snapshot and optionally track I/O timing.
349349
*/
350350
if (instrument)
351351
{
@@ -356,9 +356,11 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
356356
}
357357

358358
pg_rusage_init(&ru0);
359-
starttime=GetCurrentTimestamp();
360359
}
361360

361+
/* Used for instrumentation and stats report */
362+
starttime=GetCurrentTimestamp();
363+
362364
/*
363365
* Determine which columns to analyze
364366
*
@@ -693,9 +695,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
693695
*/
694696
if (!inh)
695697
pgstat_report_analyze(onerel,totalrows,totaldeadrows,
696-
(va_cols==NIL));
698+
(va_cols==NIL),starttime);
697699
elseif (onerel->rd_rel->relkind==RELKIND_PARTITIONED_TABLE)
698-
pgstat_report_analyze(onerel,0,0, (va_cols==NIL));
700+
pgstat_report_analyze(onerel,0,0, (va_cols==NIL),starttime);
699701

700702
/*
701703
* If this isn't part of VACUUM ANALYZE, let index AMs do cleanup.

‎src/backend/utils/activity/pgstat_relation.c‎

Lines changed: 17 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -208,19 +208,22 @@ pgstat_drop_relation(Relation rel)
208208
*/
209209
void
210210
pgstat_report_vacuum(Oidtableoid,boolshared,
211-
PgStat_Counterlivetuples,PgStat_Counterdeadtuples)
211+
PgStat_Counterlivetuples,PgStat_Counterdeadtuples,
212+
TimestampTzstarttime)
212213
{
213214
PgStat_EntryRef*entry_ref;
214215
PgStatShared_Relation*shtabentry;
215216
PgStat_StatTabEntry*tabentry;
216217
Oiddboid= (shared ?InvalidOid :MyDatabaseId);
217218
TimestampTzts;
219+
PgStat_Counterelapsedtime;
218220

219221
if (!pgstat_track_counts)
220222
return;
221223

222224
/* Store the data in the table's hash table entry. */
223225
ts=GetCurrentTimestamp();
226+
elapsedtime=TimestampDifferenceMilliseconds(starttime,ts);
224227

225228
/* block acquiring lock for the same reason as pgstat_report_autovac() */
226229
entry_ref=pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
@@ -248,11 +251,13 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
248251
{
249252
tabentry->last_autovacuum_time=ts;
250253
tabentry->autovacuum_count++;
254+
tabentry->total_autovacuum_time+=elapsedtime;
251255
}
252256
else
253257
{
254258
tabentry->last_vacuum_time=ts;
255259
tabentry->vacuum_count++;
260+
tabentry->total_vacuum_time+=elapsedtime;
256261
}
257262

258263
pgstat_unlock_entry(entry_ref);
@@ -276,12 +281,14 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
276281
void
277282
pgstat_report_analyze(Relationrel,
278283
PgStat_Counterlivetuples,PgStat_Counterdeadtuples,
279-
boolresetcounter)
284+
boolresetcounter,TimestampTzstarttime)
280285
{
281286
PgStat_EntryRef*entry_ref;
282287
PgStatShared_Relation*shtabentry;
283288
PgStat_StatTabEntry*tabentry;
284289
Oiddboid= (rel->rd_rel->relisshared ?InvalidOid :MyDatabaseId);
290+
TimestampTzts;
291+
PgStat_Counterelapsedtime;
285292

286293
if (!pgstat_track_counts)
287294
return;
@@ -315,6 +322,10 @@ pgstat_report_analyze(Relation rel,
315322
deadtuples=Max(deadtuples,0);
316323
}
317324

325+
/* Store the data in the table's hash table entry. */
326+
ts=GetCurrentTimestamp();
327+
elapsedtime=TimestampDifferenceMilliseconds(starttime,ts);
328+
318329
/* block acquiring lock for the same reason as pgstat_report_autovac() */
319330
entry_ref=pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,dboid,
320331
RelationGetRelid(rel),
@@ -338,13 +349,15 @@ pgstat_report_analyze(Relation rel,
338349

339350
if (AmAutoVacuumWorkerProcess())
340351
{
341-
tabentry->last_autoanalyze_time=GetCurrentTimestamp();
352+
tabentry->last_autoanalyze_time=ts;
342353
tabentry->autoanalyze_count++;
354+
tabentry->total_autoanalyze_time+=elapsedtime;
343355
}
344356
else
345357
{
346-
tabentry->last_analyze_time=GetCurrentTimestamp();
358+
tabentry->last_analyze_time=ts;
347359
tabentry->analyze_count++;
360+
tabentry->total_analyze_time+=elapsedtime;
348361
}
349362

350363
pgstat_unlock_entry(entry_ref);

‎src/backend/utils/adt/pgstatfuncs.c‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -106,6 +106,34 @@ PG_STAT_GET_RELENTRY_INT64(tuples_updated)
106106
/* pg_stat_get_vacuum_count */
107107
PG_STAT_GET_RELENTRY_INT64(vacuum_count)
108108

109+
#definePG_STAT_GET_RELENTRY_FLOAT8(stat)\
110+
Datum\
111+
CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS)\
112+
{\
113+
Oidrelid=PG_GETARG_OID(0);\
114+
doubleresult;\
115+
PgStat_StatTabEntry*tabentry;\
116+
\
117+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)\
118+
result=0;\
119+
else\
120+
result= (double) (tabentry->stat);\
121+
\
122+
PG_RETURN_FLOAT8(result);\
123+
}
124+
125+
/* pg_stat_get_total_vacuum_time */
126+
PG_STAT_GET_RELENTRY_FLOAT8(total_vacuum_time)
127+
128+
/* pg_stat_get_total_autovacuum_time */
129+
PG_STAT_GET_RELENTRY_FLOAT8(total_autovacuum_time)
130+
131+
/* pg_stat_get_total_analyze_time */
132+
PG_STAT_GET_RELENTRY_FLOAT8(total_analyze_time)
133+
134+
/* pg_stat_get_total_autoanalyze_time */
135+
PG_STAT_GET_RELENTRY_FLOAT8(total_autoanalyze_time)
136+
109137
#definePG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat)\
110138
Datum\
111139
CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS)\

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202501232
60+
#defineCATALOG_VERSION_NO202501281
6161

6262
#endif

‎src/include/catalog/pg_proc.dat‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5543,6 +5543,22 @@
55435543
proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
55445544
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
55455545
prosrc => 'pg_stat_get_autoanalyze_count' },
5546+
{ oid => '8406', descr => 'total vacuum time, in milliseconds',
5547+
proname => 'pg_stat_get_total_vacuum_time', provolatile => 's',
5548+
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
5549+
prosrc => 'pg_stat_get_total_vacuum_time' },
5550+
{ oid => '8407', descr => 'total autovacuum time, in milliseconds',
5551+
proname => 'pg_stat_get_total_autovacuum_time', provolatile => 's',
5552+
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
5553+
prosrc => 'pg_stat_get_total_autovacuum_time' },
5554+
{ oid => '8408', descr => 'total analyze time, in milliseconds',
5555+
proname => 'pg_stat_get_total_analyze_time', provolatile => 's',
5556+
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
5557+
prosrc => 'pg_stat_get_total_analyze_time' },
5558+
{ oid => '8409', descr => 'total autoanalyze time, in milliseconds',
5559+
proname => 'pg_stat_get_total_autoanalyze_time', provolatile => 's',
5560+
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
5561+
prosrc => 'pg_stat_get_total_autoanalyze_time' },
55465562
{ oid => '1936', descr => 'statistics: currently active backend IDs',
55475563
proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
55485564
provolatile => 's', proparallel => 'r', prorettype => 'int4',

‎src/include/pgstat.h‎

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -212,7 +212,7 @@ typedef struct PgStat_TableXactStatus
212212
* ------------------------------------------------------------
213213
*/
214214

215-
#definePGSTAT_FILE_FORMAT_ID0x01A5BCB1
215+
#definePGSTAT_FILE_FORMAT_ID0x01A5BCB2
216216

217217
typedefstructPgStat_ArchiverStats
218218
{
@@ -465,6 +465,11 @@ typedef struct PgStat_StatTabEntry
465465
PgStat_Counteranalyze_count;
466466
TimestampTzlast_autoanalyze_time;/* autovacuum initiated */
467467
PgStat_Counterautoanalyze_count;
468+
469+
PgStat_Countertotal_vacuum_time;/* times in milliseconds */
470+
PgStat_Countertotal_autovacuum_time;
471+
PgStat_Countertotal_analyze_time;
472+
PgStat_Countertotal_autoanalyze_time;
468473
}PgStat_StatTabEntry;
469474

470475
typedefstructPgStat_WalStats
@@ -649,10 +654,11 @@ extern void pgstat_assoc_relation(Relation rel);
649654
externvoidpgstat_unlink_relation(Relationrel);
650655

651656
externvoidpgstat_report_vacuum(Oidtableoid,boolshared,
652-
PgStat_Counterlivetuples,PgStat_Counterdeadtuples);
657+
PgStat_Counterlivetuples,PgStat_Counterdeadtuples,
658+
TimestampTzstarttime);
653659
externvoidpgstat_report_analyze(Relationrel,
654660
PgStat_Counterlivetuples,PgStat_Counterdeadtuples,
655-
boolresetcounter);
661+
boolresetcounter,TimestampTzstarttime);
656662

657663
/*
658664
* If stats are enabled, but pending data hasn't been prepared yet, call

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

Lines changed: 15 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1804,7 +1804,11 @@ pg_stat_all_tables| SELECT c.oid AS relid,
18041804
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
18051805
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
18061806
pg_stat_get_analyze_count(c.oid) AS analyze_count,
1807-
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
1807+
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
1808+
pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time,
1809+
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
1810+
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
1811+
pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time
18081812
FROM ((pg_class c
18091813
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
18101814
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2190,7 +2194,11 @@ pg_stat_sys_tables| SELECT relid,
21902194
vacuum_count,
21912195
autovacuum_count,
21922196
analyze_count,
2193-
autoanalyze_count
2197+
autoanalyze_count,
2198+
total_vacuum_time,
2199+
total_autovacuum_time,
2200+
total_analyze_time,
2201+
total_autoanalyze_time
21942202
FROM pg_stat_all_tables
21952203
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
21962204
pg_stat_user_functions| SELECT p.oid AS funcid,
@@ -2238,7 +2246,11 @@ pg_stat_user_tables| SELECT relid,
22382246
vacuum_count,
22392247
autovacuum_count,
22402248
analyze_count,
2241-
autoanalyze_count
2249+
autoanalyze_count,
2250+
total_vacuum_time,
2251+
total_autovacuum_time,
2252+
total_analyze_time,
2253+
total_autoanalyze_time
22422254
FROM pg_stat_all_tables
22432255
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
22442256
pg_stat_wal| SELECT wal_records,

0 commit comments

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