enhancing pg_basebackup speeds up to ~23Gbps (small fixes + io_uring/Direct I/O)

Hi,

While investigating pg_basebackup performance, I found that a significant
amount of CPU is being wasted on both the server and client sides, and that
there are substantial opportunities to improve throughput without
fundamentally changing the design (so without abandoning the simple
single-threaded, single-connection design). Keeping that design intact also
lets us answer some of the questions raised in the old parallel-backup thread
[1]. The attached patches implement a couple of changes to make benchmarking
easier (0001-0003), some small optimizations that make a significant
difference in my production-like testing (0004-0008), and then implement
io_uring (Direct I/O + Async I/O) support for pg_basebackup (0009-0010).

First, a quick performance demonstration, from the AWS benchmarks described in
full later (2x c6in.8xlarge, specs in [4]), when taking a backup without
checksum verification/SSL, while still using single socket/fd:

master + 0001-0003, writing to disk: ~1610MB/s.
master + all patches, with MPTCP: 2750-2975MB/s.

That's like ~1.85x speedup on real cloud hardware. What I found is that the
gains come from several independent sources: reducing syscall numbers,
avoiding redundant memory copies in libpq, and using Direct I/O with
asynchronous submission on the client side. Above such rates, I believe we
genuinely need independent connections and a parallel-backup design, but
everything up to that point is achievable with relatively low-invasive
changes.

OK, now let's go through the patches:

0001 and 0002 extend the existing server-side --target blackhole concept into
separate server and client (pg_basebackup) blackholes, so that each stage of
the data transfer pipeline can be measured reliably. Together with 0003, this
gives DBAs a simple way to locate a bottleneck using nothing but the tool
itself:

-t server-blackhole measures the server's raw read speed
-t client-blackhole additionally sends the data over the network and but
discard writes; and a plain(classic) -Fp -D /path run exposes all three
potential bottlenecks at once.

0003 adds a simple timing message with the average transfer rate. The idea is
that if we're going to discuss numbers on this thread, it's better if the tool
measures them the same way for everyone, rather than each of us relying on
atop or other tooling.

0004 increases SINK_BUFFER_LENGTH from its current 32 kB. Profiling the
server side shows a huge number of small pread() calls coming from
basebackup_read_file(), which is surprising given that elsewhere in the tree
(backend/storage/buffer/README) we already mention that a 256 kB ring for seq.
scans scans is used because it fits comfortably in L2 cache. On my laptop,
with no SSL, no checksum generation or verification, a hot filesystem cache,
and a client blackhole to eliminate client I/O, a 10 GB backup over loopback
runs at:
3.1 GB/s with the 32 kB buffer,
5.1 GB/s at 128 kB,
5.5 GB/s at 256 kB,
6.0 GB/s at 1 MB
(those are average of five runs). The win comes from letting pread()
swallow much larger chunks of each segment in one go: per-core L2 caches are
1-2 MB even on laptops these days, syscalls have become more expensive since
the Spectre/Meltdown mitigations, and the kernel's default readahead is
already in the 128-512 kB range, so there's little reason to trickle the data
through 32 kB at a time. I also experimented with raising PQ_SEND_BUFFER_SIZE
from 8 kB to 128 kB, but saw no improvement and sometimes a regression --
apparently it defeats libpq's direct-send optimization for large messages.

0005 issues posix_fadvise(POSIX_FADV_SEQUENTIAL) on the assumption that
segments are usually cold at backup time. Even with 0004 applied, cold-cache
throughput on my laptop's NVMe goes from 1.5 GB/s to 2 GB/s with this patch,
because the synchronous pread() calls see lower latencies (visible with eBPF
funclatency). One question I tried to answer was whether it makes sense to
issue one fadvise call per file or many smaller ones (in the spirit of
maintenance_io_concurrency), but on Linux FADV_SEQUENTIAL does exactly one
thing -- it just widens the maximum readahead horizon -- so repeated calls buy
nothing; it's simply handled with read-as-you-go by the kernel's own readahead
heuristics.

