3 ms·

>The return on investment is definitely worth it from a performance perspective. Do you have hard evidence for that? I know in "theory" it sounds good and Orac

by esaym 9y ago

>The return on investment is definitely worth it from a performance perspective. Do you have hard evidence for that? I know in "theory" it sounds good and Oracle and DB2 docs continually act like it is a requirement. My personal experience is only with DB2. Its storage was mounted on NFS (yea I know..) and due to many other app issues we were having DB performance issues (or that is what everyone though). Since it was mounted on NFS I could just sniff the wire and see what was going on. To my horror it was reading and writing in individual 4k blocks (which was the current page size). The overall network throughput was only about 2-4mbs at tops. I managed to convince the DBA to turn off O_DIRECT in the DB2 config. I didn't get much of a chance to benchmark (since so much other stuff was going on at the time and the DBA insisted that my idea was a complete was of time) but what I did see was that without O_DIRECT, the OS has taken over and was batching up the reads and writes into larger blocks on the wire (sometimes as high as 64k) and overall throughput at max on the wire was now 20-30mbs. But I only had a few minutes with this config before it was reverted and never revisited. But nonetheless, using postgresql or mysql with the same schema and data would yield reads and writes of much larger blocks and it would allow the network throughput max peaks of up to 200mbs.

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 ago
  Sounds 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).
ddorian43 9y ago
ScyllaDB does (along with many other optimizations like async and more planned ones (like writing their own filesystem?)): http://www.scylladb.com/product/technology/memory-management/ http://www.scylladb.com/product/technology/memory-management...
jandrewrogers 9y ago
BTW, with respect to your experience, O_DIRECT can use page sizes that are any multiple of 4k (e.g. I often use it with 256k blocks). Using 4k block sizes for the I/O transactions is a default behavior for O_DIRECT; if you are seeing 4k I/O blocks in a database it almost certainly means something is misconfigured or being used incorrectly.

by esaym 9y ago

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 ago
Sounds 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).

ddorian43 9y ago

ScyllaDB does (along with many other optimizations like async and more planned ones (like writing their own filesystem?)): http://www.scylladb.com/product/technology/memory-management/ http://www.scylladb.com/product/technology/memory-management...

jandrewrogers 9y ago

BTW, with respect to your experience, O_DIRECT can use page sizes that are any multiple of 4k (e.g. I often use it with 256k blocks). Using 4k block sizes for the I/O transactions is a default behavior for O_DIRECT; if you are seeing 4k I/O blocks in a database it almost certainly means something is misconfigured or being used incorrectly.