Skip to content

Commit 8d9a935

Browse files
MasaoFujiiMasaoFujii
committed
Add pg_stat_wal statistics view.
This view shows the statistics about WAL activity. Currently it has only two columns: wal_buffers_full and stats_reset. wal_buffers_full column indicates the number of times WAL data was written to the disk because WAL buffers got full. This information is useful when tuning wal_buffers. stats_reset column indicates the time at which these statistics were last reset. pg_stat_wal view is also the basic infrastructure to expose other various statistics about WAL activity later. Bump PGSTAT_FILE_FORMAT_ID due to the change in pgstat format. Bump catalog version. Author: Masahiro Ikeda Reviewed-by: Takayuki Tsunakawa, Kyotaro Horiguchi, Amit Kapila, Fujii Masao Discussion: https://postgr.es/m/188bd3f2d2233cf97753b5ced02bb050@oss.nttdata.com
1 parent 9d0bd95 commit 8d9a935

File tree

13 files changed

+276
-11
lines changed

13 files changed

+276
-11
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 61 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -424,6 +424,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
424424
</entry>
425425
</row>
426426

427+
<row>
428+
<entry><structname>pg_stat_wal</structname><indexterm><primary>pg_stat_wal</primary></indexterm></entry>
429+
<entry>One row only, showing statistics about WAL activity. See
430+
<link linkend="monitoring-pg-stat-wal-view">
431+
<structname>pg_stat_wal</structname></link> for details.
432+
</entry>
433+
</row>
434+
427435
<row>
428436
<entry><structname>pg_stat_database</structname><indexterm><primary>pg_stat_database</primary></indexterm></entry>
429437
<entry>One row per database, showing database-wide statistics. See
@@ -3280,6 +3288,56 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
32803288

32813289
</sect2>
32823290

3291+
<sect2 id="monitoring-pg-stat-wal-view">
3292+
<title><structname>pg_stat_wal</structname></title>
3293+
3294+
<indexterm>
3295+
<primary>pg_stat_wal</primary>
3296+
</indexterm>
3297+
3298+
<para>
3299+
The <structname>pg_stat_wal</structname> view will always have a
3300+
single row, containing data about WAL activity of the cluster.
3301+
</para>
3302+
3303+
<table id="pg-stat-wal-view" xreflabel="pg_stat_wal">
3304+
<title><structname>pg_stat_wal</structname> View</title>
3305+
<tgroup cols="1">
3306+
<thead>
3307+
<row>
3308+
<entry role="catalog_table_entry"><para role="column_definition">
3309+
Column Type
3310+
</para>
3311+
<para>
3312+
Description
3313+
</para></entry>
3314+
</row>
3315+
</thead>
3316+
3317+
<tbody>
3318+
<row>
3319+
<entry role="catalog_table_entry"><para role="column_definition">
3320+
<structfield>wal_buffers_full</structfield> <type>bigint</type>
3321+
</para>
3322+
<para>
3323+
Number of times WAL data was written to the disk because WAL buffers got full
3324+
</para></entry>
3325+
</row>
3326+
3327+
<row>
3328+
<entry role="catalog_table_entry"><para role="column_definition">
3329+
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
3330+
</para>
3331+
<para>
3332+
Time at which these statistics were last reset
3333+
</para></entry>
3334+
</row>
3335+
</tbody>
3336+
</tgroup>
3337+
</table>
3338+
3339+
</sect2>
3340+
32833341
<sect2 id="monitoring-pg-stat-database-view">
32843342
<title><structname>pg_stat_database</structname></title>
32853343

@@ -4668,8 +4726,9 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
46684726
argument. The argument can be <literal>bgwriter</literal> to reset
46694727
all the counters shown in
46704728
the <structname>pg_stat_bgwriter</structname>
4671-
view, or <literal>archiver</literal> to reset all the counters shown in
4672-
the <structname>pg_stat_archiver</structname> view.
4729+
view, <literal>archiver</literal> to reset all the counters shown in
4730+
the <structname>pg_stat_archiver</structname> view or <literal>wal</literal>
4731+
to reset all the counters shown in the <structname>pg_stat_wal</structname> view.
46734732
</para>
46744733
<para>
46754734
This function is restricted to superusers by default, but other users

