Skip to content

Commit 96f0526

Browse files
michaelpqmichaelpq
committed
Introduce pg_stat_checkpointer
Historically, the statistics of the checkpointer have been always part of pg_stat_bgwriter. This commit removes a few columns from pg_stat_bgwriter, and introduces pg_stat_checkpointer with equivalent, renamed columns (plus a new one for the reset timestamp): - checkpoints_timed -> num_timed - checkpoints_req -> num_requested - checkpoint_write_time -> write_time - checkpoint_sync_time -> sync_time - buffers_checkpoint -> buffers_written The fields of PgStat_CheckpointerStats and its SQL functions are renamed to match with the new field names, for consistency. Note that background writer and checkpointer have been split into two different processes in commits 806a2ae and bf405ba. The pgstat structures were already split, making this change straight-forward. Bump catalog version. Author: Bharath Rupireddy Reviewed-by: Bertrand Drouvot, Andres Freund, Michael Paquier Discussion: https://postgr.es/m/CALj2ACVxX2ii=66RypXRweZe2EsBRiPMj0aHfRfHUeXJcC7kHg@mail.gmail.com
1 parent bf01e1b commit 96f0526

File tree

14 files changed

+187
-95
lines changed

14 files changed

+187
-95
lines changed

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 75 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -451,6 +451,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
451451
</entry>
452452
</row>
453453

454+
<row>
455+
<entry><structname>pg_stat_checkpointer</structname><indexterm><primary>pg_stat_checkpointer</primary></indexterm></entry>
456+
<entry>One row only, showing statistics about the
457+
checkpointer process's activity. See
458+
<link linkend="monitoring-pg-stat-checkpointer-view">
459+
<structname>pg_stat_checkpointer</structname></link> for details.
460+
</entry>
461+
</row>
462+
454463
<row>
455464
<entry><structname>pg_stat_database</structname><indexterm><primary>pg_stat_database</primary></indexterm></entry>
456465
<entry>One row per database, showing database-wide statistics. See
@@ -2868,7 +2877,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
28682877

28692878
<para>
28702879
The <structname>pg_stat_bgwriter</structname> view will always have a
2871-
single row, containing global data for the cluster.
2880+
single row, containing data about the background writer of the cluster.
28722881
</para>
28732882

28742883
<table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
@@ -2888,77 +2897,118 @@ description | Waiting for a newly initialized WAL file to reach durable storage
28882897
<tbody>
28892898
<row>
28902899
<entry role="catalog_table_entry"><para role="column_definition">
2891-
<structfield>checkpoints_timed</structfield> <type>bigint</type>
2900+
<structfield>buffers_clean</structfield> <type>bigint</type>
28922901
</para>
28932902
<para>
2894-
Number of scheduled checkpoints that have been performed
2903+
Number of buffers written by the background writer
28952904
</para></entry>
28962905
</row>
28972906

28982907
<row>
28992908
<entry role="catalog_table_entry"><para role="column_definition">
2900-
<structfield>checkpoints_req</structfield> <type>bigint</type>
2909+
<structfield>maxwritten_clean</structfield> <type>bigint</type>
29012910
</para>
29022911
<para>
2903-
Number of requested checkpoints that have been performed
2912+
Number of times the background writer stopped a cleaning
2913+
scan because it had written too many buffers
29042914
</para></entry>
29052915
</row>
29062916

29072917
<row>
29082918
<entry role="catalog_table_entry"><para role="column_definition">
2909-
<structfield>checkpoint_write_time</structfield> <type>double precision</type>
2919+
<structfield>buffers_alloc</structfield> <type>bigint</type>
29102920
</para>
29112921
<para>
2912-
Total amount of time that has been spent in the portion of
2913-
checkpoint processing where files are written to disk, in milliseconds
2922+
Number of buffers allocated
29142923
</para></entry>
29152924
</row>
29162925

29172926
<row>
29182927
<entry role="catalog_table_entry"><para role="column_definition">
2919-
<structfield>checkpoint_sync_time</structfield> <type>double precision</type>
2928+
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
29202929
</para>
29212930
<para>
2922-
Total amount of time that has been spent in the portion of
2923-
checkpoint processing where files are synchronized to disk, in
2924-
milliseconds
2931+
Time at which these statistics were last reset
29252932
</para></entry>
29262933
</row>
2934+
</tbody>
2935+
</tgroup>
2936+
</table>
2937+
2938+
</sect2>
2939+
2940+
<sect2 id="monitoring-pg-stat-checkpointer-view">
2941+
<title><structname>pg_stat_checkpointer</structname></title>
29272942

