Re: bgwrite process is too lazy

Hi Andres Freund
Thank you for explanation

> 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.
I am aware of these optimizations, and these optimizations only mitigate
the impact, I didn't turn on wal log compression on purpose during stress
test ，

shared_buffers = '32GB'
bgwriter_delay = '10ms'
bgwriter_lru_maxpages = '8192'
bgwriter_lru_multiplier = '10.0'
wal_buffers = '64MB'
checkpoint_completion_target = '0.999'
checkpoint_timeout = '600'
max_wal_size = '32GB'
min_wal_size = '16GB'

I think in business scenarios where there are many reads and few writes, it
is indeed desirable to keep as many dirty pages in memory as
possible,However, in scenarios such as push systems and task scheduling
systems, which also have a lot of reads and writes, the impact of
checkpoints will be more obvious,Adaptive bgwrite or bgwrite triggered when
a dirty page reaches a certain watermark eliminates the impact of
checkpoints on performance jitter.From what I understand, quite a few
commercial databases residing in postgresql have added the adaptive refresh
dirty page feature, and from their internal reports, the whole stress
testing process was very smooth! Since it's a trade secret, I don't know
how they implemented this feature.