3 ms·
I've measured it in the past. The challenge is isolating the effect for the purpose of measurement, since so many other ancillary algorithm and architecture cho
by jandrewrogers 9y ago
I've measured it in the past. The challenge is isolating the effect for the purpose of measurement, since so many other ancillary algorithm and architecture choices influence performance when using O_DIRECT e.g. the quality of cache replacement implementation and the design of the operation scheduler. O_DIRECT by itself is a pessimization.
Six or seven years ago I built a testbed to measure this directly, using a full O_DIRECT implementation (io_submit, sophisticated user space cache, etc) and a fully hinted mmap() implementation (so no double buffering). The rest of the storage and database implementation was otherwise identical, a fairly conventional B-tree style design. As I recall, the difference in throughput was 2-3x depending on the workload, mostly due to the caching behavior being dramatically more effective and predictable; the kernel cache makes poor choices from the perspective of a database execution schedule, and the knobs it provides for controlling behavior and inspecting cache state are much too limited. You get some of this control back if you double buffer like PostgreSQL does, but that introduces a different set of inefficiencies that kind of leave you where you started.
The secret is that to really take advantage of what O_DIRECT offers, you have to continuously intertwine and adaptively coordinate the query execution schedule and I/O schedule at a very fine-grained level. Most database implementations do not take this level of control of query execution, instead letting the OS control which query execution context gets to run when. In clever architectures, you can dynamically reschedule query execution to be nearly optimal for the I/O schedule and vice versa, while balancing for other tradeoffs (like query latency). Nonetheless, even if you don't do this, you can see substantial performance lift from a competent O_DIRECT based architecture. That said, you can't introduce O_DIRECT incrementally and expect great results, it is a bit of an all or nothing proposition.
- esaym 9y agoSounds like you were really trying to optimize for the top 0.1% :) I think you have hinted that you can't simply "turn on" O_DIRECT and have magic happen and if O_DIRECT is not used with a proper tune, you will be in more trouble. The irony with my story is that even with the horrible DB2 config our "guru" DBA's left us with, all the issues were from the software team. There were many stupid things going on in the ORM that were causing the issues (like grabbing an entire table and then calling a method on each row that in turn got a aggregate sum of a column on the same table... doing that for each row).