Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use Postgresql's Checkpoint". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use Postgresql's Checkpoint".

What is Checkpoint?

Checkpoint, also known as checkpoint, generally checkpoint will write out all the dirty data before a certain point in time to disk in order to achieve data consistency and integrity. At present, all popular relational databases have checkpoint function, and its main purpose is to shorten crash recovery time. Take Oracle as an example, when data recovery is carried out, transaction roll forward will be executed with the nearest checkpoint as the reference point, and then roll back will be performed on transactions without commit. In the analysis of WAL mechanism, it is also mentioned that PostgreSQL will be based on the most recent checkpoint when recovering from a crash, and continue to apply the WAL log after that. The mechanism is roughly the same.

Checkpoint types of PG:

In the xlog.h file, the following code classifies checkpoint accordingly:

/ *

* OR-able request flag bits for checkpoints. The "cause" bits are used only

* for logging purposes. Note: the flags must be defined so that it's

* sensible to OR together request flags arising from different requestors.

, /

/ * These directly affect the behavior of CreateCheckPoint and subsidiaries * /

# define CHECKPOINT_IS_SHUTDOWN 0x0001 / * Checkpoint is for shutdown * /

# define CHECKPOINT_END_OF_RECOVERY 0x0002 / * Like shutdown checkpoint

* but issued at end of WAL

* recovery * /

# define CHECKPOINT_IMMEDIATE 0x0004 / * Do it without delays * /

# define CHECKPOINT_FORCE 0x0008 / * Force even if no activity * /

/ * These are important to RequestCheckpoint * /

# define CHECKPOINT_WAIT 0x0010 / * Wait for completion * /

/ * These indicate the cause of a checkpoint request * /

# define CHECKPOINT_CAUSE_XLOG 0x0020 / * XLOG consumption * /

# define CHECKPOINT_CAUSE_TIME 0x0040 / * Elapsed time * /

# define CHECKPOINT_FLUSH_ALL 0x0080 / * Flush all pages, including those

In other words, the following situations trigger the database operating system to do checkpointing operations:

Database shutdown

Database recovery completed

Super user (not available to other users) executes CHECKPOINT command

The number of XLOG logs has reached the threshold of trigger checkpoint.

Checkpoint periodically

When checkpoint is triggered, all dirty pages need to be refreshed. In order to create checkpoints periodically and reduce the crash recovery time, merging IMago PostgreSQL provides a helper process, checkpointer. It will periodically check whether the time and the above XLOG log volume threshold have been reached, while the cycle time and XLOG log volume threshold can be set by parameters. Next, we will introduce the parameters related to checkpoints.

Parameters related to checkpoints

Checkpoint_segments

The current maximum number of WAL log file segments, or when the log volume of the system reaches this large, triggers a checkpoint with a default value of 3 and the size of each segment is 16m, so the 64m log triggers a checkpoint. Increasing this number will extend the time it takes to recover after a system crash.

Checkpoint_timeout

The maximum time interval between automatic execution of checkpoint by the system. The system default value is 5 minutes.

Checkpoint_completion_target

The original text in the manual is "Specifies the target of checkpoint completion, asa fraction of total time between checkpoints". The target ratio of the time required to complete the checkpoint / the total time between the checkpoints is * 100%. It requires the system to complete the checkpoint in an appropriate time, neither too fast nor too slow. Too fast will lead to too dense IO, forming IO storms that affect the smooth operation of the system, and too slow may lead to persistent IO and reduce system performance.

Checkpoint_warning

The default value of the system is 30 seconds. If the interval between the actual occurrence of checkpoints is less than this parameter, a piece of relevant information will be written to the server log. You can set it to disable.

Checkpoint related calculations:

Checkpoint_completion_target plays a decisive role in computing.

If the parameters are configured as follows:

Shared_buffers = 128GB # 1 Compact 4 memory

Checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0-1.0

Checkpoint_timeout = 30min

Max_wal_size = 256GB # 2*shared_buffers

Min_wal_size = 64GB # shared_buffers * 1 Compact 2

1: extreme assumption that 128g is all dirty data.

(1281024) / (300600.9) = 80.90864198 MB/s

The average discrete IO of the disk is required to be at least above this, otherwise there is a bottleneck because the trigger time is more stressful.

Of course, the premise assumes that 128g is dirty, and if there is not much dirty data, the pressure will be less.

2: 256G WAL is generated in a hypothetical period

that

(256 / 1024) / (30,60) = 145.6355556 MB/s

WAL is sequential IO

3:bgWriter

Business write operations not only generate WAL, but also bgwriter writes out dirty blocks periodically, because a block may be modified many times, but writer may be less at this time.

(256 / 1024) / (30,60) = 145.6355556 MB/s

To sum up, the average throughput of database writes may be 80.9 MB/s+ 145.6 MB/s+ 145.6 MB/s=372MB/s.

The pressure at the moment of departure may exceed this value.

Thank you for your reading, the above is the content of "how to use Postgresql's Checkpoint", after the study of this article, I believe you have a deeper understanding of how to use Postgresql's Checkpoint, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.