PostgreSQL的checkpoint简析

Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint.
At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file. (The change records were previously flushed to the WAL files.) 
In the event of a crash, the crash recovery procedure looks at the latest checkpoint record to determine the point in the log (known as the redo record) from which it should start the REDO operation.
Any changes made to data files before that point are guaranteed to be already on disk.
Hence, after a checkpoint, log segments preceding the one containing the redo record are no longer needed and can be recycled or removed. (When WAL archiving is being done, the log segments must be archived before being recycled or removed.)

#define CHECKPOINT_CAUSE_TIME    0x0100    /* Elapsed time */

#define CHECKPOINT_IS_SHUTDOWN    0x0001    /* Checkpoint is for shutdown */
主要场景：数据库shutdown时

#define CHECKPOINT_END_OF_RECOVERY    0x0002    /* Like shutdown checkpoint, but  issued at end of WAL recovery */
主要场景：startup进程StartupXlog完成时

#define CHECKPOINT_IMMEDIATE    0x0004    /* Do it without delays */
主要场景：当postgres为standalone backend模式请求checkpoint时；Basebackup执行备份时

#define CHECKPOINT_FORCE        0x0008    /* Force even if no activity */
主要场景：手动执行checkpoint命令；standby实例进行promote时

#define CHECKPOINT_FLUSH_ALL    0x0010    /* Flush all pages, including those belonging to unlogged tables */
主要场景：drop database或者create database后

#define CHECKPOINT_CAUSE_XLOG    0x0040    /* XLOG consumption */
主要场景：wal新增数量大于等于CheckPointSegments – 1时，默认参数下大致是42。

在9.5后CheckPointSegments不再是一个单独参数，根据max_wal_size_mb和checkpoint_completion_target参数联动。
CalculateCheckpointSegments函数中计算CheckPointSegments = max_wal_size_mb/(wal_segment_size/(1024*1024))/(1.0 + CheckPointCompletionTarget)
                      = 1024 / (16777216/(1024*1024))/1.5 ≈ 43
XLogCheckpointNeeded函数中判断新增wal数量大于等于CheckPointSegments – 1, 满足时函数返回true，表示需要进行checkpoint。
有时checkpoint比较频繁会提示需要增大max_wal_size，根据计算公式，被除数max_wal_size越大，则CheckPointSegments越大，checkpoint的间隔就越大。

    /*
     * Update the control file.
     */
    LWLockAcquire(ControlFileLock, LW_EXCLUSIVE);
    if (shutdown)
        ControlFile->state = DB_SHUTDOWNED;
    ControlFile->checkPoint = ProcLastRecPtr;
    ControlFile->checkPointCopy = checkPoint;
    ControlFile->time = (pg_time_t) time(NULL);
    /* crash recovery should always recover to the end of WAL */
    ControlFile->minRecoveryPoint = InvalidXLogRecPtr;
    ControlFile->minRecoveryPointTLI = 0;
    /*
     * Persist unloggedLSN value. It's reset on crash recovery, so this goes
     * unused on non-shutdown checkpoints, but seems useful to store it always
     * for debugging purposes.
     */
    SpinLockAcquire(&XLogCtl->ulsn_lck);
    ControlFile->unloggedLSN = XLogCtl->unloggedLSN;
    SpinLockRelease(&XLogCtl->ulsn_lck);
    /*更新pg_control文件*/
    UpdateControlFile();
    LWLockRelease(ControlFileLock);

/*
     * Update the average distance between checkpoints if the prior checkpoint
     * exists.
     */
    if (PriorRedoPtr != InvalidXLogRecPtr)
     /*根据ptr偏移量，预估出两次checkpoint间产生的wal量CheckPointDistanceEstimate*/
        UpdateCheckPointDistanceEstimate(RedoRecPtr - PriorRedoPtr);
    /*
     * Delete old log files, those no longer needed for last checkpoint to
     * prevent the disk holding the xlog from growing full.
     */
    XLByteToSeg(RedoRecPtr, _logSegNo, wal_segment_size);
/*根据min{wal_keep_segments, min(replication_slot.restart_lsn)}计算出_logSegNo，比_logSegNo早的日志后续将会被清理掉*/
    KeepLogSeg(recptr, &_logSegNo);
    _logSegNo--;
/*首先根据CheckPointDistanceEstimate 结合一套公式，计算出开始回收重用的recycleSegNo，从这个日志开始回收重用（wal_recycle默认开启，主要是保留日志并rename为新的序列号，回收一个序列号加一）*/
/*然后将_logSegNo之前并已经归档（如果开启归档）的wal都清理掉*/
    RemoveOldXlogFiles(_logSegNo, RedoRecPtr, recptr);

/*
     * If this isn't a shutdown or forced checkpoint, and if there has been no
     * WAL activity requiring a checkpoint, skip it.  The idea here is to
     * avoid inserting duplicate checkpoints when the system is idle.
     */
    if ((flags & (CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_END_OF_RECOVERY |
                  CHECKPOINT_FORCE)) == 0)
    {
        if (last_important_lsn == ControlFile->checkPoint)
        {
            WALInsertLockRelease();
            LWLockRelease(CheckpointLock);
            END_CRIT_SECTION();
            ereport(DEBUG1,
                    (errmsg('checkpoint skipped because system is idle')));
            return;
        }
    }

last_important_lsn = WALInsertLocks[lockno].l.lastImportantAt

WALInsertLocks[lockno].l.lastImportantAt = StartPos;

ControlFile->checkPoint = ProcLastRecPtr;

/*
 * Log start of a checkpoint.
 */
static void
LogCheckpointStart(int flags, bool restartpoint)
{
    elog(LOG, '%s starting:%s%s%s%s%s%s%s%s',
         restartpoint ? 'restartpoint' : 'checkpoint',
         (flags & CHECKPOINT_IS_SHUTDOWN) ? ' shutdown' : '',
         (flags & CHECKPOINT_END_OF_RECOVERY) ? ' end-of-recovery' : '',
         (flags & CHECKPOINT_IMMEDIATE) ? ' immediate' : '',
         (flags & CHECKPOINT_FORCE) ? ' force' : '',
         (flags & CHECKPOINT_WAIT) ? ' wait' : '',
         (flags & CHECKPOINT_CAUSE_XLOG) ? ' wal' : '',
         (flags & CHECKPOINT_CAUSE_TIME) ? ' time' : '',
         (flags & CHECKPOINT_FLUSH_ALL) ? ' flush-all' : '');
}

/*
 * Log end of a checkpoint.
 */
static void
LogCheckpointEnd(bool restartpoint)
{
    /* ............*/
    elog(LOG, '%s complete: wrote %d buffers (%.1f%%); '
         '%d WAL file(s) added, %d removed, %d recycled; '
         'write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; '
         'sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; '
         'distance=%d kB, estimate=%d kB',
         restartpoint ? 'restartpoint' : 'checkpoint',
         CheckpointStats.ckpt_bufs_written,
         (double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
         CheckpointStats.ckpt_segs_added,
         CheckpointStats.ckpt_segs_removed,
         CheckpointStats.ckpt_segs_recycled,
         write_secs, write_usecs / 1000,
         sync_secs, sync_usecs / 1000,
         total_secs, total_usecs / 1000,
         CheckpointStats.ckpt_sync_rels,
         longest_secs, longest_usecs / 1000,
         average_secs, average_usecs / 1000,
         (int) (PrevCheckPointDistance / 1024.0),
         (int) (CheckPointDistanceEstimate / 1024.0));
}

2021-10-10 20:55:08.044 CST,,,10801,,615da38b.2a31,41,,2021-10-06 21:24:27 CST,,0,LOG,00000,'checkpoint starting: wal',,,,,,,,,''
2021-10-10 20:55:18.058 CST,,,10801,,615da38b.2a31,42,,2021-10-06 21:24:27 CST,,0,LOG,00000,'checkpoint complete: wrote 5776 buffers (35.3%); 0 WAL file(s) added, 0 removed, 41 recycled; write=9.147 s, sync=0.565 s, total=10.013 s; sync files=7, longest=0.333 s, average=0.080 s; distance=691976 kB, estimate=691976 kB',,,,,,,,,''

Nick postgres=# select * from pg_control_checkpoint();
-[ RECORD 1 ]--------+-------------------------
checkpoint_lsn       | 18/39FD6C88
redo_lsn             | 18/39FD6C50
redo_wal_file        | 000000010000001800000039
timeline_id          | 1
prev_timeline_id     | 1
full_page_writes     | t
next_xid             | 0:1927987
next_oid             | 51061
next_multixact_id    | 1
next_multi_offset    | 0
oldest_xid           | 479
oldest_xid_dbid      | 1
oldest_active_xid    | 1927987
oldest_multi_xid     | 1
oldest_multi_dbid    | 1
oldest_commit_ts_xid | 0
newest_commit_ts_xid | 0
checkpoint_time      | 2021-10-10 22:15:33+08