Re: bgwrite process is too lazy

On 10/2/24 17:02, Tony Wayne wrote:
>
>
> On Wed, Oct 2, 2024 at 8:14 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at
> <mailto:laurenz(dot)albe(at)cybertec(dot)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.

regards

--
Tomas Vondra