‎src/backend/access/transam/xlog.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2196,6 +2196,7 @@ AdvanceXLInsertBuffer(XLogRecPtr upto, bool opportunistic)
21962196
WriteRqst.Flush = 0;
21972197
XLogWrite(WriteRqst, false);
21982198
LWLockRelease(WALWriteLock);
2199+
WalStats.m_wal_buffers_full++;
21992200
TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY_DONE();
22002201
}
22012202
/* Re-acquire WALBufMappingLock and retry */

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -979,6 +979,12 @@ CREATE VIEW pg_stat_bgwriter AS
979979
pg_stat_get_buf_alloc() AS buffers_alloc,
980980
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
981981

982+
CREATE VIEW pg_stat_wal AS
983+
SELECT
984+
w.wal_buffers_full,
985+
w.stats_reset
986+
FROM pg_stat_get_wal() w;
987+
982988
CREATE VIEW pg_stat_progress_analyze AS
983989
SELECT
984990
S.pid AS pid, S.datid AS datid, D.datname AS datname,

‎src/backend/postmaster/checkpointer.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -504,6 +504,9 @@ CheckpointerMain(void)
504504
*/
505505
pgstat_send_bgwriter();
506506

507+
/* Send WAL statistics to the stats collector. */
508+
pgstat_send_wal();
509+
507510
/*
508511
* If any checkpoint flags have been set, redo the loop to handle the
509512
* checkpoint without sleeping.

‎src/backend/postmaster/pgstat.c

Lines changed: 114 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -135,11 +135,12 @@ char *pgstat_stat_filename = NULL;
135135
char *pgstat_stat_tmpname = NULL;
136136

137137
/*
138-
* BgWriter global statistics counters (unused in other processes).
139-
* Stored directly in a stats message structure so it can be sent
140-
* without needing to copy things around. We assume this inits to zeroes.
138+
* BgWriter and WAL global statistics counters.
139+
* Stored directly in a stats message structure so they can be sent
140+
* without needing to copy things around. We assume these init to zeroes.
141141
*/
142142
PgStat_MsgBgWriter BgWriterStats;
143+
PgStat_MsgWal WalStats;
143144

144145
/*
145146
* List of SLRU names that we keep stats for. There is no central registry of
@@ -281,6 +282,7 @@ static int localNumBackends = 0;
281282
*/
282283
static PgStat_ArchiverStats archiverStats;
283284
static PgStat_GlobalStats globalStats;
285+
static PgStat_WalStats walStats;
284286
static PgStat_SLRUStats slruStats[SLRU_NUM_ELEMENTS];
285287

286288
/*
@@ -353,6 +355,7 @@ static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
353355
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
354356
static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
355357
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
358+
static void pgstat_recv_wal(PgStat_MsgWal *msg, int len);
356359
static void pgstat_recv_slru(PgStat_MsgSLRU *msg, int len);
357360
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
358361
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
@@ -938,6 +941,9 @@ pgstat_report_stat(bool force)
938941
/* Now, send function statistics */
939942
pgstat_send_funcstats();
940943

944+
/* Send WAL statistics */
945+
pgstat_send_wal();
946+
941947
/* Finally send SLRU statistics */
942948
pgstat_send_slru();
943949
}
@@ -1370,11 +1376,13 @@ pgstat_reset_shared_counters(const char *target)
13701376
msg.m_resettarget = RESET_ARCHIVER;
13711377
else if (strcmp(target, "bgwriter") == 0)
13721378
msg.m_resettarget = RESET_BGWRITER;
1379+
else if (strcmp(target, "wal") == 0)
1380+
msg.m_resettarget = RESET_WAL;
13731381
else
13741382
ereport(ERROR,
13751383
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
13761384
errmsg("unrecognized reset target: \"%s\"", target),
1377-
errhint("Target must be \"archiver\" or \"bgwriter\".")));
1385+
errhint("Target must be \"archiver\", \"bgwriter\" or \"wal\".")));
13781386

13791387
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
13801388
pgstat_send(&msg, sizeof(msg));
@@ -2674,6 +2682,21 @@ pgstat_fetch_global(void)
26742682
return &globalStats;
26752683
}
26762684

