From af474098037c8292d9c82c711c0fe78aa1580395 Mon Sep 17 00:00:00 2001 From: Alena Rybakina Date: Tue, 4 Feb 2025 18:39:35 +0300 Subject: [PATCH] Machinery for grabbing an extended vacuum statistics on databases. Database vacuum statistics information is the collected general vacuum statistics indexes and tables owned by the databases, which they belong to. By default, vacuum database statistics are always collected, and the track_vacuum_statistics guc enables the ability to collect extended statistics for a given database's relations. This is done to achieve a balance between the allocated memory for storing statistics and having the necessary monitoring at hand to track the state of vacuum operation. In addition to the fact that there are far fewer databases in a system than relations, vacuum statistics for a database contain fewer statistics than relations, but they are enough to indicate that something may be wrong in the system and prompt the administrator to enable extended monitoring for relations. So, buffer, wal, statistics of I/O time of read and writen blocks statistics will be observed because they are collected for both tables, indexes. In addition, we show the number of errors caught during operation of the vacuum only for the error level. wraparound_failsafe_count is a number of times when the vacuum starts urgent cleanup to prevent wraparound problem which is critical for the database. Authors: Alena Rybakina , Andrei Lepikhov , Andrei Zubkov Reviewed-by: Dilip Kumar , Masahiko Sawada , Ilia Evdokimov , jian he , Kirill Reshke , Alexander Korotkov , Jim Nasby , Sami Imseih --- src/backend/access/heap/vacuumlazy.c | 75 +++++-------- src/backend/catalog/system_views.sql | 27 ++++- src/backend/utils/activity/pgstat.c | 2 +- src/backend/utils/activity/pgstat_database.c | 1 + src/backend/utils/activity/pgstat_relation.c | 52 ++++++++- src/backend/utils/adt/pgstatfuncs.c | 100 +++++++++++++++++- src/backend/utils/misc/guc_tables.c | 2 +- src/include/catalog/pg_proc.dat | 13 ++- src/include/pgstat.h | 5 +- .../vacuum-extending-in-repetable-read.spec | 6 ++ src/test/regress/expected/rules.out | 17 +++ .../expected/vacuum_index_statistics.out | 16 +-- ...ut => vacuum_tables_and_db_statistics.out} | 89 ++++++++++++++-- src/test/regress/parallel_schedule | 2 +- src/test/regress/regression.diffs | 12 +++ .../regress/sql/vacuum_index_statistics.sql | 6 +- ...ql => vacuum_tables_and_db_statistics.sql} | 69 +++++++++++- 17 files changed, 408 insertions(+), 86 deletions(-) rename src/test/regress/expected/{vacuum_tables_statistics.out => vacuum_tables_and_db_statistics.out} (82%) create mode 100644 src/test/regress/regression.diffs rename src/test/regress/sql/{vacuum_tables_statistics.sql => vacuum_tables_and_db_statistics.sql} (81%) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 991fb831726..f1b3be70785 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -363,9 +363,6 @@ extvac_stats_start(Relation rel, LVExtStatCounters *counters) { TimestampTz starttime; - if(!pgstat_track_vacuum_statistics) - return; - memset(counters, 0, sizeof(LVExtStatCounters)); starttime = GetCurrentTimestamp(); @@ -404,9 +401,6 @@ extvac_stats_end(Relation rel, LVExtStatCounters *counters, long secs; int usecs; - if(!pgstat_track_vacuum_statistics) - return; - /* Calculate diffs of global stat parameters on WAL and buffer usage. */ memset(&walusage, 0, sizeof(WalUsage)); WalUsageAccumDiff(&walusage, &pgWalUsage, &counters->walusage); @@ -454,9 +448,6 @@ void extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats, LVExtStatCountersIdx *counters) { - if(!pgstat_track_vacuum_statistics) - return; - /* Set initial values for common heap and index statistics*/ extvac_stats_start(rel, &counters->common); counters->pages_deleted = counters->tuples_removed = 0; @@ -531,7 +522,7 @@ accumulate_heap_vacuum_statistics(LVExtStatCounters *extVacCounters, LVRelState vacrel->extVacReport.table.missed_dead_tuples += vacrel->missed_dead_tuples; vacrel->extVacReport.table.missed_dead_pages += vacrel->missed_dead_pages; vacrel->extVacReport.table.index_vacuum_count += vacrel->num_index_scans; - vacrel->extVacReport.table.wraparound_failsafe_count += vacrel->wraparound_failsafe_count; + vacrel->extVacReport.wraparound_failsafe_count += vacrel->wraparound_failsafe_count; } /* @@ -850,15 +841,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, * * We are ready to send vacuum statistics information for heap relations. */ - if(pgstat_track_vacuum_statistics) - { - /* Make generic extended vacuum stats report and - * fill heap-specific extended stats fields. - */ - extvac_stats_end(vacrel->rel, &extVacCounters, &(vacrel->extVacReport)); - accumulate_heap_vacuum_statistics(&extVacCounters, vacrel); - - pgstat_report_vacuum(RelationGetRelid(rel), + accumulate_heap_vacuum_statistics(&extVacCounters, vacrel); + pgstat_report_vacuum(RelationGetRelid(rel), rel->rd_rel->relisshared, Max(vacrel->new_live_tuples, 0), vacrel->recently_dead_tuples + @@ -866,18 +850,6 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, starttime, &(vacrel->extVacReport)); - } - else - { - pgstat_report_vacuum(RelationGetRelid(rel), - rel->rd_rel->relisshared, - Max(vacrel->new_live_tuples, 0), - vacrel->recently_dead_tuples + - vacrel->missed_dead_tuples, - starttime, - NULL); - } - pgstat_progress_end_command(); if (instrument) @@ -2898,14 +2870,11 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat, istat = vac_bulkdel_one_index(&ivinfo, istat, vacrel->dead_items, vacrel->dead_items_info); - if(pgstat_track_vacuum_statistics) - { - /* Make extended vacuum stats report for index */ - extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport); - pgstat_report_vacuum(RelationGetRelid(indrel), - indrel->rd_rel->relisshared, - 0, 0, 0, &extVacReport); - } + /* Make extended vacuum stats report for index */ + extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport); + pgstat_report_vacuum(RelationGetRelid(indrel), + indrel->rd_rel->relisshared, + 0, 0, 0, &extVacReport); /* Revert to the previous phase information for error traceback */ restore_vacuum_error_info(vacrel, &saved_err_info); @@ -2962,14 +2931,11 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat, istat = vac_cleanup_one_index(&ivinfo, istat); - if(pgstat_track_vacuum_statistics) - { - /* Make extended vacuum stats report for index */ - extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport); - pgstat_report_vacuum(RelationGetRelid(indrel), - indrel->rd_rel->relisshared, - 0, 0, 0, &extVacReport); - } + /* Make extended vacuum stats report for index */ + extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport); + pgstat_report_vacuum(RelationGetRelid(indrel), + indrel->rd_rel->relisshared, + 0, 0, 0, &extVacReport); /* Revert to the previous phase information for error traceback */ restore_vacuum_error_info(vacrel, &saved_err_info); @@ -3583,6 +3549,9 @@ vacuum_error_callback(void *arg) switch (errinfo->phase) { case VACUUM_ERRCB_PHASE_SCAN_HEAP: + if(geterrelevel() == ERROR) + pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE); + if (BlockNumberIsValid(errinfo->blkno)) { if (OffsetNumberIsValid(errinfo->offnum)) @@ -3598,6 +3567,9 @@ vacuum_error_callback(void *arg) break; case VACUUM_ERRCB_PHASE_VACUUM_HEAP: + if(geterrelevel() == ERROR) + pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE); + if (BlockNumberIsValid(errinfo->blkno)) { if (OffsetNumberIsValid(errinfo->offnum)) @@ -3613,16 +3585,25 @@ vacuum_error_callback(void *arg) break; case VACUUM_ERRCB_PHASE_VACUUM_INDEX: + if(geterrelevel() == ERROR) + pgstat_report_vacuum_error(errinfo->indoid, PGSTAT_EXTVAC_INDEX); + errcontext("while vacuuming index \"%s\" of relation \"%s.%s\"", errinfo->indname, errinfo->relnamespace, errinfo->relname); break; case VACUUM_ERRCB_PHASE_INDEX_CLEANUP: + if(geterrelevel() == ERROR) + pgstat_report_vacuum_error(errinfo->indoid, PGSTAT_EXTVAC_INDEX); + errcontext("while cleaning up index \"%s\" of relation \"%s.%s\"", errinfo->indname, errinfo->relnamespace, errinfo->relname); break; case VACUUM_ERRCB_PHASE_TRUNCATE: + if(geterrelevel() == ERROR) + pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE); + if (BlockNumberIsValid(errinfo->blkno)) errcontext("while truncating relation \"%s.%s\" to %u blocks", errinfo->relnamespace, errinfo->relname, errinfo->blkno); diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index da9079afa21..a72d01102bb 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1473,4 +1473,29 @@ FROM pg_class rel JOIN pg_namespace ns ON ns.oid = rel.relnamespace, LATERAL pg_stat_get_vacuum_indexes(rel.oid) stats -WHERE rel.relkind = 'i'; \ No newline at end of file +WHERE rel.relkind = 'i'; + +CREATE VIEW pg_stat_vacuum_database AS +SELECT + db.oid as dboid, + db.datname AS dbname, + + stats.db_blks_read AS db_blks_read, + stats.db_blks_hit AS db_blks_hit, + stats.total_blks_dirtied AS total_blks_dirtied, + stats.total_blks_written AS total_blks_written, + + stats.wal_records AS wal_records, + stats.wal_fpi AS wal_fpi, + stats.wal_bytes AS wal_bytes, + + stats.blk_read_time AS blk_read_time, + stats.blk_write_time AS blk_write_time, + + stats.delay_time AS delay_time, + stats.total_time AS total_time, + stats.wraparound_failsafe AS wraparound_failsafe, + stats.errors AS errors +FROM + pg_database db, + LATERAL pg_stat_get_vacuum_database(db.oid) stats; \ No newline at end of file diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 363cbf2bb04..4cc1d09d96f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -204,7 +204,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; -bool pgstat_track_vacuum_statistics = true; +bool pgstat_track_vacuum_statistics = false; /* ---------- * state shared with pgstat_*.c diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c index 05a8ccfdb75..d5c1e2a2cf5 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -449,6 +449,7 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) pgstat_unlock_entry(entry_ref); memset(pendingent, 0, sizeof(*pendingent)); + memset(&(pendingent)->vacuum_ext, 0, sizeof(ExtVacReport)); return true; } diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index cd4ffb50bca..6d45db6d3d2 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -205,6 +205,38 @@ pgstat_drop_relation(Relation rel) } } +/* --------- + * pgstat_report_vacuum_error() - + * + * Tell the collector about an (auto)vacuum interruption. + * --------- + */ +void +pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type) +{ + PgStat_EntryRef *entry_ref; + PgStatShared_Relation *shtabentry; + PgStat_StatTabEntry *tabentry; + Oid dboid = MyDatabaseId; + PgStat_StatDBEntry *dbentry; /* pending database entry */ + + if (!pgstat_track_counts) + return; + + entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION, + dboid, tableoid, false); + + shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats; + tabentry = &shtabentry->stats; + + tabentry->vacuum_ext.type = m_type; + pgstat_unlock_entry(entry_ref); + + dbentry = pgstat_prep_database_pending(dboid); + dbentry->vacuum_ext.errors++; + dbentry->vacuum_ext.type = m_type; +} + /* * Report that the table was just vacuumed and flush IO statistics. */ @@ -216,6 +248,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_EntryRef *entry_ref; PgStatShared_Relation *shtabentry; PgStat_StatTabEntry *tabentry; + PgStatShared_Database *dbentry; Oid dboid = (shared ? InvalidOid : MyDatabaseId); TimestampTz ts; PgStat_Counter elapsedtime; @@ -237,7 +270,8 @@ pgstat_report_vacuum(Oid tableoid, bool shared, tabentry->live_tuples = livetuples; tabentry->dead_tuples = deadtuples; - pgstat_accumulate_extvac_stats(&tabentry->vacuum_ext, params, true); + if(pgstat_track_vacuum_statistics) + pgstat_accumulate_extvac_stats(&tabentry->vacuum_ext, params, true); /* * It is quite possible that a non-aggressive VACUUM ended up skipping @@ -274,6 +308,16 @@ pgstat_report_vacuum(Oid tableoid, bool shared, */ pgstat_flush_io(false); (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO); + + if (dboid != InvalidOid) + { + entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_DATABASE, + dboid, InvalidOid, false); + dbentry = (PgStatShared_Database *) entry_ref->shared_stats; + + pgstat_accumulate_extvac_stats(&dbentry->stats.vacuum_ext, params, false); + pgstat_unlock_entry(entry_ref); + } } /* @@ -1007,9 +1051,6 @@ static void pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src, bool accumulate_reltype_specific_info) { - if(!pgstat_track_vacuum_statistics) - return; - dst->total_blks_read += src->total_blks_read; dst->total_blks_hit += src->total_blks_hit; dst->total_blks_dirtied += src->total_blks_dirtied; @@ -1021,6 +1062,8 @@ pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src, dst->blk_write_time += src->blk_write_time; dst->delay_time += src->delay_time; dst->total_time += src->total_time; + dst->wraparound_failsafe_count += src->wraparound_failsafe_count; + dst->errors += src->errors; if (!accumulate_reltype_specific_info) return; @@ -1048,7 +1091,6 @@ pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src, dst->table.index_vacuum_count += src->table.index_vacuum_count; dst->table.missed_dead_pages += src->table.missed_dead_pages; dst->table.missed_dead_tuples += src->table.missed_dead_tuples; - dst->table.wraparound_failsafe_count += src->table.wraparound_failsafe_count; } else if (dst->type == PGSTAT_EXTVAC_INDEX) { diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 80e867d773f..59eb528b20c 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -2369,7 +2369,7 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS) values[i++] = Int64GetDatum(extvacuum->table.recently_dead_tuples); values[i++] = Int64GetDatum(extvacuum->table.missed_dead_tuples); - values[i++] = Int32GetDatum(extvacuum->table.wraparound_failsafe_count); + values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count); values[i++] = Int64GetDatum(extvacuum->table.index_vacuum_count); values[i++] = Int64GetDatum(extvacuum->wal_records); @@ -2499,6 +2499,104 @@ pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS) Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS); + /* Returns the record as Datum */ + PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); +} + +Datum +pg_stat_get_vacuum_database(PG_FUNCTION_ARGS) +{ + #define PG_STAT_GET_VACUUM_DATABASE_STATS_COLS 14 + + Oid dbid = PG_GETARG_OID(0); + PgStat_StatDBEntry *dbentry; + ExtVacReport *extvacuum; + TupleDesc tupdesc; + Datum values[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0}; + bool nulls[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0}; + char buf[256]; + int i = 0; + ExtVacReport allzero; + + /* Initialise attributes information in the tuple descriptor */ + tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_DATABASE_STATS_COLS); + + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "dbid", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_ blks_read", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes", + NUMERICOID, -1, 0); + + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time", + FLOAT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time", + FLOAT8OID, -1, 0); + + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time", + FLOAT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time", + FLOAT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wraparound_failsafe_count", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "errors", + INT4OID, -1, 0); + + Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS); + + BlessTupleDesc(tupdesc); + + dbentry = pgstat_fetch_stat_dbentry(dbid); + + if (dbentry == NULL) + { + /* If the subscription is not found, initialise its stats */ + memset(&allzero, 0, sizeof(ExtVacReport)); + extvacuum = &allzero; + } + else + { + extvacuum = &(dbentry->vacuum_ext); + } + + i = 0; + + values[i++] = ObjectIdGetDatum(dbid); + + values[i++] = Int64GetDatum(extvacuum->total_blks_read); + values[i++] = Int64GetDatum(extvacuum->total_blks_hit); + values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied); + values[i++] = Int64GetDatum(extvacuum->total_blks_written); + + values[i++] = Int64GetDatum(extvacuum->wal_records); + values[i++] = Int64GetDatum(extvacuum->wal_fpi); + + /* Convert to numeric, like pg_stat_statements */ + snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes); + values[i++] = DirectFunctionCall3(numeric_in, + CStringGetDatum(buf), + ObjectIdGetDatum(0), + Int32GetDatum(-1)); + + values[i++] = Float8GetDatum(extvacuum->blk_read_time); + values[i++] = Float8GetDatum(extvacuum->blk_write_time); + values[i++] = Float8GetDatum(extvacuum->delay_time); + values[i++] = Float8GetDatum(extvacuum->total_time); + values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count); + values[i++] = Int32GetDatum(extvacuum->errors); + + Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS); + /* Returns the record as Datum */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } \ No newline at end of file diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 3f7750ad6a4..7315be9b7bc 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -1485,7 +1485,7 @@ struct config_bool ConfigureNamesBool[] = NULL }, &pgstat_track_vacuum_statistics, - true, + false, NULL, NULL, NULL }, { diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index c983e069eef..df2981abd82 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12473,12 +12473,21 @@ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', prosrc => 'pg_stat_get_rev_all_frozen_pages' }, { oid => '8004', - descr => 'pg_stat_get_vacuum_indexes return stats values', + descr => 'pg_stat_get_vacuum_indexes returns vacuum stats values for index', proname => 'pg_stat_get_vacuum_indexes', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f', proretset => 't', proargtypes => 'oid', proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8}', proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}', proargnames => '{reloid,relid,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit,pages_deleted,tuples_deleted,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,total_time}', - prosrc => 'pg_stat_get_vacuum_indexes' } + prosrc => 'pg_stat_get_vacuum_indexes' }, +{ oid => '8005', + descr => 'pg_stat_get_vacuum_database returns vacuum stats values for database', + proname => 'pg_stat_get_vacuum_database', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f', + proretset => 't', + proargtypes => 'oid', + proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8,int4,int4}', + proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}', + proargnames => '{dbid,dboid,db_blks_read,db_blks_hit,total_blks_dirtied,total_blks_written,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,total_time,wraparound_failsafe,errors}', + prosrc => 'pg_stat_get_vacuum_database' }, ] diff --git a/src/include/pgstat.h b/src/include/pgstat.h index a305836f237..dcf8cb763a4 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -155,6 +155,9 @@ typedef struct ExtVacReport int64 tuples_deleted; /* tuples deleted by vacuum */ + int32 errors; + int32 wraparound_failsafe_count; /* the number of times to prevent wraparound problem */ + ExtVacReportType type; /* heap, index, etc. */ /* ---------- @@ -184,7 +187,6 @@ typedef struct ExtVacReport int64 missed_dead_tuples; /* tuples not pruned by vacuum due to failure to get a cleanup lock */ int64 missed_dead_pages; /* pages with missed dead tuples */ int64 index_vacuum_count; /* number of index vacuumings */ - int32 wraparound_failsafe_count; /* the number of times to prevent workaround problem */ } table; struct { @@ -759,6 +761,7 @@ extern void pgstat_report_vacuum(Oid tableoid, bool shared, extern void pgstat_report_analyze(Relation rel, PgStat_Counter livetuples, PgStat_Counter deadtuples, bool resetcounter, TimestampTz starttime); +extern void pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type); /* * If stats are enabled, but pending data hasn't been prepared yet, call diff --git a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec index 5893d89573d..cfec3159580 100644 --- a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec +++ b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec @@ -18,6 +18,9 @@ teardown } session s1 +setup { + SET track_vacuum_statistics TO 'on'; + } step s1_begin_repeatable_read { BEGIN transaction ISOLATION LEVEL REPEATABLE READ; select count(ival) from test_vacuum_stat_isolation where id>900; @@ -25,6 +28,9 @@ step s1_begin_repeatable_read { step s1_commit { COMMIT; } session s2 +setup { + SET track_vacuum_statistics TO 'on'; + } step s2_insert { INSERT INTO test_vacuum_stat_isolation(id, ival) SELECT ival, ival%10 FROM generate_series(1,1000) As ival; } step s2_update { UPDATE test_vacuum_stat_isolation SET ival = ival + 2 where id > 900; } step s2_delete { DELETE FROM test_vacuum_stat_isolation where id > 900; } diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 88b0e13ed58..1741753b52d 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2259,6 +2259,23 @@ pg_stat_user_tables| SELECT relid, rev_all_visible_pages FROM pg_stat_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_stat_vacuum_database| SELECT db.oid AS dboid, + db.datname AS dbname, + stats.db_blks_read, + stats.db_blks_hit, + stats.total_blks_dirtied, + stats.total_blks_written, + stats.wal_records, + stats.wal_fpi, + stats.wal_bytes, + stats.blk_read_time, + stats.blk_write_time, + stats.delay_time, + stats.total_time, + stats.wraparound_failsafe, + stats.errors + FROM pg_database db, + LATERAL pg_stat_get_vacuum_database(db.oid) stats(dboid, db_blks_read, db_blks_hit, total_blks_dirtied, total_blks_written, wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time, delay_time, total_time, wraparound_failsafe, errors); pg_stat_vacuum_indexes| SELECT rel.oid AS relid, ns.nspname AS schemaname, rel.relname, diff --git a/src/test/regress/expected/vacuum_index_statistics.out b/src/test/regress/expected/vacuum_index_statistics.out index e00a0fc683c..9e5d33342c9 100644 --- a/src/test/regress/expected/vacuum_index_statistics.out +++ b/src/test/regress/expected/vacuum_index_statistics.out @@ -16,8 +16,12 @@ SHOW track_counts; -- must be on \set sample_size 10000 -- not enabled by default, but we want to test it... SET track_functions TO 'all'; --- Test that vacuum statistics will be empty when parameter is off. -SET track_vacuum_statistics TO 'off'; +SHOW track_vacuum_statistics; -- must be off + track_vacuum_statistics +------------------------- + off +(1 row) + CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10); INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x; ANALYZE vestat; @@ -33,12 +37,7 @@ WHERE vt.relname = 'vestat'; RESET track_vacuum_statistics; DROP TABLE vestat CASCADE; -SHOW track_vacuum_statistics; -- must be on - track_vacuum_statistics -------------------------- - on -(1 row) - +SET track_vacuum_statistics TO 'on'; -- ensure pending stats are flushed SELECT pg_stat_force_next_flush(); pg_stat_force_next_flush @@ -181,3 +180,4 @@ WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid; (1 row) DROP TABLE vestat; +RESET track_vacuum_statistics; diff --git a/src/test/regress/expected/vacuum_tables_statistics.out b/src/test/regress/expected/vacuum_tables_and_db_statistics.out similarity index 82% rename from src/test/regress/expected/vacuum_tables_statistics.out rename to src/test/regress/expected/vacuum_tables_and_db_statistics.out index b5ea9c9ab1e..f79ae58fb38 100644 --- a/src/test/regress/expected/vacuum_tables_statistics.out +++ b/src/test/regress/expected/vacuum_tables_and_db_statistics.out @@ -6,7 +6,6 @@ -- number of frozen and visible pages removed by backend. -- Statistic wal_fpi is not displayed in this test because its behavior is unstable. -- --- conditio sine qua non SHOW track_counts; -- must be on track_counts -------------- @@ -16,8 +15,12 @@ SHOW track_counts; -- must be on \set sample_size 10000 -- not enabled by default, but we want to test it... SET track_functions TO 'all'; --- Test that vacuum statistics will be empty when parameter is off. -SET track_vacuum_statistics TO 'off'; +SHOW track_vacuum_statistics; -- must be off + track_vacuum_statistics +------------------------- + off +(1 row) + CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10); INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x; ANALYZE vestat; @@ -37,12 +40,12 @@ WHERE vt.relname = 'vestat'; RESET track_vacuum_statistics; DROP TABLE vestat CASCADE; -SHOW track_vacuum_statistics; -- must be on - track_vacuum_statistics -------------------------- - on -(1 row) - +CREATE DATABASE regression_statistic_vacuum_db; +CREATE DATABASE regression_statistic_vacuum_db1; +\c regression_statistic_vacuum_db; +SET track_vacuum_statistics TO on; +-- not enabled by default, but we want to test it... +SET track_functions TO 'all'; -- ensure pending stats are flushed SELECT pg_stat_force_next_flush(); pg_stat_force_next_flush @@ -165,7 +168,7 @@ FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB; dwr | dfpi | dwb -----+------+----- - t | t | t + f | t | f (1 row) -- @@ -225,3 +228,69 @@ FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relna (1 row) DROP TABLE vestat CASCADE; +-- Now check vacuum statistics for current database +SELECT dbname, + db_blks_hit > 0 AS db_blks_hit, + total_blks_dirtied > 0 AS total_blks_dirtied, + total_blks_written > 0 AS total_blks_written, + wal_records > 0 AS wal_records, + wal_fpi > 0 AS wal_fpi, + wal_bytes > 0 AS wal_bytes, + total_time > 0 AS total_time +FROM +pg_stat_vacuum_database +WHERE dbname = current_database(); + dbname | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time +--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------ + regression_statistic_vacuum_db | t | t | t | t | t | t | t +(1 row) + +-- ensure pending stats are flushed +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10); +INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x; +ANALYZE vestat; +UPDATE vestat SET x = 10001; +VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat; +\c regression_statistic_vacuum_db1; +SET track_vacuum_statistics TO on; +-- Now check vacuum statistics for postgres database from another database +SELECT dbname, + db_blks_hit > 0 AS db_blks_hit, + total_blks_dirtied > 0 AS total_blks_dirtied, + total_blks_written > 0 AS total_blks_written, + wal_records > 0 AS wal_records, + wal_fpi > 0 AS wal_fpi, + wal_bytes > 0 AS wal_bytes, + total_time > 0 AS total_time +FROM +pg_stat_vacuum_database +WHERE dbname = 'regression_statistic_vacuum_db'; + dbname | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time +--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------ + regression_statistic_vacuum_db | t | t | t | t | t | t | t +(1 row) + +\c regression_statistic_vacuum_db +SET track_vacuum_statistics TO on; +DROP TABLE vestat CASCADE; +\c regression_statistic_vacuum_db1; +SET track_vacuum_statistics TO on; +SELECT count(*) +FROM pg_database d +CROSS JOIN pg_stat_get_vacuum_tables(0) +WHERE oid = 0; -- must be 0 + count +------- + 0 +(1 row) + +\c postgres +DROP DATABASE regression_statistic_vacuum_db1; +DROP DATABASE regression_statistic_vacuum_db; +RESET track_vacuum_statistics; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 977a87a5b1f..19c76b96830 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -141,4 +141,4 @@ test: tablespace # Check vacuum statistics # ---------- test: vacuum_index_statistics -test: vacuum_tables_statistics \ No newline at end of file +test: vacuum_tables_and_db_statistics \ No newline at end of file diff --git a/src/test/regress/regression.diffs b/src/test/regress/regression.diffs new file mode 100644 index 00000000000..d4c24f41b40 --- /dev/null +++ b/src/test/regress/regression.diffs @@ -0,0 +1,12 @@ +diff -U3 /home/alena/postgrespro__copy61/src/test/regress/expected/vacuum_tables_and_db_statistics.out /home/alena/postgrespro__copy61/src/test/regress/results/vacuum_tables_and_db_statistics.out +--- /home/alena/postgrespro__copy61/src/test/regress/expected/vacuum_tables_and_db_statistics.out 2025-02-04 18:03:12.645127559 +0300 ++++ /home/alena/postgrespro__copy61/src/test/regress/results/vacuum_tables_and_db_statistics.out 2025-02-04 18:36:10.238349991 +0300 +@@ -168,7 +168,7 @@ + SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB; + dwr | dfpi | dwb + -----+------+----- +- t | t | t ++ f | t | f + (1 row) + + -- diff --git a/src/test/regress/sql/vacuum_index_statistics.sql b/src/test/regress/sql/vacuum_index_statistics.sql index ae146e1d23f..9b7e645187d 100644 --- a/src/test/regress/sql/vacuum_index_statistics.sql +++ b/src/test/regress/sql/vacuum_index_statistics.sql @@ -14,8 +14,7 @@ SHOW track_counts; -- must be on -- not enabled by default, but we want to test it... SET track_functions TO 'all'; --- Test that vacuum statistics will be empty when parameter is off. -SET track_vacuum_statistics TO 'off'; +SHOW track_vacuum_statistics; -- must be off CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10); INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x; @@ -33,7 +32,7 @@ WHERE vt.relname = 'vestat'; RESET track_vacuum_statistics; DROP TABLE vestat CASCADE; -SHOW track_vacuum_statistics; -- must be on +SET track_vacuum_statistics TO 'on'; -- ensure pending stats are flushed SELECT pg_stat_force_next_flush(); @@ -149,3 +148,4 @@ FROM pg_stat_vacuum_indexes vt, pg_class c WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid; DROP TABLE vestat; +RESET track_vacuum_statistics; diff --git a/src/test/regress/sql/vacuum_tables_statistics.sql b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql similarity index 81% rename from src/test/regress/sql/vacuum_tables_statistics.sql rename to src/test/regress/sql/vacuum_tables_and_db_statistics.sql index 5bc34bec64b..ca7dbde9387 100644 --- a/src/test/regress/sql/vacuum_tables_statistics.sql +++ b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql @@ -7,15 +7,13 @@ -- Statistic wal_fpi is not displayed in this test because its behavior is unstable. -- --- conditio sine qua non SHOW track_counts; -- must be on \set sample_size 10000 -- not enabled by default, but we want to test it... SET track_functions TO 'all'; --- Test that vacuum statistics will be empty when parameter is off. -SET track_vacuum_statistics TO 'off'; +SHOW track_vacuum_statistics; -- must be off CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10); INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x; @@ -36,7 +34,13 @@ WHERE vt.relname = 'vestat'; RESET track_vacuum_statistics; DROP TABLE vestat CASCADE; -SHOW track_vacuum_statistics; -- must be on +CREATE DATABASE regression_statistic_vacuum_db; +CREATE DATABASE regression_statistic_vacuum_db1; +\c regression_statistic_vacuum_db; +SET track_vacuum_statistics TO on; + +-- not enabled by default, but we want to test it... +SET track_functions TO 'all'; -- ensure pending stats are flushed SELECT pg_stat_force_next_flush(); @@ -180,4 +184,59 @@ VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat; SELECT vm_new_frozen_pages = :pf AS vm_new_frozen_pages,vm_new_visible_pages = :pv AS vm_new_visible_pages,vm_new_visible_frozen_pages = :pvf AS vm_new_visible_frozen_pages, rev_all_frozen_pages = :hafp AS rev_all_frozen_pages,rev_all_visible_pages = :havp AS rev_all_visible_pages FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid; -DROP TABLE vestat CASCADE; \ No newline at end of file +DROP TABLE vestat CASCADE; + +-- Now check vacuum statistics for current database +SELECT dbname, + db_blks_hit > 0 AS db_blks_hit, + total_blks_dirtied > 0 AS total_blks_dirtied, + total_blks_written > 0 AS total_blks_written, + wal_records > 0 AS wal_records, + wal_fpi > 0 AS wal_fpi, + wal_bytes > 0 AS wal_bytes, + total_time > 0 AS total_time +FROM +pg_stat_vacuum_database +WHERE dbname = current_database(); + +-- ensure pending stats are flushed +SELECT pg_stat_force_next_flush(); + +CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10); +INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x; +ANALYZE vestat; +UPDATE vestat SET x = 10001; +VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat; + +\c regression_statistic_vacuum_db1; +SET track_vacuum_statistics TO on; + +-- Now check vacuum statistics for postgres database from another database +SELECT dbname, + db_blks_hit > 0 AS db_blks_hit, + total_blks_dirtied > 0 AS total_blks_dirtied, + total_blks_written > 0 AS total_blks_written, + wal_records > 0 AS wal_records, + wal_fpi > 0 AS wal_fpi, + wal_bytes > 0 AS wal_bytes, + total_time > 0 AS total_time +FROM +pg_stat_vacuum_database +WHERE dbname = 'regression_statistic_vacuum_db'; + +\c regression_statistic_vacuum_db +SET track_vacuum_statistics TO on; + +DROP TABLE vestat CASCADE; + +\c regression_statistic_vacuum_db1; +SET track_vacuum_statistics TO on; +SELECT count(*) +FROM pg_database d +CROSS JOIN pg_stat_get_vacuum_tables(0) +WHERE oid = 0; -- must be 0 + +\c postgres +DROP DATABASE regression_statistic_vacuum_db1; +DROP DATABASE regression_statistic_vacuum_db; +RESET track_vacuum_statistics; \ No newline at end of file -- 2.34.1