A few remarks about MySQL and ZFS
If you‘ve installed MySQL using the SmartOS-repo (like the amp-script does) the default data-directory of MySQL is „/var/mysql“. It is a regular directory on a ZFS-filesystem (usually rpool).
For SOHO requirements it‘s perfectly good and normally there’s no need to change it, but there are some options to improve this configuration (i. e. modifying the blocksize, MySQL uses 16k, ZFS uses 128k per default).
I found
this article and
this presentation about best practises for using MySQL with ZFS. In general these guides are for professional use, therefore not every advice is an option for SOHO systems. But there are 3 modifications left, which IMHO are always useful for SOHO systems, even for the smallest one:
(1) Create ZFS-filesystems for MySQL
(2) Set primarycache = metadata
(3) Skip InnoDB double write
What I did:
(1) Create BootEnvironment
(2) Stop MySQL daemon
(3) Move MySQL original data-directory
(4) Create two ZFS-filesystems with different blocksizes (for InnoDB and Logs)
(5) Set user privilegs
(6) Copy and edit MySQL config-file
Code:
Code:
beadm create pre_mysql_mod
mv /var/mysql /var/mysql.org
zfs create -o recordsize=16k -o primarycache=metadata -o mountpoint=/var/mysql rpool/export/home/mysql
zfs create -o recordsize=128k rpool/export/home/mysql/.iblogs
chown -R mysql:mysql /var/mysql
cp /opt/local/my.cnf /opt/local/etc
Creating „iblogs“ as hidden („.iblogs“) prevents the directory to be shown (incorrectly as a database) in phpMyAdmin. Next I had to edit the MySQL config-file „/opt/local/etc/my.cnf“ to tell MySQL where to find needed directories:
Code:
basedir = /opt/local
datadir = /var/mysql
innodb_data_home_dir = /var/mysql
innodb_log_group_home_dir = /var/mysql/.iblogs
skip-innodb_doublewrite
Finally I initialized the new created MySQL directories (which are ZFS-filessystems now), started the MySQL daemon and reperformed mysql_secure_installation:
Code:
mysql_install_db --user=mysql
svcadm enable mysql
/opt/local/bin/mysql_secure_installation