Re: Recovery does not honor io_combine_limit, causing IOPS saturation - Mailing list pgsql-hackers

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
behaviourof the system and how the block device affects the redo performance of the server recovery process (redo done
elapsedtime).
 

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
adataset that reproduces the same recovery every time. pg_waldump shows ~50% of the records have type "Heap" and ~80%
ofthose 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
fullspeed, and are also more asynchronous in nature for filesystems. I used 100 IOPS as the limit, which is quite low,
toexaggerate 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
cacheso 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
secondswhich very slow. By tracing the pread64 system call however the patch did what it was supposed to, 128KB pread64
callsinstead 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
8KBwhich 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
ReadI/O pattern as they are so many of them. They are small 8KB reads, and are asynchronous. However, being
asynchronousdoes not help much because we have a hard IOPS limit and there is no I/O combining taking place in the
linuxkernel.
 

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
statisticsagain, 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-levelreadahead is in effect then this patch at worst case may only reduce the pread64 count and only offer CPU-level
overheadreduction.
 

Also, it would be good if the fadvise problem is addressed. Maybe it will be fixed by:
https://www.postgresql.org/message-id/9bdb428a-fc8e-4801-8fff-1ad0bc6fa76b@postgrespro.ru

-- 
Markos Fountoulakis
PlanetScale Postgres Core Team