Thank you for explaining,If do not change this static parameter,Can refer to the parameter autovacuum_vacuum_cost_delay and lower the minimum value of the bgwrite_delay parameter to 2ms? Let bgwrite write more dirty pages to reduce the impact on performance when checkpoint occurs?After all, extending the checkpoint time and crash recovery time needs to find a balance, and cannot be increased indefinitely (checkpoint_timeout ,max_wal_size)
On 10/2/24 17:02, Tony Wayne wrote: > > > On Wed, Oct 2, 2024 at 8:14 PM Laurenz Albe <laurenz.albe@cybertec.at > <mailto:laurenz.albe@cybertec.at>> wrote: > > On Wed, 2024-10-02 at 16:48 +0800, wenhui qiu wrote: > > Whenever I check the checkpoint information in a log, most dirty > pages are written by the checkpoint process > > That's exactly how it should be! > > is it because if bgwriter frequently flushes, the disk io will be more?🤔
Yes, pretty much. But it's also about where the writes happen.
Checkpoint flushes dirty buffers only once per checkpoint interval, which is the lowest amount of write I/O that needs to happen.
Every other way of flushing buffers is less efficient, and is mostly a sign of memory pressure (shared buffers not large enough for active part of the data).
But it's also happens about where the writes happen. Checkpoint does that in the background, not as part of regular query execution. What we don't want is for the user backends to flush buffers, because it's expensive and can cause result in much higher latency.
The bgwriter is somewhere in between - it's happens in the background, but may not be as efficient as doing it in the checkpointer. Still much better than having to do this in regular backends.
