agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Julien Rouhaud <[email protected]>
Subject: [PATCH v5 3/3] Keep track of (auto)vacuum WAL usage in pg_stat_database.
Date: Wed, 4 Mar 2020 20:09:22 +0100
---
src/backend/catalog/system_views.sql | 6 ++
src/backend/commands/vacuum.c | 11 ++++
src/backend/postmaster/pgstat.c | 56 ++++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 96 ++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 24 +++++++
src/include/pgstat.h | 27 +++++++-
src/test/regress/expected/rules.out | 6 ++
7 files changed, 225 insertions(+), 1 deletion(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b8a3f46912..a3d1ac2523 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -892,6 +892,12 @@ CREATE VIEW pg_stat_database AS
pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,
pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,
pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,
+ pg_stat_get_db_vac_wal_records(D.oid) AS vac_wal_records,
+ pg_stat_get_db_vac_wal_fp_records(D.oid) AS vac_wal_fp_records,
+ pg_stat_get_db_vac_wal_bytes(D.oid) AS vac_wal_bytes,
+ pg_stat_get_db_autovac_wal_records(D.oid) AS autovac_wal_records,
+ pg_stat_get_db_autovac_wal_fp_records(D.oid) AS autovac_wal_fp_records,
+ pg_stat_get_db_autovac_wal_bytes(D.oid) AS autovac_wal_bytes,
pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
FROM (
SELECT 0 AS oid, NULL::name AS datname
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d625d17bf4..14a235a2ed 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -275,6 +275,8 @@ vacuum(List *relations, VacuumParams *params,
BufferAccessStrategy bstrategy, bool isTopLevel)
{
static bool in_vacuum = false;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
const char *stmttype;
volatile bool in_outer_xact,
@@ -489,6 +491,15 @@ vacuum(List *relations, VacuumParams *params,
{
in_vacuum = false;
VacuumCostActive = false;
+ walusage.wal_records = pgWalUsage.wal_records -
+ walusage_start.wal_records;
+ walusage.wal_fp_records = pgWalUsage.wal_fp_records -
+ walusage_start.wal_fp_records;
+ walusage.wal_bytes = pgWalUsage.wal_bytes - walusage_start.wal_bytes;
+ pgstat_report_vac_wal_usage(walusage.wal_records,
+ walusage.wal_fp_records,
+ walusage.wal_bytes,
+ IsAutoVacuumWorkerProcess());
}
PG_END_TRY();
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index f9287b7942..c9d65669ce 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -330,6 +330,7 @@ static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int le
static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len);
static void pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len);
static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
+static void pgstat_recv_vac_walusage(PgStat_MsgVacWalUsage *msg, int len);
/* ------------------------------------------------------------
* Public functions called from postmaster follow
@@ -1572,6 +1573,30 @@ pgstat_report_tempfile(size_t filesize)
pgstat_send(&msg, sizeof(msg));
}
+/* --------
+ * pgstat_report_vac_wal_usage() -
+ *
+ * Tell the collector about (auto)vacuum WAL usage.
+ * --------
+ */
+void
+pgstat_report_vac_wal_usage(long wal_records, long wal_fp_records,
+ long wal_bytes, bool autovacuum)
+{
+ PgStat_MsgVacWalUsage msg;
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACWALUSAGE);
+ msg.m_databaseid = MyDatabaseId;
+ msg.m_wal_records = wal_records;
+ msg.m_wal_fp_records = wal_fp_records;
+ msg.m_wal_bytes = wal_bytes;
+ msg.m_autovacuum = autovacuum;
+ pgstat_send(&msg, sizeof(msg));
+}
+
/* ----------
* pgstat_ping() -
@@ -4525,6 +4550,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_tempfile(&msg.msg_tempfile, len);
break;
+ case PGSTAT_MTYPE_VACWALUSAGE:
+ pgstat_recv_vac_walusage(&msg.msg_vac_walusage, len);
+ break;
+
case PGSTAT_MTYPE_CHECKSUMFAILURE:
pgstat_recv_checksum_failure(&msg.msg_checksumfailure,
len);
@@ -6282,6 +6311,33 @@ pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len)
dbentry->n_temp_files += 1;
}
+/* ----------
+ * pgstat_recv_vac_walusage() -
+ *
+ * Process a VACWALUSAGE message.
+ * ----------
+ */
+static void
+pgstat_recv_vac_walusage(PgStat_MsgVacWalUsage *msg, int len)
+{
+ PgStat_StatDBEntry *dbentry;
+
+ dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+ if (msg->m_autovacuum)
+ {
+ dbentry->n_autovac_wal_records += msg->m_wal_records;
+ dbentry->n_autovac_wal_fp_records += msg->m_wal_fp_records;
+ dbentry->autovac_wal_bytes += msg->m_wal_bytes;
+ }
+ else
+ {
+ dbentry->n_vac_wal_records += msg->m_wal_records;
+ dbentry->n_vac_wal_fp_records += msg->m_wal_fp_records;
+ dbentry->vac_wal_bytes += msg->m_wal_bytes;
+ }
+}
+
/* ----------
* pgstat_recv_funcstat() -
*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index cea01534a5..1bc7291c2b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1606,6 +1606,102 @@ pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(result);
}
+Datum
+pg_stat_get_db_vac_wal_records(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->n_vac_wal_records);
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_vac_wal_fp_records(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->n_vac_wal_fp_records);
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_vac_wal_bytes(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->vac_wal_bytes);
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_records(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->n_autovac_wal_records);
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_fp_records(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->n_autovac_wal_fp_records);
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_bytes(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->autovac_wal_bytes);
+
+ PG_RETURN_INT64(result);
+}
+
Datum
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7fb574f9dc..8b1c1487ca 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5372,6 +5372,30 @@
proname => 'pg_stat_get_db_blk_write_time', provolatile => 's',
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
prosrc => 'pg_stat_get_db_blk_write_time' },
+{ oid => '8176', descr => 'statistics: number of vacuum wal records',
+ proname => 'pg_stat_get_db_vac_wal_records', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_vac_wal_records' },
+{ oid => '8177', descr => 'statistics: number of vacuum wal full page records',
+ proname => 'pg_stat_get_db_vac_wal_fp_records', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_vac_wal_fp_records' },
+{ oid => '8178', descr => 'statistics: number of vacuum wal bytes',
+ proname => 'pg_stat_get_db_vac_wal_bytes', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_vac_wal_bytes' },
+{ oid => '8179', descr => 'statistics: number of autovacuum wal records',
+ proname => 'pg_stat_get_db_autovac_wal_records', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_autovac_wal_records' },
+{ oid => '8180', descr => 'statistics: number of autovacuum wal full page records',
+ proname => 'pg_stat_get_db_autovac_wal_fp_records', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_autovac_wal_fp_records' },
+{ oid => '8181', descr => 'statistics: number of autovacuum wal bytes',
+ proname => 'pg_stat_get_db_autovac_wal_bytes', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_autovac_wal_bytes' },
{ oid => '3195', descr => 'statistics: information about WAL archiver',
proname => 'pg_stat_get_archiver', proisstrict => 'f', provolatile => 's',
proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 1a19921f80..40fb97bdc9 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -65,7 +65,8 @@ typedef enum StatMsgType
PGSTAT_MTYPE_RECOVERYCONFLICT,
PGSTAT_MTYPE_TEMPFILE,
PGSTAT_MTYPE_DEADLOCK,
- PGSTAT_MTYPE_CHECKSUMFAILURE
+ PGSTAT_MTYPE_CHECKSUMFAILURE,
+ PGSTAT_MTYPE_VACWALUSAGE
} StatMsgType;
/* ----------
@@ -544,6 +545,21 @@ typedef struct PgStat_MsgChecksumFailure
TimestampTz m_failure_time;
} PgStat_MsgChecksumFailure;
+/* ----------
+ * PgStat_MsgVacWalUsage Sent by the backend to tell the collector
+ * about (auto)vacuum WAL usage.
+ * ----------
+ */
+typedef struct PgStat_MsgVacWalUsage
+{
+ PgStat_MsgHdr m_hdr;
+ Oid m_databaseid;
+ long m_wal_records;
+ long m_wal_fp_records;
+ long m_wal_bytes;
+ bool m_autovacuum;
+} PgStat_MsgVacWalUsage;
+
/* ----------
* PgStat_Msg Union over all possible messages.
@@ -571,6 +587,7 @@ typedef union PgStat_Msg
PgStat_MsgDeadlock msg_deadlock;
PgStat_MsgTempFile msg_tempfile;
PgStat_MsgChecksumFailure msg_checksumfailure;
+ PgStat_MsgVacWalUsage msg_vac_walusage;
} PgStat_Msg;
@@ -613,6 +630,12 @@ typedef struct PgStat_StatDBEntry
TimestampTz last_checksum_failure;
PgStat_Counter n_block_read_time; /* times in microseconds */
PgStat_Counter n_block_write_time;
+ PgStat_Counter n_vac_wal_records;
+ PgStat_Counter n_vac_wal_fp_records;
+ PgStat_Counter vac_wal_bytes;
+ PgStat_Counter n_autovac_wal_records;
+ PgStat_Counter n_autovac_wal_fp_records;
+ PgStat_Counter autovac_wal_bytes;
TimestampTz stat_reset_timestamp;
TimestampTz stats_timestamp; /* time of db stats file update */
@@ -1261,6 +1284,8 @@ extern void pgstat_bestart(void);
extern void pgstat_report_activity(BackendState state, const char *cmd_str);
extern void pgstat_report_tempfile(size_t filesize);
+extern void pgstat_report_vac_wal_usage(long wal_records, long wal_fp_record,
+ long wal_bytes, bool autovacuum);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
extern const char *pgstat_get_wait_event(uint32 wait_event_info);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index c7304611c3..aa311cd594 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1833,6 +1833,12 @@ pg_stat_database| SELECT d.oid AS datid,
pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure,
pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time,
pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time,
+ pg_stat_get_db_vac_wal_records(d.oid) AS vac_wal_records,
+ pg_stat_get_db_vac_wal_fp_records(d.oid) AS vac_wal_fp_records,
+ pg_stat_get_db_vac_wal_bytes(d.oid) AS vac_wal_bytes,
+ pg_stat_get_db_autovac_wal_records(d.oid) AS autovac_wal_records,
+ pg_stat_get_db_autovac_wal_fp_records(d.oid) AS autovac_wal_fp_records,
+ pg_stat_get_db_autovac_wal_bytes(d.oid) AS autovac_wal_bytes,
pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset
FROM ( SELECT 0 AS oid,
NULL::name AS datname
--
2.20.1
--jI8keyz6grp/JLjh--
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v5 3/3] Keep track of (auto)vacuum WAL usage in pg_stat_database.
In-Reply-To: <no-message-id-198904@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox