Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit8d9a935

Browse files
committed
Add pg_stat_wal statistics view.
This view shows the statistics about WAL activity. Currently it has onlytwo columns: wal_buffers_full and stats_reset. wal_buffers_full columnindicates the number of times WAL data was written to the disk becauseWAL buffers got full. This information is useful when tuning wal_buffers.stats_reset column indicates the time at which these statistics werelast reset.pg_stat_wal view is also the basic infrastructure to expose othervarious statistics about WAL activity later.Bump PGSTAT_FILE_FORMAT_ID due to the change in pgstat format.Bump catalog version.Author: Masahiro IkedaReviewed-by: Takayuki Tsunakawa, Kyotaro Horiguchi, Amit Kapila, Fujii MasaoDiscussion:https://postgr.es/m/188bd3f2d2233cf97753b5ced02bb050@oss.nttdata.com
1 parent9d0bd95 commit8d9a935

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+
CREATEVIEWpg_stat_walAS
983+
SELECT
984+
w.wal_buffers_full,
985+
w.stats_reset
986+
FROM pg_stat_get_wal() w;
987+
982988
CREATEVIEWpg_stat_progress_analyzeAS
983989
SELECT
984990
S.pidAS pid,S.datidAS datid,D.datnameAS 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 soit can be sent
140-
* without needing to copy things around. We assumethis inits to zeroes.
138+
* BgWriterand WALglobal statistics counters.
139+
* Stored directly in a stats message structure sothey can be sent
140+
* without needing to copy things around. We assumethese init to zeroes.
141141
*/
142142
PgStat_MsgBgWriterBgWriterStats;
143+
PgStat_MsgWalWalStats;
143144

144145
/*
145146
* List of SLRU names that we keep stats for. There is no central registry of
@@ -281,6 +282,7 @@ static intlocalNumBackends = 0;
281282
*/
282283
staticPgStat_ArchiverStatsarchiverStats;
283284
staticPgStat_GlobalStatsglobalStats;
285+
staticPgStat_WalStatswalStats;
284286
staticPgStat_SLRUStatsslruStats[SLRU_NUM_ELEMENTS];
285287

286288
/*
@@ -353,6 +355,7 @@ static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
353355
staticvoidpgstat_recv_analyze(PgStat_MsgAnalyze*msg,intlen);
354356
staticvoidpgstat_recv_archiver(PgStat_MsgArchiver*msg,intlen);
355357
staticvoidpgstat_recv_bgwriter(PgStat_MsgBgWriter*msg,intlen);
358+
staticvoidpgstat_recv_wal(PgStat_MsgWal*msg,intlen);
356359
staticvoidpgstat_recv_slru(PgStat_MsgSLRU*msg,intlen);
357360
staticvoidpgstat_recv_funcstat(PgStat_MsgFuncstat*msg,intlen);
358361
staticvoidpgstat_recv_funcpurge(PgStat_MsgFuncpurge*msg,intlen);
@@ -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
elseif (strcmp(target,"bgwriter")==0)
13721378
msg.m_resettarget=RESET_BGWRITER;
1379+
elseif (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+
staticconstPgStat_MsgWalall_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+
casePGSTAT_MTYPE_WAL:
4717+
pgstat_recv_wal(&msg.msg_wal,len);
4718+
break;
4719+
46614720
casePGSTAT_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 globalandarchiver statistics so they start from zero in
5190-
* case we can't load an existing statsfile.
5254+
* Clear out global, archiver, WALandSLRU statistics so they start from
5255+
*zero incase 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
gotodone;
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+
gotodone;
5347+
}
5348+
52715349
/*
52725350
* Read SLRU stats struct
52735351
*/
@@ -5578,6 +5656,7 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
55785656
PgStat_StatDBEntrydbentry;
55795657
PgStat_GlobalStatsmyGlobalStats;
55805658
PgStat_ArchiverStatsmyArchiverStats;
5659+
PgStat_WalStatsmyWalStats;
55815660
PgStat_SLRUStatsmySLRUStats[SLRU_NUM_ELEMENTS];
55825661
FILE*fpin;
55835662
int32format_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+
elseif (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+
staticvoid
6533+
pgstat_recv_wal(PgStat_MsgWal*msg,intlen)
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+
#definePG_STAT_GET_WAL_COLS2
1707+
TupleDesctupdesc;
1708+
Datumvalues[PG_STAT_GET_WAL_COLS];
1709+
boolnulls[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)
[8]ページ先頭
©2009-2025 Movatter.jp