0006 avoids buffering client-side writes with glibc stdio (FILE *). I
initially thought this is going to be as easy as a setvbuf(3) call, since the
extractor path was issuing a mismatched 4 kB + 258 kB write pair per 256 kB
received. A 1MB setvbuf buffer produced the tidy syscall pattern I wanted, but
it actually got slower when measured carefully on tmpfs: glibc's fwrite()
internally stats the file for its block size and introduces an extra memcpy()
for large writes. Direct I/O would bypass this entirely, but that felt
premature at this stage, so the patch takes a more conservative approach.
Throughput is roughly unchanged (3.1 vs 3.2 GB/s to a ramdisk), but the
syscall pattern becomes a clean one-to-one recvfrom()/write() pairing, which
matters for the later patches.

0007 preallocates output files via posix_fallocate(). This gave about 7%
essentially for free, though you need to already be writing fast (probably
more than of 1 GB/s) before it becomes visible.

0008 eliminates a redundant memory copy in the receive path. After the fixes
above, perf showed PQgetCopyData()'s internal memcpy() consuming 60-70% of
pg_basebackup's single CPU: libpq allocates a buffer and copies the incoming
data into it, only for the callback (ReceiveArchiveStreamChunk and friends) to
immediately consume that same buffer at an offset of one byte, skipping the
protocol message byte. Since pg_basebackup is the only consumer here, the copy
can simply be avoided. On the receive path the kernel already copies from the
NIC via DMA to a kernel buffer and again into userspace, so above roughly 10
Gbps this extra PostgreSQL-side copy becomes very visible. With the copy
removed, a CPU-saturated pg_basebackup over localhost goes from 2.4 GB/s to
~5.5 GB/s when not writing to disk; when writing even to a ramdisk, the
bottleneck shifts to fundamental kernel-side vfs_write() costs such as cgroups
v2 memory accounting.

0009: Even with all of the above, there was still idle sequential write
bandwidth left on the table, and buffered writes couldn't reach it (without
some parallelism). Direct I/O alone (O_DIRECT) carried an unavoidable latency
hit and wouldn't be usable here alone either, so it had be combined with
asynchronous submission, which means liburing (client-side only). So this
patch adds io_uring (Direct I/O) for larger files on the client side. The
patch adds PG_BASEBACKUP_NODIO too for experimenting with this (set/unset it
to see the difference).

0010 fixes a serious regression that 0009 introduced for -Ft (tar) output.
Because the final archive length isn't known in advance -- unlike -Fp (plain),
where each file's target size is known -- every Direct I/O write via io_uring
also extended the file, triggering synchronous space allocation and
serializing on ext4's per-inode i_rwsem, which defeated the entire point of a
deep async queue. The fix is proper posix_fallocate() for the tar path, with a
fallback to buffered writes when preallocation isn't possible. It's a separate
commit because it needed its own explanation: without it, tar output ran at
410MB/s; with it, ~2400MB/s.

Now, the real AWS benchmarks. These are averages of 3-5 runs with the
lowest outliers discarded, on 2x c6in.8xlarge with ENA Express (full specs in
[4]), using --no-verify-checksums and --manifest-checksums=NONE, and no SSL
unless stated otherwise:

stageI: hw baseline
===================
a1. master + 000[123] -t server-blackhole # 11573MB/s (disk I/O possible
on serverside from hot pagecache)
a2. master + 000[123] -t client-blackhole # 1820MB/s (single-thread TCP limit,
no writing client side)
a3. master + 000[123] -D /db/backup # ~1610MB/s (when writing, proper backup)

stageII: basic optimizations
============================

+0004 SINK_BUFFER_LENGTH increase:
b. master + 000[1234] -t client-blackhole # 2158MB/s

cold-cache scenarios (how efficient we are when data is not in the VFS cache
server-side; echo 3 > drop_caches):
c1. master + 000[1234] -t client-blackhole # 1476MB/s
+ 0005 posix_fadvise whole file:
c2. master + 000[12345] -t client-blackhole # 1610MB/s

+0006, hot-cache scenarios again, real I/O by pg_basebackup, 100% CPU
throughout for the tests below:
d1. master + 000[12345] -D /db/backup # ~1639MB/s
d2. master + 000[123456] -D /db/backup # around the same, but we now issue
matched recvfrom()+write() pairs

+0007 preallocate file
e. master + 000[1234567] -D /db/backup # ~1687MB/s

+0008 avoid the second memcpy in libpq
f. master + 000[12345678] -D /db/backup # 1818MB/s (we cannot get more;
this saturates the AWS's ENA Express link on a single connection/
technically maxes out AWS network fabric/SDN)

crosscheck against baseline, just in case:
g. master + 000[123] -D /db/backup # 1613MB/s

stageIII: MPTCP
===============
I had to fall back to MPTCP [2] because the maximum single-stream TCP
bandwidth even with ENA Express fluctuated far too much to isolate. That patch
is going to posted independently in its own thread [1], but attached here too.

h0. iperf3 max with MPTCP, single connection: 43Gbit/s (but fluctuating down
to just 27Gbit/s sometimes; just 2-3 subflows)
h1. master + 000[123] + PGMPTCP=0 -t client-blackhole # 2079MB/s
h2. master + 000[123] + PGMPTCP=1 -t client-blackhole # 2770MB/s
h3. master + 000[123] + PGMPTCP=0 -D /db/backup # 1870MB/s, occasionally
slightly lower (ENA Express)
h4. master + 000[12345678] + PGMPTCP=1 -t client-blackhole # 3840MB/s, a
real indication that the earlier ceiling was just AWS network --
though note we are not actually writing here

stageIV: DIO+AIO
================
Because pg_basebackup was pinned at 100% CPU while the storage could still
take more, we need io_uring with DIO+AIO (+0009):

i1. master + 000[123456789] + PGMPTCP=1 -D /db/backup # 2750-2975MB/s; we
max out MPTCP bandwidth with the core saturated by writing
i2. master + 000[123456789] + PGMPTCP=0 -D /db/backup # 1695MB/s, a sample
of the fluctuations (down from 1870MB/s)
i3. master + 000[123456789] + PGMPTCP=1 without --no-verify-checksums
# still 2772MB/s (server-side checksum calculation is fast enough!)
i4. master + 000[123456789] + PGMPTCP=1 without --no-verify-checksums and
with default CRC32C for manifest checksumming # still 2673MB/s

So "i4." divided by "a3." gives 2975MB/ 1610 = ~1.85x , with some of the 0009
benefits greatly take from ideas in 0007 and 0008.

There were several surprises in the above, at least to me -- I hadn't
anticipated hardware-assisted checksums being that fast.

Tar (-Ft) output initially suffered badly from the synchronous space
allocation (because that was not done), as per details described under 0010.
With proper posix_fallocate() it recovers performance. Without checksums and
without SSL:
j1. master + 000[123456789] + PGMPTCP=1 -D /db/backup.tar -Ft # 410MB/s,
regression due to the lack of effective posix_fallocate()
j2. master + 000[123456789]+0010 + PGMPTCP=1 -D /db/backup.tar -Ft
# ~2400MB/s

TLS
===
Some measurements of TLS itself in this setup (TLSv1.3, cipher
TLS_AES_256_GCM_SHA384):
k1. ssl=on + master + 000[123] + PGMPTCP=1 -D /db/backup
#baseline of ~1247MB/s
k2. ssl=on + master + 000[123456789] + PGMPTCP=1 -D /db/backup
#1668-1791MB/s

On SSL specifically: the cipher alone can push close to 8-10 GB/s (64-80
Gbps) on a single CPU with openssl -bench, and reducing the number of
encryption rounds doesn't appear to yield any real benefit. But since SSL/TLS
is additional work performed on the __same__ thread, it imposes a clear
CPU overhead here: 1791 / 2975 = 0.60x.

A few conclusions:

- pg_basebackup today tops out at roughly 10-15 Gbps, because in the end we
are constrained by what a single core can do.

