Identifying checkpoint overhead

In this recipe, we will be discussing the checkpoint process, which may cause more I/O usage while flushing the dirty buffers into the disk.

Getting ready

In PostgreSQL, the checkpoint is an activity that flushes all the dirty buffers from the shared buffers into the persistent storage, which is followed by updating the consistent transaction log sequence number in the data, WAL files. That means this checkpoint guarantees that all the information up to this checkpoint number is stored in a persistent disk storage. PostgreSQL internally issues the checkpoint as per the checkpoint_timeout and max_wal_size settings, which keep the data in a consistent storage as configured. In case the configured shared_buffers are huge in size, then the probability of holding dirty buffers will also be greater in size, which leads to more I/O usage for the next checkpoint process. It is also not recommended to tune the checkpoint_timeout and max_wal_size settings to be aggressive, as it will lead to frequent I/O load spikes on the server and may cause system instability.

How to do it...

To identify the overhead of the checkpoint process, we need to monitor the disk usage by using some native tools such as iostat, iotop, and so on, while the checkpoint process is in progress. To identify this checkpoint process status, we have to either query the pg_stat_activity view as follows, or otherwise we have to consider enabling the log_checkpoints parameter, which will log an implicit checkpoint begin status as checkpoint starting: xlog and for the explicit requests as checkpoint starting: immediate force wait xlog:

SELECT * FROM pg_stat_activity WHERE wait_event = 'CheckpointLock'; 

How it works...

Once we find that the I/O usage is high enough when the checkpoint process is running, then we have to consider tuning the checkpoint_completion_target parameter, which will limit the transfer rate between the memory and disk. This parameter accepts real numbers between 0 and 1, which will smooth the I/O transfer rate if the parameter value gets close to 1. PostgreSQL version 9.6 introduced a new parameter called checkpoint_flush_after, which will guarantee flushing the buffers from the kernel page cache at certain operating systems.

Note

For more information about the checkpoint process and its behavior, refer to the following URL: https://www.postgresql.org/docs/9.6/static/wal-configuration.html.