In this article, where we will examine the features of the pg_stat_bgwriter and pg_stat_checkpointer views and the behaviors of the processes, let’s start by talking about the history of the subject. The “background write” and “checkpoint” processes, which were previously the tasks of the “Background Writer” (bgwriter) process, were divided into two separate processes in PostgreSQL 9.2 and updated so that bgwriter would do the “background write” process and the checkpoint process would do the checkpoint process. (There was no checkpointer process before version 9.2). One of the most important reasons for this change, as you can see in this link, was that checkpoint had to stop the “background write” operation in order to perform the last fsync, and factors like these had a negative performance effect. The change we mentioned was made in version 9.2 and the checkpointer process was activated, but this change made in background processes was not reflected in the statistics views and the information belonging to both processes continued to be kept in the pg_stat_bgwriter view. In PostgreSQL version 17, these statistics are now kept in different views.
In the meantime, if we recall the “background write” and “checkpoint” operations, first of all, let’s state that both operations write the “dirty” data blocks in the shared buffers, that is, committed but not updated in the data file on the disk, to the disk. The checkpoint operation is triggered every “checkpoint_timeout” seconds, or when the size of the WAL segments exceeds “max_wal_size”. On the other hand, the “background write” operation is triggered when the number of clean/usable blocks on the shared buffers is deemed insufficient. As a result of this trigger, some of the dirty blocks in the buffer area are written to the disk and marked clean, freeing up space in the shared buffer.
You can also read the details about checkpoint and Background Writer concepts in the PostgreSQL documentation.
https://www.postgresql.org/docs/current/wal-configuration.html
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER
With PostgreSQL version 17, the columns of the views are structured as follows:
postgres=# \d pg_stat_bgwriter
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
buffers_clean | bigint | | |
maxwritten_clean | bigint | | |
buffers_alloc | bigint | | |
stats_reset | timestamp with time zone | | |
postgres=# \d pg_stat_checkpointer
View "pg_catalog.pg_stat_checkpointer"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+---------
num_timed | bigint | | |
num_requested | bigint | | |
restartpoints_timed | bigint | | |
restartpoints_req | bigint | | |
restartpoints_done | bigint | | |
write_time | double precision | | |
sync_time | double precision | | |
buffers_written | bigint | | |
stats_reset | timestamp with time zone | | |Now, in the first phase of our tests, we create a new PostgreSQL cluster and query these two views in our database:
postgres=# select * from pg_stat_bgwriter;
buffers_clean | maxwritten_clean | buffers_alloc | stats_reset
---------------+------------------+---------------+-------------------------------
0 | 0 | 175 | 2024-11-25 13:23:41.271027+00
(1 row)
postgres=# select * from pg_stat_checkpointer;
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written | stats_reset
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-11-25 13:23:41.271027+00
(1 row)In the pg_stat_bgwriter output, we see that only the “buffers_alloc” value is different from zero. This value is a counter value that increases with each “buffer allocation” operation on a cluster basis, and the background writer process uses this value in its own algorithm to determine when to run/kick in. Of the other columns, “buffers_clean” shows the number of buffers cleaned by bgwriter, and “maxwritten_clean” shows the number of times the bgwriter process stopped after cleaning more buffers than its upper limit during a cleaning operation. We expect these values to be 0 in our new database. At the same time, we see that all the columns in the pg_stat_checkpointer output are also 0.
Now let’s create a table, insert records into it, and examine the views again:
postgres=# create table io_test (c1 integer,c2 text);
postgres=# INSERT INTO io_test( c1, c2)
SELECT floor(random() * 999 + 1)::int,
left(md5(random()::text),3)
FROM generate_series(1,3000000);
INSERT 0 3000000
postgres=# select * from pg_stat_bgwriter;
buffers_clean | maxwritten_clean | buffers_alloc | stats_reset
---------------+------------------+---------------+-------------------------------
0 | 0 | 13583 | 2024-11-25 13:23:41.271027+00
(1 row)
postgres=# select * from pg_stat_checkpointer;
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written | stats_reset
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2024-11-25 13:23:41.271027+00
(1 row)With the operation we performed, we inserted 3 million records into the table and queried the views. However, since neither checkpoint nor bgwriter has been triggered yet, we can say that the entire insert operation was performed in memory and no data has been written to disk yet. We only see that a “buffer allocation” was made in shared buffers with the insert operation and the buffers_alloc value increased.
Now let’s trigger a manual checkpoint and examine the pg_stat_checkpointer view again.
postgres=# checkpoint;
CHECKPOINT
postgres=# select * from pg_stat_checkpointer;
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written | stats_reset
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------
0 | 1 | 0 | 0 | 0 | 397 | 84 | 13315 | 2024-11-25 13:23:41.271027+00
(1 row)
postgres=# select * from pg_stat_bgwriter;
buffers_clean | maxwritten_clean | buffers_alloc | stats_reset
---------------+------------------+---------------+-------------------------------
0 | 0 | 13839 | 2024-11-25 13:23:41.271027+00
(1 row)We can see that 13315 dirty buffer blocks were written to disk (buffers_written) with the checkpoint, and the process took 481 ms. (write_time+sync_time). The same information will be written to the log file when the “log_checkpoints” parameter is on. We can also see from the view output that only one “requested checkpoint”, that is, a checkpoint was received upon request before the checkpoint_timeout period expired (num_requested=1), and no automatic checkpoint was received after the checkpoint_timeout period expired (num_timed=0).
Now, to create free space pressure in the shared buffer, let’s read all the data in the table and write it back to the table, and examine the views after a few seconds.
postgres=# insert into io_test select * from io_test ;
INSERT 0 3000000
postgres=# select * from pg_stat_bgwriter;
buffers_clean | maxwritten_clean | buffers_alloc | stats_reset
---------------+------------------+---------------+-------------------------------
4466 | 20 | 27558 | 2024-11-25 13:23:41.271027+00
(1 row)
postgres=# select * from pg_stat_checkpointer;
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written | stats_reset
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------
1 | 1 | 0 | 0 | 0 | 4737 | 103 | 13358 | 2024-11-25 13:23:41.271027+00
(1 row)As a result of the process, we can see that the background writer process has been activated and has written 4466 “dirty pages” from the shared buffers area to the disk, freeing up space. 20 of these write operations were cut off because they wrote more pages than the “bgwriter_lru_maxpages” value (maxwritten_clean=20). This value was 100 by default in our test environment. If the maxwritten_clean column is observed to be constantly increasing in the pg_stat_bgwriter view output, increasing the “bgwriter_lru_maxpages” parameter may be considered. In the pg_stat_checkpointer output, we see that 1 automatic checkpoint was taken during the test, but very few pages were written with this checkpoint. We can say that this checkpoint was completed before our insertion process.
Now, after waiting a little more than 5 minutes (checkpoint_timeout) for another automatic checkpoint, we query the views again.
postgres=# select * from pg_stat_bgwriter;
buffers_clean | maxwritten_clean | buffers_alloc | stats_reset
---------------+------------------+---------------+-------------------------------
4837 | 26 | 27669 | 2024-11-11 09:50:11.810176+00
(1 row)
postgres=# select * from pg_stat_checkpointer;
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written | stats_reset
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------
2 | 1 | 0 | 0 | 0 | 6184 | 121 | 19140 | 2024-11-25 13:23:41.271027+00
(1 row)We can see from the pg_stat_checkpointer view output that some of the dirty pages resulting from our last insert/select operation were cleaned by bgwriter, and the rest were cleaned by automatic checkpoint. Another view where we can see the statistics of the checkpointer and background writer processes is pg_stat_io. Finally, below we see the pg_stat_io output after our tests.
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
--------------------+----------+----------+-------+--------+------------+---------+----------+---------+-----------+--------+--------
client backend | relation | bulkread | 7368 | 0 | 0 | | 8192 | 5907 | 32 | 7336 |
client backend | relation | normal | 325 | 3 | 0 | 26560 | 8192 | 6056898 | 10764 | | 0
autovacuum worker | relation | normal | 246 | 0 | 0 | 11 | 8192 | 35912 | 128 | | 0
autovacuum worker | relation | vacuum | 10994 | 320 | 0 | 0 | 8192 | 55639 | 256 | 10605 |
standalone backend | relation | normal | 537 | 1010 | 1010 | 661 | 8192 | 86875 | 0 | | 0
standalone backend | relation | vacuum | 9 | 0 | 0 | 0 | 8192 | 906 | 0 | 0 |
background writer | relation | normal | | 5034 | 4992 | | 8192 | | | | 0
checkpointer | relation | normal | | 19431 | 19406 | | 8192 | | | | 40
(8 rows)Checkpoint operation is a very important factor in terms of database performance and it is very important to tune checkpoint and background write operations correctly in busy databases. If you have any questions about this, you can contact us.
Bir yanıt yazın