Hacker News Reader

Comment by ptrwis | Hacker News Reader

I'm thinking lower resource usage (no double caching of data), shorter path to data so I would expect fewer bad things might happen during commit, and better performance in terms of transactions per second. Otherwise, I can't explain it any better than one of the lead developers himself: https://www.postgresql.org/message-id/20210223100344.llw5an2...

View on HN

The new debug_io_direct flag only triggers direct IO in very limited cases, and is only tangentially related to the AIO patchset discussed in that thread.

Note that the documentation on the config flag explicitly warns about not using it in production:

> Currently this feature reduces performance, and is intended for developer testing only.

Also note that very few things will actually do IO during commit - the only IO that I can think of are 1.) the WAL-logging of the commit (often small, a few 100 bytes at most), and 2.) replying to the COMMIT command (10s of bytes at most). It is quite unlikely that this will see much performance benefit from IO_DIRECT without further infrastructure inside PostgreSQL around io_uring and other async kernel IO apis.

I know, but it's still nice to see progress in this area. Even PG17 would probably be too early to expect this work to be finished.

Yes when direct io is brought up, it is usually followed with the "double buffering" argument. That is valid, but only if your disk speeds are well above 1,000MB/s. Outside of hardware like that, you are always going to be waiting on disk.

From Linus himself[0]

"The thing that has always disturbed me about O_DIRECT is that the whole interface is just stupid, and was probably designed by a deranged monkey on some serious mind-controlling substances"

[0] https://lkml.org/lkml/2002/5/11/58

Have you read the entire thread you linked? People explained to Linus why direct IO is important to them. Besides, it's 20 years old and there were even no SSDs back then. The lack of direct IO in PG was one of (one of) the reasons why Uber moved to MySQL (https://www.uber.com/en-PL/blog/postgres-to-mysql-migration/, "The Buffer Pool" Section). With buffered IO you will likely store a lot of the same data in memory twice- once in DB's memory and then in page cache. Now you can just give the memory used by page cache directly to DB, because it knows better what and when it needs.

The thread is large, maybe I read the whole thing at one time, but the point of it is literally someone asking why using direct io is slower. That is the point I make, you can't just enable direct io (which by passes all the logic to speed up reads and writes) and expect increased performance without a lot of extra up front work.

But back to my first question, I am still curious what your workload is that you feel will benefit from direct io :)

As others have written, this work entails implementing asynchronous IO. One system I worked with that was more heavily loaded than the usual intranet CRUD was an SMS lottery run by a radio station. They had "bonus rounds" that lasted a few minutes. When the host said "now send!" we were receiving a huge amount of data at the same time. However, it worked for years on Postgres 7.4 and regular hard drives :)

I suspect Linus' position has radically changed with io-uring and proliferation of NVMe storage devices.

I use io-uring with O_DIRECT at work and the performance graphs of TLB pressure are beautiful.

Reply on news.ycombinator.com