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

Commit76def4c

Browse files
committed
Add WAL data to backend statistics
This commit adds per-backend WAL statistics, providing the sameinformation as pg_stat_wal, except that it is now possible to know howmuch WAL activity is happening in each backend rather than an overallaggregate of all the activity. Like pg_stat_wal, the implementationrelies on pgWalUsage, tracking the difference of activity between tworeports to pgstats.This data can be retrieved with a new system function calledpg_stat_get_backend_wal(), that returns one tuple based on the PIDprovided in input. Like pg_stat_get_backend_io(), this is useful whenjoined with pg_stat_activity to get a live picture of the WAL generatedfor each running backend, showing how the activity is [un]balanced.pgstat_flush_backend() gains a new flag value, able to control the flushof the WAL stats.This commit relies mostly on the infrastructure provided by9aea73f, that has introduced backend statistics.Bump catalog version. A bump of PGSTAT_FILE_FORMAT_ID is not required,as backend stats do not persist on disk.Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>Reviewed-by: Michael Paquier <michael@paquier.xyz>Reviewed-by: Nazir Bilal Yavuz <byavuz81@gmail.com>Reviewed-by: Xuneng Zhou <xunengzhou@gmail.com>Discussion:https://postgr.es/m/Z3zqc4o09dM/Ezyz@ip-10-97-1-34.eu-west-3.compute.internal
1 parent59a1592 commit76def4c

File tree

10 files changed

+185
-27
lines changed

10 files changed

+185
-27
lines changed

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4866,6 +4866,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage
48664866
</para></entry>
48674867
</row>
48684868

4869+
<row>
4870+
<entry id="pg-stat-get-backend-wal" role="func_table_entry"><para role="func_signature">
4871+
<indexterm>
4872+
<primary>pg_stat_get_backend_wal</primary>
4873+
</indexterm>
4874+
<function>pg_stat_get_backend_wal</function> ( <type>integer</type> )
4875+
<returnvalue>record</returnvalue>
4876+
</para>
4877+
<para>
4878+
Returns WAL statistics about the backend with the specified
4879+
process ID. The output fields are exactly the same as the ones in the
4880+
<structname>pg_stat_wal</structname> view.
4881+
</para>
4882+
<para>
4883+
The function does not return WAL statistics for the checkpointer,
4884+
the background writer, the startup process and the autovacuum launcher.
4885+
</para></entry>
4886+
</row>
4887+
48694888
<row>
48704889
<entry role="func_table_entry"><para role="func_signature">
48714890
<indexterm>

‎src/backend/utils/activity/pgstat_backend.c‎

Lines changed: 84 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,14 @@
3838
*/
3939
staticPgStat_BackendPendingPendingBackendStats= {0};
4040

