Message-ID: <1c9fe4ba-1f49-4116-9c44-add3ed0310e5@planetscale.com>
Date: Mon, 14 Sep 2026 14:29:34 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Recovery does not honor io_combine_limit, causing IOPS saturation
To: Mats Kindahl <mats.kindahl@gmail.com>
References: <30e5bab4-8f61-4f03-9cec-e486540a78fb@gmail.com>
Content-Language: en-US
From: Markos Fountoulakis <markos@planetscale.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
In-Reply-To: <30e5bab4-8f61-4f03-9cec-e486540a78fb@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/1c9fe4ba-1f49-4116-9c44-add3ed0310e5%40planetscale.com>
Precedence: bulk

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/9bdb428a-fc8e-4801-8fff-1ad0bc6fa76b@postgrespro.ru

-- 
Markos Fountoulakis
PlanetScale Postgres Core Team