Comments on: ZFS from a MySQL perspective

By: chris scott

chris scott — Wed, 15 Aug 2018 13:10:53 +0000

In reply to architectonic. Dont forget arc is compressed now as well

By: takaidohigasi

takaidohigasi — Tue, 09 Jan 2018 22:43:57 +0000

thank you for your nice post!

I’d like to introduce this article for users in Japan by translating this article into Japanese.
Would you mind if I publish translation?

By: michael morse

michael morse — Fri, 24 Nov 2017 00:28:39 +0000

In reply to Yves Trudeau. good information, would be great to see some benchmarks playing with a few different options and workloads.

By: Yves Trudeau

Yves Trudeau — Thu, 23 Nov 2017 22:05:40 +0000

In reply to michael morse. The only reason to use O_DIRECT is to avoid double buffering. With ZFS, if you limit the size of the ARC or runs the filesystem where the InnoDB data is located with primarycache=metadata, you do limit the double buffering.

By: michael morse

michael morse — Thu, 23 Nov 2017 21:13:11 +0000

Transparent/native filesystem compression and of course no doublewrite buffering need are nice tangible wins which are readily seen/appreciated in a production environment. There is the downside of no O_DIRECT support (I assume fcntl(fd, F_SETFL, O_DIRECT) is still not supported?). From a practical point of view these days, mysql seems to be able to take the option (at least the environments I’ve been in), but will eventually lead to a corrupted instance which I assume is due to that lack of support.

Would be interesting to explore, or hear from someone, the tradeoffs more in depth and whether ZFS being COW makes lack of O_DIRECT support less (or maybe more) of a downside (with various workloads) for applications with their own buffering management like mysql.

By: SuperQ

SuperQ — Wed, 22 Nov 2017 20:18:36 +0000

In reply to Yves Trudeau. Nice, that's pretty good. Although, the shell code isn't bad, the GREP=`which grep` pattern you're using is oddly unnecessary. If you're relying on the PATH to find grep, just call grep.

By: SuperQ

SuperQ — Wed, 22 Nov 2017 20:12:57 +0000

In reply to Phil Stracchino. Cool, when I was still doing this stuff, we used xtrabackup and mydumper to get consistent restorable data. We had a fully automated system that would allow async nodes to be bootstrapped by an automated provisioning system that would restore the xtrabackup data from HDFS. We had a manual process for doing the mydumper restores. Be careful with mydumper and galera, we had too many issues with read pressure causing write stalls. We ended up using async (GTID) nodes for doing SQL dump backups even with galera.

By: Yves Trudeau

Yves Trudeau — Wed, 22 Nov 2017 19:16:43 +0000

In reply to SuperQ. Like a thousand times :) My scripts are in https://github.com/y-trudeau/Yves-zfs-tools. The mysql-zfs-snap.sh creates a snapshot with the master position in a file in the snapshot. The mk-mysql-clone.sh creates clone based MySQL slaves from the snapshots created by the previous script. Both script have run for a long time under cron.

By: Yves Trudeau

Yves Trudeau — Wed, 22 Nov 2017 19:11:47 +0000

In reply to architectonic. The comparison is limited to a memory cache...

By: Phil Stracchino

Phil Stracchino — Wed, 22 Nov 2017 16:35:53 +0000

In reply to SuperQ. I did a number of test restores with good results. It did trigger an InnoDB crash recovery because the files were captured in unclean state, but before-and-after verification showed no data was lost. However, I'm no longer using that backup method because I'm no longer running a DB node on Solaris. Currently I'm running a three-node Galera cluster and backing it up using mydumper.

By: SuperQ

SuperQ — Wed, 22 Nov 2017 11:35:32 +0000

In reply to Phil Stracchino. How often do you test restores from that? Have you used those backups to create additional async replicas?

By: architectonic

architectonic — Fri, 17 Nov 2017 21:30:17 +0000

nice post thanks! I can hardly wait for the next.
How can we compare the ARC to the innodb buffer pool in such a setup? Most of the tutorials on the web would favor innodb buffer pool and assign it the lion’s share of RAM. However the ARC is more advanced and is not a simple LRU cache, and this can be beneficial for servers with several databases so that rows touched once do not evict ones used frequently. Do you have an opinion on this topic?

By: Jiawei Zhao

Jiawei Zhao — Fri, 17 Nov 2017 15:26:33 +0000

Mysql on ZFS+SSD is amazing as well, one can use lz4 compression on ZFS, and raidzX is fast enough compared to have to use raid10 with mechanical disks for most use cases. This allows upgrading from mechanical disks to a full ssd setup with more capacity and lower cost, but 10x more performance.

By: Yves Trudeau

Yves Trudeau — Thu, 16 Nov 2017 18:51:14 +0000

In reply to Mark Callaghan. You'll certainly be interested by how the writes are distributed between the SLOG and the data device.

By: Pavel Katiushyn

Pavel Katiushyn — Thu, 16 Nov 2017 08:31:55 +0000

Very interesting! The next posts will definitely attract my attention!

By: Mark Callaghan

Mark Callaghan — Wed, 15 Nov 2017 23:30:33 +0000

This is a nice first post. I look forward to more.

By: Phil Stracchino

Phil Stracchino — Wed, 15 Nov 2017 15:49:32 +0000

When I was running my primary DB on a Solaris 10 box, I used ZFS to perform my MySQL backups. The process was very simple:

1. Flush tables with read lock
2. Snapshot the DB data filesystem
3. Release the read lock
4. Clone the snapshot
5. Mount the clone
6. Perform file-level backup of the cloned snapshot
7. Unmount the cloned snapshot
8. Free the snapshot and clone

Steps 1-3 took literally a couple of seconds.