ZFS from a MySQL perspective
percona.com
percona.com
It might sound like a small difference, but it does keep the files independent on the VFS layer. You can modify a file without fear of unwantingly modifying other files at the same time, which would happen if they were hard linked.
Through our own research and experimentation we settled on a few critical tweaks to make InnoDB and MySQL play nicely together... the highlights are:
- disable the innodb doublewrite buffer, ZFS protects against partial page writes
- tune the ARC aggressively: we size it waaay down on our db servers and let innodb's buffer pool handle in-memory caching, and disable the arc prefetch. On our application databases, our core tables fit entirely into the innodb buffer pool
- particularly important on spinning-platter systems, give the zpool scrub jobs higher IO priority... the default settings for zpool scrub delay scan_min_time are extremely conservative and safe, but on high-IO slaves we found that there was essentially never enough idle time for the scrub to complete. we sacrifice some IO overhead to allow scrubs to finish regularly.
I'm really curious to see some more detailed writing on MySQL/ZFS from the Percona team... their benchmarks are usually pretty well thought out, and they certainly understand InnoDB better than we do. What we've got works well, but I'd be surprised if a truly ideal setup down to the kind of detail that they dig into.
The biggest issue is that there is no official tool to have a list of files that are currently deduped (I had to write a script that parse the output of zdb) so if you're not sure you have to move all the data out of the dataset.
There is some ideological complexity, namely the ARC (in implementation, not in algorithm) is a pretty big wart. It works well when properly dressed, but it is pretty hard to make work well with most OS kernel allocators and memory pressure.
>"ZFS is not affected by the RAID-5 write hole."
The "write hole" can exists in other RAID configurations such as RAID 10, RAID 1, RAID 6 etc. Is ZFS only insulated from these "write holes" in RAID 5 or all ZFS supported RAID configurations?
ZFS's stripe-mirror vdev is mirrored pair with a stripe written across those mirrors. How is that not actual RAID? Is that not RAID 10 with the ZFS addition of checksumming?
But I was to some extent conflating the different redundancy methods ZFS has. Some of them use the same sectors on each disk (I think) but some definitely don't. The writes are completely independent of each other under some settings.
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?
My own experiments put tokudb far far ahead:
https://williamedwardscoder.tumblr.com/post/160628887798/com...
Unfortunately tokudb has taken a massive performance hit recently - it was so fast when tokutek integrated it into 5.6 but the percona and Mariadb packaging in 5.7 is much slower than innodb - not sure what they haven't done but wish they'd profile and fix it - you'd think that percona, having brought tokutek - would have noticed and benchmarked and care :(
Sometimes a slowdown comes because somebody noticed a problem and fixed it, such as the default compile options including a nasty bug that makes it unsafe. I don't have any specific knowledge of this case, but it the kind of thing I've seem happen multiple times in the past, so I wouldn't be surprised.
I'm looking forward to them myself.
I believe Facebook heavily uses btrfs, which has a similar design and feature set. But they may only use it for object storage.
Did they try using it for MySQL databases?
I'd say add a bit of extra RAM for ARC overhead you wouldn't usually, and of course invest in SLOG devices.
ZFS is never going to win any speed races, but what we gain in snapshotting/rollback/trivial remote backups makes it a great fit for many of our pgsql/mysql installs.
This approach makes sense for Aurora because it leverages cloud storage to provide data high availability across availability zones. Just putting InnoDB in a file system seems less interesting unless you add features that are strikingly different from what it does now.
See http://www.allthingsdistributed.com/files/p1041-verbitski.pd... for a full description.