Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

pganalyze.com

Post reply on HN
Print view View original

91–100 of 159 posts

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#91

Post by jiggawatts »

saltcured wrote: post #65
greenavocado wrote: post #43

Earlier quoted context omitted.

PostgreSQL uses heap files for the primary table storage, not B-trees. In PostgreSQL table data is primarily stored in heap files (unordered collections of pages/blocks). Indexes (including primary key indexes) use B-trees (specifically B+ trees). When you query a table via an index, the B-tree index points to locations in the heap file InnoDB uses a clustered index approach. The primary key index is a B-tree. The ac…

It's also deeply entwined with the MVCC concurrency control and the ability to do DDL in transactions, right?

SQL Server supports every combination of heap storage, clustered storage, MVCC, and DDL in transactions.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#92

Post by tehlike »

seunosewa wrote: post #18
skeptrune wrote: post #11

How close is this to the way MySQL does it with InnoDB? It appears to be about the same.

Yep. It's a low hanging fruit they should've picked years ago. They will eventually figure out using b-trees for tables too.

There's a lot more PG needs to do for storage layout / access pov, and they have been working on it for a while. Orioledb has shown what might be opssible, and they have been upstreaming it.

Having the ability to do something LSM as a storage engine would be great - and potentially allow better compression than what we currently get with TOAST - which is not a lot... PG doesn't even have oob page compression...

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#93

Post by kev009 »

tiffanyh wrote: post #78
kev009 wrote: post #75

A lot of work has gone into FreeBSD's aio(4) so it will be interesting to see how that works, because it doesn't have the drawbacks of Linux/glibc aio.

Would you mind expanding more on this topic. Is FreeBSD doing anything significantly different and/or better?

Sure two separate things really.

Linux aio (the POSIX one, not the broader concept that now includes io_uring) has been fundamentally bad. Part of it is some of the implementation is delegated to user space in glibc as worker threads. https://lwn.net/Articles/671649/ surveys the issues. I have not done a deep dive into this topic in a long time but as far as I know the situation never greatly improved. io_uring does not suffer from the problems, although it is a new and non-standard API with associated pros and cons.

Thomas Munro has gone into some of the benefits and also gaps of FreeBSD's aio(4) vs io_uring here https://wiki.postgresql.org/wiki/FreeBSD/AIO. Notably, because the implementation is in kernel and has received continuous improvement it is gradually removing downsides and there are several users that need it to work well. This document undersells the problems of ZFS though: the ARC is a necromanced buffer cache which Sun ironically worked very hard to remove (http://mcvoy.com/lm/papers/SunOS.vm_arch.pdf) and nobody has fixed this in 20 years. But for UFS or raw block devs or vendor file systems that doesn't matter.

FreeBSD being a complete "src" tree yields some advantages. In concrete, there are some in tree consumers like ctld (a CAM/iSCSI server) that have been used as vehicles to provide end to end implementation of things like NIC offload of complex protocols that play well with backend block devices such that you can make an offloaded data path that is pumped via asynchronous completion events on both sides (network and block). A related whitepaper https://www.chelsio.com/wp-content/uploads/resources/T6-100G... but this concept can be extended to iSCSI, NVMeOF etc. It seems NVMeOF work sponsored by Chelsio is yielding a lot of the finishing touches https://papers.freebsd.org/2023/eurobsdcon/baldwin-implement.... I believe my colleagues are also improving aio to further optimize the Netflix case of KTLS NIC with data on disk but I am not the right person to extrapolate on that.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#95

Post by dpacmittal »

niux wrote: post #3

I recently deployed Postgres on a dedicated Hetzner EX-44 server (20 cores, 64GB RAM, 2x 512GB NVMe SSDs in RAID 1) for €39/month. The price-to-performance ratio is exceptional, providing enterprise-level capacity at a fraction of typical cloud costs. For security, I implemented TailScale which adds only ~5ms of latency while completely eliminating public network exposure - a worthwhile tradeoff for the significant s…

EX44 is 14 physical cores. Just wanted to point it out.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#96

Post by cryptonector »

ARandomerDude wrote: post #30
seunosewa wrote: post #18

Earlier quoted context omitted.

Yep. It's a low hanging fruit they should've picked years ago. They will eventually figure out using b-trees for tables too.

Can you elaborate on this B-tree part of your comment? I know B-tree is the default index type in pg, but it sounds like there’s more to the story that I’m not familiar with.

SQLite3 only has b-trees for files and indices.

PostgreSQL only has heaps for tables, and various other data structures for indices, with b-tree being the default for indices.

There are many cases where having b-trees for tables would make performance better.

GP is basically poking fun at PG for still having nothing but heaps for tables.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#97

Post by amluto »

anarazel wrote: post #66
the8472 wrote: post #63

Earlier quoted context omitted.

Ah yeah, getting good kernel userspace oneshot memcpy performance for large files is surprisingly hard. mmap has setup/teardown overhead that's significant for oneshot transfers, regular read/write calls suffer from page cache/per page overhead. Hopefully all the large folio work in the kernel will help with that.

From what I've seen a surprisingly large part of the overhead is due to SMAP when doing larger reads from the page cache - i.e. if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly. On Intel it's also not hard to simply reach the per-core memory bandwidth with modern storage HW. This matters most prominently for writes by the checkpointing…

SMAP overhead should be roughly constant, and I’d be quite surprised if it’s noticeable for large reads. Small reads are a different story.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#98

Post by derefr »

anarazel wrote: post #66
the8472 wrote: post #63

Earlier quoted context omitted.

Ah yeah, getting good kernel userspace oneshot memcpy performance for large files is surprisingly hard. mmap has setup/teardown overhead that's significant for oneshot transfers, regular read/write calls suffer from page cache/per page overhead. Hopefully all the large folio work in the kernel will help with that.

From what I've seen a surprisingly large part of the overhead is due to SMAP when doing larger reads from the page cache - i.e. if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly. On Intel it's also not hard to simply reach the per-core memory bandwidth with modern storage HW. This matters most prominently for writes by the checkpointing…

> if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly.

Is there a page anywhere that collects these sorts of "turn the whole hardware security layer off" switches that can be flipped to get better throughput out of modern x86 CPUs, when your system has no real attack surface to speak of (e.g. air-gapped single-tenant HPC)?

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#99

Post by anarazel »

amluto wrote: post #97
anarazel wrote: post #66

Earlier quoted context omitted.

From what I've seen a surprisingly large part of the overhead is due to SMAP when doing larger reads from the page cache - i.e. if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly. On Intel it's also not hard to simply reach the per-core memory bandwidth with modern storage HW. This matters most prominently for writes by the checkpointing…

SMAP overhead should be roughly constant, and I’d be quite surprised if it’s noticeable for large reads. Small reads are a different story.

It turns out to be the other way round, curiously. The bigger the reads (i.e. how much to read in one syscall) and the bigger the target area of the reads (how long before a target memory location is reused), the bigger the overhead of SMAP gets.

If interesting I can dig up the reproducer I had at some point.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#100

Post by p_ing »

znpy wrote: post #81
p_ing wrote: post #35

Is io_uring still plagued by security issues enabled by it's use? Or have those largely been fixed? My understanding was many Linux admins (or even distros by default?) were disabling io_uring.

Disabling io_uring because “guy on the internet said so” or “$faang_company says so” is beyond dumb. One should evaluate the risk according to their specific use case. It can be a good idea to disable it of you run untrusted workloads (eg: other people’s containers, sharing the same kernel) but if you have a kernel on a machine (virtual or real) dedicated to your own workload you can pretty much keep using io_uring.…

Yes, every security mechanism should be reviewed to validate it is applicable, i.e. I had a vulnerable version of node running but I wasn't using the particular aspect of node so it was a non-issue (and amazingly, IT sec agreed!).

But in the case of io_uring, it was outright bypassing other security layers. And while we all like to think we're running trusted services/code, we have to think about supply-chain attacks that may surprise us, or zero days, etc.

Post reply on HN