Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 0146924

Browse filesBrowse 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
Copy full SHA for 0146924

File tree

Expand file treeCollapse file tree

7 files changed

+104
-9
lines changed
Filter options
Expand file treeCollapse file tree

7 files changed

+104
-9
lines changed

‎doc/src/sgml/monitoring.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/monitoring.sgml
+27Lines 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

Copy file name to clipboardExpand all lines: src/backend/catalog/system_views.sql
+3Lines 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

Copy file name to clipboardExpand all lines: src/backend/postmaster/pgstat.c
+38Lines 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

Copy file name to clipboardExpand all lines: src/backend/utils/adt/pgstatfuncs.c
+22-5Lines 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

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.dat
+3-2Lines 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

Copy file name to clipboardExpand all lines: src/include/pgstat.h
+6Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/expected/rules.out
+5-2Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.