41+
/*
42+
* WAL usage counters saved from pgWalUsage at the previous call to
43+
* pgstat_report_wal(). This is used to calculate how much WAL usage
44+
* happens between pgstat_report_wal() calls, by subtracting the previous
45+
* counters from the current ones.
46+
*/
47+
staticWalUsageprevBackendWalUsage;
48+
4149
/*
4250
* Utility routines to report I/O stats for backends, kept here to avoid
4351
* exposing PendingBackendStats to the outside world.
@@ -184,6 +192,57 @@ pgstat_flush_backend_entry_io(PgStat_EntryRef *entry_ref)
184192
MemSet(&PendingBackendStats.pending_io,0,sizeof(PgStat_PendingIO));
185193
}
186194

195+
/*
196+
* To determine whether WAL usage happened.
197+
*/
198+
staticinlinebool
199+
pgstat_backend_wal_have_pending(void)
200+
{
201+
return (pgWalUsage.wal_records!=prevBackendWalUsage.wal_records);
202+
}
203+
204+
/*
205+
* Flush out locally pending backend WAL statistics. Locking is managed
206+
* by the caller.
207+
*/
208+
staticvoid
209+
pgstat_flush_backend_entry_wal(PgStat_EntryRef*entry_ref)
210+
{
211+
PgStatShared_Backend*shbackendent;
212+
PgStat_WalCounters*bktype_shstats;
213+
WalUsagewal_usage_diff= {0};
214+
215+
/*
216+
* This function can be called even if nothing at all has happened for WAL
217+
* statistics. In this case, avoid unnecessarily modifying the stats
218+
* entry.
219+
*/
220+
if (!pgstat_backend_wal_have_pending())
221+
return;
222+
223+
shbackendent= (PgStatShared_Backend*)entry_ref->shared_stats;
224+
bktype_shstats=&shbackendent->stats.wal_counters;
225+
226+
/*
227+
* Calculate how much WAL usage counters were increased by subtracting the
228+
* previous counters from the current ones.
229+
*/
230+
WalUsageAccumDiff(&wal_usage_diff,&pgWalUsage,&prevBackendWalUsage);
231+
232+
#defineWALSTAT_ACC(fld,var_to_add) \
233+
(bktype_shstats->fld += var_to_add.fld)
234+
WALSTAT_ACC(wal_buffers_full,wal_usage_diff);
235+
WALSTAT_ACC(wal_records,wal_usage_diff);
236+
WALSTAT_ACC(wal_fpi,wal_usage_diff);
237+
WALSTAT_ACC(wal_bytes,wal_usage_diff);
238+
#undef WALSTAT_ACC
239+
240+
/*
241+
* Save the current counters for the subsequent calculation of WAL usage.
242+
*/
243+
prevBackendWalUsage=pgWalUsage;
244+
}
245+
187246
/*
188247
* Flush out locally pending backend statistics
189248
*
@@ -194,12 +253,23 @@ bool
194253
pgstat_flush_backend(boolnowait,bits32flags)
195254
{
196255
PgStat_EntryRef*entry_ref;
256+
boolhas_pending_data= false;
197257

198258
if (!pgstat_tracks_backend_bktype(MyBackendType))
199259
return false;
200260

201-
if (pg_memory_is_all_zeros(&PendingBackendStats,
202-
sizeof(structPgStat_BackendPending)))
261+
/* Some IO data pending? */
262+
if ((flags&PGSTAT_BACKEND_FLUSH_IO)&&
263+
!pg_memory_is_all_zeros(&PendingBackendStats.pending_io,
264+
sizeof(structPgStat_PendingIO)))
265+
has_pending_data= true;
266+
267+
/* Some WAL data pending? */
268+
if ((flags&PGSTAT_BACKEND_FLUSH_WAL)&&
269+
pgstat_backend_wal_have_pending())
270+
has_pending_data= true;
271+
272+
if (!has_pending_data)
203273
return false;
204274

205275
entry_ref=pgstat_get_entry_ref_locked(PGSTAT_KIND_BACKEND,InvalidOid,
@@ -211,6 +281,9 @@ pgstat_flush_backend(bool nowait, bits32 flags)
211281
if (flags&PGSTAT_BACKEND_FLUSH_IO)
212282
pgstat_flush_backend_entry_io(entry_ref);
213283

284+
if (flags&PGSTAT_BACKEND_FLUSH_WAL)
285+
pgstat_flush_backend_entry_wal(entry_ref);
286+
214287
pgstat_unlock_entry(entry_ref);
215288

216289
return false;
@@ -226,7 +299,8 @@ pgstat_backend_have_pending_cb(void)
226299
return false;
227300

228301
return (!pg_memory_is_all_zeros(&PendingBackendStats,
229-
sizeof(structPgStat_BackendPending)));
302+
sizeof(structPgStat_BackendPending))||
303+
pgstat_backend_wal_have_pending());
230304
}
231305

232306
/*
@@ -261,6 +335,13 @@ pgstat_create_backend(ProcNumber procnum)
261335
pgstat_unlock_entry(entry_ref);
262336

263337
MemSet(&PendingBackendStats,0,sizeof(PgStat_BackendPending));
338+
339+
/*
340+
* Initialize prevBackendWalUsage with pgWalUsage so that
341+
* pgstat_backend_flush_cb() can calculate how much pgWalUsage counters
342+
* are increased by subtracting prevBackendWalUsage from pgWalUsage.
343+
*/
344+
prevBackendWalUsage=pgWalUsage;
264345
}
265346