- pg_basebackup is very vulnerable to single-TCP-flow performance on real
networks. These tests were intentionally run on real hardware and networks
within a single zone -- the kind people actually use -- rather than on an
isolated network. See table [3] for apparent real-world limitations;
single-flow TCP is IMHO the top constraint today, often well below 10-25
Gbps, and without MPTCP there is no simple way to get past it...

- Some tuning is possible on our side without invasive protocol changes.

- MPTCP is easy to set up and enabled almost everywhere today, but it has
its own ceiling: apparently ~40 Gbps in iperf3 and realistically ~25 Gbps
when we are also writing single-threaded. Potentially in far future we
could put recvfrom() (where MPTCP reassembly happens) on one thread and
issue SQs/io_uring_submit() from a second thread, but that would require
Thomas's pg_thr_*() APIs to be in place.

- I researched SSL/kTLS/sendfile/zcrx a bit as well, but this email is
already too long, so that's not covered here.

Based on the measurements and conclusions above, I think we can improve
pg_basebackup's single-threaded performance for now with these
relatively low-invasive patches.

Thanks for reading that far.

-J.

[1] - https://www.postgresql.org/message-id/20200420201922.55ab7ovg6535suyz%40alap3.anarazel.de
[2] - https://www.postgresql.org/message-id/flat/CAKZiRmy6j9PBzDHZwdgwHavwKDzv5GWtRSWOTj6-jv6SCOZ%3DYA%40mail.gmail.com
[3] - a small audit of the single-TCP-connection speeds one can expect
depending on where one is running:

Hardware type | Max aggr bw | 1x TCP LAN | 1x TCP MAN| 1x TCP WAN
Physical server | <= 200 GbE | 1 CPU | 1-10Gbps | 1-10Gbps?
VM (Xen, VMware, etc) | <= 100 GbE? | 1 CPU | 1-10Gbps | 1-10Gbps?
lowend AWS VM | 5-40 Gbps | 5 Gbps | 5 Gbps | <= 5 Gbps
lowend AWS VM Cluster Pl. | <= 200 Gbps | 10 Gbps | 5 Gbps | <= 5 Gbps
highend AWS VM | <= 200 Gbps | 5 Gbps | 5 Gbps | <= 5 Gbps
highend AWS VM+ENA Express| <= 200 Gbps | 25 Gbps | 25 Gbps | <= 5 Gbps
lowend Azure VM | 3(!)-40 Gbps| 3(!) Gbps | 1.5-3 Gbps| 1.5-3 Gbps
highend Azure VM+acc. net.| <= 200 Gbps | 10-12.5Gbps| 10 Gbps | 1.5-3 Gbps

[4] - HW: 2x c6in.8xlarge EC2 (each with 32 vCPUs, 64 GB RAM), scale
4000 / 50 GB cluster size, 3x NVMe of 50 GB / 8k IOPS each in LVM/RAID0
(lvcreate -i 3), ext4, tuned TCP stack (BBR), ENA Express enabled. In theory
and sometimes in practice this gives a real ~23 Gbps, sometimes more like
15 Gbps -- highly nondeterministic and dependent on zone, region, and time of
day. Aggregate ~50 Gbps bandwidth was always available without
problems; only the single-flow TCP performance fluctuated, typically between
15 and 19 Gbps during these measurements, and I rather lost hope of it being
deterministic. It seems to be a function of the env rather than the
configuration, which was identical throughout the excercies. I suspect that on
raw hardware with >= 40 GbE interfaces higher speeds are possible, but the
intent here was to simulate what real customers actually see.

sysctls:
net.core.rmem_max = 33554432
net.core.wmem_max = 33554432
net.core.rmem_default = 2097152
net.core.wmem_default = 2097152
net.ipv4.tcp_rmem = 4096 87380 33554432
net.ipv4.tcp_wmem = 4096 65536 33554432
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_sack = 1
net.core.default_qdisc = fq
net.ipv4.tcp_congestion_control = bbr
mptcp enabled by default so no change