Skip to content

Commit 0146924

Browse files
MasaoFujiiMasaoFujii
committed
Track total number of WAL records, FPIs and bytes generated in the cluster.
Commit 6b466bf allowed pg_stat_statements to track the number of WAL records, full page images and bytes that each statement generated. Similarly this commit allows us to track the cluster-wide WAL statistics counters. New columns wal_records, wal_fpi and wal_bytes are added into the pg_stat_wal view, and reports the total number of WAL records, full page images and bytes generated in the , respectively. Author: Masahiro Ikeda Reviewed-by: Amit Kapila, Movead Li, Kyotaro Horiguchi, Fujii Masao Discussion: https://postgr.es/m/35ef960128b90bfae3b3fdf60a3a860f@oss.nttdata.com
1 parent 91624c2 commit 0146924

File tree

7 files changed

+104
-9
lines changed

7 files changed

+104
-9
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3447,6 +3447,33 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
34473447
</thead>
34483448

34493449
<tbody>
3450+
<row>
3451+
<entry role="catalog_table_entry"><para role="column_definition">
3452+
<structfield>wal_records</structfield> <type>bigint</type>
3453+
</para>
3454+
<para>
3455+
Total number of WAL records generated
3456+
</para></entry>
3457+
</row>
3458+
3459+
<row>
3460+
<entry role="catalog_table_entry"><para role="column_definition">
3461+
<structfield>wal_fpi</structfield> <type>bigint</type>
3462+
</para>
3463+
<para>
3464+
Total number of WAL full page images generated
3465+
</para></entry>
3466+
</row>
3467+
3468+
<row>
3469+
<entry role="catalog_table_entry"><para role="column_definition">
3470+
<structfield>wal_bytes</structfield> <type>numeric</type>
3471+
</para>
3472+
<para>
3473+
Total amount of WAL bytes generated
3474+
</para></entry>
3475+
</row>
3476+
34503477
<row>
34513478
<entry role="catalog_table_entry"><para role="column_definition">
34523479
<structfield>wal_buffers_full</structfield> <type>bigint</type>

‎src/backend/catalog/system_views.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -993,6 +993,9 @@ CREATE VIEW pg_stat_bgwriter AS
993993

994994
CREATE VIEW pg_stat_wal AS
995995
SELECT
996+
w.wal_records,
997+
w.wal_fpi,
998+
w.wal_bytes,
996999
w.wal_buffers_full,
9971000
w.stats_reset
9981001
FROM pg_stat_get_wal() w;

‎src/backend/postmaster/pgstat.c

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@
4141
#include "catalog/pg_database.h"
4242
#include "catalog/pg_proc.h"
4343
#include "common/ip.h"
44+
#include "executor/instrument.h"
4445
#include "libpq/libpq.h"
4546
#include "libpq/pqsignal.h"
4647
#include "mb/pg_wchar.h"
@@ -143,6 +144,14 @@ char *pgstat_stat_tmpname = NULL;
143144
PgStat_MsgBgWriter BgWriterStats;
144145
PgStat_MsgWal WalStats;
145146

147+
/*
148+
* WAL usage counters saved from pgWALUsage at the previous call to
149+
* pgstat_send_wal(). This is used to calculate how much WAL usage
150+
* happens between pgstat_send_wal() calls, by substracting
151+
* the previous counters from the current ones.
152+
*/
153+
static WalUsage prevWalUsage;
154+
146155
/*
147156
* List of SLRU names that we keep stats for. There is no central registry of
148157
* SLRUs, so we use this fixed list instead. The "other" entry is used for
@@ -3048,6 +3057,13 @@ pgstat_initialize(void)
30483057
MyBEEntry = &BackendStatusArray[MaxBackends + MyAuxProcType];
30493058
}
30503059

3060+
/*
3061+
* Initialize prevWalUsage with pgWalUsage so that pgstat_send_wal() can
3062+
* calculate how much pgWalUsage counters are increased by substracting
3063+
* prevWalUsage from pgWalUsage.
3064+
*/
3065+
prevWalUsage = pgWalUsage;
3066+
30513067
/* Set up a process-exit hook to clean up */
30523068
on_shmem_exit(pgstat_beshutdown_hook, 0);
30533069
}
@@ -4577,6 +4593,20 @@ pgstat_send_wal(void)
45774593
/* We assume this initializes to zeroes */
45784594
static const PgStat_MsgWal all_zeroes;
45794595