266347
/*

‎src/backend/utils/activity/pgstat_wal.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,7 @@ pgstat_report_wal(bool force)
5252

5353
/* flush wal stats */
5454
(void)pgstat_wal_flush_cb(nowait);
55+
pgstat_flush_backend(nowait,PGSTAT_BACKEND_FLUSH_WAL);
5556

5657
/* flush IO stats */
5758
pgstat_flush_io(nowait);

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

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1609,8 +1609,8 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
16091609
/*
16101610
* pg_stat_wal_build_tuple
16111611
*
1612-
* Helper routine for pg_stat_get_wal()returning one tuple based on the
1613-
* contents of wal_counters.
1612+
* Helper routine for pg_stat_get_wal()and pg_stat_get_backend_wal()
1613+
*returning one tuple based on thecontents of wal_counters.
16141614
*/
16151615
staticDatum
16161616
pg_stat_wal_build_tuple(PgStat_WalCounterswal_counters,
@@ -1659,6 +1659,28 @@ pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters,
16591659
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc,values,nulls)));
16601660
}
16611661

1662+
/*
1663+
* Returns WAL statistics for a backend with given PID.
1664+
*/
1665+
Datum
1666+
pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
1667+
{
1668+
intpid;
1669+
PgStat_Backend*backend_stats;
1670+
PgStat_WalCountersbktype_stats;
1671+
1672+
pid=PG_GETARG_INT32(0);
1673+
backend_stats=pgstat_fetch_stat_backend_by_pid(pid,NULL);
1674+
1675+
if (!backend_stats)
1676+
PG_RETURN_NULL();
1677+
1678+
bktype_stats=backend_stats->wal_counters;
1679+
1680+
/* save tuples with data from this PgStat_WalCounters */
1681+
return (pg_stat_wal_build_tuple(bktype_stats,backend_stats->stat_reset_timestamp));
1682+
}
1683+
16621684
/*
16631685
* Returns statistics of WAL activity
16641686
*/

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202503071
60+
#defineCATALOG_VERSION_NO202503111
6161

6262
#endif

‎src/include/catalog/pg_proc.dat‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5973,6 +5973,13 @@
59735973
proargmodes => '{o,o,o,o,o}',
59745974
proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}',
59755975
prosrc => 'pg_stat_get_wal' },
5976+
{ oid => '8037', descr => 'statistics: backend WAL activity',
5977+
proname => 'pg_stat_get_backend_wal', provolatile => 'v', proparallel => 'r',
5978+
prorettype => 'record', proargtypes => 'int4',
5979+
proallargtypes => '{int4,int8,int8,numeric,int8,timestamptz}',
5980+
proargmodes => '{i,o,o,o,o,o}',
5981+
proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}',
5982+
prosrc => 'pg_stat_get_backend_wal' },
59765983
{ oid => '6248', descr => 'statistics: information about WAL prefetching',
59775984
proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't',
59785985
provolatile => 'v', prorettype => 'record', proargtypes => '',

‎src/include/pgstat.h‎

Lines changed: 23 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -340,24 +340,6 @@ typedef struct PgStat_IO
340340
PgStat_BktypeIOstats[BACKEND_NUM_TYPES];
341341
}PgStat_IO;
342342

343-
typedefstructPgStat_Backend
344-
{
345-
TimestampTzstat_reset_timestamp;
346-
PgStat_BktypeIOio_stats;
347-
}PgStat_Backend;
348-
349-
/* ---------
350-
* PgStat_BackendPendingNon-flushed backend stats.
351-
* ---------
352-
*/
353-
typedefstructPgStat_BackendPending
354-
{
355-
/*
356-
* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO.
357-
*/
358-
PgStat_PendingIOpending_io;
359-
}PgStat_BackendPending;
360-
361343
typedefstructPgStat_StatDBEntry
362344
{
363345
PgStat_Counterxact_commit;
@@ -500,6 +482,29 @@ typedef struct PgStat_WalStats
500482
TimestampTzstat_reset_timestamp;
501483
}PgStat_WalStats;
502484