2943+
<indexterm>
2944+
<primary>pg_stat_checkpointer</primary>
2945+
</indexterm>
2946+
2947+
<para>
2948+
The <structname>pg_stat_checkpointer</structname> view will always have a
2949+
single row, containing data about the checkpointer process of the cluster.
2950+
</para>
2951+
2952+
<table id="pg-stat-checkpointer-view" xreflabel="pg_stat_checkpointer">
2953+
<title><structname>pg_stat_checkpointer</structname> View</title>
2954+
<tgroup cols="1">
2955+
<thead>
29282956
<row>
29292957
<entry role="catalog_table_entry"><para role="column_definition">
2930-
<structfield>buffers_checkpoint</structfield> <type>bigint</type>
2958+
Column Type
29312959
</para>
29322960
<para>
2933-
Number of buffers written during checkpoints
2961+
Description
29342962
</para></entry>
29352963
</row>
2964+
</thead>
29362965

2966+
<tbody>
29372967
<row>
29382968
<entry role="catalog_table_entry"><para role="column_definition">
2939-
<structfield>buffers_clean</structfield> <type>bigint</type>
2969+
<structfield>num_timed</structfield> <type>bigint</type>
29402970
</para>
29412971
<para>
2942-
Number of buffers written by the background writer
2972+
Number of scheduled checkpoints that have been performed
29432973
</para></entry>
29442974
</row>
29452975

29462976
<row>
29472977
<entry role="catalog_table_entry"><para role="column_definition">
2948-
<structfield>maxwritten_clean</structfield> <type>bigint</type>
2978+
<structfield>num_requested</structfield> <type>bigint</type>
29492979
</para>
29502980
<para>
2951-
Number of times the background writer stopped a cleaning
2952-
scan because it had written too many buffers
2981+
Number of requested checkpoints that have been performed
29532982
</para></entry>
29542983
</row>
29552984

29562985
<row>
29572986
<entry role="catalog_table_entry"><para role="column_definition">
2958-
<structfield>buffers_alloc</structfield> <type>bigint</type>
2987+
<structfield>write_time</structfield> <type>double precision</type>
29592988
</para>
29602989
<para>
2961-
Number of buffers allocated
2990+
Total amount of time that has been spent in the portion of
2991+
checkpoint processing where files are written to disk, in milliseconds
2992+
</para></entry>
2993+
</row>
2994+
2995+
<row>
2996+
<entry role="catalog_table_entry"><para role="column_definition">
2997+
<structfield>sync_time</structfield> <type>double precision</type>
2998+
</para>
2999+
<para>
3000+
Total amount of time that has been spent in the portion of
3001+
checkpoint processing where files are synchronized to disk, in
3002+
milliseconds
3003+
</para></entry>
3004+
</row>
3005+
3006+
<row>
3007+
<entry role="catalog_table_entry"><para role="column_definition">
3008+
<structfield>buffers_written</structfield> <type>bigint</type>
3009+
</para>
3010+
<para>
3011+
Number of buffers written during checkpoints
29623012
</para></entry>
29633013
</row>
29643014

@@ -4669,8 +4719,10 @@ description | Waiting for a newly initialized WAL file to reach durable storage
46694719
Resets some cluster-wide statistics counters to zero, depending on the
46704720
argument. The argument can be <literal>bgwriter</literal> to reset
46714721
all the counters shown in
4672-
the <structname>pg_stat_bgwriter</structname>
4673-
view, <literal>archiver</literal> to reset all the counters shown in
4722+
the <structname>pg_stat_bgwriter</structname> view,
4723+
<literal>checkpointer</literal> to reset all the counters shown in
4724+
the <structname>pg_stat_checkpointer</structname> view,
4725+
<literal>archiver</literal> to reset all the counters shown in
46744726
the <structname>pg_stat_archiver</structname> view,
46754727
<literal>io</literal> to reset all the counters shown in the
46764728
<structname>pg_stat_io</structname> view,

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6349,8 +6349,8 @@ LogCheckpointEnd(bool restartpoint)
63496349
CheckpointStats.ckpt_sync_end_t);
63506350

63516351
/* Accumulate checkpoint timing summary data, in milliseconds. */
6352-
PendingCheckpointerStats.checkpoint_write_time += write_msecs;
6353-
PendingCheckpointerStats.checkpoint_sync_time += sync_msecs;
6352+
PendingCheckpointerStats.write_time += write_msecs;
6353+
PendingCheckpointerStats.sync_time += sync_msecs;
63546354

