Re: Use WALReadFromBuffers in more places - Mailing list pgsql-hackers

Hi,

On Mon, Sep 22, 2025 at 8:26 AM Jeff Davis <pgsql@j-davis.com> wrote:
>
> On Sat, 2025-09-13 at 22:04 -0700, Bharath Rupireddy wrote:
> > Thanks for looking at this. Yes, the WAL writers can zero out flushed
> > buffers before WALReadFromBuffers gets to them. However,
> > WALReadFromBuffers was intentionally designed as an opportunistic
> > optimization - it's a "try this first, quickly" approach before
> > falling back to reading from WAL files.
>
> IIRC, one motivation (perhaps the primary motivation?) was to make it
> possible to read buffers before they are flushed. It was always
> possible to read already-flushed buffers.
>
> The benefit of reading unflushed buffers is that we can replicate the
> WAL sooner (though it can't be replayed until the primary flushes it).
> Is that right?

Thanks for taking a look at it. Yes, that's correct.

In general, I would like to reiterate the benefit of reading WAL from
WAL buffers not just for logical walsenders but physical walsenders as
well:

1/ Helps avoid reading from WAL files (which can either be OS page
cache reads or disk reads) whenever possible.

2/ Helps make it more realistic to use direct IO for WAL.
https://www.postgresql.org/message-id/20230114203403.4zpg72fw2qb34awf%40awork3.anarazel.de

3/ Helps implement the ability to stream out WAL before it has been
locally written out and flushed to improve synchronous replication
performance. I have plans to work on this feature for PG20.
https://www.postgresql.org/message-id/20230125211540.zylu74dj2uuh3k7w%40awork3.anarazel.de

However, particularly for this patch, I would like to enable the
logical walsenders also to read WAL from WAL buffers when possible.

I did a quick experiment with [1] and the following are the results.

With WAL direct IO on, the patch reduces the walsender's WAL reads
from 2.3 GB to 3 MB per run, removing 17 MB/s of physical disk reads
and improving publisher throughput by about 10% (6,701 to 7,361 TPS).
The throughput gain comes from eliminating the WAL read IO on disk, so
WAL writes no longer compete with WAL reads for disk IO. With WAL
direct IO off, the same reads are eliminated at the syscall level with
no throughput change, so it never regresses.

#   build     WAL direct IO   TPS     walsender read MB   WAL-disk
reads   WAL-disk writes   replication lag
1   HEAD      on              6,701   2,332               17.1 MB/s
    16.3 MB/s         23 KB
2   PATCHED   on              7,361   3.0                 0
    17.4 MB/s         14 KB
3   HEAD      off             7,672   2,715               0
    17.8 MB/s         13 KB
4   PATCHED   off             7,694   2.9                 0
    17.8 MB/s         14 KB

Please find the attached v5 patch. Thanks.

[1]
Test setup: two Amazon EC2 r7i.4xlarge instances (16 vCPU, 128 GB RAM)
in the same AZ, one publisher and one subscriber, pg_wal on a
dedicated gp3 disk. Publisher runs an insert-only pgbench workload (16
clients) into a two-column table (bigint, text), each insert writing
400 bytes, no indexes. Subscriber tails closely (lag in KB) so the WAL
the walsender reads is still in the 2 GB wal_buffers. wal_buffers=2GB,
debug_io_direct='wal'.

Metrics: TPS is publisher pgbench insert throughput. Walsender read MB
is read_bytes from pg_stat_io for the walsender (bytes read through
WALRead()). WAL-disk reads and WAL-disk writes are peak throughput
from iostat on the pg_wal disk. Replication lag is
pg_stat_replication, sampled every 5 seconds, taken as the max over
the run.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com