485+
/* -------
486+
* PgStat_BackendBackend statistics
487+
* -------
488+
*/
489+
typedefstructPgStat_Backend
490+
{
491+
TimestampTzstat_reset_timestamp;
492+
PgStat_BktypeIOio_stats;
493+
PgStat_WalCounterswal_counters;
494+
}PgStat_Backend;
495+
496+
/* ---------
497+
* PgStat_BackendPendingNon-flushed backend stats.
498+
* ---------
499+
*/
500+
typedefstructPgStat_BackendPending
501+
{
502+
/*
503+
* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO.
504+
*/
505+
PgStat_PendingIOpending_io;
506+
}PgStat_BackendPending;
507+
503508
/*
504509
* Functions in pgstat.c
505510
*/

‎src/include/utils/pgstat_internal.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -622,7 +622,8 @@ extern void pgstat_archiver_snapshot_cb(void);
622622

623623
/* flags for pgstat_flush_backend() */
624624
#definePGSTAT_BACKEND_FLUSH_IO(1 << 0)/* Flush I/O statistics */
625-
#definePGSTAT_BACKEND_FLUSH_ALL(PGSTAT_BACKEND_FLUSH_IO)
625+
#definePGSTAT_BACKEND_FLUSH_WAL (1 << 1)/* Flush WAL statistics */
626+
#definePGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL)
626627

627628
externboolpgstat_flush_backend(boolnowait,bits32flags);
628629
externboolpgstat_backend_flush_cb(boolnowait);

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

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -908,8 +908,11 @@ SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELEC
908908

909909
-- Test pg_stat_checkpointer checkpointer-related stats, together with pg_stat_wal
910910
SELECT num_requested AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
911-
-- Test pg_stat_wal (and make a temp table so our temp schema exists)
911+
-- Test pg_stat_wal
912912
SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
913+
-- Test pg_stat_get_backend_wal()
914+
SELECT wal_bytes AS backend_wal_bytes_before from pg_stat_get_backend_wal(pg_backend_pid()) \gset
915+
-- Make a temp table so our temp schema exists
913916
CREATE TEMP TABLE test_stats_temp AS SELECT 17;
914917
DROP TABLE test_stats_temp;
915918
-- Checkpoint twice: The checkpointer reports stats after reporting completion
@@ -929,6 +932,18 @@ SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
929932
t
930933
(1 row)
931934

935+
SELECT pg_stat_force_next_flush();
936+
pg_stat_force_next_flush
937+
--------------------------
938+
939+
(1 row)
940+
941+
SELECT wal_bytes > :backend_wal_bytes_before FROM pg_stat_get_backend_wal(pg_backend_pid());
942+
?column?
943+
----------
944+
t
945+
(1 row)
946+
932947
-- Test pg_stat_get_backend_idset() and some allied functions.
933948
-- In particular, verify that their notion of backend ID matches
934949
-- our temp schema index.

‎src/test/regress/sql/stats.sql‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -426,9 +426,13 @@ SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELEC
426426
-- Test pg_stat_checkpointer checkpointer-related stats, together with pg_stat_wal
427427
SELECT num_requestedAS rqst_ckpts_beforeFROM pg_stat_checkpointer \gset
428428

429-
-- Test pg_stat_wal (and make a temp table so our temp schema exists)
429+
-- Test pg_stat_wal
430430
SELECT wal_bytesAS wal_bytes_beforeFROM pg_stat_wal \gset
431431

432+
-- Test pg_stat_get_backend_wal()
433+
SELECT wal_bytesAS backend_wal_bytes_beforefrom pg_stat_get_backend_wal(pg_backend_pid()) \gset
434+
435+
-- Make a temp table so our temp schema exists
432436
CREATE TEMP TABLE test_stats_tempASSELECT17;
433437
DROPTABLE test_stats_temp;
434438

@@ -441,6 +445,9 @@ CHECKPOINT;
441445
SELECT num_requested> :rqst_ckpts_beforeFROM pg_stat_checkpointer;
442446
SELECT wal_bytes> :wal_bytes_beforeFROM pg_stat_wal;
443447

448+
SELECT pg_stat_force_next_flush();
449+
SELECT wal_bytes> :backend_wal_bytes_beforeFROM pg_stat_get_backend_wal(pg_backend_pid());
450+
444451
-- Test pg_stat_get_backend_idset() and some allied functions.
445452
-- In particular, verify that their notion of backend ID matches
446453
-- our temp schema index.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp