From f9a09aa2952039a9956b44d929b9df74d62a4cd4 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Tue, 28 Oct 2025 16:21:51 +0900 Subject: [PATCH] Add wal_fpi_bytes to pg_stat_wal and pg_stat_get_backend_wal() This new counter, called "wal_fpi_bytes", tracks the total amount in bytes of full page images (FPIs) generated in WAL. This data becomes available globally via pg_stat_wal, and for backend statistics via pg_stat_get_backend_wal(). Previously, this information could only be retrieved with pg_waldump or pg_walinspect, which may not be available depending on the environment, and are expensive to execute. It offers hints about how much FPIs impact the WAL generated, which could be a large percentage for some workloads, as well as the effects of wal_compression or page holes. Bump catalog version. Bump PGSTAT_FILE_FORMAT_ID, due to the addition of wal_fpi_bytes in PgStat_WalCounters. Author: Shinya Kato Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/CAOzEurQtZEAfg6P0kU3Wa-f9BWQOi0RzJEMPN56wNTOmJLmfaQ@mail.gmail.com --- doc/src/sgml/monitoring.sgml | 9 +++++++++ src/backend/access/transam/xloginsert.c | 6 ++++++ src/backend/catalog/system_views.sql | 1 + src/backend/executor/instrument.c | 2 ++ src/backend/utils/activity/pgstat_backend.c | 1 + src/backend/utils/activity/pgstat_wal.c | 1 + src/backend/utils/adt/pgstatfuncs.c | 20 ++++++++++++++------ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 12 ++++++------ src/include/executor/instrument.h | 1 + src/include/pgstat.h | 3 ++- src/test/regress/expected/rules.out | 3 ++- 12 files changed, 46 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index d5f0fb7ba7c..f3bf527d5b4 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -3323,6 +3323,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + wal_fpi_bytes numeric + + + Total amount of WAL full page images in bytes + + + wal_buffers_full bigint diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c index 496e0fa4ac6..b3abf386f80 100644 --- a/src/backend/access/transam/xloginsert.c +++ b/src/backend/access/transam/xloginsert.c @@ -33,12 +33,14 @@ #include "access/xloginsert.h" #include "catalog/pg_control.h" #include "common/pg_lzcompress.h" +#include "executor/instrument.h" #include "miscadmin.h" #include "pg_trace.h" #include "replication/origin.h" #include "storage/bufmgr.h" #include "storage/proc.h" #include "utils/memutils.h" +#include "utils/pgstat_internal.h" /* * Guess the maximum buffer size required to store a compressed version of @@ -796,6 +798,10 @@ XLogRecordAssemble(RmgrId rmid, uint8 info, } total_len += bimg.length; + + /* Track the WAL full page images in bytes */ + pgWalUsage.wal_fpi_bytes += bimg.length; + pgstat_report_fixed = true; } if (needs_data) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 823776c1498..dec8df4f8ee 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1221,6 +1221,7 @@ CREATE VIEW pg_stat_wal AS w.wal_records, w.wal_fpi, w.wal_bytes, + w.wal_fpi_bytes, w.wal_buffers_full, w.stats_reset FROM pg_stat_get_wal() w; diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c index 56e635f4700..9e11c662a7c 100644 --- a/src/backend/executor/instrument.c +++ b/src/backend/executor/instrument.c @@ -280,6 +280,7 @@ WalUsageAdd(WalUsage *dst, WalUsage *add) dst->wal_bytes += add->wal_bytes; dst->wal_records += add->wal_records; dst->wal_fpi += add->wal_fpi; + dst->wal_fpi_bytes += add->wal_fpi_bytes; dst->wal_buffers_full += add->wal_buffers_full; } @@ -289,5 +290,6 @@ WalUsageAccumDiff(WalUsage *dst, const WalUsage *add, const WalUsage *sub) dst->wal_bytes += add->wal_bytes - sub->wal_bytes; dst->wal_records += add->wal_records - sub->wal_records; dst->wal_fpi += add->wal_fpi - sub->wal_fpi; + dst->wal_fpi_bytes += add->wal_fpi_bytes - sub->wal_fpi_bytes; dst->wal_buffers_full += add->wal_buffers_full - sub->wal_buffers_full; } diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index a864ae8e6a6..199ba2cc17a 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -252,6 +252,7 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref) WALSTAT_ACC(wal_records, wal_usage_diff); WALSTAT_ACC(wal_fpi, wal_usage_diff); WALSTAT_ACC(wal_bytes, wal_usage_diff); + WALSTAT_ACC(wal_fpi_bytes, wal_usage_diff); #undef WALSTAT_ACC /* diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c index 0d04480d2f6..d4edb8b5733 100644 --- a/src/backend/utils/activity/pgstat_wal.c +++ b/src/backend/utils/activity/pgstat_wal.c @@ -121,6 +121,7 @@ pgstat_wal_flush_cb(bool nowait) WALSTAT_ACC(wal_records, wal_usage_diff); WALSTAT_ACC(wal_fpi, wal_usage_diff); WALSTAT_ACC(wal_bytes, wal_usage_diff); + WALSTAT_ACC(wal_fpi_bytes, wal_usage_diff); WALSTAT_ACC(wal_buffers_full, wal_usage_diff); #undef WALSTAT_ACC diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 1fe33df2756..a710508979e 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1637,7 +1637,7 @@ static Datum pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters, TimestampTz stat_reset_timestamp) { -#define PG_STAT_WAL_COLS 5 +#define PG_STAT_WAL_COLS 6 TupleDesc tupdesc; Datum values[PG_STAT_WAL_COLS] = {0}; bool nulls[PG_STAT_WAL_COLS] = {0}; @@ -1651,9 +1651,11 @@ pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters, INT8OID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 3, "wal_bytes", NUMERICOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 4, "wal_buffers_full", + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "wal_fpi_bytes", + NUMERICOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "wal_buffers_full", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 5, "stats_reset", + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "stats_reset", TIMESTAMPTZOID, -1, 0); BlessTupleDesc(tupdesc); @@ -1669,12 +1671,18 @@ pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters, ObjectIdGetDatum(0), Int32GetDatum(-1)); - values[3] = Int64GetDatum(wal_counters.wal_buffers_full); + snprintf(buf, sizeof buf, UINT64_FORMAT, wal_counters.wal_fpi_bytes); + values[3] = DirectFunctionCall3(numeric_in, + CStringGetDatum(buf), + ObjectIdGetDatum(0), + Int32GetDatum(-1)); + + values[4] = Int64GetDatum(wal_counters.wal_buffers_full); if (stat_reset_timestamp != 0) - values[4] = TimestampTzGetDatum(stat_reset_timestamp); + values[5] = TimestampTzGetDatum(stat_reset_timestamp); else - nulls[4] = true; + nulls[5] = true; /* Returns the record as Datum */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 1b0b16a343f..18e95179ab6 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202510221 +#define CATALOG_VERSION_NO 202510281 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index eecb43ec6f0..9121a382f76 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6029,16 +6029,16 @@ { oid => '1136', descr => 'statistics: information about WAL activity', proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => '', - proallargtypes => '{int8,int8,numeric,int8,timestamptz}', - proargmodes => '{o,o,o,o,o}', - proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}', + proallargtypes => '{int8,int8,numeric,numeric,int8,timestamptz}', + proargmodes => '{o,o,o,o,o,o}', + proargnames => '{wal_records,wal_fpi,wal_bytes,wal_fpi_bytes,wal_buffers_full,stats_reset}', prosrc => 'pg_stat_get_wal' }, { oid => '6313', descr => 'statistics: backend WAL activity', proname => 'pg_stat_get_backend_wal', provolatile => 'v', proparallel => 'r', prorettype => 'record', proargtypes => 'int4', - proallargtypes => '{int4,int8,int8,numeric,int8,timestamptz}', - proargmodes => '{i,o,o,o,o,o}', - proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}', + proallargtypes => '{int4,int8,int8,numeric,numeric,int8,timestamptz}', + proargmodes => '{i,o,o,o,o,o,o}', + proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_fpi_bytes,wal_buffers_full,stats_reset}', prosrc => 'pg_stat_get_backend_wal' }, { oid => '6248', descr => 'statistics: information about WAL prefetching', proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't', diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h index 03653ab6c6c..ffe470f2b84 100644 --- a/src/include/executor/instrument.h +++ b/src/include/executor/instrument.h @@ -53,6 +53,7 @@ typedef struct WalUsage int64 wal_records; /* # of WAL records produced */ int64 wal_fpi; /* # of WAL full page images produced */ uint64 wal_bytes; /* size of WAL records produced */ + uint64 wal_fpi_bytes; /* size of WAL full page images produced */ int64 wal_buffers_full; /* # of times the WAL buffers became full */ } WalUsage; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index bc8077cbae6..7ae503e71a2 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -212,7 +212,7 @@ typedef struct PgStat_TableXactStatus * ------------------------------------------------------------ */ -#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB9 +#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBA typedef struct PgStat_ArchiverStats { @@ -473,6 +473,7 @@ typedef struct PgStat_WalCounters PgStat_Counter wal_records; PgStat_Counter wal_fpi; uint64 wal_bytes; + uint64 wal_fpi_bytes; PgStat_Counter wal_buffers_full; } PgStat_WalCounters; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 16753b2e4c0..77e25ca029e 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2306,9 +2306,10 @@ pg_stat_user_tables| SELECT relid, pg_stat_wal| SELECT wal_records, wal_fpi, wal_bytes, + wal_fpi_bytes, wal_buffers_full, stats_reset - FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_reset); + FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_fpi_bytes, wal_buffers_full, stats_reset); pg_stat_wal_receiver| SELECT pid, status, receive_start_lsn, -- 2.39.5