Horton

Re: AIO v2.5

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: Andres Freund <andres@anarazel.de>
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-15T00:30:10Z
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


On 7/14/25 20:36, Andres Freund wrote:
> 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.
> 

True. I don't have a great idea either. FWIW most of our defaults are
very conservative/low, and you have to bump them up on bigger machines
anyway. So having to bump one more GUC is not a big deal, and I don't
think we have to invent some magic formula for this one.

Also, autoconf wouldn't even know about s_b size, it'd have to be
something at startup. We could do some automated sizing if set to -1,
perhaps. But is s_b even a good value to tie this to? I doubt that.

> 
> 
>>
>> 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...
> 

Too bad the patch doesn't collect any stats about how utilized the
workers are :-( That'd make it a bit easier, we could even print
something into the log if the queues overflow "too often", similarly to
max_wal_size when checkpoints happen too often.

> 
>> 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)...
> 

Yeah, I've started a test on a cloud VM. Will see in a day or two. And
another on FreeBSD, for good measure.

> 
>> 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?
> 

No, the charts are there, you don't need to generate them.

Look into the "with-indexscan-prefetch-run2-17-checksums" branch. E.g.
this is the same "ryzen" plot I shared earlier, but with "indexscan
prefetch" column:

https://github.com/tvondra/iomethod-tests/blob/with-indexscan-prefetch-run2-17-checksums/ryzen-rows-cold-32GB-16-unscaled.pdf

It might be better to look at the "scaled" charts, which makes it easier
to compare the different scans (and the benefit of prefetching).

https://github.com/tvondra/iomethod-tests/blob/with-indexscan-prefetch-run2-17-checksums/ryzen-rows-cold-32GB-16-scaled.pdf

> 
>> 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


regards

-- 
Tomas Vondra