63556355
/*
63566356
* All of the published timing statistics are accounted for. Only

‎src/backend/catalog/system_views.sql‎

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1111,16 +1111,20 @@ CREATE VIEW pg_stat_archiver AS
11111111

11121112
CREATE VIEW pg_stat_bgwriter AS
11131113
SELECT
1114-
pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
1115-
pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
1116-
pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
1117-
pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
1118-
pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
11191114
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
11201115
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
11211116
pg_stat_get_buf_alloc() AS buffers_alloc,
11221117
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
11231118

1119+
CREATE VIEW pg_stat_checkpointer AS
1120+
SELECT
1121+
pg_stat_get_checkpointer_num_timed() AS num_timed,
1122+
pg_stat_get_checkpointer_num_requested() AS num_requested,
1123+
pg_stat_get_checkpointer_write_time() AS write_time,
1124+
pg_stat_get_checkpointer_sync_time() AS sync_time,
1125+
pg_stat_get_checkpointer_buffers_written() AS buffers_written,
1126+
pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
1127+
11241128
CREATE VIEW pg_stat_io AS
11251129
SELECT
11261130
b.backend_type,

‎src/backend/postmaster/checkpointer.c‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -358,7 +358,7 @@ CheckpointerMain(void)
358358
if (((volatile CheckpointerShmemStruct *) CheckpointerShmem)->ckpt_flags)
359359
{
360360
do_checkpoint = true;
361-
PendingCheckpointerStats.requested_checkpoints++;
361+
PendingCheckpointerStats.num_requested++;
362362
}
363363

364364
/*
@@ -372,7 +372,7 @@ CheckpointerMain(void)
372372
if (elapsed_secs >= CheckPointTimeout)
373373
{
374374
if (!do_checkpoint)
375-
PendingCheckpointerStats.timed_checkpoints++;
375+
PendingCheckpointerStats.num_timed++;
376376
do_checkpoint = true;
377377
flags |= CHECKPOINT_CAUSE_TIME;
378378
}
@@ -569,7 +569,7 @@ HandleCheckpointerInterrupts(void)
569569
* updates the statistics, increment the checkpoint request and flush
570570
* out pending statistic.
571571
*/
572-
PendingCheckpointerStats.requested_checkpoints++;
572+
PendingCheckpointerStats.num_requested++;
573573
ShutdownXLOG(0, 0);
574574
pgstat_report_checkpointer();
575575
pgstat_report_wal(true);

‎src/backend/storage/buffer/bufmgr.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2751,7 +2751,7 @@ BufferSync(int flags)
27512751
if (SyncOneBuffer(buf_id, false, &wb_context) & BUF_WRITTEN)
27522752
{
27532753
TRACE_POSTGRESQL_BUFFER_SYNC_WRITTEN(buf_id);
2754-
PendingCheckpointerStats.buf_written_checkpoints++;
2754+
PendingCheckpointerStats.buffers_written++;
27552755
num_written++;
27562756
}
27572757
}

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

Lines changed: 11 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -47,11 +47,11 @@ pgstat_report_checkpointer(void)
4747
pgstat_begin_changecount_write(&stats_shmem->changecount);
4848

4949
#define CHECKPOINTER_ACC(fld) stats_shmem->stats.fld += PendingCheckpointerStats.fld
50-
CHECKPOINTER_ACC(timed_checkpoints);
51-
CHECKPOINTER_ACC(requested_checkpoints);
52-
CHECKPOINTER_ACC(checkpoint_write_time);
53-
CHECKPOINTER_ACC(checkpoint_sync_time);
54-
CHECKPOINTER_ACC(buf_written_checkpoints);
50+
CHECKPOINTER_ACC(num_timed);
51+
CHECKPOINTER_ACC(num_requested);
52+
CHECKPOINTER_ACC(write_time);
53+
CHECKPOINTER_ACC(sync_time);
54+
CHECKPOINTER_ACC(buffers_written);
5555
#undef CHECKPOINTER_ACC
5656

5757
pgstat_end_changecount_write(&stats_shmem->changecount);
@@ -92,6 +92,7 @@ pgstat_checkpointer_reset_all_cb(TimestampTz ts)
9292
&stats_shmem->stats,
9393
sizeof(stats_shmem->stats),
9494
&stats_shmem->changecount);
95+
stats_shmem->stats.stat_reset_timestamp = ts;
9596
LWLockRelease(&stats_shmem->lock);
9697
}
9798

