Thread: Re: bgwrite process is too lazy

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!

Yours,
Laurenz Albe

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.


regards

-- 
Tomas Vondra

Hi,

On 2024-10-02 18:36:44 +0200, Tomas Vondra wrote:
> 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).

It's implied, but to make it more explicit: One big efficiency advantage of
writes by checkpointer is that they are sorted and can often be combined into
larger writes. That's often a lot more efficient: For network attached storage
it saves you iops, for local SSDs it's much friendlier to wear leveling.


> 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.

Another aspect is that checkpointer's writes are much easier to pace over time
than e.g. bgwriters, because bgwriter is triggered by a fairly short term
signal.  Eventually we'll want to combine writes by bgwriter too, but that's
always going to be more expensive than doing it in a large batched fashion
like checkpointer does.

I think we could improve checkpointer's pacing further, fwiw, by taking into
account that the WAL volume at the start of a spread-out checkpoint typically
is bigger than at the end.

Greetings,

Andres Freund

Hi,

On 2024-10-04 09:31:45 +0800, wenhui qiu wrote:
> > It's implied, but to make it more explicit: One big efficiency advantage
> of
> > writes by checkpointer is that they are sorted and can often be combined
> into
> > larger writes. That's often a lot more efficient: For network attached
> storage
> > it saves you iops, for local SSDs it's much friendlier to wear leveling.
>
> thank you for explanation, I think bgwrite also can merge io ,It  writes
> asynchronously to the file system cache, scheduling by os, .

Because bgwriter writes are just ordered by their buffer id (further made less
sequential due to only writing out not-recently-used buffers), they are often
effectively random. The OS can't do much about that.



> > Another aspect is that checkpointer's writes are much easier to pace over
> time
> > than e.g. bgwriters, because bgwriter is triggered by a fairly short term
> > signal.  Eventually we'll want to combine writes by bgwriter too, but
> that's
> > always going to be more expensive than doing it in a large batched fashion
> > like checkpointer does.
>
> > I think we could improve checkpointer's pacing further, fwiw, by taking
> into
> > account that the WAL volume at the start of a spread-out checkpoint
> typically
> > is bigger than at the end.
>
> I'm also very keen to improve checkpoints , Whenever I do stress test,
> bgwrite does not write dirty pages when the data set is smaller than
> shard_buffer size,

It *SHOULD NOT* do anything in that situation. There's absolutely nothing to
be gained by bgwriter writing in that case.


> Before the checkpoint, the pressure measurement tps was stable and the
> highest during the entire pressure measurement phase，Other databases
> refresh dirty pages at a certain frequency, at intervals, and at dirty page
> water levels，They have a much smaller impact on performance when
> checkpoints occur

I doubt that slowdown is caused by bgwriter not being active enough. I suspect
what you're seeing is one or more of:

a) The overhead of doing full page writes (due to increasing the WAL
   volume). You could verify whether that's the case by turning
   full_page_writes off (but note that that's not generally safe!) or see if
   the overhead shrinks if you set wal_compression=zstd or wal_compression=lz4
   (don't use pglz, it's too slow).

b) The overhead of renaming WAL segments during recycling. You could see if
   this is related by specifying --wal-segsize 512 or such during initdb.

Greetings,

Andres