2685+
/*
2686+
* ---------
2687+
* pgstat_fetch_stat_wal() -
2688+
*
2689+
* Support function for the SQL-callable pgstat* functions. Returns
2690+
* a pointer to the WAL statistics struct.
2691+
* ---------
2692+
*/
2693+
PgStat_WalStats *
2694+
pgstat_fetch_stat_wal(void)
2695+
{
2696+
backend_read_statsfile();
2697+
2698+
return &walStats;
2699+
}
26772700

26782701
/*
26792702
* ---------
@@ -4419,6 +4442,38 @@ pgstat_send_bgwriter(void)
44194442
MemSet(&BgWriterStats, 0, sizeof(BgWriterStats));
44204443
}
44214444

4445+
/* ----------
4446+
* pgstat_send_wal() -
4447+
*
4448+
* Send WAL statistics to the collector
4449+
* ----------
4450+
*/
4451+
void
4452+
pgstat_send_wal(void)
4453+
{
4454+
/* We assume this initializes to zeroes */
4455+
static const PgStat_MsgWal all_zeroes;
4456+
4457+
/*
4458+
* This function can be called even if nothing at all has happened. In
4459+
* this case, avoid sending a completely empty message to the stats
4460+
* collector.
4461+
*/
4462+
if (memcmp(&WalStats, &all_zeroes, sizeof(PgStat_MsgWal)) == 0)
4463+
return;
4464+
4465+
/*
4466+
* Prepare and send the message
4467+
*/
4468+
pgstat_setheader(&WalStats.m_hdr, PGSTAT_MTYPE_WAL);
4469+
pgstat_send(&WalStats, sizeof(WalStats));
4470+
4471+
/*
4472+
* Clear out the statistics buffer, so it can be re-used.
4473+
*/
4474+
MemSet(&WalStats, 0, sizeof(WalStats));
4475+
}
4476+
44224477
/* ----------
44234478
* pgstat_send_slru() -
44244479
*
@@ -4658,6 +4713,10 @@ PgstatCollectorMain(int argc, char *argv[])
46584713
pgstat_recv_bgwriter(&msg.msg_bgwriter, len);
46594714
break;
46604715

4716+
case PGSTAT_MTYPE_WAL:
4717+
pgstat_recv_wal(&msg.msg_wal, len);
4718+
break;
4719+
46614720
case PGSTAT_MTYPE_SLRU:
46624721
pgstat_recv_slru(&msg.msg_slru, len);
46634722
break;
@@ -4927,6 +4986,12 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
49274986
rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
49284987
(void) rc; /* we'll check for error with ferror */
49294988

4989+
/*
4990+
* Write WAL stats struct
4991+
*/
4992+
rc = fwrite(&walStats, sizeof(walStats), 1, fpout);
4993+
(void) rc; /* we'll check for error with ferror */
4994+
49304995
/*
49314996
* Write SLRU stats struct
49324997
*/
@@ -5186,11 +5251,12 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
51865251
HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
51875252

51885253
/*
5189-
* Clear out global and archiver statistics so they start from zero in
5190-
* case we can't load an existing statsfile.
5254+
* Clear out global, archiver, WAL and SLRU statistics so they start from
5255+
* zero in case we can't load an existing statsfile.
51915256
*/
51925257
memset(&globalStats, 0, sizeof(globalStats));
51935258
memset(&archiverStats, 0, sizeof(archiverStats));
5259+
memset(&walStats, 0, sizeof(walStats));
51945260
memset(&slruStats, 0, sizeof(slruStats));
51955261

51965262
/*
@@ -5199,6 +5265,7 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
51995265
*/
52005266
globalStats.stat_reset_timestamp = GetCurrentTimestamp();
52015267
archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
5268+
walStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
52025269

