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

This article mainly introduces "what is the influence of checkpoint_completion_target parameters in PostgreSQL on checkpoint scheduling". In daily operation, I believe many people have doubts about the impact of checkpoint_completion_target parameters on checkpoint scheduling in PostgreSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what is the impact of checkpoint_completion_target parameters on checkpoint scheduling in PostgreSQL"! Next, please follow the editor to study!

The scheduling of checkpoint process by PostgreSQL is related to the size and time of the log generated by the database.

Journal

The CalculateCheckpointSegments and IsCheckpointOnSchedule functions in the PostgreSQL source code show the influence of checkpoint_completion_target parameters on checkpoint scheduling. Before giving the calculation formula, several variables are defined:

Checkpoint Progress progress:

Progress= (num_processed / num_to_scan)

Where:

Number of dirty blocks processed by 1.num_processed

Total number of blocks to be processed by 2.num_to_scan

The ratio of logs generated after checkpoint startup to the maximum number of logs allowed:

Elapsed_xlogs = elapsed_xlogsize/wal_segment_size/checkpoint_segmentscheckpoint_segments = max_wal_size_mb/wal_segment_size/ (1.0 + checkpoint_completion_target)

Where:

1.elapsed_xlogsize refers to the amount of logs generated after checkpoint is started (in MB)

2.wal_segment_size defaults to 16MB

3.max_wal_size_mb is in the postgresql.conf configuration file

When the following conditions are met:

Progress*checkpoint_completion_target ≥ elapsed_xlogs

The checkpointer process will 100ms for a certain period of time to control the progress of dirty brushing (including fsync).

It can be seen that the critical points of the above conditions are:

Progress*checkpoint_completion_target = elapsed_xlogs

Now transform the formula at this critical point so that checkpoint_completion_target is placed on the right side of the equation:

(num_processed / num_to_scan) * checkpoint_completion_target = elapsed_xlogsize* (1. 0 + checkpoint_completion_target) / max_wal_size_mb-- > num_processed / num_to_scan = elapsed_xlogsize* (1.0/checkpoint_completion_target + 1) / max_wal_size_mb

Thus it can be seen that when other variables are constant, the value of the right formula is inversely proportional to checkpoint_completion_target. The larger the checkpoint_completion_target, the smaller the value, which means that the checkpointer process has more chances to sleep.

time

Let elapsed_seconds= (now.tv_sec-ckpt_start_time + now.tv_usec / 1000000.0) denote the elapsed time of the past

Checkpointer progress can be dormant if the following conditions are met:

Num_processed/num_to_scan) * checkpoint_completion_target ≥ elapsed_seconds/ checkpoint_timeout-- > num_processed/num_to_scan ≥ elapsed_seconds/ (checkpoint_completion_target*checkpoint_timeout)

Expression on the right under the condition that other variables are constant, the larger the checkpoint_completion_target, the smaller the value, and the greater the chance that checkpointer can sleep.

At this point, the study on "what is the impact of checkpoint_completion_target parameters in PostgreSQL on checkpoint scheduling" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.