AIO & DIO for PostgreSQL on FreeBSD

AIO & DIO
for PostgreSQL on FreeBSD*
Work in progress report
*And other animals
Thomas Munro
PostgreSQL hacker @ Microsoft
PGCon 2022, which should have been in Ottawa

View Slide

Modernising PostgreSQL’s disk IO
And some things I learned about FreeBSD along the journey
• PostgreSQL disk AIO project started by Andres Freund, later joined by
Thomas Munro, Melanie Plageman, David Rowley, being proposed for
PostgreSQL 16
io_method={worker,io_uring,posix_aio,windows_iocp}

io_data_direct={on,off}
 
io_wal_direct={on,off}

All
supported
OSes
Linux
POSIX,
especially
FreeBSD
Windows

View Slide

Problems we want to solve

Direct I/O sounds easy
 
AIO is a portability nightmare

FreeBSD thoughts

Q&A

View Slide

Kernel Storage
PostgreSQL
• RAM is wasted by having two levels
of bu
ff
er

• Data is transferred between caches
8KB at a time with a lot of
synchronous pread/pwrite

• Relying on kernel heuristics for
readhead and writeback

• We give limited hints about future
reads and fsyncs on some OSes for
IO concurrency (but not FreeBSD 😔)
Bu
ff
er cache/ARC
Bu
ff
er pool
WAL bu
ff
ers
PostgreSQL’s traditional disk I/O

View Slide

Kernel Storage
PostgreSQL
• Optionally skip the kernel bu
ff
er cache,
no double bu
ff
ering, no kernel copying

• Submit and complete batches of IOs
with minimal system calls via modern*
asynchronous interfaces

• Combine IOs (adjacent data, scatter/
gather)

• Make our own specialised IO
streaming predictors (not just “detect
sequential”, but data-dependent)

• Model and control IO with certain
goals (latency, resource usage, …)
Bu
ff
er pool
WAL bu
ff
ers
AIO/DIO

View Slide

postgres=# set max_parallel_workers_per_gather = 0;

SET

 
postgres=# select count(*) from t;

count

---------

1000000

(1 row)

Disable query executor
parallelism, to make this
example simple
We’ll scan a table that isn’t in
PostgreSQL’s buffer pool

View Slide

kevent(4,0x0,0,{ 8,EVFILT_READ,0x0,0,0x1d,0x8016fc610 },1,0x0) = 1 (0x1)

recvfrom(8,"Q\0\0\0\^\select count(*) from t"...,8192,0,NULL,0x0) = 29 (0x1d)

pread(62,"\0\0\0\08\^]M\Â\0\0\^D\0\240\^C"...,8192,0x0) = 8192 (0x2000)

