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

61–70 of 159 posts

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

#61

Post by farazbabar »

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…

Don't forget high speed committed writes to append only tables (the opposite of scans), postgres approach is better here as well.

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

#62

Post by anarazel »

gavinray wrote: post #58
anarazel wrote: post #53

Earlier quoted context omitted.

FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease. Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost. It turns out, as some of the results in Lukas' post show, that the of…

Do you think there's a possibility of Direct IO being adopted at some point in the future now that AIO is available?

> Do you think there's a possibility of Direct IO being adopted at some point in the future now that AIO is available?

Explicitly a goal.

You can turn it on today, with a bunch of caveats (via debug_io_direct=data). If you have the right workload - e.g. read only and lots of seqscans, bitmap index scans etc you can see rather substantial perf gains. But it'll suck in any cases in 18.

We need at least:

- AIO writes in checkpointer, bgwriter and backend buffer replacement (think bulk loading data with COPY)

- readahead support in a few more places, most crucially index range scan (works out ok today if the heap is correlated with the index, sucks badly otherwise)

EDIT: Formatting

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

#63

Post by the8472 »

anarazel wrote: post #53
the8472 wrote: post #5

On linux there also is preadv2(..., RWF_NOWAIT) which can be used to do optimistic non-blocking read from the page cache. That might be useful for io_method = worker to shave off a bit of latency. Try reading on the main thread with NOWAIT and only offload to a worker thread when that fails.

FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease. Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost. It turns out, as some of the results in Lukas' post show, that the of…

Ah yeah, getting good kerneluserspace 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.

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

#64

Post by Hilift »

martinald wrote: post #13

I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important. We're effectively optimising c…

Everything in the cloud is throttled. Network, IOPS, CPU. And probably implemented incorrectly. AWS makes billions if the customer infrastructure is great or terrible. I found that anything smaller than an AWS EC2 m5.8xlarge had noticeably bad performance on loaded servers (Windows). The list price for that would be about $13k per year, but most organizations get lower than list prices.

This also applies to services, not only compute. Anything associated with Microsoft Office 365 Exchange, scripts may run 10x slower against the cloud using the MSOnline cmdlets. It's absolute insanity, I used to perform a dump of all mailbox statistics that would take about one hour, it could take almost 24 hours against Office 365. You have to be careful to not use the same app or service account in multiple places, because the throttle limits are per-account.

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

#65

Post by saltcured »

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…

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

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

#66

Post by anarazel »

the8472 wrote: post #63
anarazel wrote: post #53

Earlier quoted context omitted.

FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease. Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost. It turns out, as some of the results in Lukas' post show, that the of…

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 process, which needs to compute data checksums given the current postgres implementation (if enabled). But even for reads it can be a bottleneck, e.g. when prewarming the buffer pool after a restart.

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

#67

Post by immibis »

martinald wrote: post #13

I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important. We're effectively optimising c…

I noticed this with bandwidth. AWS price for bandwidth: $90.00/TB after 0.1TB/month. Price everywhere else (low cost VPSes): $1.50/TB after 1-5TB/month. Price some places (dedicated servers): $0.00/TB up to ~100TB/month, $1.50/TB after.

You pay 60 times the price for the privilege of being on AWS.

Bandwidth is just their most egregious price difference. The servers are more expensive too. The storage is more expensive (except for Glacier). The serverless platforms are mostly more expensive than using a cheap server.

There are only two AWS products that I understand to have good prices: S3 Glacier (and only if you never restore!), and serverless apps (Lambda / API Gateway) if your traffic is low enough to fit in the Always Free tier. For everything else, it appears you get ripped off by using AWS.

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

#68

Post by yxhuvud »

the8472 wrote: post #63
anarazel wrote: post #53

Earlier quoted context omitted.

FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease. Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost. It turns out, as some of the results in Lukas' post show, that the of…

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

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

#69

Post by [deleted] »

greenavocado wrote: post #48
p_ing wrote: post #33

Earlier quoted context omitted.

Yeah, surprise Linux had to play catch up to a Windows 1994 release! Same with the scheduler, I'd argue Windows does OOM better than Linux today... Windows even had the concept of io_uring before, but network only with Registered I/O back in the Windows 8 (8.1?) days. Linux still lacks the "all I/O is async" NT has. The underlying kernel and executive of Windows aren't primitive pieces of trash. They're quite advance…

How difficult would it be to completely tear out the Windows desktop experience and just use the system and display drivers without the rest? Has anybody attempted such a feat?

[deleted]

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

#70

Post by yxhuvud »

martinald wrote: post #13

I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important. We're effectively optimising c…

FWIW, using the same approach as in the article, ie io_uring, is one of the few ways to actually reach anywhere close to that 1 million, so it is not as if they are competing concerns.
Post reply on HN