Hi,
On 7/28/22 11:38 AM, Nitin Jadhav wrote:
To understand the performance effects of the above, I have taken the
average of five checkpoints with the patch and without the patch in my
environment. Here are the results.
With patch: 269.65 s
Without patch: 269.60 s
Those look like timed checkpoints - if the checkpoints are sleeping a
part of the time, you're not going to see any potential overhead.
Yes. The above data is collected from timed checkpoints.
create table t1(a int);
insert into t1 select * from generate_series(1,10000000);
I generated a lot of data by using the above queries which would in
turn trigger the checkpoint (wal).
---
To see whether this has an effect you'd have to make sure there's a
certain number of dirty buffers (e.g. by doing CREATE TABLE AS
some_query) and then do a manual checkpoint and time how long that
times.
For this case I have generated data by using below queries.
create table t1(a int);
insert into t1 select * from generate_series(1,8000000);
This does not trigger the checkpoint automatically. I have issued the
CHECKPOINT manually and measured the performance by considering an
average of 5 checkpoints. Here are the details.
With patch: 2.457 s
Without patch: 2.334 s
Please share your thoughts.
v6 was not applying anymore, due to a change in
doc/src/sgml/ref/checkpoint.sgml done by b9eb0ff09e (Rename
pg_checkpointer predefined role to pg_checkpoint).
Please find attached a rebase in v7.
While working on this rebase, I also noticed that "pg_checkpointer" is
still mentioned in some translation files:
"
$ git grep pg_checkpointer
src/backend/po/de.po:msgid "must be superuser or have privileges of
pg_checkpointer to do CHECKPOINT"
src/backend/po/ja.po:msgid "must be superuser or have privileges of
pg_checkpointer to do CHECKPOINT"
src/backend/po/ja.po:msgstr
"CHECKPOINTを実行するにはスーパーユーザーであるか、またはpg_checkpointerの権限を持つ必要があります"
src/backend/po/sv.po:msgid "must be superuser or have privileges of
pg_checkpointer to do CHECKPOINT"
"
I'm not familiar with how the translation files are handled (looks like
they have their own set of commits, see 3c0bcdbc66 for example) but
wanted to mention that "pg_checkpointer" is still mentioned (even if
that may be expected as the last commit related to translation files
(aka 3c0bcdbc66) is older than the one that renamed pg_checkpointer to
pg_checkpoint (aka b9eb0ff09e)).
That said, back to this patch: I did not look closely but noticed that
the buffers_total reported by pg_stat_progress_checkpoint:
postgres=# select type,flags,start_lsn,phase,buffers_total,new_requests
from pg_stat_progress_checkpoint;
type | flags | start_lsn | phase
| buffers_total | new_requests
------------+-----------------------+------------+-----------------------+---------------+--------------
checkpoint | immediate force wait | 1/E6C523A8 | checkpointing
buffers | 1024275 | false
(1 row)
is a little bit different from what is logged once completed:
2022-11-04 08:18:50.806 UTC [3488442] LOG: checkpoint complete: wrote
1024278 buffers (97.7%);
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
commit 5198f5010be0febd019b1888817e2d78b8e42b21
Author: bdrouvot <bdrou...@gmail.com>
Date: Thu Nov 3 12:59:10 2022 +0000
v7-0001-pg_stat_progress_checkpoint-view.patch
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index e5d622d514..ceb7d60ffa 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -414,6 +414,13 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss
11:34 0:00 postgres: ser
See <xref linkend='copy-progress-reporting'/>.
</entry>
</row>
+
+ <row>
+
<entry><structname>pg_stat_progress_checkpoint</structname><indexterm><primary>pg_stat_progress_checkpoint</primary></indexterm></entry>
+ <entry>One row only, showing the progress of the checkpoint.
+ See <xref linkend='checkpoint-progress-reporting'/>.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -5784,7 +5791,7 @@ FROM pg_stat_get_backend_idset() AS backendid;
which support progress reporting are <command>ANALYZE</command>,
<command>CLUSTER</command>,
<command>CREATE INDEX</command>, <command>VACUUM</command>,
- <command>COPY</command>,
+ <command>COPY</command>, <command>CHECKPOINT</command>
and <xref linkend="protocol-replication-base-backup"/> (i.e., replication
command that <xref linkend="app-pgbasebackup"/> issues to take
a base backup).
@@ -7072,6 +7079,402 @@ FROM pg_stat_get_backend_idset() AS backendid;
</table>
</sect2>
+ <sect2 id="checkpoint-progress-reporting">
+ <title>Checkpoint Progress Reporting</title>
+
+ <indexterm>
+ <primary>pg_stat_progress_checkpoint</primary>
+ </indexterm>
+
+ <para>
+ Whenever the checkpoint operation is running, the
+ <structname>pg_stat_progress_checkpoint</structname> view will contain a
+ single row indicating the progress of the checkpoint. The tables below
+ describe the information that will be reported and provide information about
+ how to interpret it.
+ </para>
+
+ <table id="pg-stat-progress-checkpoint-view"
xreflabel="pg_stat_progress_checkpoint">
+ <title><structname>pg_stat_progress_checkpoint</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pid</structfield> <type>integer</type>
+ </para>
+ <para>
+ Process ID of the checkpointer process.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the checkpoint. See <xref linkend="checkpoint-types"/>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>flags</structfield> <type>text</type>
+ </para>
+ <para>
+ Flags of the checkpoint. See <xref linkend="checkpoint-flags"/>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>start_lsn</structfield> <type>text</type>
+ </para>
+ <para>
+ The checkpoint start location.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>start_time</structfield> <type>timestamp with time
zone</type>
+ </para>
+ <para>
+ Start time of the checkpoint.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>phase</structfield> <type>text</type>
+ </para>
+ <para>
+ Current processing phase. See <xref linkend="checkpoint-phases"/>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_total</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of buffers to be written. This is estimated and reported
+ as of the beginning of buffer write operation.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_processed</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of buffers processed. This counter increases when the targeted
+ buffer is processed. This number will eventually become equal to
+ <literal>buffers_total</literal> when the checkpoint is
+ complete.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_written</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of buffers written. This counter only advances when the targeted
+ buffers is written. Note that some of the buffers are processed but may
+ not required to be written. So this count will always be less than or
+ equal to <literal>buffers_total</literal>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>files_total</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of files to be synced. This is estimated and reported as of
+ the beginning of sync operation.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>files_synced</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of files synced. This counter advances when the targeted file is
+ synced. This number will eventually become equal to
+ <literal>files_total</literal> when the checkpoint is complete.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>new_requests</structfield> <type>text</type>
+ </para>
+ <para>
+ True if any of the backend is requested for a checkpoint while the
+ current checkpoint is in progress, False otherwise.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="checkpoint-types">
+ <title>Checkpoint Types</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Types</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>checkpoint</literal></entry>
+ <entry>
+ The current operation is checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>restartpoint</literal></entry>
+ <entry>
+ The current operation is restartpoint.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="checkpoint-flags">
+ <title>Checkpoint Flags</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Flags</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>shutdown</literal></entry>
+ <entry>
+ The checkpoint is for shutdown.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>end-of-recovery</literal></entry>
+ <entry>
+ The checkpoint is for end-of-recovery.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>immediate</literal></entry>
+ <entry>
+ The checkpoint happens without delays.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>force</literal></entry>
+ <entry>
+ The checkpoint is started because some operation (for which the
+ checkpoint is necessary) forced a checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>flush all</literal></entry>
+ <entry>
+ The checkpoint flushes all pages, including those belonging to unlogged
+ tables.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>wait</literal></entry>
+ <entry>
+ The operations which requested the checkpoint waits for completion
+ before returning.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>requested</literal></entry>
+ <entry>
+ The checkpoint request has been made.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>wal</literal></entry>
+ <entry>
+ The checkpoint is started because <literal>max_wal_size</literal> is
+ reached.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>time</literal></entry>
+ <entry>
+ The checkpoint is started because <literal>checkpoint_timeout</literal>
+ expired.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="checkpoint-phases">
+ <title>Checkpoint Phases</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Phase</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>initializing</literal></entry>
+ <entry>
+ The checkpointer process is preparing to begin the checkpoint operation.
+ This phase is expected to be very brief.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>getting virtual transaction IDs</literal></entry>
+ <entry>
+ The checkpointer process is getting the virtual transaction IDs that
+ are delaying the checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing replication slots</literal></entry>
+ <entry>
+ The checkpointer process is currently flushing all the replication slots
+ to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing logical replication snapshot
files</literal></entry>
+ <entry>
+ The checkpointer process is currently removing all the serialized
+ snapshot files that are not required anymore.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing logical rewrite mapping
files</literal></entry>
+ <entry>
+ The checkpointer process is currently removing unwanted or flushing
+ required logical rewrite mapping files.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing replication origin</literal></entry>
+ <entry>
+ The checkpointer process is currently performing a checkpoint of each
+ replication origin's progress with respect to the replayed remote LSN.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing commit log pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing commit log pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing commit time stamp pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing commit time stamp pages to
+ disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing subtransaction pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing subtransaction pages to
disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing multixact pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing multixact pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing predicate lock pages</literal></entry>
+ <entry>
+ The checkpointer process is currently writing predicate lock pages to
disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing buffers</literal></entry>
+ <entry>
+ The checkpointer process is currently writing buffers to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>processing file sync requests</literal></entry>
+ <entry>
+ The checkpointer process is currently processing file sync requests.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>performing two phase checkpoint</literal></entry>
+ <entry>
+ The checkpointer process is currently performing two phase checkpoint.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>performing post checkpoint cleanup</literal></entry>
+ <entry>
+ The checkpointer process is currently performing post checkpoint
cleanup.
+ It removes any lingering files that can be safely removed.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>invalidating replication slots</literal></entry>
+ <entry>
+ The checkpointer process is currently invalidating replication slots.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>recycling old WAL files</literal></entry>
+ <entry>
+ The checkpointer process is currently recycling old WAL files.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>truncating subtransactions</literal></entry>
+ <entry>
+ The checkpointer process is currently removing the subtransaction
+ segments.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>finalizing</literal></entry>
+ <entry>
+ The checkpointer process is finalizing the checkpoint operation.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
</sect1>
<sect1 id="dynamic-trace">
diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml
index 28a1d717b8..906883336d 100644
--- a/doc/src/sgml/ref/checkpoint.sgml
+++ b/doc/src/sgml/ref/checkpoint.sgml
@@ -55,6 +55,14 @@ CHECKPOINT
Only superusers or users with the privileges of
the <link
linkend="predefined-roles-table"><literal>pg_checkpoint</literal></link>
role can call <command>CHECKPOINT</command>.
+
+ <para>
+ The checkpointer process running the checkpoint will report its progress
+ in the <structname>pg_stat_progress_checkpoint</structname> view except for
+ the shutdown and end-of-recovery cases. See
+ <xref linkend="checkpoint-progress-reporting"/> for details.
+ </para>
+
</para>
</refsect1>
diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml
index 6a38b53744..733a8d2837 100644
--- a/doc/src/sgml/wal.sgml
+++ b/doc/src/sgml/wal.sgml
@@ -531,7 +531,11 @@
adjust the <xref linkend="guc-archive-timeout"/> parameter rather than the
checkpoint parameters.)
It is also possible to force a checkpoint by using the SQL
- command <command>CHECKPOINT</command>.
+ command <command>CHECKPOINT</command>. The checkpointer process running the
+ checkpoint will report its progress in the
+ <structname>pg_stat_progress_checkpoint</structname> view except for the
+ shutdown and end-of-recovery cases. See
+ <xref linkend="checkpoint-progress-reporting"/> for details.
</para>
<para>
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index be54c23187..f2cebe0973 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -67,6 +67,7 @@
#include "catalog/catversion.h"
#include "catalog/pg_control.h"
#include "catalog/pg_database.h"
+#include "commands/progress.h"
#include "common/controldata_utils.h"
#include "common/file_utils.h"
#include "executor/instrument.h"
@@ -698,6 +699,8 @@ static void WALInsertLockAcquireExclusive(void);
static void WALInsertLockRelease(void);
static void WALInsertLockUpdateInsertingAt(XLogRecPtr insertingAt);
+static void checkpoint_progress_start(int flags, int type);
+
/*
* Insert an XLOG record represented by an already-constructed chain of data
* chunks. This is a low-level routine; to construct the WAL record header
@@ -6622,6 +6625,9 @@ CreateCheckPoint(int flags)
XLogCtl->RedoRecPtr = checkPoint.redo;
SpinLockRelease(&XLogCtl->info_lck);
+ /* Prepare to report progress of the checkpoint. */
+ checkpoint_progress_start(flags, PROGRESS_CHECKPOINT_TYPE_CHECKPOINT);
+
/*
* If enabled, log checkpoint start. We postpone this until now so as
not
* to log anything if we decided to skip the checkpoint.
@@ -6704,6 +6710,8 @@ CreateCheckPoint(int flags)
* clog and we will correctly flush the update below. So we cannot miss
* any xacts we need to wait for.
*/
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_GET_VIRTUAL_TRANSACTION_IDS);
vxids = GetVirtualXIDsDelayingChkpt(&nvxids, DELAY_CHKPT_START);
if (nvxids > 0)
{
@@ -6819,6 +6827,8 @@ CreateCheckPoint(int flags)
/*
* Let smgr do post-checkpoint cleanup (eg, deleting old files).
*/
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_POST_CHECKPOINT_CLEANUP);
SyncPostCheckpoint();
/*
@@ -6834,6 +6844,9 @@ CreateCheckPoint(int flags)
*/
XLByteToSeg(RedoRecPtr, _logSegNo, wal_segment_size);
KeepLogSeg(recptr, &_logSegNo);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_INVALIDATE_REPLI_SLOTS);
+
if (InvalidateObsoleteReplicationSlots(_logSegNo))
{
/*
@@ -6844,6 +6857,8 @@ CreateCheckPoint(int flags)
KeepLogSeg(recptr, &_logSegNo);
}
_logSegNo--;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_RECYCLE_OLD_XLOG);
RemoveOldXlogFiles(_logSegNo, RedoRecPtr, recptr,
checkPoint.ThisTimeLineID);
@@ -6862,11 +6877,21 @@ CreateCheckPoint(int flags)
* StartupSUBTRANS hasn't been called yet.
*/
if (!RecoveryInProgress())
+ {
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_TRUNCATE_SUBTRANS);
TruncateSUBTRANS(GetOldestTransactionIdConsideredRunning());
+ }
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_FINALIZE);
/* Real work is done; log and update stats. */
LogCheckpointEnd(false);
+ /* Stop reporting progress of the checkpoint. */
+ pgstat_progress_end_command();
+
/* Reset the process title */
update_checkpoint_display(flags, false, true);
@@ -7023,29 +7048,63 @@ static void
CheckPointGuts(XLogRecPtr checkPointRedo, int flags)
{
CheckPointRelationMap();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_REPLI_SLOTS);
CheckPointReplicationSlots();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_SNAPSHOTS);
CheckPointSnapBuild();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_LOGICAL_REWRITE_MAPPINGS);
CheckPointLogicalRewriteHeap();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_REPLI_ORIGIN);
CheckPointReplicationOrigin();
/* Write out all dirty data in SLRUs and the main buffer pool */
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_START(flags);
CheckpointStats.ckpt_write_t = GetCurrentTimestamp();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_CLOG_PAGES);
CheckPointCLOG();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_COMMITTS_PAGES);
CheckPointCommitTs();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_SUBTRANS_PAGES);
CheckPointSUBTRANS();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_MULTIXACT_PAGES);
CheckPointMultiXact();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_PREDICATE_LOCK_PAGES);
CheckPointPredicate();
+
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_BUFFERS);
CheckPointBuffers(flags);
/* Perform all queued up fsyncs */
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_SYNC_START();
CheckpointStats.ckpt_sync_t = GetCurrentTimestamp();
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_SYNC_FILES);
ProcessSyncRequests();
CheckpointStats.ckpt_sync_end_t = GetCurrentTimestamp();
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_DONE();
/* We deliberately delay 2PC checkpointing as long as possible */
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_TWO_PHASE);
CheckPointTwoPhase(checkPointRedo);
}
@@ -7195,6 +7254,9 @@ CreateRestartPoint(int flags)
MemSet(&CheckpointStats, 0, sizeof(CheckpointStats));
CheckpointStats.ckpt_start_t = GetCurrentTimestamp();
+ /* Prepare to report progress of the restartpoint. */
+ checkpoint_progress_start(flags, PROGRESS_CHECKPOINT_TYPE_RESTARTPOINT);
+
if (log_checkpoints)
LogCheckpointStart(flags, true);
@@ -7278,6 +7340,9 @@ CreateRestartPoint(int flags)
replayPtr = GetXLogReplayRecPtr(&replayTLI);
endptr = (receivePtr < replayPtr) ? replayPtr : receivePtr;
KeepLogSeg(endptr, &_logSegNo);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_INVALIDATE_REPLI_SLOTS);
+
if (InvalidateObsoleteReplicationSlots(_logSegNo))
{
/*
@@ -7304,6 +7369,8 @@ CreateRestartPoint(int flags)
if (!RecoveryInProgress())
replayTLI = XLogCtl->InsertTimeLineID;
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_RECYCLE_OLD_XLOG);
RemoveOldXlogFiles(_logSegNo, RedoRecPtr, endptr, replayTLI);
/*
@@ -7320,11 +7387,20 @@ CreateRestartPoint(int flags)
* this because StartupSUBTRANS hasn't been called yet.
*/
if (EnableHotStandby)
+ {
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_TRUNCATE_SUBTRANS);
TruncateSUBTRANS(GetOldestTransactionIdConsideredRunning());
+ }
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_PHASE,
+
PROGRESS_CHECKPOINT_PHASE_FINALIZE);
/* Real work is done; log and update stats. */
LogCheckpointEnd(true);
+ /* Stop reporting progress of the restartpoint. */
+ pgstat_progress_end_command();
+
/* Reset the process title */
update_checkpoint_display(flags, true, true);
@@ -8958,3 +9034,29 @@ SetWalWriterSleeping(bool sleeping)
XLogCtl->WalWriterSleeping = sleeping;
SpinLockRelease(&XLogCtl->info_lck);
}
+
+/*
+ * Start reporting progress of the checkpoint.
+ */
+static void
+checkpoint_progress_start(int flags, int type)
+{
+ const int index[] = {
+ PROGRESS_CHECKPOINT_TYPE,
+ PROGRESS_CHECKPOINT_FLAGS,
+ PROGRESS_CHECKPOINT_LSN,
+ PROGRESS_CHECKPOINT_START_TIMESTAMP,
+ PROGRESS_CHECKPOINT_PHASE
+ };
+ int64 val[5];
+
+ pgstat_progress_start_command(PROGRESS_COMMAND_CHECKPOINT, InvalidOid);
+
+ val[0] = type;
+ val[1] = flags;
+ val[2] = RedoRecPtr;
+ val[3] = CheckpointStats.ckpt_start_t;
+ val[4] = PROGRESS_CHECKPOINT_PHASE_INIT;
+
+ pgstat_progress_update_multi_param(5, index, val);
+}
\ No newline at end of file
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 2d8104b090..384ca35833 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1267,6 +1267,57 @@ CREATE VIEW pg_stat_progress_copy AS
FROM pg_stat_get_progress_info('COPY') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
+CREATE VIEW pg_stat_progress_checkpoint AS
+ SELECT
+ S.pid AS pid,
+ CASE S.param1 WHEN 1 THEN 'checkpoint'
+ WHEN 2 THEN 'restartpoint'
+ END AS type,
+ ( CASE WHEN (S.param2 & 4) > 0 THEN 'immediate ' ELSE '' END ||
+ CASE WHEN (S.param2 & 8) > 0 THEN 'force ' ELSE '' END ||
+ CASE WHEN (S.param2 & 16) > 0 THEN 'flush-all ' ELSE '' END ||
+ CASE WHEN (S.param2 & 32) > 0 THEN 'wait ' ELSE '' END ||
+ CASE WHEN (S.param2 & 128) > 0 THEN 'wal ' ELSE '' END ||
+ CASE WHEN (S.param2 & 256) > 0 THEN 'time ' ELSE '' END
+ ) AS flags,
+ ( '0/0'::pg_lsn +
+ ((CASE
+ WHEN S.param3 < 0 THEN pow(2::numeric, 64::numeric)::numeric
+ ELSE 0::numeric
+ END) +
+ S.param3::numeric)
+ ) AS start_lsn,
+ to_timestamp(946684800 + (S.param4::float8 / 1000000)) AS start_time,
+ CASE S.param5 WHEN 1 THEN 'initializing'
+ WHEN 2 THEN 'getting virtual transaction IDs'
+ WHEN 3 THEN 'checkpointing replication slots'
+ WHEN 4 THEN 'checkpointing logical replication snapshot
files'
+ WHEN 5 THEN 'checkpointing logical rewrite mapping files'
+ WHEN 6 THEN 'checkpointing replication origin'
+ WHEN 7 THEN 'checkpointing commit log pages'
+ WHEN 8 THEN 'checkpointing commit time stamp pages'
+ WHEN 9 THEN 'checkpointing subtransaction pages'
+ WHEN 10 THEN 'checkpointing multixact pages'
+ WHEN 11 THEN 'checkpointing predicate lock pages'
+ WHEN 12 THEN 'checkpointing buffers'
+ WHEN 13 THEN 'processing file sync requests'
+ WHEN 14 THEN 'performing two phase checkpoint'
+ WHEN 15 THEN 'performing post checkpoint cleanup'
+ WHEN 16 THEN 'invalidating replication slots'
+ WHEN 17 THEN 'recycling old WAL files'
+ WHEN 18 THEN 'truncating subtransactions'
+ WHEN 19 THEN 'finalizing'
+ END AS phase,
+ S.param6 AS buffers_total,
+ S.param7 AS buffers_processed,
+ S.param8 AS buffers_written,
+ S.param9 AS files_total,
+ S.param10 AS files_synced,
+ CASE S.param11 WHEN 0 THEN 'false'
+ WHEN 1 THEN 'true'
+ END AS new_requests
+ FROM pg_stat_get_progress_info('CHECKPOINT') AS S;
+
CREATE VIEW pg_user_mappings AS
SELECT
U.oid AS umid,
diff --git a/src/backend/postmaster/checkpointer.c
b/src/backend/postmaster/checkpointer.c
index 5fc076fc14..21bf75b058 100644
--- a/src/backend/postmaster/checkpointer.c
+++ b/src/backend/postmaster/checkpointer.c
@@ -39,6 +39,7 @@
#include "access/xlog.h"
#include "access/xlog_internal.h"
#include "access/xlogrecovery.h"
+#include "commands/progress.h"
#include "libpq/pqsignal.h"
#include "miscadmin.h"
#include "pgstat.h"
@@ -163,7 +164,7 @@ static pg_time_t last_xlog_switch_time;
static void HandleCheckpointerInterrupts(void);
static void CheckArchiveTimeout(void);
static bool IsCheckpointOnSchedule(double progress);
-static bool ImmediateCheckpointRequested(void);
+static bool ImmediateCheckpointRequested(int flags);
static bool CompactCheckpointerRequestQueue(void);
static void UpdateSharedMemoryConfig(void);
@@ -667,16 +668,24 @@ CheckArchiveTimeout(void)
* there is one pending behind it.)
*/
static bool
-ImmediateCheckpointRequested(void)
+ImmediateCheckpointRequested(int flags)
{
volatile CheckpointerShmemStruct *cps = CheckpointerShmem;
+ if (cps->ckpt_flags & CHECKPOINT_REQUESTED)
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_NEW_REQUESTS,
true);
+
/*
* We don't need to acquire the ckpt_lck in this case because we're only
* looking at a single flag bit.
*/
if (cps->ckpt_flags & CHECKPOINT_IMMEDIATE)
+ {
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_FLAGS,
+ (flags
| CHECKPOINT_IMMEDIATE));
return true;
+ }
+
return false;
}
@@ -708,7 +717,7 @@ CheckpointWriteDelay(int flags, double progress)
*/
if (!(flags & CHECKPOINT_IMMEDIATE) &&
!ShutdownRequestPending &&
- !ImmediateCheckpointRequested() &&
+ !ImmediateCheckpointRequested(flags) &&
IsCheckpointOnSchedule(progress))
{
if (ConfigReloadPending)
diff --git a/src/backend/storage/buffer/bufmgr.c
b/src/backend/storage/buffer/bufmgr.c
index 73d30bf619..6d69255667 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -39,6 +39,7 @@
#include "catalog/catalog.h"
#include "catalog/storage.h"
#include "catalog/storage_xlog.h"
+#include "commands/progress.h"
#include "executor/instrument.h"
#include "lib/binaryheap.h"
#include "miscadmin.h"
@@ -2026,6 +2027,8 @@ BufferSync(int flags)
WritebackContextInit(&wb_context, &checkpoint_flush_after);
TRACE_POSTGRESQL_BUFFER_SYNC_START(NBuffers, num_to_scan);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_BUFFERS_TOTAL,
+ num_to_scan);
/*
* Sort buffers that need to be written to reduce the likelihood of
random
@@ -2143,6 +2146,8 @@ BufferSync(int flags)
bufHdr = GetBufferDescriptor(buf_id);
num_processed++;
+
pgstat_progress_update_param(PROGRESS_CHECKPOINT_BUFFERS_PROCESSED,
+
num_processed);
/*
* We don't need to acquire the lock here, because we're only
looking
@@ -2163,6 +2168,8 @@ BufferSync(int flags)
TRACE_POSTGRESQL_BUFFER_SYNC_WRITTEN(buf_id);
PendingCheckpointerStats.buf_written_checkpoints++;
num_written++;
+
pgstat_progress_update_param(PROGRESS_CHECKPOINT_BUFFERS_WRITTEN,
+
num_written);
}
}
diff --git a/src/backend/storage/sync/sync.c b/src/backend/storage/sync/sync.c
index 9d6a9e9109..aa25215910 100644
--- a/src/backend/storage/sync/sync.c
+++ b/src/backend/storage/sync/sync.c
@@ -23,6 +23,7 @@
#include "access/multixact.h"
#include "access/xlog.h"
#include "access/xlogutils.h"
+#include "commands/progress.h"
#include "commands/tablespace.h"
#include "miscadmin.h"
#include "pgstat.h"
@@ -368,6 +369,9 @@ ProcessSyncRequests(void)
/* Now scan the hashtable for fsync requests to process */
absorb_counter = FSYNCS_PER_ABSORB;
hash_seq_init(&hstat, pendingOps);
+ pgstat_progress_update_param(PROGRESS_CHECKPOINT_FILES_TOTAL,
+
hash_get_num_entries(pendingOps));
+
while ((entry = (PendingFsyncEntry *) hash_seq_search(&hstat)) != NULL)
{
int failures;
@@ -431,6 +435,8 @@ ProcessSyncRequests(void)
longest = elapsed;
total_elapsed += elapsed;
processed++;
+
pgstat_progress_update_param(PROGRESS_CHECKPOINT_FILES_SYNCED,
+
processed);
if (log_checkpoints)
elog(DEBUG1, "checkpoint sync:
number=%d file=%s time=%.3f ms",
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index 96bffc0f2a..8281d961bf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -497,6 +497,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
cmdtype = PROGRESS_COMMAND_BASEBACKUP;
else if (pg_strcasecmp(cmd, "COPY") == 0)
cmdtype = PROGRESS_COMMAND_COPY;
+ else if (pg_strcasecmp(cmd, "CHECKPOINT") == 0)
+ cmdtype = PROGRESS_COMMAND_CHECKPOINT;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index a28938caf4..33a64d2f0b 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -151,4 +151,42 @@
#define PROGRESS_COPY_TYPE_PIPE 3
#define PROGRESS_COPY_TYPE_CALLBACK 4
+/* Progress parameters for checkpoint */
+#define PROGRESS_CHECKPOINT_TYPE 0
+#define PROGRESS_CHECKPOINT_FLAGS 1
+#define PROGRESS_CHECKPOINT_LSN 2
+#define PROGRESS_CHECKPOINT_START_TIMESTAMP 3
+#define PROGRESS_CHECKPOINT_PHASE 4
+#define PROGRESS_CHECKPOINT_BUFFERS_TOTAL 5
+#define PROGRESS_CHECKPOINT_BUFFERS_PROCESSED 6
+#define PROGRESS_CHECKPOINT_BUFFERS_WRITTEN 7
+#define PROGRESS_CHECKPOINT_FILES_TOTAL 8
+#define PROGRESS_CHECKPOINT_FILES_SYNCED 9
+#define PROGRESS_CHECKPOINT_NEW_REQUESTS 10
+
+/* Types of checkpoint (as advertised via PROGRESS_CHECKPOINT_TYPE) */
+#define PROGRESS_CHECKPOINT_TYPE_CHECKPOINT 1
+#define PROGRESS_CHECKPOINT_TYPE_RESTARTPOINT 2
+
+/* Phases of checkpoint (as advertised via PROGRESS_CHECKPOINT_PHASE) */
+#define PROGRESS_CHECKPOINT_PHASE_INIT 1
+#define PROGRESS_CHECKPOINT_PHASE_GET_VIRTUAL_TRANSACTION_IDS 2
+#define PROGRESS_CHECKPOINT_PHASE_REPLI_SLOTS 3
+#define PROGRESS_CHECKPOINT_PHASE_SNAPSHOTS 4
+#define PROGRESS_CHECKPOINT_PHASE_LOGICAL_REWRITE_MAPPINGS 5
+#define PROGRESS_CHECKPOINT_PHASE_REPLI_ORIGIN 6
+#define PROGRESS_CHECKPOINT_PHASE_CLOG_PAGES 7
+#define PROGRESS_CHECKPOINT_PHASE_COMMITTS_PAGES 8
+#define PROGRESS_CHECKPOINT_PHASE_SUBTRANS_PAGES 9
+#define PROGRESS_CHECKPOINT_PHASE_MULTIXACT_PAGES 10
+#define PROGRESS_CHECKPOINT_PHASE_PREDICATE_LOCK_PAGES 11
+#define PROGRESS_CHECKPOINT_PHASE_BUFFERS 12
+#define PROGRESS_CHECKPOINT_PHASE_SYNC_FILES 13
+#define PROGRESS_CHECKPOINT_PHASE_TWO_PHASE 14
+#define PROGRESS_CHECKPOINT_PHASE_POST_CHECKPOINT_CLEANUP 15
+#define PROGRESS_CHECKPOINT_PHASE_INVALIDATE_REPLI_SLOTS 16
+#define PROGRESS_CHECKPOINT_PHASE_RECYCLE_OLD_XLOG 17
+#define PROGRESS_CHECKPOINT_PHASE_TRUNCATE_SUBTRANS 18
+#define PROGRESS_CHECKPOINT_PHASE_FINALIZE 19
+
#endif
diff --git a/src/include/utils/backend_progress.h
b/src/include/utils/backend_progress.h
index 47bf8029b0..02d51fb948 100644
--- a/src/include/utils/backend_progress.h
+++ b/src/include/utils/backend_progress.h
@@ -27,7 +27,8 @@ typedef enum ProgressCommandType
PROGRESS_COMMAND_CLUSTER,
PROGRESS_COMMAND_CREATE_INDEX,
PROGRESS_COMMAND_BASEBACKUP,
- PROGRESS_COMMAND_COPY
+ PROGRESS_COMMAND_COPY,
+ PROGRESS_COMMAND_CHECKPOINT
} ProgressCommandType;
#define PGSTAT_NUM_PROGRESS_PARAM 20
diff --git a/src/test/regress/expected/rules.out
b/src/test/regress/expected/rules.out
index 624d0e5aae..eab68d7f14 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1913,6 +1913,76 @@ pg_stat_progress_basebackup| SELECT s.pid,
s.param4 AS tablespaces_total,
s.param5 AS tablespaces_streamed
FROM pg_stat_get_progress_info('BASEBACKUP'::text) s(pid, datid, relid,
param1, param2, param3, param4, param5, param6, param7, param8, param9,
param10, param11, param12, param13, param14, param15, param16, param17,
param18, param19, param20);
+pg_stat_progress_checkpoint| SELECT s.pid,
+ CASE s.param1
+ WHEN 1 THEN 'checkpoint'::text
+ WHEN 2 THEN 'restartpoint'::text
+ ELSE NULL::text
+ END AS type,
+ (((((
+ CASE
+ WHEN ((s.param2 & (4)::bigint) > 0) THEN 'immediate '::text
+ ELSE ''::text
+ END ||
+ CASE
+ WHEN ((s.param2 & (8)::bigint) > 0) THEN 'force '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (16)::bigint) > 0) THEN 'flush-all '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (32)::bigint) > 0) THEN 'wait '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (128)::bigint) > 0) THEN 'wal '::text
+ ELSE ''::text
+ END) ||
+ CASE
+ WHEN ((s.param2 & (256)::bigint) > 0) THEN 'time '::text
+ ELSE ''::text
+ END) AS flags,
+ ('0/0'::pg_lsn + (
+ CASE
+ WHEN (s.param3 < 0) THEN pow((2)::numeric, (64)::numeric)
+ ELSE (0)::numeric
+ END + (s.param3)::numeric)) AS start_lsn,
+ to_timestamp(((946684800)::double precision + ((s.param4)::double
precision / (1000000)::double precision))) AS start_time,
+ CASE s.param5
+ WHEN 1 THEN 'initializing'::text
+ WHEN 2 THEN 'getting virtual transaction IDs'::text
+ WHEN 3 THEN 'checkpointing replication slots'::text
+ WHEN 4 THEN 'checkpointing logical replication snapshot
files'::text
+ WHEN 5 THEN 'checkpointing logical rewrite mapping files'::text
+ WHEN 6 THEN 'checkpointing replication origin'::text
+ WHEN 7 THEN 'checkpointing commit log pages'::text
+ WHEN 8 THEN 'checkpointing commit time stamp pages'::text
+ WHEN 9 THEN 'checkpointing subtransaction pages'::text
+ WHEN 10 THEN 'checkpointing multixact pages'::text
+ WHEN 11 THEN 'checkpointing predicate lock pages'::text
+ WHEN 12 THEN 'checkpointing buffers'::text
+ WHEN 13 THEN 'processing file sync requests'::text
+ WHEN 14 THEN 'performing two phase checkpoint'::text
+ WHEN 15 THEN 'performing post checkpoint cleanup'::text
+ WHEN 16 THEN 'invalidating replication slots'::text
+ WHEN 17 THEN 'recycling old WAL files'::text
+ WHEN 18 THEN 'truncating subtransactions'::text
+ WHEN 19 THEN 'finalizing'::text
+ ELSE NULL::text
+ END AS phase,
+ s.param6 AS buffers_total,
+ s.param7 AS buffers_processed,
+ s.param8 AS buffers_written,
+ s.param9 AS files_total,
+ s.param10 AS files_synced,
+ CASE s.param11
+ WHEN 0 THEN 'false'::text
+ WHEN 1 THEN 'true'::text
+ ELSE NULL::text
+ END AS new_requests
+ FROM pg_stat_get_progress_info('CHECKPOINT'::text) s(pid, datid, relid,
param1, param2, param3, param4, param5, param6, param7, param8, param9,
param10, param11, param12, param13, param14, param15, param16, param17,
param18, param19, param20);
pg_stat_progress_cluster| SELECT s.pid,
s.datid,
d.datname,
