前言
之前写了一篇通过新增last_(auto)vacuum_start last_(auto)analyze_start来提升vacuum/analze执行时长的观测能力，即通过查询pg_stat_all_tables就可以获取上一次Vacuum/analyze的执行时长。
但是，这种方案必须在一次完整的Vacuum/analye结束后，才可以使用last_(auto)vacuum - last_(auto)vacuum_start去获取执行时长，使用起来就有点别扭了。
所以朋友告诉我，为什么不直接记录last_(auto)vacuum_duration呢，这样随时查pg_stat_all_tables可以直接获取表上次vacuum/analyze的时长，更直观更准确。
方案
新增统计信息，即对pg_stat_all_tables新增last_(auto)vacuum_duration,last_(auto)analyze_duration。
以vacuum为例，伪代码：
heap_vacuum_rel:
begintime=GetCurrentTimestamp();
...
do vacuum:
...
endtime=GetCurrentTimestamp();
duration = endtime - begintime;
last_vacuum_duration = duration;
代码
From caeddbf1ead4078d3dd6af36c0bab27f747409a9 Mon Sep 17 00:00:00 2001
From: Nickyoung0 <yxbshare@163.com>
Date: Fri, 13 Jun 2025 16:29:55 +0800
Subject: [PATCH] add the duration for last_vacuum/analyze or
last_autovacuum/autoanalyze
---
.../src/backend/access/heap/vacuumlazy.c | 38 ++++++++++++++++++-
.../src/backend/catalog/system_views.sql | 6 ++-
.../src/backend/commands/analyze.c | 38 ++++++++++++++++++-
.../src/backend/utils/adt/pgstatfuncs.c | 30 +++++++++++++++
.../src/include/catalog/pg_proc.dat | 16 ++++++++
postgresql-17.4/src/include/pgstat.h | 4 ++
6 files changed, 127 insertions(+), 5 deletions(-)
diff --git a/postgresql-17.4/src/backend/access/heap/vacuumlazy.c b/postgresql-17.4/src/backend/access/heap/vacuumlazy.c
index f2d2598..9c78200 100644
--- a/postgresql-17.4/src/backend/access/heap/vacuumlazy.c
+++ b/postgresql-17.4/src/backend/access/heap/vacuumlazy.c
@@ -59,7 +59,7 @@
#include "utils/memutils.h"
#include "utils/pg_rusage.h"
#include "utils/timestamp.h"
-
+#include "utils/pgstat_internal.h"
/*
* Space/time tradeoff parameters: do these need to be user-tunable?
@@ -305,7 +305,14 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
new_rel_pages,
new_rel_allvisible;
PGRUsage ru0;
- TimestampTz starttime = 0;
+ TimestampTz starttime = 0,
+ begintime = 0;
+ long secs;
+ int usecs;
+ int msecs;
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
PgStat_Counter startreadtime = 0,
startwritetime = 0;
WalUsage startwalusage = pgWalUsage;
@@ -327,6 +334,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
}
}
+ begintime = GetCurrentTimestamp();
+
pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
RelationGetRelid(rel));
@@ -591,6 +600,31 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
vacrel->missed_dead_tuples);
pgstat_progress_end_command();
+ TimestampDifference(begintime,
+ GetCurrentTimestamp(),
+ &secs, &usecs);
+ msecs = usecs / 1000;
+ /* Add commentMore actions
+ * Store the data in the table's hash table entry.
+ * block acquiring lock for the same reason as pgstat_report_autovac()
+ */
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ MyDatabaseId, RelationGetRelid(rel), false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ if (AmAutoVacuumWorkerProcess())
+
+ {
+ snprintf(tabentry->last_autovacuum_duration, 32, "%ld.%03d",
+ secs * 1000 + msecs, usecs % 1000);
+ }
+ else
+ snprintf(tabentry->last_vacuum_duration, 32, "%ld.%03d",
+ secs * 1000 + msecs, usecs % 1000);
+ pgstat_unlock_entry(entry_ref);
+
if (instrument)
{
TimestampTz endtime = GetCurrentTimestamp();
diff --git a/postgresql-17.4/src/backend/catalog/system_views.sql b/postgresql-17.4/src/backend/catalog/system_views.sql
index efb29ad..411609f 100644
--- a/postgresql-17.4/src/backend/catalog/system_views.sql
+++ b/postgresql-17.4/src/backend/catalog/system_views.sql
@@ -695,7 +695,11 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
- pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
+ pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+ pg_stat_get_last_vacuum_duration(c.oid) AS last_vacuum_duration,
+ pg_stat_get_last_autovacuum_duration(c.oid) AS last_autovacuum_duration,
+ pg_stat_get_last_analyze_duration(c.oid) AS last_analyze_duration,
+ pg_stat_get_last_autoanalyze_duration(c.oid) AS last_autoanalyze_duration
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/postgresql-17.4/src/backend/commands/analyze.c b/postgresql-17.4/src/backend/commands/analyze.c
index c590a2a..a98da59 100644
--- a/postgresql-17.4/src/backend/commands/analyze.c
+++ b/postgresql-17.4/src/backend/commands/analyze.c
@@ -57,7 +57,7 @@
#include "utils/spccache.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
-
+#include "utils/pgstat_internal.h"
/* Per-index data for ANALYZE */
typedef struct AnlIndexData
@@ -298,7 +298,14 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
totaldeadrows;
HeapTuple *rows;
PGRUsage ru0;
- TimestampTz starttime = 0;
+ TimestampTz starttime = 0,
+ begintime = 0;
+ long secs;
+ int usecs;
+ int msecs;
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
MemoryContext caller_context;
Oid save_userid;
int save_sec_context;
@@ -353,6 +360,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
starttime = GetCurrentTimestamp();
}
+ begintime = GetCurrentTimestamp();
+
/*
* Determine which columns to analyze
*
@@ -722,6 +731,31 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
/* Done with indexes */
vac_close_indexes(nindexes, Irel, NoLock);
+
+ TimestampDifference(begintime,
+ GetCurrentTimestamp(),
+ &secs, &usecs);
+ msecs = usecs / 1000;
+ /* Add commentMore actions
+ * Store the data in the table's hash table entry.
+ * block acquiring lock for the same reason as pgstat_report_autovac()
+ */
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ MyDatabaseId, RelationGetRelid(onerel), false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ if (AmAutoVacuumWorkerProcess())
+
+ {
+ snprintf(tabentry->last_autoanalyze_duration, 32, "%ld.%03d",
+ secs * 1000 + msecs, usecs % 1000);
+ }
+ else
+ snprintf(tabentry->last_analyze_duration, 32, "%ld.%03d",
+ secs * 1000 + msecs, usecs % 1000);
+ pgstat_unlock_entry(entry_ref);
/* Log the action if appropriate */
if (AmAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
diff --git a/postgresql-17.4/src/backend/utils/adt/pgstatfuncs.c b/postgresql-17.4/src/backend/utils/adt/pgstatfuncs.c
index 2575dba..7aaab84 100644
--- a/postgresql-17.4/src/backend/utils/adt/pgstatfuncs.c
+++ b/postgresql-17.4/src/backend/utils/adt/pgstatfuncs.c
@@ -140,6 +140,36 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time)
/* pg_stat_get_lastscan */
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan)
+#define PG_STAT_GET_RELENTRY_STRING(stat) \
+Datum \
+CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS) \
+{ \
+ Oid relid = PG_GETARG_OID(0); \
+ char *result; \
+ PgStat_StatTabEntry *tabentry; \
+ \
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL) \
+ PG_RETURN_NULL(); \
+ else \
+ result = tabentry->stat; \
+ \
+ if (result == NULL || result[0] == '\0') \
+ PG_RETURN_NULL(); \
+ else \
+ PG_RETURN_TEXT_P(cstring_to_text(result)); \
+}
+/* pg_stat_get_last_vacuum_duration */
+PG_STAT_GET_RELENTRY_STRING(last_vacuum_duration)
+
+/* pg_stat_get_last_autovacuum_duration */
+PG_STAT_GET_RELENTRY_STRING(last_autovacuum_duration)
+
+/* pg_stat_get_last_analyze_duration */
+PG_STAT_GET_RELENTRY_STRING(last_analyze_duration)
+
+/* pg_stat_get_last_autoanalyze_duration */
+PG_STAT_GET_RELENTRY_STRING(last_autoanalyze_duration)
+
Datum
pg_stat_get_function_calls(PG_FUNCTION_ARGS)
{
diff --git a/postgresql-17.4/src/include/catalog/pg_proc.dat b/postgresql-17.4/src/include/catalog/pg_proc.dat
index 6a5476d..2809c39 100644
--- a/postgresql-17.4/src/include/catalog/pg_proc.dat
+++ b/postgresql-17.4/src/include/catalog/pg_proc.dat
@@ -5445,6 +5445,22 @@
proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
prosrc => 'pg_stat_get_autoanalyze_count' },
+{ oid => '6347', descr => 'statistics: last manual vacuum duration for a table',
+ proname => 'pg_stat_get_last_vacuum_duration', provolatile => 's',
+ proparallel => 'r', prorettype => 'text', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_vacuum_duration' },
+{ oid => '6348', descr => 'statistics: last auto vacuum duration for a table',
+ proname => 'pg_stat_get_last_autovacuum_duration', provolatile => 's',
+ proparallel => 'r', prorettype => 'text', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_autovacuum_duration' },
+{ oid => '6349', descr => 'statistics: last manual analyze duration for a table',
+ proname => 'pg_stat_get_last_analyze_duration', provolatile => 's',
+ proparallel => 'r', prorettype => 'text', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_analyze_duration' },
+{ oid => '6350', descr => 'statistics: last auto analyze duration for a table',
+ proname => 'pg_stat_get_last_autoanalyze_duration', provolatile => 's',
+ proparallel => 'r', prorettype => 'text', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_autoanalyze_duration' },
{ oid => '1936', descr => 'statistics: currently active backend IDs',
proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/postgresql-17.4/src/include/pgstat.h b/postgresql-17.4/src/include/pgstat.h
index 2136239..f3f3423 100644
--- a/postgresql-17.4/src/include/pgstat.h
+++ b/postgresql-17.4/src/include/pgstat.h
@@ -426,6 +426,10 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter analyze_count;
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+ char last_vacuum_duration[32];
+ char last_autovacuum_duration[32];
+ char last_analyze_duration[32];
+ char last_autoanalyze_duration[32];
} PgStat_StatTabEntry;
typedef struct PgStat_WalStats
--
2.39.5 (Apple Git-154)
验证
pg_stat_all_tables中已经记录了last (auto)vacuum/(auto)analyze的执行时间。
比如可以精细化统计关键表的Autovacuum时长，定制监控项来预警。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
