MySQL On ZFS On Ubuntu

Posted on | In

MySQL on ZFS on Ubuntu

For Ubuntu 16.04, ZFS was support natively, simply:

sudo apt install zfs

为了安装Typecho，需要安装MySQL和Apache （Nginx对Typecho的url支持有些问题，网上有教程，太复杂，直接Apache）。希望MySQL的数据库安装在ZFS上，由于InnoDB和ZFS缺省的Block Size不一样，可能有性能的问题（其实对个人网站影响可以忽略不计，生命贵在折腾），于是有以下的总结（参考www.antanst.com） ：

DO NOT use RAID-Z. RAID-Z unsuitable for random IO;
InnoDB
InnoDB uses a 16k block size for data and 128k for logs.
InnoDB uses it's own cache, so we force ZFS to cache metadata only to avoid double-caching.
InnoDB has a double write mechanism, which is unnecessary with ZFS.
Limit the ARC to prefer cache inside MySQL/Innodb.

Limiting the ARC in Linux: In /etc/modprobe.d/zfs.conf (max set as 80% of total memory):

# Min 2048MB / Max 8192 MB Limit
options zfs zfs_arc_min=2147483648
options zfs zfs_arc_max=8589934592

Reboot and check via the following commands:

# cat /proc/spl/kstat/zfs/arcstats |grep c_
# cat /sys/module/zfs/parameters/zfs_arc_min
# cat /sys/module/zfs/parameters/zfs_arc_max
Use ZFS volumes for InnoDB data and log files:

We use default recordsize of 128k for log files, and 8k record size for data files.

zfs create -o compression=lz4 -o recordsize=16k -o primarycache=metadata tank/mysql-innodb-data
zfs create -o compression=lz4 -o recordsize=128k -o primarycache=metadata tank/mysql-innodb-logs
Force ZFS to cache metadata only:
# zfs set primarycache=metadata tank/mysql-innodb-data
Create and use separate ZFS volumes for data files and logs:

In my.cnf:

innodb_data_home_dir = /tank/mysql-innodb-data
innodb_log_group_home_dir = /tank/mysql-innodb-logs
Disable InnoDB’s double write mechanism:

Innodb provides checksum and compression, but so does ZFS, and it also heals automatically. In my.cnf:

innodb_doublewrite=0
Important note about InnoDB:

If you have the innodb_file_per_table parameter enabled, the innodb_data_home_dir parameter is ignored, and the databases are stored in datadir/ directory. So, you’ll have to create separate ZFS filesystems for each database in this case.