Hi, over the last ~year I spent a lot of time trying to figure out how we could add AIO (asynchronous IO) and DIO (direct IO) support to postgres. While there's still a *lot* of open questions, I think I now have a decent handle on most of the bigger architectural questions. Thus this long email.
Just to be clear: I don't expect the current to design to survive as-is. If there's a few sentences below that sound a bit like describing the new world, that's because they're from the README.md in the patch series... ## Why Direct / unbuffered IO? The main reason to want to use Direct IO are: - Lower CPU usage / higher throughput. Particularly on modern storage buffered writes are bottlenecked by the operating system having to copy data from the kernel's page cache to postgres buffer pool using the CPU. Whereas direct IO can often move the data directly between the storage devices and postgres' buffer cache, using DMA. While that transfer is ongoing, the CPU is free to perform other work, with little impact. - Avoiding double buffering between operating system cache and postgres' shared_buffers. - Better control over the timing and pace of dirty data writeback. - Potential for concurrent WAL writes (via O_DIRECT | O_DSYNC writes) The main reason *not* to use Direct IO are: - Without AIO, Direct IO is unusably slow for most purposes. - Even with AIO, many parts of postgres need to be modified to perform explicit prefetching. - In situations where shared_buffers cannot be set appropriately large, e.g. because there are many different postgres instances hosted on shared hardware, performance will often be worse then when using buffered IO. ## Why Asynchronous IO - Without AIO we cannot use DIO - Without asynchronous IO (AIO) PG has to rely on the operating system to hide the cost of synchronous IO from Postgres. While this works surprisingly well in a lot of workloads, it does not do as good a job on prefetching and controlled writeback as we would like. - There are important expensive operations like fdatasync() where the operating system cannot hide the storage latency. This is particularly important for WAL writes, where the ability to asynchronously issue fdatasync() or O_DSYNC writes can yield significantly higher throughput. - Fetching data into shared buffers asynchronously and concurrently with query execution means there is more CPU time for query execution. ## High level difficulties adding AIO/DIO support - Optionally using AIO leads to convoluted and / or duplicated code. - Platform dependency: The common AIO APIs are typically specific to one platform (linux AIO, linux io_uring, windows IOCP, windows overlapped IO) or a few platforms (posix AIO, but there's many differences). - There are a lot of separate places doing IO in PG. Moving all of these to use efficiently use AIO is an, um, large undertaking. - Nothing in the buffer management APIs expects there to be more than one IO to be in progress at the same time - which is required to do AIO. ## Portability & Duplication To avoid the issue of needing non-AIO codepaths to support platforms without native AIO support a worker process based AIO implementation exists (and is currently the default). This also is convenient to check if a problem is related to the native IO implementation or not. Thanks to Thomas Munro for helping a *lot* around this area. He wrote the worker mode, the posix aio mode, added CI, did a lot of other testing, listened to me... ## Deadlock and Starvation Dangers due to AIO Using AIO in a naive way can easily lead to deadlocks in an environment where the source/target of AIO are shared resources, like pages in postgres' shared_buffers. Consider one backend performing readahead on a table, initiating IO for a number of buffers ahead of the current "scan position". If that backend then performs some operation that blocks, or even just is slow, the IO completion for the asynchronously initiated read may not be processed. This AIO implementation solves this problem by requiring that AIO methods either allow AIO completions to be processed by any backend in the system (e.g. io_uring, and indirectly posix, via signal handlers), or to guarantee that AIO processing will happen even when the issuing backend is blocked (e.g. worker mode, which offloads completion processing to the AIO workers). ## AIO API overview The main steps to use AIO (without higher level helpers) are: 1) acquire an "unused" AIO: pgaio_io_get() 2) start some IO, this is done by functions like pgaio_io_start_(read|write|fsync|flush_range)_(smgr|sb|raw|wal) The (read|write|fsync|flush_range) indicates the operation, whereas (smgr|sb|raw|wal) determines how IO completions, errors, ... are handled. (see below for more details about this design choice - it might or not be right) 3) optionally: assign a backend-local completion callback to the IO (pgaio_io_on_completion_local()) 4) 2) alone does *not* cause the IO to be submitted to the kernel, but to be put on a per-backend list of pending IOs. The pending IOs can be explicitly be flushed pgaio_submit_pending(), but will also be submitted if the pending list gets to be too large, or if the current backend waits for the IO. The are two main reasons not to submit the IO immediately: - If adjacent, we can merge several IOs into one "kernel level" IO during submission. Larger IOs are considerably more efficient. - Several AIO APIs allow to submit a batch of IOs in one system call. 5) wait for the IO: pgaio_io_wait() waits for an IO "owned" by the current backend. When other backends may need to wait for an IO to finish, pgaio_io_ref() can put a reference to that AIO in shared memory (e.g. a BufferDesc), which can be waited for using pgaio_io_wait_ref(). 6) Process the results of the request. If a callback was registered in 3), this isn't always necessary. The results of AIO can be accessed using pgaio_io_result() which returns an integer where negative numbers are -errno, and positive numbers are the [partial] success conditions (e.g. potentially indicating a short read). 7) release ownership of the io (pgaio_io_release()) or reuse the IO for another operation (pgaio_io_recycle()) Most places that want to use AIO shouldn't themselves need to care about managing the number of writes in flight, or the readahead distance. To help with that there are two helper utilities, a "streaming read" and a "streaming write". The "streaming read" helper uses a callback to determine which blocks to prefetch - that allows to do readahead in a sequential fashion but importantly also allows to asynchronously "read ahead" non-sequential blocks. E.g. for vacuum, lazy_scan_heap() has a callback that uses the visibility map to figure out which block needs to be read next. Similarly lazy_vacuum_heap() uses the tids in LVDeadTuples to figure out which blocks are going to be needed. Here's the latter as an example: https://github.com/anarazel/postgres/commit/a244baa36bfb252d451a017a273a6da1c09f15a3#diff-3198152613d9a28963266427b380e3d4fbbfabe96a221039c6b1f37bc575b965R1906 ## IO initialization layering One difficulty I had in this process was how to initialize IOs in light of the layering (from bufmgr.c over smgr.c and md.c to fd.c and back, but also e.g. xlog.c). Sometimes AIO needs to be initialized on the bufmgr.c level, sometimes on the md.c level, sometimes on the level of fd.c. But to be able to react to the completion of any such IO metadata about the operation is needed. Early on fd.c initialized IOs, and the context information was just passed through to fd.c. But that seems quite wrong - fd.c shouldn't have to know about which Buffer an IO is about. But higher levels shouldn't know about which files an operation resides in either, so they can't do all the work either... To avoid that, I ended up splitting the "start an AIO" operation into a higher level part, e.g. pgaio_io_start_read_smgr() - which doesn't know about which smgr implementation is in use and thus also not what file/offset we're dealing with, which calls into smgr->md->fd to actually "prepare" the IO (i.e. figure out file / offset). This currently looks like: void pgaio_io_start_read_smgr(PgAioInProgress *io, struct SMgrRelationData* smgr, ForkNumber forknum, BlockNumber blocknum, char *bufdata) { pgaio_io_prepare(io, PGAIO_OP_READ); smgrstartread(io, smgr, forknum, blocknum, bufdata); io->scb_data.read_smgr.tag = (AioBufferTag){ .rnode = smgr->smgr_rnode, .forkNum = forknum, .blockNum = blocknum }; pgaio_io_stage(io, PGAIO_SCB_READ_SMGR); } Once this reaches the fd.c layer the new FileStartRead() function calls pgaio_io_prep_read() on the IO - but doesn't need to know anything about weird higher level stuff like relfilenodes. The _sb (_sb for shared_buffers) variant stores the Buffer, backend and mode (as in ReadBufferMode). I'm not sure this is the right design - but it seems a lot better than what I had earlier... ## Callbacks In the core AIO pieces there are two different types of callbacks at the moment: Shared callbacks, which can be invoked by any backend (normally the issuing backend / the AIO workers, but can be other backends if they are waiting for the IO to complete). For operations on shared resources (e.g. shared buffer reads/writes, or WAL writes) these shared callback needs to transition the state of the object the IO is being done for to completion. E.g. for a shared buffer read that means setting BM_VALID / unsetting BM_IO_IN_PROGRESS. The main reason these callbacks exist is that they make it safe for a backend to issue non-blocking IO on buffers (see the deadlock section above). As any blocked backend can cause the IO to complete, the deadlock danger is gone. Local callbacks, one of which the issuer of an IO can associate with the IO. These can be used to issue further readahead. I initially did not have these, but I found it hard to have a controllable numbers of IO in flight. They are currently mainly used for error handling (e.g. erroring out when XLogFileInit() cannot create the file due to ENOSPC), and to issue more IO (e.g. readahead for heapam). The local callback system isn't quite right, and there's ## AIO conversions Currently the patch series converts a number of subsystems to AIO. They are of very varying quality. I mainly did the conversions that I considered either be of interest architecturally, or that caused a fair bit of pain due to slowness (e.g. VACUUMing without AIO is no fun at all when using DIO). Some also for fun ;) Most conversions are fairly simple. E.g. heap scans, checkpointer, bgwriter, VACUUM are all not too complicated. There are two conversions that are good bit more complicated/experimental: 1) Asynchronous, concurrent, WAL writes. This is important because we right now are very bottlenecked by IO latency, because there effectively only ever is one WAL IO in flight at the same time. Even though in many situations it is possible to issue a WAL write, have one [set of] backends wait for that write as its completions satisfies their XLogFlush() needs, but concurrently already issue the next WAL write(s) that other backends need. The code here is very crufty, but I think the concept is mostly right. 2) Asynchronous buffer replacement. Even with buffered IO we experience a lot of pain when ringbuffers need to write out data (VACUUM!). But with DIO the issue gets a lot worse - the kernel can't hide the write latency from us anymore. This change makes each backend asynchronously clean out buffers that it will need soon. When a ringbuffer is is use this means cleaning out buffers in the ringbuffer, when not, performing the clock sweep and cleaning out victim buffers. Due to 1) the XLogFlush() can also be done asynchronously. There are a *lot* of places that haven't been converted to use AIO. ## Stats There are two new views: pg_stat_aios showing AIOs that are currently in-progress, pg_stat_aio_backends showing per-backend statistics about AIO. ## Code: https://github.com/anarazel/postgres/tree/aio I was not planning to attach all the patches on the way to AIO - it's too many right now... I hope I can reduce the size of the series iteratively into easier to look at chunks. ## TL;DR: Performance numbers This is worth an email on its own, and it's pretty late here already and I want to rerun benchmarks before posting more numbers. So here are just a few that I could run before falling asleep. 1) 60s of parallel COPY BINARY of a 89MB into separate tables (s_b = 96GB): slow NVMe SSD branch dio clients tps/stddev checkpoint write time master n 8 3.0/2296 ms 4.1s / 379647 buffers = 723MiB/s aio n 8 3.8/1985 ms 11.5s / 1028669 buffers = 698MiB/ aio y 8 4.7/204 ms 10.0s / 1164933 buffers = 910MiB/s raid of 2 fast NVMe SSDs (on pcie3): branch dio clients tps/stddev checkpoint write time master n 8 9.7/62 ms 7.6s / 1206376 buffers = 1240MiB/s aio n 8 11.4/82 ms 14.3s / 2838129 buffers = 1550MiB/s aio y 8 18.1/56 ms 8.9s / 4486170 buffers = 3938MiB/s 2) pg prewarm speed raid of 2 fast NVMe SSDs (on pcie3): pg_prewarm(62GB, read) branch dio time bw master n 17.4s 3626MiB/s aio n 10.3s 6126MiB/s (higher cpu usage) aio y 9.8s 6438MiB/s pg_prewarm(62GB, buffer) branch dio time bw master n 38.3s 1647MiB/s aio n 13.6s 4639MiB/s (higher cpu usage) aio y 10.7s 5897MiB/s 3) parallel sequential scan speed parallel sequential scan + count(*) of 59GB table: branch dio max_parallel time master n 0 40.5s master n 1 22.6s master n 2 16.4s master n 4 10.9s master n 8 9.3s aio y 0 33.1s aio y 1 17.2s aio y 2 11.8s aio y 4 9.0s aio y 8 9.2s On local SSDs there's some, but not a huge performance advantage in most transactional r/w workloads. But on cloud storage - which has a lot higher latency - AIO can yield huge advantages. I've seen over 4x. There's definitely also cases where AIO currently hurts - most of those I just didn't get aroung to address. There's a lot more cases in which DIO currently hurts - mostly because the necessary smarts haven't yet been added. Comments? Questions? I plan to send separate emails about smaller chunks of this seperately - the whole topic is just too big. In particular I plan to send something around buffer locking / state management - it's a one of the core issues around this imo. Regards, Andres