4596+
WalUsage walusage;
4597+
4598+
/*
4599+
* Calculate how much WAL usage counters are increased by substracting the
4600+
* previous counters from the current ones. Fill the results in WAL stats
4601+
* message.
4602+
*/
4603+
MemSet(&walusage, 0, sizeof(WalUsage));
4604+
WalUsageAccumDiff(&walusage, &pgWalUsage, &prevWalUsage);
4605+
4606+
WalStats.m_wal_records = walusage.wal_records;
4607+
WalStats.m_wal_fpi = walusage.wal_fpi;
4608+
WalStats.m_wal_bytes = walusage.wal_bytes;
4609+
45804610
/*
45814611
* This function can be called even if nothing at all has happened. In
45824612
* this case, avoid sending a completely empty message to the stats
@@ -4591,6 +4621,11 @@ pgstat_send_wal(void)
45914621
pgstat_setheader(&WalStats.m_hdr, PGSTAT_MTYPE_WAL);
45924622
pgstat_send(&WalStats, sizeof(WalStats));
45934623

4624+
/*
4625+
* Save the current counters for the subsequent calculation of WAL usage.
4626+
*/
4627+
prevWalUsage = pgWalUsage;
4628+
45944629
/*
45954630
* Clear out the statistics buffer, so it can be re-used.
45964631
*/
@@ -6759,6 +6794,9 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
67596794
static void
67606795
pgstat_recv_wal(PgStat_MsgWal *msg, int len)
67616796
{
6797+
walStats.wal_records += msg->m_wal_records;
6798+
walStats.wal_fpi += msg->m_wal_fpi;
6799+
walStats.wal_bytes += msg->m_wal_bytes;
67626800
walStats.wal_buffers_full += msg->m_wal_buffers_full;
67636801
}
67646802

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 22 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1703,10 +1703,11 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
17031703
Datum
17041704
pg_stat_get_wal(PG_FUNCTION_ARGS)
17051705
{
1706-
#define PG_STAT_GET_WAL_COLS 2
1706+
#define PG_STAT_GET_WAL_COLS 5
17071707
TupleDesc tupdesc;
17081708
Datum values[PG_STAT_GET_WAL_COLS];
17091709
bool nulls[PG_STAT_GET_WAL_COLS];
1710+
char buf[256];
17101711
PgStat_WalStats *wal_stats;
17111712

17121713
/* Initialise values and NULL flags arrays */
@@ -1715,9 +1716,15 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
17151716

17161717
/* Initialise attributes information in the tuple descriptor */
17171718
tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_WAL_COLS);
1718-
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "wal_buffers_full",
1719+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "wal_records",
17191720
INT8OID, -1, 0);
1720-
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "stats_reset",
1721+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "wal_fpi",
1722+
INT8OID, -1, 0);
1723+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "wal_bytes",
1724+
NUMERICOID, -1, 0);
1725+
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "wal_buffers_full",
1726+
INT8OID, -1, 0);
1727+
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "stats_reset",
17211728
TIMESTAMPTZOID, -1, 0);
17221729

17231730
BlessTupleDesc(tupdesc);
@@ -1726,8 +1733,18 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
17261733
wal_stats = pgstat_fetch_stat_wal();
17271734

17281735
/* Fill values and NULLs */
1729-
values[0] = Int64GetDatum(wal_stats->wal_buffers_full);
1730-
values[1] = TimestampTzGetDatum(wal_stats->stat_reset_timestamp);
1736+
values[0] = Int64GetDatum(wal_stats->wal_records);
1737+
values[1] = Int64GetDatum(wal_stats->wal_fpi);
1738+
1739+
/* Convert to numeric. */
1740+
snprintf(buf, sizeof buf, UINT64_FORMAT, wal_stats->wal_bytes);
1741+
values[2] = DirectFunctionCall3(numeric_in,
1742+
CStringGetDatum(buf),
1743+
ObjectIdGetDatum(0),
1744+
Int32GetDatum(-1));
1745+
1746+
values[3] = Int64GetDatum(wal_stats->wal_buffers_full);
1747+
values[4] = TimestampTzGetDatum(wal_stats->stat_reset_timestamp);
17311748

17321749
/* Returns the record as Datum */
17331750
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5500,8 +5500,9 @@
55005500
{ oid => '1136', descr => 'statistics: information about WAL activity',
55015501
proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
55025502
proparallel => 'r', prorettype => 'record', proargtypes => '',
5503-
proallargtypes => '{int8,timestamptz}', proargmodes => '{o,o}',
5504-
proargnames => '{wal_buffers_full,stats_reset}',
5503+
proallargtypes => '{int8,int8,numeric,int8,timestamptz}',
5504+
proargmodes => '{o,o,o,o,o}',
5505+
proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}',
55055506
prosrc => 'pg_stat_get_wal' },
55065507

55075508
{ oid => '2306', descr => 'statistics: information about SLRU caches',

‎src/include/pgstat.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -459,6 +459,9 @@ typedef struct PgStat_MsgBgWriter
459459
typedef struct PgStat_MsgWal
460460
{
461461
PgStat_MsgHdr m_hdr;
462+
PgStat_Counter m_wal_records;
463+
PgStat_Counter m_wal_fpi;
464+
uint64 m_wal_bytes;
462465
PgStat_Counter m_wal_buffers_full;
463466
} PgStat_MsgWal;
464467

@@ -798,6 +801,9 @@ typedef struct PgStat_GlobalStats
798801
*/
799802
typedef struct PgStat_WalStats
800803
{
804+
PgStat_Counter wal_records;
805+
PgStat_Counter wal_fpi;
806+
uint64 wal_bytes;
801807
PgStat_Counter wal_buffers_full;
802808
TimestampTz stat_reset_timestamp;
803809
} PgStat_WalStats;

‎src/test/regress/expected/rules.out

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2138,9 +2138,12 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
21382138
pg_stat_all_tables.autoanalyze_count
21392139
FROM pg_stat_all_tables
21402140
WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
2141-
pg_stat_wal| SELECT w.wal_buffers_full,
2141+
pg_stat_wal| SELECT w.wal_records,
2142+
w.wal_fpi,
2143+
w.wal_bytes,
2144+
w.wal_buffers_full,
21422145
w.stats_reset
2143-
FROM pg_stat_get_wal() w(wal_buffers_full, stats_reset);
2146+
FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_reset);
21442147
pg_stat_wal_receiver| SELECT s.pid,
21452148
s.status,
21462149
s.receive_start_lsn,

0 commit comments

Comments
 (0)