Message-ID: <d2018eee32e211bdfc505862e9ae24b55cec5af0.camel@j-davis.com>
Subject: Re: Should io_method=worker remain the default?
From: Jeff Davis <pgsql@j-davis.com>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, pgsql-hackers@postgresql.org
Date: Mon, 08 Sep 2025 12:08:10 -0700
In-Reply-To: 
 <CA+hUKG+wCagh6mogKS_VL-SVABomho9beg7pLpntHRK2P6QrWA@mail.gmail.com>
References: <d68e2a4f8c356107e5167408ad80eaa2fac0f57d.camel@j-davis.com>
	 <adywrhdn5zcfeldzug2pkzbdxizactr46goh4doiztvexrqomy@lgvl26gui63a>
	 <a81f2f7ef34afc24a89c613671ea017e3651329c.camel@j-davis.com>
	 <CA+hUKG+wCagh6mogKS_VL-SVABomho9beg7pLpntHRK2P6QrWA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/d2018eee32e211bdfc505862e9ae24b55cec5af0.camel%40j-davis.com>
Precedence: bulk

On Mon, 2025-09-08 at 14:39 +1200, Thomas Munro wrote:
> Some raw thoughts on this topic, and how we got here:=C2=A0 This type of
> extreme workload, namely not doing any physical I/O, just copying the
> same data from the kernel page cache to the buffer pool over and over
> again,

Isn't that one of the major selling points of AIO? It does "real
readahead" from kernel buffers into PG buffers ahead of the time, so
that the backend doesn't have to do the memcpy and checksum
calculation. The benefit will be even larger when AIO eventually
enables effective Direct IO, so that shared_buffers can be a larger
share of system memory, and we don't need to move back-and-forth
between kernel buffers and PG buffers (and recalculate the checksum).

The only problem right now is that it doesn't (yet) work great when the
concurrency is higher because:

(a) we don't adapt well to saturated workers; and
(b) there's lock contention on the queue if there are more workers

and I believe both of those problems can be solved in 19.

>  is also the workload where io_method=3Dworker can beat
> io_method=3Dio_uring (and other native I/O methods I've prototyped),
> assuming io_workers is increased to a level that keeps up.

Right, when the workers are not saturated, they *increase* the
parallelism because there are more processes doing the work (unless you
run into lock contention).


> didn't matter much before the
> checksums-by-default change went in just a little ahead of basic AIO
> support.

Yeah, the trade-offs are much different when checksums are on vs off.

>=20
> Interesting that it shows up so clearly for Andres but not for you.

When I increase the io_worker count, then it does seem to be limited by
lock contention (at least I'm seeing evidence with -DLWLOCK_STATS). I
suppose my case is just below some threshold.

>=20
> BTW There are already a couple of levels of signal suppression: if
> workers are not idle then we don't set any latches, and even if we
> did, SetLatch() only sends signals when the recipient is actually
> waiting, which shouldn't happen when the pool is completely busy.

Oh, good to know.

> +=C2=A0=C2=A0=C2=A0 nextWakeupWorker =3D (nextWakeupWorker + 1) % io_work=
ers;
>=20
> FWIW, I experimented extensively with wakeup distribution schemes=20

My patch was really just to try to test the two hypotheses; I wasn't
proposing it. But I was curious whether a simpler scheme might be just
as good, and looks like you already considered and rejected it.

>=20
> I would value your feedback and this type of analysis on the thread
> about automatic tuning for v19.

OK, I will continue the tuning discussion there.

Regarding $SUBJECT: it looks like others are just fine with worker mode
as the default in 18. I have added discussion links to the "no change"
entry in the Open Items list.

I think we'll probably see some of the effects (worker saturation or
lock contention) from my test case appear in real workloads, but
affected users can change to sync mode until we sort these things out
in 19.

Regards,
	Jeff Davis