The pg_stat_bgwriter
view
provides some interesting information about the background writer. This
view provides only one row. If you want to analyze these data, these
results has to be stored somewhere with a timestamp. The following shall
show that it allows an insight into the changes performed in shared
buffer cache and also hints for tuning several server parameters.
If a backend process (a client) requests data it is either found in a
block in shared buffer cache or the block has to be allocated (read from
disk). The latter is counted in buffers_alloc column. The usage count
of this block is increased. The backend process modifies data in this
requested page, so this page becomes dirty. With the commit the
transaction is written to WAL file. At some moment the background writer
will synchronize the dirty page with the disk and mark this page as
clean.
This latter asynchronous process can be triggered by the following three events:
The regular checkpoint is triggered either when the number of available
checkpoint_segments are exhausted or when the checkpoint_timeout is
reached. The first are counted in checkpoints_req column, the latter
in checkpoints_timed column. How many blocks during checkpoint are
written is counted in buffers_checkpoint column.
It may happen that a dirty page is requested by a backend process. In
this case the page is synched to disk before the page is returned to the
client. The number of those pages are incremented in buffers_backend
column.
It may happen that a new page has to be allocated and for this an
allocated page has to be cleared. PostgreSQL may clear pages with a low
usage count in advance. The process scans for dirty pages with a low
usage count so that they could be cleared if necessay. Buffers written
by this process increment the buffers_clean column.
Writing dirty buffers on backend request may cause a delay of the
response to the client request. Using the clean writer process may
mitigate this effect. Writing least recently used pages (which have a
low usage count) is enabled if bgwriter_lru_maxpages has a value
bigger than 0. The number of pages to be written is calculated via the
number of recently allocated buffers (you can see it in buffers_alloc)
multiplied by bgwriter_lru_multiplier (which defaults to 2).
So a higher allocation rate may lead to a higher amount of buffers clean operations. These cause a higher I/O between checkpoints caused by background writer. On the other hand the higher allocation rate results in a lower cache hit ratio. This may a have serious impact on client applications e.g. it could cause canceling of querys with a very short query timeout. The shared buffer cache should be increased.
On a healthy system there should more than 90% of all checkpoints
triggered by time. Based on a history of entries of pg_stat_bgwriter
view this can easily verified by comparing the calculated checkpoint
interval with current setting. Furthermore buffers written on checkpoint
should be higher than 80% of the this total number of blocks written.
The total number of blocks written is the sum of
So the ratio of cleaned buffers should be small while the ratio of buffers written by a regular checkpoint should be higher.
The statistics view pg_stat_bgwriter provides us the current state of
statistics. We can reset the statistic:
select pg_stat_reset_shared('bgwriter‘);
If you want to make analysis on background writer statistics you either have to reset the statistics or collect data into into a log table. From these values important indicators of database state can be calculated and can give hints which setting of the PostgreSQL cluster has to be adjusted.
Interesting further reading: