Horton

Re: AIO v2.5

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Tomas Vondra <tomas@vondra.me>
Cc: Antonin Houska <ah@cybertec.at>, Noah Misch <noah@leadboat.com>, pgsql-hackers@postgresql.org, Thomas Munro <thomas.munro@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, Robert Haas <robertmhaas@gmail.com>, Jakub Wartak <jakub.wartak@enterprisedb.com>, Jelte Fennema-Nio <postgres@jeltef.nl>
Date: 2025-07-14T18:36:49Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. aio: Fix assertion, clarify README

  2. aio: Fix reference to outdated name

  3. aio: Fix possible state confusions due to interrupt processing

  4. aio: Improve debug logging around waiting for IOs

  5. aio: Fix crash potential for pg_aios views due to late state update

  6. Increase BAS_BULKREAD based on effective_io_concurrency

  7. localbuf: Add Valgrind buffer access instrumentation

  8. aio: Make AIO more compatible with valgrind

  9. aio: Avoid spurious coverity warning

  10. tests: Fix incompatibility of test_aio with *_FORCE_RELEASE

  11. tests: Cope with WARNINGs during failed CREATE DB on windows

  12. aio: Add errcontext for processing I/Os for another backend

  13. aio: Add README.md explaining higher level design

  14. aio: Minor comment improvements

  15. aio: Add test_aio module

  16. aio: Add pg_aios view

  17. docs: Add acronym and glossary entries for I/O and AIO

  18. Enable IO concurrency on all systems

  19. read_stream: Introduce and use optional batchmode support

  20. docs: Reframe track_io_timing related docs as wait time

  21. bufmgr: Use AIO in StartReadBuffers()

  22. bufmgr: Implement AIO read support

  23. aio: Add WARNING result status

  24. Let caller of PageIsVerified() control ignore_checksum_failure

  25. pgstat: Allow checksum errors to be reported in critical sections

  26. Add errhint_internal()

  27. localbuf: Track pincount in BufferDesc as well

  28. aio, bufmgr: Comment fixes/improvements

  29. Fix mis-attribution of checksum failure stats to the wrong database

  30. aio: Implement support for reads in smgr/md/fd

  31. aio: Add io_method=io_uring

  32. aio: Add liburing dependency

  33. aio: Rename pgaio_io_prep_* to pgaio_io_start_*

  34. aio: Pass result of local callbacks to ->report_return

  35. aio: Be more paranoid about interrupts

  36. Redefine max_files_per_process to control additionally opened files

  37. aio: Change prefix of PgAioResultStatus values to PGAIO_RS_

  38. bufmgr: Improve stats when a buffer is read in concurrently

  39. aio: Add io_method=worker

  40. aio: Infrastructure for io_method=worker

  41. aio: Add core asynchronous I/O infrastructure

  42. aio: Basic subsystem initialization

  43. tests: Expand temp table tests to some pin related matters

  44. localbuf: Introduce FlushLocalBuffer()

  45. localbuf: Introduce TerminateLocalBufferIO()

  46. localbuf: Fix dangerous coding pattern in GetLocalVictimBuffer()

  47. localbuf: Introduce StartLocalBufferIO()

  48. localbuf: Introduce InvalidateLocalBuffer()

  49. Allow lwlocks to be disowned

  50. Make jsonb casts to scalar types translate JSON null to SQL NULL.

  51. bufmgr/smgr: Don't cross segment boundaries in StartReadBuffers()

  52. Use aux process resource owner in walsender

  53. bufmgr: Return early in ScheduleBufferTagForWriteback() if fsync=off

Hi,

On 2025-07-11 23:03:53 +0200, Tomas Vondra wrote:
> I've been running some benchmarks comparing the io_methods, to help with
> resolving this PG18 open item. So here are some results, and my brief
> analysis of it.

Thanks for doing that!