pread(62,"\0\0\0\0\M-hUM\Â\0\0\^D\0\240"...,8192,0x2000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M^X\M^NM\Â\0\0\^D\0"...,8192,0x4000) = 8192 (0x2000)

pread(62,"\0\0\0\0H\M-GM\Â\0\0\^D\0\240"...,8192,0x6000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M-`\M^?M\Â\0\0\^D\0"...,8192,0x8000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M^P8N\Â\0\0\^D\0\240"...,8192,0xa000) = 8192 (0x2000)

pread(62,"\0\0\0\[email protected]\Â\0\0\^D\0\240\^C"...,8192,0xc000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M-p\M-)N\Â\0\0\^D\0"...,8192,0xe000) = 8192 (0x2000)

pread(62,"\0\0\0\0\240\M-bN\Â\0\0\^D\0"...,8192,0x10000) = 8192 (0x2000)

pread(62,"\0\0\0\08\^[O\Â\0\0\^D\0\240\^C"...,8192,0x12000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M-hSO\Â\0\0\^D\0\240"...,8192,0x14000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M^X\M^LO\Â\0\0\^D\0"...,8192,0x16000) = 8192 (0x2000)

pread(62,"\0\0\0\0H\M-EO\Â\0\0\^D\0\240"...,8192,0x18000) = 8192 (0x2000)

…

pread(62,"\0\0\0\0HpL\^C\0\0\^D\0\240\^C"...,8192,0x120c000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M-x\M-(L\^C\0\0\^D\0"...,8192,0x120e000) = 8192 (0x2000)

pread(62,"\0\0\0\0\M-(\M-aL\^C\0\0\^D\0"...,8192,0x1210000) = 8192 (0x2000)

sendto(8,"T\0\0\0\^^\0\Âcount\0\0\0\0\0\0"...,69,0,NULL,0) = 69 (0x45)

recvfrom(8,0xe19cd0,8192,0,0x0,0x0) ERR#35 'Resource temporarily unavailable'
O
ld
w
ay

View Slide

kevent(4,0x0,0,{ 8,EVFILT_READ,0x0,0,0x1d,0x8016ee790 },1,0x0) = 1 (0x1)


kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

kill(52213,SIGURG) = 0 (0x0)

sendto(8,"T\0\0\0\^^\0\^Acount\0\0\0\0\0\0"...,69,0,NULL,0) = 69 (0x45)

recvfrom(8,0xe46480,8192,0,0x0,0x0) ERR#35 'Resource temporarily unavailable'
io_m
ethod=

w
orker

View Slide

kevent(8,0x0,0,{ SIGURG,EVFILT_SIGNAL,EV_CLEAR,0,0x1,0x80160a580 },1,0x0) = 1 (0x1)

preadv(0xd,0x7fffffffa3d0,0x1,0x1da000) = 131072 (0x20000)

preadv(0xd,0x7fffffffa3d0,0x1,0x1fa000) = 131072 (0x20000)

preadv(0xd,0x7fffffffa3d0,0x1,0x21a000) = 131072 (0x20000)





preadv(0xd,0x7fffffffa3d0,0x2,0x2ba000) = 131072 (0x20000)












preadv(0xd,0x7fffffffa3d0,0x1,0x3ba000) = 131072 (0x20000)






IO
w
orker process

View Slide

kevent(4,0x0,0,{ 8,EVFILT_READ,0x0,0,0x1d,0x8016ee790 },1,0x0) = 1 (0x1)


lio_listio(LIO_NOWAIT,[{ 32,1941504,0x80372a000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE }
 
{ 32,2072576,0x80374a000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE }
 
lio_listio(LIO_NOWAIT,[{ 32,2465792,0x8037aa000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE }
 
{ 32,2596864,0x8037ca000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE }
lio_listio(LIO_NOWAIT,[{ 32,2727936,0x8037ea000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE }
 

{ 32,2875392,0x80470e000,114688,LIO_READ,{ sigev_notify=SIGEV_NONE }
aio_waitcomplete({ 32,2072576,0x80374a000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE } },{ 0
aio_waitcomplete({ 32,2465792,0x8037aa000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE } },{ 0
aio_waitcomplete({ 32,2596864,0x8037ca000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE } },{ 0
aio_waitcomplete({ 32,2859008,0x80380a000,16384,LIO_READ,{ sigev_notify=SIGEV_NONE } },{ 0.
aio_waitcomplete({ 32,2727936,0x8037ea000,131072,LIO_READ,{ sigev_notify=SIGEV_NONE } },{ 0
aio_waitcomplete({ 32,2875392,0x80470e000,114688,LIO_READ,{ sigev_notify=SIGEV_NONE } },{ 0
 
…
io_m
ethod=
 
posix_aio
(FreeBSD
)
A FreeBSD-speci
fi
c way to
consume completion events

(one of many supported ways, see
next section "Portability
nightmares”)
The POSIX way to start
multiple IOs with one
system call

View Slide

epoll_wait(12, [{EPOLLIN, {u32=2507243840, u64=94075175928128}}], 1, -1) = 1

recvfrom(16, "Q\0\0\0\34select count(*) from t;\0", 8192, 0, NULL, NULL) = 29

io_uring_enter(9, 1, 0, 0, NULL, 8) = 1









…




sendto(16, "T\0\0\0\36\0\1count\0\0\0\0\0\0\0\0\0\0\24\0\10\377\377\377\377\0\0D"..., 69, 0,
0) = 69

recvfrom(16, 0x558f937066a0, 8192, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavaila

epoll_wait(12, 0x558f95718188, 1, -1) = -1 EINTR (Interrupted system call)
io_m
ethod=

io_uring
(Linux)
Simple tracing tools can’t see
submissions and completions as
they’re in user space queues, not in
sys call arguments/results

View Slide

AIO
streaming
controller

callback
Data
consumer/
producer
PG AIO API

Logic for controlling IO depth is
common infrastructure, working in
batches, to allow for IO combining;

Research topic: control theory
algorithms
Logic for how to initiate one more IO
is provided by from consumer/
producer
Index scans,
recovery/
replication system,
checkpointer, …

View Slide

Primary node Replica node
A system with
many clients can
be faulting in
pages
concurrently
Replicas would
synchronously
fault in one page
at a time;
fi
xed
with AIO-based
prefetching
postgres=# select wal_distance, io_depth from pg_stat_recovery_prefetch;

wal_distance | io_depth
 
——————————————+—————————-

3272 | 7

View Slide

postgres=# select op, scb, owner_pid, "desc" from pg_stat_aios where flags != 'PGAIOIP_IDLE';

┌───────┬─────┬───────────┬───────────────────────────────────────────────────────────────────────────────────────────┐

│ op │ scb │ owner_pid │ desc │

├───────┼─────┼───────────┼───────────────────────────────────────────────────────────────────────────────────────────┤

│ read │ sb │ 67223 │ fd: 13, mode: 0, offset: 28188672, nbytes: 8192, already_done: 0, buffid: 10797 │



│ write │ wal │ 67227 │ write_no: 23, fd: 7, offset: 3244032, nbytes: 8192, already_done: 0, bufdata: 0x103582000 │




└───────┴─────┴───────────┴───────────────────────────────────────────────────────────────────────────────────────────┘

View Slide


 

FreeBSD thoughts

Q&A

View Slide

O_DIRECT
• IRIX gave us open(O_DIRECT), widely adopted, but not covered by POSIX so no
formal meaning; general idea “don’t bu
ff
er my data”

• Systems di
ff
er in details (block alignment requirements, e
ff
ects on descriptors with
di
ff
erent
fl
ags, support on di
ff
erent
fi
le systems)

• There were also some other ideas: Sun’s directio() and mount options, Apple’s
F_NOCACHE

• AFAIK, mostly of interest to database hackers and big data graphics/science

• Speculation: one of the motivations for 90s databases to use raw disk partitions was
perhaps to skip the kernel’s bu
ff
er cache, IO scheduler and concurrency/locking
problems; O_DIRECT and highly concurrent
fi
le systems address those issues

View Slide

O_DIRECT gives you more problems
• Our bu
ff
er replacement logic and bu
ff
er pool size had better be good,
because the kernel’s bu
ff
ering won’t be cushioning our misses

• We’ll need to supply readhead, writeback, clustering and concurrency logic,
even for the simplest straight-line cases

• -> AIO and DIO go together like peanut butter and jelly

• Slower unbu
ff
ered reads and writes may reveal locking problems in
fi
lesystem
implementation (gulp)

View Slide


 

FreeBSD thoughts

Q&A

View Slide

• 1993: Windows NT

• Brings ideas from VMS, RSX-11D,
RSX-11M

• Asynchronous interfaces everywhere
(“overlapped”)

• Event-based polling and callbacks

• 1994: Windows NT 3.5

• Adds IOCP for queue-like event
processing

• 1993: POSIX AIO, RT signals

• IOs can now be started
asynchronously

• RT signals can carry a payload for
example an IO number/pointer,
and be consumed synchronously
from a per-process queue or call
handlers

• Alternative polling scheme
Nearly three decades of AIO

View Slide

io_method= worker posix_aio io_uring windows_iocp
AIX ✓ ✓
Dragon
fl
yBSD ✓ N/A
FreeBSD ✓ ✓
HP-UX ✓ ❌
Linux (glibc, musl) ✓ ✓🧵 ✓
macOS ✓ ✓*
NetBSD ✓ 💥
OpenBSD ✓ N/A
Solaris ✓ ✓🧵
Windows ✓ N/A ✓

View Slide

POSIX AIO: submission APIs
• aio_read(), aio_write(), aio_fsync()

• lio_listio() for batched submission of LIO_READ, LIO_WRITE

• FreeBSD 13 added non-standard extensions aio_readv(), aio_writev(),
LIO_READV, LIO_WRITEV for vector IO (scatter/gather)

• Along the same lines, preadv()/pwritev() are also curiously absent from POSIX,
but are obvious combinations of pread()/pwrite() and readv()/writev()

View Slide

POSIX AIO: completion APIs
• aio_error() to
fi
nd out if it’s
fi
nished, failed or EINPROGRESS

• aio_return() to get the result

• aio_suspend() to wait for a list of IOs to complete, then see above to
fi
nd out
which ones (reads and writes only, no fsyncs, though almost all
implementations allow sync to be waited for too)

View Slide

POSIX AIO: standard notification mechanisms
• SIGEV_SIGNAL

• sigwaitinfo() can give you read-from-a-queue style interface, at a cost of three syscalls per IO
(sigwaitinfo(), aio_error(), aio_return()), but macOS didn’t implement it! You could do sigwait() and then
aio_error() for all outstanding IOs, for O(n^2) syscalls…

• Using signal handlers would be unpleasant, and glibc has some questionable async signal safety

• SIGEV_THREAD

• Call a function pointer in some unspeci
fi
ed thread. As a matter of policy we probably don’t want
threads for this, but in any case macOS didn’t implement it…

• SIGEV_NONE

• You’ll have to use aio_suspend() to wait, and then O(n^2). Only HP-UX doesn’t allow aio_fsync() to be
waited for with aio_suspend() (which is allowed by POSIX, why?!)

View Slide

POSIX AIO: extended completion APIs
Almost every OS invented a much nicer queue-like system
• SIGEV_KEVENT (FreeBSD): drain N from a kqueue

• You still have to call aio_return() on each IO, and possibly aio_error() too*, so you cannot
get below 1.x syscalls per IO!

• macOS and NetBSD have both kqueue and AIO, but they are not connected; Dragon
fl
y
removed AIO.

• aio_waitcomplete() (FreeBSD): drain one like a queue; no aio_error() or aio_return()

• AIX’s aio_nwait() (but doesn’t work for aio_fsync()!), Solaris’s aio_waitn(), HP-UX’s aio_reap()
could all read batches of completions, some without even entering the kernel

• AIX and Solaris also added Windows IOCP-style APIs (not explored by me; SIGEV_PORT?)

View Slide

POSIX AIO: Assorted surprises
• What should happen if you close a descriptor while an IO is pending?

• The IO should run to completion

• ECANCELED

• The system should go bananas and run the IO against some later user of the same
fi
le descriptor number

• What should happen if you perform multiple IOs on the same descriptor?

• They should run in parallel to the extend possible

• Serialise them, surely the calling program jests

• POSIX says that aio_suspend() can’t wait for aio_fsync() (only HP-UX doesn’t support fsync here)

• You can’t complete IOs started by another process. OK, maybe not so surprising. PostgreSQL really needs
to go multi-threaded…

View Slide


 

FreeBSD thoughts

Q&A

View Slide

OpenZFS will hopefully support O_DIRECT
• Direct IO is coming!

• https://openzfs.org/wiki/OpenZFS_Developer_Summit_2021

• I haven’t tried it out yet, but this looks interesting for databases.

View Slide

Assorted tricky problems
• UFS O_SYNC/O_DSYNC waits *for each block* instead of whole writes

• UFS has a fast bu
ff
erless path for O_DIRECT reads, but not vector reads
(which should ideally produce a single multi-segment read at the device level),
and no equivalent for writes

• UFS fsync()/fdatasync() doesn’t
fl
ush device caches or use FUA

• UFS uses VFS-level writes locks for write() and fsync(), preventing concurrency

• VFS doesn’t allow for true asynchronous IO, instead kernel threads call the
synchronous entry points and sleep

View Slide

Improve AIO completions via kqueue?
• I would like to be able to use SEGEV_KEVENT to consume completion events
from a kqueue without having to call aio_return(). Then we could get below 1
system call per IO, like on other OSes.

• I have some prototype code for this: see D33271, D33144. It’s tricky, there
are many places in the kernel that assume that IO is synchronous…

• I have heard suggestions that kevent() should also be able to start IOs too

View Slide


 

FreeBSD thoughts

Q&A

View Slide

https://wiki.postgresql.org/wiki/AIO

https://wiki.postgresql.org/wiki/FreeBSD/AIO

View Slide

AIO & DIO for PostgreSQL on FreeBSD

AIO & DIO for PostgreSQL on FreeBSD

Thomas Munro

More Decks by Thomas Munro

Other Decks in Programming

Featured

Transcript