52035270
/*
52045271
* Set the same reset timestamp for all SLRU items too.
@@ -5268,6 +5335,17 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
52685335
goto done;
52695336
}
52705337

5338+
/*
5339+
* Read WAL stats struct
5340+
*/
5341+
if (fread(&walStats, 1, sizeof(walStats), fpin) != sizeof(walStats))
5342+
{
5343+
ereport(pgStatRunningInCollector ? LOG : WARNING,
5344+
(errmsg("corrupted statistics file \"%s\"", statfile)));
5345+
memset(&walStats, 0, sizeof(walStats));
5346+
goto done;
5347+
}
5348+
52715349
/*
52725350
* Read SLRU stats struct
52735351
*/
@@ -5578,6 +5656,7 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
55785656
PgStat_StatDBEntry dbentry;
55795657
PgStat_GlobalStats myGlobalStats;
55805658
PgStat_ArchiverStats myArchiverStats;
5659+
PgStat_WalStats myWalStats;
55815660
PgStat_SLRUStats mySLRUStats[SLRU_NUM_ELEMENTS];
55825661
FILE *fpin;
55835662
int32 format_id;
@@ -5633,6 +5712,17 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
56335712
return false;
56345713
}
56355714

5715+
/*
5716+
* Read WAL stats struct
5717+
*/
5718+
if (fread(&myWalStats, 1, sizeof(myWalStats), fpin) != sizeof(myWalStats))
5719+
{
5720+
ereport(pgStatRunningInCollector ? LOG : WARNING,
5721+
(errmsg("corrupted statistics file \"%s\"", statfile)));
5722+
FreeFile(fpin);
5723+
return false;
5724+
}
5725+
56365726
/*
56375727
* Read SLRU stats struct
56385728
*/
@@ -6213,6 +6303,12 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
62136303
memset(&archiverStats, 0, sizeof(archiverStats));
62146304
archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
62156305
}
6306+
else if (msg->m_resettarget == RESET_WAL)
6307+
{
6308+
/* Reset the WAL statistics for the cluster. */
6309+
memset(&walStats, 0, sizeof(walStats));
6310+
walStats.stat_reset_timestamp = GetCurrentTimestamp();
6311+
}
62166312

62176313
/*
62186314
* Presumably the sender of this message validated the target, don't
@@ -6427,6 +6523,18 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
64276523
globalStats.buf_alloc += msg->m_buf_alloc;
64286524
}
64296525

6526+
/* ----------
6527+
* pgstat_recv_wal() -
6528+
*
6529+
* Process a WAL message.
6530+
* ----------
6531+
*/
6532+
static void
6533+
pgstat_recv_wal(PgStat_MsgWal *msg, int len)
6534+
{
6535+
walStats.wal_buffers_full += msg->m_wal_buffers_full;
6536+
}
6537+
64306538
/* ----------
64316539
* pgstat_recv_slru() -
64326540
*

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

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1697,6 +1697,42 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
16971697
PG_RETURN_INT64(pgstat_fetch_global()->buf_alloc);
16981698
}
16991699

1700+
/*
1701+
* Returns statistics of WAL activity
1702+
*/
1703+
Datum
1704+
pg_stat_get_wal(PG_FUNCTION_ARGS)
1705+
{
1706+
#define PG_STAT_GET_WAL_COLS 2
1707+
TupleDesc tupdesc;
1708+
Datum values[PG_STAT_GET_WAL_COLS];
1709+
bool nulls[PG_STAT_GET_WAL_COLS];
1710+
PgStat_WalStats *wal_stats;
1711+
1712+
/* Initialise values and NULL flags arrays */
1713+
MemSet(values, 0, sizeof(values));
1714+
MemSet(nulls, 0, sizeof(nulls));
1715+
1716+
/* Initialise attributes information in the tuple descriptor */
1717+
tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_WAL_COLS);
1718+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "wal_buffers_full",
1719+
INT8OID, -1, 0);
1720+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "stats_reset",
1721+
TIMESTAMPTZOID, -1, 0);
1722+
1723+
BlessTupleDesc(tupdesc);
1724+
1725+
/* Get statistics about WAL activity */
1726+
wal_stats = pgstat_fetch_stat_wal();
1727+
1728+
/* Fill values and NULLs */
1729+
values[0] = Int64GetDatum(wal_stats->wal_buffers_full);
1730+
values[1] = TimestampTzGetDatum(wal_stats->stat_reset_timestamp);
1731+
1732+
/* Returns the record as Datum */
1733+
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
1734+
}
1735+
17001736
/*
17011737
* Returns statistics of SLRU caches.
17021738
*/

0 commit comments

Comments
 (0)