On 2026-07-26 08:47 +0300, Mats Kindahl wrote:
Hi all,
I have been running recovery of Postgres clusters and small AWS instances and
with a long history to replay, this quickly eats up the I/O budget for the
device. For example, with a gp3 with an IOPS limit of 3000 you quickly eat it up
and get long stalls to recover the cluster. This is because the WAL replay reads
the WAL in 8 KiB block rather than honoring io_combine_limit, which typically
could allow reading 256 KiB in one go.
Since Postgres already have the option io_combine_limit to combine multiple
block reads into a single read, it is straightforward to honor it during
recovery using a simple cache (see attached patch). Since WAL replay is mostly
from beginning to end, it is a simple way to reduce IOPS usage.
With this patch, and the default io_combine_limit of 16, I managed to reduce the
number of I/O reads significantly:
| | reads | read_bytes | avg bytes/read |
|------------|--------|-------------|----------------|
| WAL before | 31,283 | 256,270,336 | 8,192 |
| WAL after | 1,955 | 256,131,072 | 131,013 |
Some back-of-the-envelope calculations give that with this patch we should not
be
able to saturate the device interface with the maximum read bandwidth for gp3 is
approx 125 MiB/s, but the maximum read speed if you want to not hit the limit
when you read 8 KiB blocks is 23-24 MiB/s.
I thought I'd share the patch and the results and hear if people think this is a
useful improvement. There is already some work on pre-fetching, but this is such
a simple patch that I think it could be worth to add to the current version.
(I also have some Perl scripts to set up crash recovery and some bpftrace
scripts that checks the reads, if anybody is interested, but I need to clean
them up a little.)
Best wishes,
Mats Kindahl, Multigres Engineer, Supabase
Dear Mats,
I tested this patch on an Ubuntu 26.04 VM over BTRFS filesystem and a throttled
block device to see the actual behaviour of the system and how the block device
affects the redo performance of the server recovery process (redo done elapsed
time).
I generated the dataset with pgbench as seen below:
=====================
pgbench -s 20 -i test
pgbench --progress=1 -T 1000 -j 64 -c 64 --skip-some-updates test & sleep 5 ;
killall -9 /usr/local/pgsql/bin/postgres
=====================
This kills the server ungracefully during the load generation. By backing up the PGDATA
directory at this point we get a dataset that reproduces the same recovery every time.
pg_waldump shows ~50% of the records have type "Heap" and ~80% of those
records' size is FPI (this will be useful later).
I throttled the device Read IOPS side, which is the condition under which this
patch improves performance. Writes go at full speed, and are also more
asynchronous in nature for filesystems. I used 100 IOPS as the limit, which is
quite low, to exaggerate the impact of the patch. I used cgroups v2 to throttle
the block device as seen below:
=====================
echo "+io" > /sys/fs/cgroup/cgroup.subtree_control
echo "254:16 riops=100" | tee /sys/fs/cgroup/*/io.max /sys/fs/cgroup/.*/io.max
=====================
Before every server restart, that triggers the redo process, I simulated a
crashed host by cleaning the linux page cache so as to issue new Read I/O
requests to the block device:
=====================
sync ; echo 3 > /proc/sys/vm/drop_caches
=====================
Comparing the redo done elapsed time of postgres with and without the patch
there was no difference, both at ~18 seconds which very slow. By tracing the
pread64 system call however the patch did what it was supposed to, 128KB
pread64 calls instead of 8KB ones:
=====================
strace -f --syscall-times=us --trace=pread64 postgres -D /usr/local/pgsql/data
=====================
However, the real time block device statistics (iostat) in both cases had an
average Read request size very close to 8KB which does not explain things yet.
I traced the root cause of this bad I/O access pattern to fadvise calls:
=====================
pread64(6, "..."..., 131072, 12320768) = 131072 <0.000119>
fadvise64(13, 966656, 8192, POSIX_FADV_WILLNEED) = 0 <0.000041>
fadvise64(13, 966656, 8192, POSIX_FADV_WILLNEED) = 0 <0.000010>
fadvise64(13, 966656, 8192, POSIX_FADV_WILLNEED) = 0 <0.000031>
fadvise64(13, 983040, 8192, POSIX_FADV_WILLNEED) = 0 <0.000025>
fadvise64(13, 999424, 8192, POSIX_FADV_WILLNEED) = 0 <0.000031>
fadvise64(13, 1015808, 8192, POSIX_FADV_WILLNEED) = 0 <0.000050>
fadvise64(13, 1032192, 8192, POSIX_FADV_WILLNEED) = 0 <0.000055>
fadvise64(13, 1007616, 8192, POSIX_FADV_WILLNEED) = 0 <0.000035>
fadvise64(13, 1015808, 8192, POSIX_FADV_WILLNEED) = 0 <0.000024>
fadvise64(13, 1032192, 8192, POSIX_FADV_WILLNEED) = 0 <0.000120>
fadvise64(13, 1040384, 8192, POSIX_FADV_WILLNEED) = 0 <0.000068>
pread64(6, "..."..., 131072, 12451840) = 131072 <0.000054>
...
=====================
Those fadvise calls are probably issued for Full Page Write reasons (remember
FPI size of WAL above) and dominate the Read I/O pattern as they are so many of
them. They are small 8KB reads, and are asynchronous. However, being
asynchronous does not help much because we have a hard IOPS limit and there is
no I/O combining taking place in the linux kernel.
Since there is no runtime flag to disable fadvise I did a quick hack to kill
the hints:
=====================
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 190c9974494..113b7d4d5a9 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -2072,7 +2072,7 @@ FilePrefetch(File file, pgoff_t offset, pgoff_t amount,
uint32 wait_event_info)
file, VfdCache[file].fileName,
(int64) offset, (int64) amount));
-#if defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_WILLNEED)
+#if 0//defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_WILLNEED)
{
int returnCode;
=====================
After disabling fadvise calls altogether we get a x30 speedup with a redo
elapsed time of ~0.6 seconds versus ~18 seconds. However, the patch still does
show any improvement over the upstream code. Examining the real block device
statistics again, one can see that Read I/O request sizes are similar even
though the pread64 syscalls are very different. The reason for that is
filesystem readahead behaviour. I disabled BTRFS readahead as such:
=====================
echo 8 > /sys/fs/btrfs/a1bc685b-8e1e-4a53-be23-c26036fb5dba/bdi/read_ahead_kb
=====================
By doing that we can finally see the different in I/O request sizes in the
block device and the patch delivers x15 performance, for x16 larger pread64
calls.
This means that the usefulness of this patch is affected by the OS kernel and
the filesystem readahead logic. If OS-level readahead is in effect then this
patch at worst case may only reduce the pread64 count and only offer CPU-level
overhead reduction.
Also, it would be good if the fadvise problem is addressed. Maybe it will be
fixed by:
https://www.postgresql.org/message-id/[email protected]
--
Markos Fountoulakis
PlanetScale Postgres Core Team