@@ -113,10 +114,10 @@ pgstat_checkpointer_snapshot_cb(void)
113114

114115
/* compensate by reset offsets */
115116
#define CHECKPOINTER_COMP(fld) pgStatLocal.snapshot.checkpointer.fld -= reset.fld;
116-
CHECKPOINTER_COMP(timed_checkpoints);
117-
CHECKPOINTER_COMP(requested_checkpoints);
118-
CHECKPOINTER_COMP(checkpoint_write_time);
119-
CHECKPOINTER_COMP(checkpoint_sync_time);
120-
CHECKPOINTER_COMP(buf_written_checkpoints);
117+
CHECKPOINTER_COMP(num_timed);
118+
CHECKPOINTER_COMP(num_requested);
119+
CHECKPOINTER_COMP(write_time);
120+
CHECKPOINTER_COMP(sync_time);
121+
CHECKPOINTER_COMP(buffers_written);
121122
#undef CHECKPOINTER_COMP
122123
}

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

Lines changed: 18 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1182,21 +1182,21 @@ PG_STAT_GET_DBENTRY_FLOAT8_MS(idle_in_transaction_time)
11821182
PG_STAT_GET_DBENTRY_FLOAT8_MS(session_time)
11831183

11841184
Datum
1185-
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
1185+
pg_stat_get_checkpointer_num_timed(PG_FUNCTION_ARGS)
11861186
{
1187-
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->timed_checkpoints);
1187+
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->num_timed);
11881188
}
11891189

11901190
Datum
1191-
pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS)
1191+
pg_stat_get_checkpointer_num_requested(PG_FUNCTION_ARGS)
11921192
{
1193-
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->requested_checkpoints);
1193+
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->num_requested);
11941194
}
11951195

11961196
Datum
1197-
pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
1197+
pg_stat_get_checkpointer_buffers_written(PG_FUNCTION_ARGS)
11981198
{
1199-
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buf_written_checkpoints);
1199+
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buffers_written);
12001200
}
12011201

12021202
Datum
@@ -1212,19 +1212,25 @@ pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
12121212
}
12131213

12141214
Datum
1215-
pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS)
1215+
pg_stat_get_checkpointer_write_time(PG_FUNCTION_ARGS)
12161216
{
12171217
/* time is already in msec, just convert to double for presentation */
12181218
PG_RETURN_FLOAT8((double)
1219-
pgstat_fetch_stat_checkpointer()->checkpoint_write_time);
1219+
pgstat_fetch_stat_checkpointer()->write_time);
12201220
}
12211221

12221222
Datum
1223-
pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
1223+
pg_stat_get_checkpointer_sync_time(PG_FUNCTION_ARGS)
12241224
{
12251225
/* time is already in msec, just convert to double for presentation */
12261226
PG_RETURN_FLOAT8((double)
1227-
pgstat_fetch_stat_checkpointer()->checkpoint_sync_time);
1227+
pgstat_fetch_stat_checkpointer()->sync_time);
1228+
}
1229+
1230+
Datum
1231+
pg_stat_get_checkpointer_stat_reset_time(PG_FUNCTION_ARGS)
1232+
{
1233+
PG_RETURN_TIMESTAMPTZ(pgstat_fetch_stat_checkpointer()->stat_reset_timestamp);
12281234
}
12291235

12301236
Datum
@@ -1684,14 +1690,9 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
16841690
if (strcmp(target, "archiver") == 0)
16851691
pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
16861692
else if (strcmp(target, "bgwriter") == 0)
1687-
{
1688-
/*
1689-
* Historically checkpointer was part of bgwriter, continue to reset
1690-
* both for now.
1691-
*/
16921693
pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
1694+
else if (strcmp(target, "checkpointer") == 0)
16931695
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
1694-
}
16951696
else if (strcmp(target, "io") == 0)
16961697
pgstat_reset_of_kind(PGSTAT_KIND_IO);
16971698
else if (strcmp(target, "recovery_prefetch") == 0)
@@ -1702,7 +1703,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
17021703
ereport(ERROR,
17031704
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
17041705
errmsg("unrecognized reset target: \"%s\"", target),
1705-
errhint("Target must be \"archiver\", \"bgwriter\", \"io\", \"recovery_prefetch\", or \"wal\".")));
1706+
errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", or \"wal\".")));
17061707

17071708
PG_RETURN_VOID();
17081709
}

‎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-
#define CATALOG_VERSION_NO 202310271
60+
#define CATALOG_VERSION_NO 202310301
6161

6262
#endif

0 commit comments

Comments
 (0)