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

71–80 of 159 posts

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

#71

Post by the8472 »

yxhuvud wrote: post #68
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.

Well, nowadays there is https://www.phoronix.com/news/Linux-RWF_UNCACHED-2024

That doesn't speed up uerspacekernel memcopy, it just reduces cache churn. Despite its name it still goes through the page cache, it just triggers writeback and drops the pages once that's done. For example when copying to a tmpfs it makes zero difference since that lives entirely in memory.

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

#72

Post by digdugdirk »

codegeek wrote: post #31
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…

Would you be willing to open source the setup ? I would love to learn from it.

Seconded. This corner of the programming world is a deep dark and scary place for people who haven't had solid industry experience. It'd be hugely helpful to have a barebones starting point to begin learning best practices.

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

#73

Post by Sesse__ »

greenavocado wrote: post #43
ARandomerDude wrote: post #30

Earlier quoted context omitted.

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.

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…

Indexes that point directly to the disk column are also significantly faster to access; it is a persistent pain point for OLAP on InnoDB that all secondary indexes are indirect. You can work around it by adding additional columns to the index to make your lookups covering, but it's kludgy and imprecise and tends to bloat the index even further. (The flip side is that if you have tons of indexes, and update some unrelated column, InnoDB doesn't need to update those indexes to point to the location of the new row. But I'm generally very rarely annoyed by that in comparison.)

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

#74

Post by jauntywundrkind »

merb wrote: post #22
anonymars wrote: post #20

Earlier quoted context omitted.

> given even a consumer $100-200 NVMe gives ~1million+ IOPS these days In the face of sustained writes? For how long?

sustained reads would not even give 1 mio iops in that case. Maybe wen you only read the same file that fits into the nvme cache. Which probably never happens in a production database..

I think you'd be surprised. Sustained write performance has gotten pretty good. Decent but not fancy consumer drives will often do 1GBps sustained, for bulkier writes. That's much better than we used to expect: flash has gotten much better with so many layers! This mid-range PCIe5 drive sustains a nice 1.5GBps: https://www.techpowerup.com/review/team-group-ge-pro-2-tb/6....

I don't think sustained reads are a problem? Benches like the CrystalDiskMark do a full disk random read test; they're designed to bust through cache afaik. 7.2GBp of 4k reads would translate to 1.8MIOps. Even if this is massively optimistic, you need to slash a lot of zeroes/orders of magnitude to get down to 20kIOps, which you will also pay >$100/mo for.

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

#76

Post by wtallis »

p_ing wrote: post #40
merb wrote: post #22

Earlier quoted context omitted.

sustained reads would not even give 1 mio iops in that case. Maybe wen you only read the same file that fits into the nvme cache. Which probably never happens in a production database..

Samsung 9910 has a 1:1 TB:GB cache size of LPDDR4X memory. I won't pretend to understand the magic NVMe drives possess, but if you got a 4TB or 8TB 9910, could you not in theory pull in all of the data you require to cache? I would assume, and it might be a poor assumption, that NVMe controllers don't pull in files, but rather blocks, so even if you had a database that exceeded cache size, in theory if the active blo…

The DRAM on a SSD like that isn't for caching user data, it's for caching the drive's metadata about which logical blocks (as seen by the OS) correspond to which physical locations in the flash memory.

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

#77

Post by senderista »

the8472 wrote: post #71
yxhuvud wrote: post #68

Earlier quoted context omitted.

Well, nowadays there is https://www.phoronix.com/news/Linux-RWF_UNCACHED-2024

That doesn't speed up uerspace kernel memcopy, it just reduces cache churn. Despite its name it still goes through the page cache, it just triggers writeback and drops the pages once that's done. For example when copying to a tmpfs it makes zero difference since that lives entirely in memory.

So you're less dependent on the page replacement algorithm being scan-resistant, since you can use this flag for scan/loop workloads, right?

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

#78

Post by tiffanyh »

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?

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

#79

Post by slashdev »

the8472 wrote: post #51
slashdev wrote: post #36

Earlier quoted context omitted.

My understanding is this not true, only when the instance permanently fails and is moved.

stop or hibernate kills it. https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/instance...

Yeah, so restart does not.

Which means you can count on it about as much as a server of your own, if you could not repair the server.

I know a database company that uses instance storage as the primary storage. It’s common.

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

#80

Post by KronisLV »

song wrote: post #59

Are there good performance comparisons between postgres, mariadb and percona? I'm really curious at this point in which case each of those database shine.

Probably depends on the particular workload, but there are at least some attempts at benchmarking vaguely typical workloads: https://datasystemreviews.com/postgresql-vs-mariadb-performa...
Post reply on HN