From 6052a2e9c0c01e53fa083f9e63e1cee610ae09a0 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Thu, 16 Nov 2017 17:47:16 +0900 Subject: [PATCH 4/4] Add truncation information and oldestxmin to pg_stat_all_tables This patch adds truncated and tried-but-not-truncated pages in the last vacuum. This is intended to use to find uncertain failure of truncation or unwanted aggressive trancation. This also adds oldestxmin, find whether a long transaction hindered vacuuming or not. --- doc/src/sgml/monitoring.sgml | 15 ++++++++++++ src/backend/catalog/system_views.sql | 3 +++ src/backend/commands/vacuum.c | 25 +++++++++++--------- src/backend/commands/vacuumlazy.c | 15 ++++++++++++ src/backend/postmaster/pgstat.c | 12 ++++++++++ src/backend/utils/adt/pgstatfuncs.c | 45 ++++++++++++++++++++++++++++++++++++ src/include/catalog/pg_proc.h | 6 +++++ src/include/pgstat.h | 9 ++++++++ src/test/regress/expected/rules.out | 9 ++++++++ 9 files changed, 128 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index e2bf2d2..d496fe8 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -2581,11 +2581,26 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i (not counting VACUUM FULL) + last_vacuum_truncated + bigint + Number actually truncated pages during the last vacuum on this table + + + last_vacuum_untruncated + bigint + Number tried but actually not truncated pages during the last vacuum on this table + + last_vacuum_index_scans integer Number of splitted index scans performed during the last vacuum on this table + last_vacuum_oldext_xmin + bigint + The oldest xmin used by the last vacuum on this table + + last_vacuum_status text The status of last autovacuum. diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index cd0ea69..0eb3a76 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -529,7 +529,10 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_last_analyze_time(C.oid) as last_analyze, pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, pg_stat_get_vacuum_count(C.oid) AS vacuum_count, + pg_stat_get_last_vacuum_truncated(C.oid) AS last_vacuum_truncated, + pg_stat_get_last_vacuum_untruncated(C.oid) AS last_vacuum_untruncated, pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans, + pg_stat_get_last_vacuum_oldest_xmin(C.oid) AS last_vacuum_oldest_xmin, pg_stat_get_last_vacuum_status(C.oid) AS last_vacuum_status, pg_stat_get_autovacuum_fail_count(C.oid) AS autovacuum_fail_count, pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count, diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index ac7c2ac..a0c5a12 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1467,8 +1467,9 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) if (!onerel) { - pgstat_report_vacuum(relid, false, 0, 0, 0, - PGSTAT_VACUUM_SKIP_LOCK_FAILED, 0, 0); + pgstat_report_vacuum(relid, false, + 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_LOCK_FAILED, + InvalidTransactionId, 0, 0); PopActiveSnapshot(); CommitTransactionCommand(); return false; @@ -1503,7 +1504,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared, - 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0); + 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET, + InvalidTransactionId, 0, 0); PopActiveSnapshot(); CommitTransactionCommand(); @@ -1525,8 +1527,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared, - 0, 0, 0, - PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0); + 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET, + InvalidTransactionId, 0, 0); PopActiveSnapshot(); CommitTransactionCommand(); @@ -1546,8 +1548,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared, - 0, 0, 0, - PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0); + 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET, + InvalidTransactionId, 0, 0); PopActiveSnapshot(); CommitTransactionCommand(); @@ -1565,8 +1567,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared, - 0, 0, 0, - PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0); + 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET, + InvalidTransactionId, 0, 0); PopActiveSnapshot(); CommitTransactionCommand(); @@ -1622,8 +1624,9 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ cluster_rel(relid, InvalidOid, false, (options & VACOPT_VERBOSE) != 0); - pgstat_report_vacuum(relid, isshared, 0, 0, 0, - PGSTAT_VACUUM_FULL_FINISHED, 0, 0); + pgstat_report_vacuum(relid, isshared, 0, 0, 0, 0, 0, + PGSTAT_VACUUM_FULL_FINISHED, + InvalidTransactionId, 0, 0); } else lazy_vacuum_rel(onerel, options, params, vac_strategy); diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index af38962..fcd1e3e 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -121,6 +121,7 @@ typedef struct LVRelStats double new_rel_tuples; /* new estimated total # of tuples */ double new_dead_tuples; /* new estimated total # of dead tuples */ BlockNumber pages_removed; + BlockNumber pages_not_removed; double tuples_deleted; BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */ /* List of TIDs of tuples we intend to delete */ @@ -246,6 +247,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, vacrelstats->old_rel_tuples = onerel->rd_rel->reltuples; vacrelstats->num_index_scans = 0; vacrelstats->pages_removed = 0; + vacrelstats->pages_not_removed = 0; vacrelstats->lock_waiter_detected = false; /* @@ -284,8 +286,15 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, * Optionally truncate the relation. */ if (should_attempt_truncation(vacrelstats)) + { lazy_truncate_heap(onerel, vacrelstats); + /* just paranoia */ + if (vacrelstats->rel_pages >= vacrelstats->nonempty_pages) + vacrelstats->pages_not_removed += + vacrelstats->rel_pages - vacrelstats->nonempty_pages; + } + /* Report that we are now doing final cleanup */ pgstat_progress_update_param(PROGRESS_VACUUM_PHASE, PROGRESS_VACUUM_PHASE_FINAL_CLEANUP); @@ -350,7 +359,10 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, onerel->rd_rel->relisshared, new_live_tuples, vacrelstats->new_dead_tuples, + vacrelstats->pages_removed, + vacrelstats->pages_not_removed, vacrelstats->num_index_scans, + OldestXmin, aggressive ? PGSTAT_VACUUM_AGGRESSIVE_FINISHED : PGSTAT_VACUUM_FINISHED, @@ -2262,7 +2274,10 @@ lazy_vacuum_cancel_handler(void) stats->shared, stats->new_rel_tuples, stats->new_dead_tuples, + stats->pages_removed, + stats->pages_not_removed, stats->num_index_scans, + OldestXmin, err, phase, geterrcode()); } diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 540c580..2d3a6ae 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -1404,7 +1404,10 @@ pgstat_report_autovac(Oid dboid) void pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples, PgStat_Counter deadtuples, + PgStat_Counter pages_removed, + PgStat_Counter pages_not_removed, PgStat_Counter num_index_scans, + TransactionId oldestxmin, PgStat_Counter status, PgStat_Counter last_phase, PgStat_Counter errcode) { @@ -1420,7 +1423,10 @@ pgstat_report_vacuum(Oid tableoid, bool shared, msg.m_vacuumtime = GetCurrentTimestamp(); msg.m_live_tuples = livetuples; msg.m_dead_tuples = deadtuples; + msg.m_pages_removed = pages_removed; + msg.m_pages_not_removed = pages_not_removed; msg.m_num_index_scans = num_index_scans; + msg.m_oldest_xmin = oldestxmin; msg.m_vacuum_status = status; msg.m_vacuum_last_phase = last_phase; msg.m_vacuum_errcode = errcode; @@ -4592,7 +4598,10 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create) result->n_live_tuples = 0; result->n_dead_tuples = 0; result->changes_since_analyze = 0; + result->n_pages_removed = 0; + result->n_pages_not_removed = 0; result->n_index_scans = 0; + result->oldest_xmin = InvalidTransactionId; result->blocks_fetched = 0; result->blocks_hit = 0; result->vacuum_timestamp = 0; @@ -6008,7 +6017,10 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) case PGSTAT_VACUUM_AGGRESSIVE_FINISHED: tabentry->n_live_tuples = msg->m_live_tuples; tabentry->n_dead_tuples = msg->m_dead_tuples; + tabentry->n_pages_removed = msg->m_pages_removed; + tabentry->n_pages_not_removed = msg->m_pages_not_removed; tabentry->n_index_scans = msg->m_num_index_scans; + tabentry->oldest_xmin = msg->m_oldest_xmin; tabentry->vacuum_failcount = 0; if (msg->m_autovacuum) diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index bc5d370..769a196 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -204,6 +204,36 @@ pg_stat_get_vacuum_necessity(PG_FUNCTION_ARGS) } Datum +pg_stat_get_last_vacuum_truncated(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 result; + PgStat_StatTabEntry *tabentry; + + if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL) + result = 0; + else + result = (int64) (tabentry->n_pages_removed); + + PG_RETURN_INT64(result); +} + +Datum +pg_stat_get_last_vacuum_untruncated(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 result; + PgStat_StatTabEntry *tabentry; + + if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL) + result = 0; + else + result = (int64) (tabentry->n_pages_not_removed); + + PG_RETURN_INT64(result); +} + +Datum pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS) { Oid relid = PG_GETARG_OID(0); @@ -219,6 +249,21 @@ pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS) } Datum +pg_stat_get_last_vacuum_oldest_xmin(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + TransactionId result; + PgStat_StatTabEntry *tabentry; + + if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL) + result = InvalidTransactionId; + else + result = (int32) (tabentry->oldest_xmin); + + return TransactionIdGetDatum(result); +} + +Datum pg_stat_get_last_vacuum_status(PG_FUNCTION_ARGS) { Oid relid = PG_GETARG_OID(0); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index a51e321..a3623dd 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2889,6 +2889,12 @@ DATA(insert OID = 6118 ( pg_stat_get_subscription PGNSP PGUID 12 1 0 0 0 f f f DESCR("statistics: information about subscription"); DATA(insert OID = 2579 ( pg_stat_get_vacuum_necessity PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_vacuum_necessity _null_ _null_ _null_ )); DESCR("statistics: true if needs vacuum"); +DATA(insert OID = 3422 ( pg_stat_get_last_vacuum_untruncated PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 20 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_untruncated _null_ _null_ _null_ )); +DESCR("statistics: pages left untruncated in the last vacuum"); +DATA(insert OID = 3423 ( pg_stat_get_last_vacuum_truncated PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 20 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_truncated _null_ _null_ _null_ )); +DESCR("statistics: pages truncated in the last vacuum"); +DATA(insert OID = 3424 ( pg_stat_get_last_vacuum_oldest_xmin PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 28 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_oldest_xmin _null_ _null_ _null_ )); +DESCR("statistics: The oldest xmin used in the last vacuum"); DATA(insert OID = 3281 ( pg_stat_get_last_vacuum_index_scans PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 23 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_index_scans _null_ _null_ _null_ )); DESCR("statistics: number of index scans in the last vacuum"); DATA(insert OID = 3420 ( pg_stat_get_last_vacuum_status PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_status _null_ _null_ _null_ )); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 62c2369..5b8bf7e 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -383,7 +383,10 @@ typedef struct PgStat_MsgVacuum TimestampTz m_vacuumtime; PgStat_Counter m_live_tuples; PgStat_Counter m_dead_tuples; + PgStat_Counter m_pages_removed; + PgStat_Counter m_pages_not_removed; PgStat_Counter m_num_index_scans; + TransactionId m_oldest_xmin; PgStat_Counter m_vacuum_status; PgStat_Counter m_vacuum_last_phase; PgStat_Counter m_vacuum_errcode; @@ -647,7 +650,10 @@ typedef struct PgStat_StatTabEntry PgStat_Counter n_live_tuples; PgStat_Counter n_dead_tuples; PgStat_Counter changes_since_analyze; + PgStat_Counter n_pages_removed; + PgStat_Counter n_pages_not_removed; PgStat_Counter n_index_scans; + TransactionId oldest_xmin; PgStat_Counter blocks_fetched; PgStat_Counter blocks_hit; @@ -1189,7 +1195,10 @@ extern void pgstat_reset_single_counter(Oid objectid, PgStat_Single_Reset_Type t extern void pgstat_report_autovac(Oid dboid); extern void pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples, PgStat_Counter deadtuples, + PgStat_Counter pages_removed, + PgStat_Counter pages_not_removed, PgStat_Counter num_index_scans, + TransactionId oldextxmin, PgStat_Counter status, PgStat_Counter last_phase, PgStat_Counter errcode); extern void pgstat_report_analyze(Relation rel, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f0a8416..fb1ea49 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1765,7 +1765,10 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, + pg_stat_get_last_vacuum_truncated(c.oid) AS last_vacuum_truncated, + pg_stat_get_last_vacuum_untruncated(c.oid) AS last_vacuum_untruncated, pg_stat_get_last_vacuum_index_scans(c.oid) AS last_vacuum_index_scans, + pg_stat_get_last_vacuum_oldest_xmin(c.oid) AS last_vacuum_oldest_xmin, pg_stat_get_last_vacuum_status(c.oid) AS last_vacuum_status, pg_stat_get_autovacuum_fail_count(c.oid) AS autovacuum_fail_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, @@ -1916,7 +1919,10 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, + pg_stat_all_tables.last_vacuum_truncated, + pg_stat_all_tables.last_vacuum_untruncated, pg_stat_all_tables.last_vacuum_index_scans, + pg_stat_all_tables.last_vacuum_oldest_xmin, pg_stat_all_tables.last_vacuum_status, pg_stat_all_tables.autovacuum_fail_count, pg_stat_all_tables.autovacuum_count, @@ -1963,7 +1969,10 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, + pg_stat_all_tables.last_vacuum_truncated, + pg_stat_all_tables.last_vacuum_untruncated, pg_stat_all_tables.last_vacuum_index_scans, + pg_stat_all_tables.last_vacuum_oldest_xmin, pg_stat_all_tables.last_vacuum_status, pg_stat_all_tables.autovacuum_fail_count, pg_stat_all_tables.autovacuum_count, -- 2.9.2