+/* ----------
+ * Backend types
+ * ----------
You seem to forget to add "*/" into the above comment.
This issue could cause the following compiler warning.
../../src/include/pgstat.h:761:1: warning: '/*' within block comment
[-Wcomment]
Thanks for the comment. I fixed.
The contents of pg_stat_walwrites are reset when the server
is restarted. Isn't this problematic? IMO since pg_stat_walwrites
is a collected statistics view, basically its contents should be
kept even in the case of server restart.
I agree your opinion.
I modified to use the statistics collector and persist the wal
statistics.
I changed the view name from pg_stat_walwrites to pg_stat_walwriter.
I think it is better to match naming scheme with other views like
pg_stat_bgwriter,
which is for bgwriter statistics but it has the statistics related to
backend.
The pg_stat_walwriter is not security restricted now, so ordinary users
can access it.
I has the same security level as pg_stat_archiver.If you have any
comments, please let me know.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d973e1149a..9ea5c9b2e2 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -424,6 +424,13 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_walwriter</structname><indexterm><primary>pg_stat_walwriter</primary></indexterm></entry>
+ <entry>One row only, showing statistics about the WAL writing activity. See
+ <xref linkend="monitoring-pg-stat-walwriter-view"/> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_database</structname><indexterm><primary>pg_stat_database</primary></indexterm></entry>
<entry>One row per database, showing database-wide statistics. See
@@ -3264,6 +3271,56 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</sect2>
+ <sect2 id="monitoring-pg-stat-walwriter-view">
+ <title><structname>pg_stat_walwriter</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_walwriter</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_walwriter</structname> view will always have a
+ single row, containing data about the WAL writing activity of the cluster.
+ </para>
+
+ <table id="pg-stat-walwriter-view" xreflabel="pg_stat_walwriter">
+ <title><structname>pg_stat_walwriter</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dirty_writes</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of dirty WAL writes when the <xref linkend="guc-wal-buffers"/> are full
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+</sect2>
+
<sect2 id="monitoring-pg-stat-database-view">
<title><structname>pg_stat_database</structname></title>
@@ -4652,8 +4709,9 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
argument. The argument can be <literal>bgwriter</literal> to reset
all the counters shown in
the <structname>pg_stat_bgwriter</structname>
- view, or <literal>archiver</literal> to reset all the counters shown in
- the <structname>pg_stat_archiver</structname> view.
+ view, <literal>archiver</literal> to reset all the counters shown in
+ the <structname>pg_stat_archiver</structname> view ,or <literal>walwriter</literal>
+ to reset all the counters shown in the <structname>pg_stat_walwriter</structname> view.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 09c01ed4ae..47b148b3b5 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2193,6 +2193,7 @@ AdvanceXLInsertBuffer(XLogRecPtr upto, bool opportunistic)
WriteRqst.Write = OldPageRqstPtr;
WriteRqst.Flush = 0;
XLogWrite(WriteRqst, false);
+ WalWriterStats.m_xlog_dirty_writes++;
LWLockRelease(WALWriteLock);
TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY_DONE();
}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index a2d61302f9..6b43ad61be 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1000,6 +1000,11 @@ CREATE VIEW pg_stat_progress_analyze AS
FROM pg_stat_get_progress_info('ANALYZE') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
+CREATE VIEW pg_stat_walwriter AS
+ SELECT
+ pg_stat_get_xlog_dirty_writes() AS dirty_writes,
+ pg_stat_get_walwriter_stat_reset_time() AS stats_reset;
+
CREATE VIEW pg_stat_progress_vacuum AS
SELECT
S.pid AS pid, S.datid AS datid, D.datname AS datname,
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 8116b23614..154cdc5ddb 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -141,6 +141,14 @@ char *pgstat_stat_tmpname = NULL;
*/
PgStat_MsgBgWriter BgWriterStats;
+/*
+ * WalWriter global statistics counter.
+ * This counter is incremented by each XLogWrite call,
+ * both in the wal writer process and each backend.
+ * And then, sent to the stat collector process.
+ */
+PgStat_MsgWalWriter WalWriterStats;
+
/*
* List of SLRU names that we keep stats for. There is no central registry of
* SLRUs, so we use this fixed list instead. The "other" entry is used for
@@ -281,6 +289,7 @@ static int localNumBackends = 0;
*/
static PgStat_ArchiverStats archiverStats;
static PgStat_GlobalStats globalStats;
+static PgStat_WalwriterStats walwriterStats;
static PgStat_SLRUStats slruStats[SLRU_NUM_ELEMENTS];
/*
@@ -353,6 +362,7 @@ static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
+static void pgstat_recv_walwriter(PgStat_MsgWalWriter *msg, int len);
static void pgstat_recv_slru(PgStat_MsgSLRU *msg, int len);
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
@@ -938,6 +948,9 @@ pgstat_report_stat(bool force)
/* Now, send function statistics */
pgstat_send_funcstats();
+ /* Now, send wal writer statistics */
+ pgstat_send_walwriter();
+
/* Finally send SLRU statistics */
pgstat_send_slru();
}
@@ -1370,11 +1383,13 @@ pgstat_reset_shared_counters(const char *target)
msg.m_resettarget = RESET_ARCHIVER;
else if (strcmp(target, "bgwriter") == 0)
msg.m_resettarget = RESET_BGWRITER;
+ else if (strcmp(target, "walwriter") == 0)
+ msg.m_resettarget = RESET_WALWRITER;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"", target),
- errhint("Target must be \"archiver\" or \"bgwriter\".")));
+ errhint("Target must be \"archiver\" or \"bgwriter\" or \"walwriter\".")));
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
pgstat_send(&msg, sizeof(msg));
@@ -2674,6 +2689,21 @@ pgstat_fetch_global(void)
return &globalStats;
}
+/*
+ * ---------
+ * pgstat_fetch_stat_walwriter() -
+ *
+ * Support function for the SQL-callable pgstat* functions. Returns
+ * a pointer to the walwriter statistics struct.
+ * ---------
+ */
+PgStat_WalwriterStats *
+pgstat_fetch_stat_walwriter(void)
+{
+ backend_read_statsfile();
+
+ return &walwriterStats;
+}
/*
* ---------
@@ -4407,6 +4437,38 @@ pgstat_send_bgwriter(void)
MemSet(&BgWriterStats, 0, sizeof(BgWriterStats));
}
+/* ----------
+ * pgstat_send_walwriter() -
+ *
+ * Send walwriter statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_walwriter(void)
+{
+ /* We assume this initializes to zeroes */
+ static const PgStat_MsgWalWriter all_zeroes;
+
+ /*
+ * This function can be called even if nothing at all has happened. In
+ * this case, avoid sending a completely empty message to the stats
+ * collector.
+ */
+ if (memcmp(&WalWriterStats, &all_zeroes, sizeof(PgStat_MsgWalWriter)) == 0)
+ return;
+
+ /*
+ * Prepare and send the message
+ */
+ pgstat_setheader(&WalWriterStats.m_hdr, PGSTAT_MTYPE_WALWRITER);
+ pgstat_send(&WalWriterStats, sizeof(WalWriterStats));
+
+ /*
+ * Clear out the statistics buffer, so it can be re-used.
+ */
+ MemSet(&WalWriterStats, 0, sizeof(WalWriterStats));
+}
+
/* ----------
* pgstat_send_slru() -
*
@@ -4646,6 +4708,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_bgwriter(&msg.msg_bgwriter, len);
break;
+ case PGSTAT_MTYPE_WALWRITER:
+ pgstat_recv_walwriter(&msg.msg_walwriter, len);
+ break;
+
case PGSTAT_MTYPE_SLRU:
pgstat_recv_slru(&msg.msg_slru, len);
break;
@@ -4915,6 +4981,12 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
(void) rc; /* we'll check for error with ferror */
+ /*
+ * Write archiver stats struct
+ */
+ rc = fwrite(&walwriterStats, sizeof(walwriterStats), 1, fpout);
+ (void) rc; /* we'll check for error with ferror */
+
/*
* Write SLRU stats struct
*/
@@ -5179,6 +5251,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
*/
memset(&globalStats, 0, sizeof(globalStats));
memset(&archiverStats, 0, sizeof(archiverStats));
+ memset(&walwriterStats, 0, sizeof(walwriterStats));
memset(&slruStats, 0, sizeof(slruStats));
/*
@@ -5187,6 +5260,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
*/
globalStats.stat_reset_timestamp = GetCurrentTimestamp();
archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
+ walwriterStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
/*
* Set the same reset timestamp for all SLRU items too.
@@ -5256,6 +5330,17 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
goto done;
}
+ /*
+ * Read walwriter stats struct
+ */
+ if (fread(&walwriterStats, 1, sizeof(walwriterStats), fpin) != sizeof(walwriterStats))
+ {
+ ereport(pgStatRunningInCollector ? LOG : WARNING,
+ (errmsg("corrupted statistics file \"%s\"", statfile)));
+ memset(&walwriterStats, 0, sizeof(walwriterStats));
+ goto done;
+ }
+
/*
* Read SLRU stats struct
*/
@@ -5266,7 +5351,6 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
memset(&slruStats, 0, sizeof(slruStats));
goto done;
}
-
/*
* We found an existing collector stats file. Read it and put all the
* hashtable entries into place.
@@ -5620,6 +5704,17 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
return false;
}
+ /*
+ * Read walwriter stats struct
+ */
+ if (fread(&walwriterStats, 1, sizeof(walwriterStats), fpin) != sizeof(walwriterStats))
+ {
+ ereport(pgStatRunningInCollector ? LOG : WARNING,
+ (errmsg("corrupted statistics file \"%s\"", statfile)));
+ FreeFile(fpin);
+ return false;
+ }
+
/*
* Read SLRU stats struct
*/
@@ -6196,6 +6291,12 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
memset(&archiverStats, 0, sizeof(archiverStats));
archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
}
+ else if (msg->m_resettarget == RESET_WALWRITER)
+ {
+ /* Reset the walwriter statistics for the cluster. */
+ memset(&walwriterStats, 0, sizeof(walwriterStats));
+ walwriterStats.stat_reset_timestamp = GetCurrentTimestamp();
+ }
/*
* Presumably the sender of this message validated the target, don't
@@ -6410,6 +6511,18 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
globalStats.buf_alloc += msg->m_buf_alloc;
}
+/* ----------
+ * pgstat_recv_walwriter() -
+ *
+ * Process a WALWRITER message.
+ * ----------
+ */
+static void
+pgstat_recv_walwriter(PgStat_MsgWalWriter *msg, int len)
+{
+ walwriterStats.xlog_dirty_writes += msg->m_xlog_dirty_writes;
+}
+
/* ----------
* pgstat_recv_slru() -
*
diff --git a/src/backend/postmaster/walwriter.c b/src/backend/postmaster/walwriter.c
index 45a2757969..309cce2c64 100644
--- a/src/backend/postmaster/walwriter.c
+++ b/src/backend/postmaster/walwriter.c
@@ -243,6 +243,8 @@ WalWriterMain(void)
else if (left_till_hibernate > 0)
left_till_hibernate--;
+ pgstat_send_walwriter();
+
/*
* Sleep until we are signaled or WalWriterDelay has elapsed. If we
* haven't done anything useful for quite some time, lengthen the
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 95738a4e34..672ed9c8ca 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1697,6 +1697,18 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
PG_RETURN_INT64(pgstat_fetch_global()->buf_alloc);
}
+Datum
+pg_stat_get_xlog_dirty_writes(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(pgstat_fetch_stat_walwriter()->xlog_dirty_writes);
+}
+
+Datum
+pg_stat_get_walwriter_stat_reset_time(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_TIMESTAMPTZ(pgstat_fetch_stat_walwriter()->stat_reset_timestamp);
+}
+
/*
* Returns statistics of SLRU caches.
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1dd325e0e6..131709ebfe 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5476,6 +5476,14 @@
proname => 'pg_stat_get_buf_alloc', provolatile => 's', proparallel => 'r',
prorettype => 'int8', proargtypes => '', prosrc => 'pg_stat_get_buf_alloc' },
+{ oid => '8000', descr => 'statistics: number of dirty WAL writes',
+ proname => 'pg_stat_get_xlog_dirty_writes', provolatile => 's', proparallel => 'r',
+ prorettype => 'int8', proargtypes => '', prosrc => 'pg_stat_get_xlog_dirty_writes' },
+{ oid => '8001', descr => 'statistics: last reset for the walwriter',
+ proname => 'pg_stat_get_walwriter_stat_reset_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
+ prosrc => 'pg_stat_get_walwriter_stat_reset_time' },
+
{ oid => '2306', descr => 'statistics: information about SLRU caches',
proname => 'pg_stat_get_slru', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 807a9c1edf..873da60e70 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -61,6 +61,7 @@ typedef enum StatMsgType
PGSTAT_MTYPE_ANALYZE,
PGSTAT_MTYPE_ARCHIVER,
PGSTAT_MTYPE_BGWRITER,
+ PGSTAT_MTYPE_WALWRITER,
PGSTAT_MTYPE_SLRU,
PGSTAT_MTYPE_FUNCSTAT,
PGSTAT_MTYPE_FUNCPURGE,
@@ -122,7 +123,8 @@ typedef struct PgStat_TableCounts
typedef enum PgStat_Shared_Reset_Target
{
RESET_ARCHIVER,
- RESET_BGWRITER
+ RESET_BGWRITER,
+ RESET_WALWRITER
} PgStat_Shared_Reset_Target;
/* Possible object types for resetting single counters */
@@ -436,6 +438,17 @@ typedef struct PgStat_MsgBgWriter
PgStat_Counter m_checkpoint_sync_time;
} PgStat_MsgBgWriter;
+/* ----------
+ * PgStat_MsgWalWriter Sent by the walwriter to update statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgWalWriter
+{
+ PgStat_MsgHdr m_hdr;
+
+ PgStat_Counter m_xlog_dirty_writes; /* number of WAL write caused by full of WAL buffers */
+} PgStat_MsgWalWriter;
+
/* ----------
* PgStat_MsgSLRU Sent by a backend to update SLRU statistics.
* ----------
@@ -596,6 +609,7 @@ typedef union PgStat_Msg
PgStat_MsgAnalyze msg_analyze;
PgStat_MsgArchiver msg_archiver;
PgStat_MsgBgWriter msg_bgwriter;
+ PgStat_MsgWalWriter msg_walwriter;
PgStat_MsgSLRU msg_slru;
PgStat_MsgFuncstat msg_funcstat;
PgStat_MsgFuncpurge msg_funcpurge;
@@ -745,6 +759,20 @@ typedef struct PgStat_GlobalStats
TimestampTz stat_reset_timestamp;
} PgStat_GlobalStats;
+/*
+ * Walwriter statistics kept in the stats collector
+ */
+typedef struct PgStat_WalwriterStats
+{
+ PgStat_Counter xlog_dirty_writes; /* number of WAL write caused by full of WAL buffers */
+ TimestampTz stat_reset_timestamp; /* last time when the stats reset */
+} PgStat_WalwriterStats;
+
+/* ----------
+ * Backend types
+ * ----------
+ */
+
/*
* SLRU statistics kept in the stats collector
*/
@@ -1261,6 +1289,11 @@ extern char *pgstat_stat_filename;
*/
extern PgStat_MsgBgWriter BgWriterStats;
+/*
+ * WAL writes statistics counter is updated in XLogWrite function
+ */
+extern PgStat_MsgWalWriter WalWriterStats;
+
/*
* Updated by pgstat_count_buffer_*_time macros
*/
@@ -1460,6 +1493,7 @@ extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
extern void pgstat_send_archiver(const char *xlog, bool failed);
extern void pgstat_send_bgwriter(void);
+extern void pgstat_send_walwriter(void);
/* ----------
* Support functions for the SQL-callable functions to
@@ -1474,6 +1508,7 @@ extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
extern int pgstat_fetch_stat_numbackends(void);
extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
extern PgStat_GlobalStats *pgstat_fetch_global(void);
+extern PgStat_WalwriterStats *pgstat_fetch_stat_walwriter(void);
extern PgStat_SLRUStats *pgstat_fetch_slru(void);
extern void pgstat_count_slru_page_zeroed(int slru_idx);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2a18dc423e..4c7e6c5316 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2146,6 +2146,8 @@ pg_stat_wal_receiver| SELECT s.pid,
s.conninfo
FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port, conninfo)
WHERE (s.pid IS NOT NULL);
+pg_stat_walwriter| SELECT pg_stat_get_xlog_dirty_writes() AS dirty_writes,
+ pg_stat_get_walwriter_stat_reset_time() AS stats_reset;
pg_stat_xact_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
c.relname,
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 1cffc3349d..1e828ccfde 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -76,6 +76,13 @@ select count(*) >= 0 as ok from pg_prepared_xacts;
t
(1 row)
+-- There will surely and maximum one record
+select count(*) = 1 as ok from pg_stat_walwriter;
+ ok
+----
+ t
+(1 row)
+
-- This is to record the prevailing planner enable_foo settings during
-- a regression test run.
select name, setting from pg_settings where name like 'enable%';
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index ac4a0e1cbb..ab1a416c13 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -37,6 +37,9 @@ select count(*) = 0 as ok from pg_prepared_statements;
-- See also prepared_xacts.sql
select count(*) >= 0 as ok from pg_prepared_xacts;
+-- There will surely and maximum one record
+select count(*) = 1 as ok from pg_stat_walwriter;
+
-- This is to record the prevailing planner enable_foo settings during
-- a regression test run.
select name, setting from pg_settings where name like 'enable%';