> The TL;DR version
> -----------------
> 
> * The "worker" method seems good, and I think we should keep it as a
> default. We should probably think about increasing the number of workers
> a bit, the current io_workers=3 seems to be too low and regresses in a
> couple tests.
> 
> * The "sync" seems OK too, but it's more of a conservative choice, i.e.
> more weight for keeping the PG17 behavior / not causing regressions. But
> I haven't seen that (with enough workers). And there are cases when the
> "worker" is much faster. It'd be a shame to throw away that benefit.
> 
> * There might be bugs in "worker", simply because it has to deal with
> multiple concurrent processes etc. But I guess we'll fix those just like
> other bugs. I don't think it's a good argument against "worker" default.
> 
> * All my tests were done on Linux and NVMe drives. It'd be good to do
> similar testing on other platforms (e.g. FreeBSD) and/or storage. I plan
> to do some of that, but it'd be great to cover more cases. I can help
> with getting my script running, a run takes ~1-2 days.

FWIW, in my very limited tests on windows, the benefit of worker was
considerably bigger there, due to having much more minimal readahead not
having posix_fadvise...


> The test also included PG17 for comparison, but I forgot PG18 enabled
> checksums by default. So PG17 results are with checksums off, which in
> some cases means PG17 seems a little bit faster. I'm re-running it with
> checksums enabled on PG17, and that seems to eliminate the differences
> (as expected).

My sneaking suspicion is that, independent of AIO, we're not really ready to
default to checksums defaulting to on.


> Findings
> --------
> 
> I'm attaching only three PDFs with charts from the cold runs, to keep
> the e-mail small (each PDF is ~100-200kB). Feel free to check the other
> PDFs in the git repository, but it's all very similar and the attached
> PDFs are quite representative.
> 
> Some basic observations:
> 
> a) index scans
> 
> There's almost no difference for indexscans, i.e. the middle column in
> the PDFs. There's a bit of variation on some of the cyclic/linear data
> sets, but it seems more like random noise than a systemic difference.
>
> Which is not all that surprising, considering index scans don't really
> use read_stream yet, so there's no prefetching etc.

Indeed.


> The "ryzen" results however demonstrate that 3 workers may be too low.
> The timing spikes to ~3000ms (at ~1% selectivity), before quickly
> dropping back to ~1000ms. The other datasets show similar difference.
> With 12 workers, there's no such problem.

I don't really know what to do about that - for now we don't have dynamic
#workers, and starting 12 workers on a tiny database doesn't really make
sense...  I suspect that on most hardware & queries it won't matter that much,
but clearly, if you have high iops hardware it might.  I can perhaps see
increasing the default to 5 or so, but after that...  I guess we could try
some autoconf formula based on the size of s_b or such? But that seems
somewhat awkward too.



> 
> e) indexscan regression (ryzen-indexscan-uniform-pg17-checksums.png)
> 
> There's an interesting difference difference I noticed in the run with
> checksums on PG17. The full PDF is available here:

(there's a subsequent email about this, will reply there)


> Conclusion
> ----------
> 
> That's all I have at the moment. I still think it makes sense to keep
> io_method=worker, but bump up the io_workers a bit higher. Could we also
> add some suggestions how to pick a good value to the docs?

.oO(/me ponders a troll patch to re-add a reference the number of spindles in
that tuning advice)

I'm not sure what advice to give here.  Maybe just to set it to a considerably
larger number once not running on a tiny system? The incremental overhead of
having an idle worker is rather small unless you're on a really tiny system...


> You might also run the benchmark on different hardware, and either
> build/publish the plots somewhere, or just give me the CSV and I'll do
> that. Better to find strange stuff / regressions now.

Probably the most interesting thing would be some runs with cloud-ish storage
(relatively high iops, very high latency)...


> The repository also has branches with plots showing results with WIP
> indexscan prefetching. (It's excluded from the PDFs I presented here).

Hm, I looked for those, but I couldn't quickly find any plots that include
them.  Would I have to generate the plots from a checkout of the repo?


> The conclusions are similar to what we found here - "worker" is good
> with enough workers, io_uring is good too. Sync has issues for some of
> the data sets, but still helps a lot.

Nice.

Greetings,

Andres Freund