Re: bgwrite process is too lazy

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