public inbox for [email protected]
help / color / mirror / Atom feedFrom: wenhui qiu <[email protected]>
To: Andres Freund <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Tony Wayne <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: bgwrite process is too lazy
Date: Mon, 7 Oct 2024 14:07:40 +0800
Message-ID: <CAGjGUAJMcdkRqovWM+x43e5omU48R355WB0Go=iiPrzCW_xtng@mail.gmail.com> (raw)
In-Reply-To: <cixso3buqeddrsqh3cf4svus3dakho2jwvohstwz64aqttg647@pqd4kwtdcso7>
References: <CAGjGUALHnmQFXmBYaFCupXQu7nx7HZ79xN29+HoE5s-USqprUg@mail.gmail.com>
<[email protected]>
<CAAW5B8yE4DD+5fTE9jQff7k8qyMJ9MNGMJTyNQt_PcRdfCXq7Q@mail.gmail.com>
<[email protected]>
<guvros46lag5mbdt36y2rvcccfjttctby2vuiojngu6gp234ny@766fvsquuv53>
<CAGjGUA+7sTOCROOuYq=tmS1=himmEPGKauXfhcrU6yoL0ZobHQ@mail.gmail.com>
<cixso3buqeddrsqh3cf4svus3dakho2jwvohstwz64aqttg647@pqd4kwtdcso7>
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.
view thread (5+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: bgwrite process is too lazy
In-Reply-To: <CAGjGUAJMcdkRqovWM+x43e5omU48R355WB0Go=iiPrzCW_xtng@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox