I thought databases needed O_DIRECT, which isn't implemented yet in ZoL.
The return on investment is definitely worth it from a performance perspective. However, it makes portability a hassle and implementation requires a high degree of specialized skill, so open source generally avoids it for the sake of easy maintainability.
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.
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.
I believe MySQL with innodDB(by default) and Postgres both rely on the page cache no?