public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nitin Jadhav <[email protected]>
To: Bharath Rupireddy <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Magnus Hagander <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Report checkpoint progress with pg_stat_progress_checkpoint (was: Report checkpoint progress in server logs)
Date: Thu, 10 Feb 2022 12:22:48 +0530
Message-ID: <CAMm1aWY_wThAxmhC_7mkFsYHcNP2_JQLAKJEoY5EcHBi0Q42QQ@mail.gmail.com> (raw)
In-Reply-To: <CALj2ACUGWAC56yqhVOisNPXhewfRdeHG4NGTOK_sOStL=7yP+w@mail.gmail.com>
References: <CALj2ACV-F+K+z+XW8fnK4MV71qz2gzAMxFnYziRgZURMB5ycAQ@mail.gmail.com>
<CABUevEyX6-NSnhMhWzicg_PpmF52Fv_Zm7tgLT+9iDAO4W8Ftg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAMm1aWZLvBAKJJc0+hGqYkofAGxrKFLGi49joYKdPNbQBzrXiA@mail.gmail.com>
<CALj2ACUGWAC56yqhVOisNPXhewfRdeHG4NGTOK_sOStL=7yP+w@mail.gmail.com>
> > We need at least a trace of the number of buffers to sync (num_to_scan) before the checkpoint start, instead of just emitting the stats at the end.
> >
> > Bharat, it would be good to show the buffers synced counter and the total buffers to sync, checkpointer pid, substep it is running, whether it is on target for completion, checkpoint_Reason
> > (manual/times/forced). BufferSync has several variables tracking the sync progress locally, and we may need some refactoring here.
>
> I agree to provide above mentioned information as part of showing the
> progress of current checkpoint operation. I am currently looking into
> the code to know if any other information can be added.
Here is the initial patch to show the progress of checkpoint through
pg_stat_progress_checkpoint view. Please find the attachment.
The information added to this view are pid - process ID of a
CHECKPOINTER process, kind - kind of checkpoint indicates the reason
for checkpoint (values can be wal, time or force), phase - indicates
the current phase of checkpoint operation, total_buffer_writes - total
number of buffers to be written, buffers_processed - number of buffers
processed, buffers_written - number of buffers written,
total_file_syncs - total number of files to be synced, files_synced -
number of files synced.
There are many operations happen as part of checkpoint. For each of
the operation I am updating the phase field of
pg_stat_progress_checkpoint view. The values supported for this field
are initializing, checkpointing replication slots, checkpointing
snapshots, checkpointing logical rewrite mappings, checkpointing CLOG
pages, checkpointing CommitTs pages, checkpointing SUBTRANS pages,
checkpointing MULTIXACT pages, checkpointing SLRU pages, checkpointing
buffers, performing sync requests, performing two phase checkpoint,
recycling old XLOG files and Finalizing. In case of checkpointing
buffers phase, the fields total_buffer_writes, buffers_processed and
buffers_written shows the detailed progress of writing buffers. In
case of performing sync requests phase, the fields total_file_syncs
and files_synced shows the detailed progress of syncing files. In
other phases, only the phase field is getting updated and it is
difficult to show the progress because we do not get the total number
of files count without traversing the directory. It is not worth to
calculate that as it affects the performance of the checkpoint. I also
gave a thought to just mention the number of files processed, but this
wont give a meaningful progress information (It can be treated as
statistics). Hence just updating the phase field in those scenarios.
Apart from above fields, I am planning to add few more fields to the
view in the next patch. That is, process ID of the backend process
which triggered a CHECKPOINT command, checkpoint start location, filed
to indicate whether it is a checkpoint or restartpoint and elapsed
time of the checkpoint operation. Please share your thoughts. I would
be happy to add any other information that contributes to showing the
progress of checkpoint.
As per the discussion in this thread, there should be some mechanism
to show the progress of checkpoint during shutdown and end-of-recovery
cases as we cannot access pg_stat_progress_checkpoint in those cases.
I am working on this to use log_startup_progress_interval mechanism to
log the progress in the server logs.
Kindly review the patch and share your thoughts.
On Fri, Jan 28, 2022 at 12:24 PM Bharath Rupireddy
<[email protected]> wrote:
>
> On Fri, Jan 21, 2022 at 11:07 AM Nitin Jadhav
> <[email protected]> wrote:
> >
> > > I think the right choice to solve the *general* problem is the
> > > mentioned pg_stat_progress_checkpoints.
> > >
> > > We may want to *additionally* have the ability to log the progress
> > > specifically for the special cases when we're not able to use that
> > > view. And in those case, we can perhaps just use the existing
> > > log_startup_progress_interval parameter for this as well -- at least
> > > for the startup checkpoint.
> >
> > +1
> >
> > > We need at least a trace of the number of buffers to sync (num_to_scan) before the checkpoint start, instead of just emitting the stats at the end.
> > >
> > > Bharat, it would be good to show the buffers synced counter and the total buffers to sync, checkpointer pid, substep it is running, whether it is on target for completion, checkpoint_Reason
> > > (manual/times/forced). BufferSync has several variables tracking the sync progress locally, and we may need some refactoring here.
> >
> > I agree to provide above mentioned information as part of showing the
> > progress of current checkpoint operation. I am currently looking into
> > the code to know if any other information can be added.
>
> As suggested in the other thread by Julien, I'm changing the subject
> of this thread to reflect the discussion.
>
> Regards,
> Bharath Rupireddy.
Attachments:
[application/octet-stream] 0001-pg_stat_progress_checkpoint-view.patch (25.3K, ../CAMm1aWY_wThAxmhC_7mkFsYHcNP2_JQLAKJEoY5EcHBi0Q42QQ@mail.gmail.com/2-0001-pg_stat_progress_checkpoint-view.patch)
download | inline diff:
From 5b68df74d68922685dff4eb665a6431c5046f848 Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <[email protected]>
Date: Wed, 9 Feb 2022 12:00:11 +0000
Subject: [PATCH] pg_stat_progress_checkpoint view
---
doc/src/sgml/monitoring.sgml | 263 +++++++++++++++++++++++++++
src/backend/access/transam/xlog.c | 123 ++++++++++++-
src/backend/catalog/system_views.sql | 29 +++
src/backend/storage/buffer/bufmgr.c | 10 +
src/backend/storage/sync/sync.c | 10 +-
src/backend/utils/adt/pgstatfuncs.c | 2 +
src/include/access/xlog.h | 4 +
src/include/commands/progress.h | 31 ++++
src/include/storage/sync.h | 2 +-
src/include/utils/backend_progress.h | 3 +-
10 files changed, 473 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62f2a3332b..a6893d4543 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -401,6 +401,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 <command>CHECKPOINT</command> operation.
+ See <xref linkend='checkpoint-progress-reporting'/>.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -6886,6 +6893,262 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
</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 checkpoint operation. 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 a CHECKPOINTER process.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>kinds</structfield> <type>text</type>
+ </para>
+ <para>
+ Kind of checkpoint. See <xref linkend="checkpoint-kinds"/>.
+ </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>total_buffer_writes</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> total_buffer_writes </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>total_buffer_writes</literal>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_file_syncs</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>total_file_syncs</literal> when the checkpoint is complete.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="checkpoint-kinds">
+ <title>CHECKPOINT kinds</title>
+ <tgroup cols="2">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Kinds</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>wal</literal></entry>
+ <entry>
+ The checkpoint operation is requested due to XLOG filling.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>time</literal></entry>
+ <entry>
+ The checkpoint operation is requested due to timeout.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>force</literal></entry>
+ <entry>
+ The checkpoint operation is forced even if no XLOG activity has occurred
+ since the last one.
+ </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>checkpointing replication slots</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently flushing all the replication slots to
+ disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing snapshots</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently removing all the serialized
+ snapshots that are not required anymore.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing logical rewrite mappings</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently removing/flushing the logical
+ rewrite mappings.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing CLOG pages</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently writing CLOG pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing CommitTs pages</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently writing CommitTs pages to disk.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>checkpointing SUBTRANS pages</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently writing SUBTRANS 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 SLRU pages</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently writing SLRU 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>performing sync requests</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently performing 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>recycling old XLOG files</literal></entry>
+ <entry>
+ The CHECKPOINTER process is currently recycling old XLOG files.
+ </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/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 958220c495..df568eecd8 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -9089,6 +9089,9 @@ CreateCheckPoint(int flags)
if (RecoveryInProgress() && (flags & CHECKPOINT_END_OF_RECOVERY) == 0)
elog(ERROR, "can't create a checkpoint during recovery");
+ /* Prepare to report progress of the checkpoint. */
+ checkpoint_progress_start(flags);
+
/*
* Prepare to accumulate statistics.
*
@@ -9432,8 +9435,12 @@ CreateCheckPoint(int flags)
KeepLogSeg(recptr, &_logSegNo);
}
_logSegNo--;
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_OLD_XLOG_RECYCLE);
RemoveOldXlogFiles(_logSegNo, RedoRecPtr, recptr,
checkPoint.ThisTimeLineID);
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_FINALIZE);
/*
* Make more log segments if needed. (Do this after recycling old log
@@ -9455,6 +9462,9 @@ CreateCheckPoint(int flags)
/* Real work is done; log and update stats. */
LogCheckpointEnd(false);
+ /* Stop reporting progress of the checkpoint. */
+ checkpoint_progress_end(flags);
+
/* Reset the process title */
update_checkpoint_display(flags, false, true);
@@ -9568,29 +9578,60 @@ static void
CheckPointGuts(XLogRecPtr checkPointRedo, int flags)
{
CheckPointRelationMap();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_REPLI_SLOTS);
CheckPointReplicationSlots();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_SNAPSHOTS);
CheckPointSnapBuild();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_LOGICAL_REWRITE_MAPPINGS);
CheckPointLogicalRewriteHeap();
CheckPointReplicationOrigin();
/* Write out all dirty data in SLRUs and the main buffer pool */
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_START(flags);
CheckpointStats.ckpt_write_t = GetCurrentTimestamp();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_CLOG_PAGES);
CheckPointCLOG();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_COMMITTS_PAGES);
CheckPointCommitTs();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_SUBTRANS_PAGES);
CheckPointSUBTRANS();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_MULTIXACT_PAGES);
CheckPointMultiXact();
+
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_SLRU_PAGES);
CheckPointPredicate();
+
+ checkpoint_progress_update_param(flags, 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();
- ProcessSyncRequests();
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_FILE_SYNC);
+ ProcessSyncRequests(flags);
CheckpointStats.ckpt_sync_end_t = GetCurrentTimestamp();
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_DONE();
/* We deliberately delay 2PC checkpointing as long as possible */
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_TWO_PHASE);
CheckPointTwoPhase(checkPointRedo);
}
@@ -9727,6 +9768,9 @@ CreateRestartPoint(int flags)
XLogCtl->RedoRecPtr = lastCheckPoint.redo;
SpinLockRelease(&XLogCtl->info_lck);
+ /* Prepare to report progress of the checkpoint. */
+ checkpoint_progress_start(flags);
+
/*
* Prepare to accumulate statistics.
*
@@ -9837,7 +9881,11 @@ CreateRestartPoint(int flags)
if (!RecoveryInProgress())
replayTLI = XLogCtl->InsertTimeLineID;
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_OLD_XLOG_RECYCLE);
RemoveOldXlogFiles(_logSegNo, RedoRecPtr, endptr, replayTLI);
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_FINALIZE);
/*
* Make more log segments if needed. (Do this after recycling old log
@@ -9858,6 +9906,9 @@ CreateRestartPoint(int flags)
/* Real work is done; log and update stats. */
LogCheckpointEnd(true);
+ /* Stop reporting progress of the checkpoint. */
+ checkpoint_progress_end(flags);
+
/* Reset the process title */
update_checkpoint_display(flags, true, true);
@@ -13242,3 +13293,73 @@ XLogRequestWalReceiverReply(void)
{
doRequestWalReceiverReply = true;
}
+
+/*
+ * Start reporting progress of the checkpoint.
+ */
+void
+checkpoint_progress_start(int flags)
+{
+ /* In bootstrap mode, we don't actually record anything. */
+ if (IsBootstrapProcessingMode())
+ return;
+
+ /*
+ * Cannot access pg_stat_progress_checkpoint view in case of checkpoint
+ * during shutdown and end-of-recovery.
+ */
+ if ((flags & (CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_END_OF_RECOVERY)) == 0)
+ {
+ pgstat_progress_start_command(PROGRESS_COMMAND_CHECKPOINT, InvalidOid);
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_PHASE,
+ PROGRESS_CHECKPOINT_PHASE_INIT);
+ if (flags & CHECKPOINT_CAUSE_XLOG)
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_KIND,
+ PROGRESS_CHECKPOINT_KIND_WAL);
+ else if (flags & CHECKPOINT_CAUSE_TIME)
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_KIND,
+ PROGRESS_CHECKPOINT_KIND_TIME);
+ else if (flags & CHECKPOINT_FORCE)
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_KIND,
+ PROGRESS_CHECKPOINT_KIND_FORCE);
+ else
+ checkpoint_progress_update_param(flags, PROGRESS_CHECKPOINT_KIND,
+ PROGRESS_CHECKPOINT_KIND_UNKNOWN);
+ }
+}
+
+/*
+ * Update index'th member in st_progress_param[] array with the latest value.
+ */
+void
+checkpoint_progress_update_param(int flags, int index, int64 val)
+{
+ /* In bootstrap mode, we don't actually record anything. */
+ if (IsBootstrapProcessingMode())
+ return;
+
+ /*
+ * Cannot access pg_stat_progress_checkpoint view in case of checkpoint
+ * during shutdown and end-of-recovery.
+ */
+ if ((flags & (CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_END_OF_RECOVERY)) == 0)
+ pgstat_progress_update_param(index, val);
+}
+
+/*
+ * Stop reporting progress of the checkpoint.
+ */
+void
+checkpoint_progress_end(int flags)
+{
+ /* In bootstrap mode, we don't actually record anything. */
+ if (IsBootstrapProcessingMode())
+ return;
+
+ /*
+ * Cannot access pg_stat_progress_checkpoint view in case of checkpoint
+ * during shutdown and end-of-recovery.
+ */
+ if ((flags & (CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_END_OF_RECOVERY)) == 0)
+ pgstat_progress_end_command();
+}
\ No newline at end of file
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 3cb69b1f87..6a90d63fff 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1286,3 +1286,32 @@ CREATE VIEW pg_stat_subscription_workers AS
FROM pg_subscription_rel) sr,
LATERAL pg_stat_get_subscription_worker(sr.subid, sr.relid) w
JOIN pg_subscription s ON (w.subid = s.oid);
+
+CREATE VIEW pg_stat_progress_checkpoint AS
+ SELECT
+ S.pid AS pid,
+ CASE S.param1 WHEN 0 THEN 'wal'
+ WHEN 1 THEN 'time'
+ WHEN 2 THEN 'force'
+ END AS kind,
+ CASE S.param2 WHEN 0 THEN 'initializing'
+ WHEN 1 THEN 'checkpointing replication slots'
+ WHEN 2 THEN 'checkpointing snapshots'
+ WHEN 3 THEN 'checkpointing logical rewrite mappings'
+ WHEN 4 THEN 'checkpointing CLOG pages'
+ WHEN 5 THEN 'checkpointing CommitTs pages'
+ WHEN 6 THEN 'checkpointing SUBTRANS pages'
+ WHEN 7 THEN 'checkpointing MULTIXACT pages'
+ WHEN 8 THEN 'checkpointing SLRU pages'
+ WHEN 9 THEN 'checkpointing buffers'
+ WHEN 10 THEN 'performing sync requests'
+ WHEN 11 THEN 'performing two phase checkpoint'
+ WHEN 12 THEN 'recycling old XLOG files'
+ WHEN 13 THEN 'Finalizing'
+ END AS phase,
+ S.param3 AS total_buffer_writes,
+ S.param4 AS buffers_processed,
+ S.param5 AS buffers_written,
+ S.param6 AS total_file_syncs,
+ S.param7 AS files_synced
+ FROM pg_stat_get_progress_info('CHECKPOINT') AS S;
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index f5459c68f8..cf0ad299f3 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -38,6 +38,7 @@
#include "access/xlogutils.h"
#include "catalog/catalog.h"
#include "catalog/storage.h"
+#include "commands/progress.h"
#include "executor/instrument.h"
#include "lib/binaryheap.h"
#include "miscadmin.h"
@@ -2012,6 +2013,9 @@ BufferSync(int flags)
WritebackContextInit(&wb_context, &checkpoint_flush_after);
TRACE_POSTGRESQL_BUFFER_SYNC_START(NBuffers, num_to_scan);
+ checkpoint_progress_update_param(flags,
+ PROGRESS_CHECKPOINT_TOTAL_BUFFER_WRITES,
+ num_to_scan);
/*
* Sort buffers that need to be written to reduce the likelihood of random
@@ -2129,6 +2133,9 @@ BufferSync(int flags)
bufHdr = GetBufferDescriptor(buf_id);
num_processed++;
+ checkpoint_progress_update_param(flags,
+ PROGRESS_CHECKPOINT_BUFFERS_PROCESSED,
+ num_processed);
/*
* We don't need to acquire the lock here, because we're only looking
@@ -2149,6 +2156,9 @@ BufferSync(int flags)
TRACE_POSTGRESQL_BUFFER_SYNC_WRITTEN(buf_id);
PendingCheckpointerStats.m_buf_written_checkpoints++;
num_written++;
+ checkpoint_progress_update_param(flags,
+ PROGRESS_CHECKPOINT_BUFFERS_WRITTEN,
+ num_written);
}
}
diff --git a/src/backend/storage/sync/sync.c b/src/backend/storage/sync/sync.c
index 543f691f2d..b8f6aebb7c 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"
@@ -277,7 +278,7 @@ SyncPostCheckpoint(void)
* ProcessSyncRequests() -- Process queued fsync requests.
*/
void
-ProcessSyncRequests(void)
+ProcessSyncRequests(int flags)
{
static bool sync_in_progress = false;
@@ -355,6 +356,10 @@ ProcessSyncRequests(void)
/* Now scan the hashtable for fsync requests to process */
absorb_counter = FSYNCS_PER_ABSORB;
hash_seq_init(&hstat, pendingOps);
+ checkpoint_progress_update_param(flags,
+ PROGRESS_CHECKPOINT_TOTAL_FILE_SYNCS,
+ hash_get_num_entries(pendingOps));
+
while ((entry = (PendingFsyncEntry *) hash_seq_search(&hstat)) != NULL)
{
int failures;
@@ -418,6 +423,9 @@ ProcessSyncRequests(void)
longest = elapsed;
total_elapsed += elapsed;
processed++;
+ checkpoint_progress_update_param(flags,
+ 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 15cb17ace4..7438e0ce84 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -494,6 +494,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/access/xlog.h b/src/include/access/xlog.h
index a4b1c1286f..58c547b2d5 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -353,6 +353,10 @@ extern void do_pg_abort_backup(int code, Datum arg);
extern void register_persistent_abort_backup_handler(void);
extern SessionBackupState get_backup_status(void);
+extern void checkpoint_progress_start(int flags);
+extern void checkpoint_progress_update_param(int flags, int index, int64 val);
+extern void checkpoint_progress_end(int flags);
+
/* File path names (all relative to $PGDATA) */
#define RECOVERY_SIGNAL_FILE "recovery.signal"
#define STANDBY_SIGNAL_FILE "standby.signal"
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index a28938caf4..e1c574d053 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -151,4 +151,35 @@
#define PROGRESS_COPY_TYPE_PIPE 3
#define PROGRESS_COPY_TYPE_CALLBACK 4
+/* Progress parameters for checkpoint */
+#define PROGRESS_CHECKPOINT_KIND 0
+#define PROGRESS_CHECKPOINT_PHASE 1
+#define PROGRESS_CHECKPOINT_TOTAL_BUFFER_WRITES 2
+#define PROGRESS_CHECKPOINT_BUFFERS_PROCESSED 3
+#define PROGRESS_CHECKPOINT_BUFFERS_WRITTEN 4
+#define PROGRESS_CHECKPOINT_TOTAL_FILE_SYNCS 5
+#define PROGRESS_CHECKPOINT_FILES_SYNCED 6
+
+/* Kinds of checkpoint (as advertised via PROGRESS_CHECKPOINT_KIND) */
+#define PROGRESS_CHECKPOINT_KIND_WAL 0
+#define PROGRESS_CHECKPOINT_KIND_TIME 1
+#define PROGRESS_CHECKPOINT_KIND_FORCE 2
+#define PROGRESS_CHECKPOINT_KIND_UNKNOWN 3
+
+/* Phases of checkpoint (as advertised via PROGRESS_CHECKPOINT_PHASE) */
+#define PROGRESS_CHECKPOINT_PHASE_INIT 0
+#define PROGRESS_CHECKPOINT_PHASE_REPLI_SLOTS 1
+#define PROGRESS_CHECKPOINT_PHASE_SNAPSHOTS 2
+#define PROGRESS_CHECKPOINT_PHASE_LOGICAL_REWRITE_MAPPINGS 3
+#define PROGRESS_CHECKPOINT_PHASE_CLOG_PAGES 4
+#define PROGRESS_CHECKPOINT_PHASE_COMMITTS_PAGES 5
+#define PROGRESS_CHECKPOINT_PHASE_SUBTRANS_PAGES 6
+#define PROGRESS_CHECKPOINT_PHASE_MULTIXACT_PAGES 7
+#define PROGRESS_CHECKPOINT_PHASE_SLRU_PAGES 8
+#define PROGRESS_CHECKPOINT_PHASE_BUFFERS 9
+#define PROGRESS_CHECKPOINT_PHASE_FILE_SYNC 10
+#define PROGRESS_CHECKPOINT_PHASE_TWO_PHASE 11
+#define PROGRESS_CHECKPOINT_PHASE_OLD_XLOG_RECYCLE 12
+#define PROGRESS_CHECKPOINT_PHASE_FINALIZE 13
+
#endif
diff --git a/src/include/storage/sync.h b/src/include/storage/sync.h
index 9737e1eb67..fed52efa30 100644
--- a/src/include/storage/sync.h
+++ b/src/include/storage/sync.h
@@ -58,7 +58,7 @@ typedef struct FileTag
extern void InitSync(void);
extern void SyncPreCheckpoint(void);
extern void SyncPostCheckpoint(void);
-extern void ProcessSyncRequests(void);
+extern void ProcessSyncRequests(int flags);
extern void RememberSyncRequest(const FileTag *ftag, SyncRequestType type);
extern bool RegisterSyncRequest(const FileTag *ftag, SyncRequestType type,
bool retryOnError);
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
--
2.25.1
view thread (74+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Report checkpoint progress with pg_stat_progress_checkpoint (was: Report checkpoint progress in server logs)
In-Reply-To: <CAMm1aWY_wThAxmhC_7mkFsYHcNP2_JQLAKJEoY5EcHBi0Q42QQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox