Arch Linux

Smith oo4

Member

From: Calgary, Alberta, Canada

Registered: 2007-12-30

Posts: 42

Mariadb and ZFS

Does anyone have some experience with Mariadb and ZFS? I have found lots of articles online going over the theory of tuning ZFS and Mysql/Mariadb to work well together, below are some that looked the best to me:

https://openzfs.github.io/openzfs-docs/ … ysql#mysql
https://www.usenix.org/system/files/log … 9_jude.pdf
https://shatteredsilicon.net/blog/2020/ … db-on-zfs/

However, I can't seem to find any good examples on how to set this up and I don't seem smart enough to figure this out on my own. This is especially true Mariadb tuning.

I think I get the  ZFS part. Below is what I got let me know where I when wrong:

sudo zfs create -o mountpoint=/var/lib/mysql tank/mysql   #datasets for InnoDB’s data files
sudo zfs set compression=lz4 atime=off relatime=off recordsize=16k primarycache=metadata logbias=throughput tank/mysql

sudo zfs create -o mountpoint=/var/lib/mysql tank/mysql-log   #datasets for log files
sudo zfs set compression=lz4 atime=off relatime=off primarycache=metadata tank/mysql-log

The tuning of Mariadb is where I get lost. I will be creating a new database, so I hope that helps. Do I need to edit my.cnf file first or run mysql_install_db first? When I run  mysql_install_db do I need to include special flags other than the typical:

sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

I can't fine any flag for "logdir".

As for my.cnf I am guessing the best thing to would be to create a new file /etc/my.cnf.d/zfs.cnf. As for what go in this file I am not sure where to begin.

Thank you for your help

Awebb

Member

Registered: 2010-05-06

Posts: 6,688

Re: Mariadb and ZFS

https://www.percona.com/blog/2017/12/07 … ith-mysql/ ?

Smith oo4

Member

From: Calgary, Alberta, Canada

Registered: 2007-12-30

Posts: 42

Re: Mariadb and ZFS

OK, spent some time trying to follow the example and doing some googling to fill in the blanks and the part that did not line up. Below is what I got for creating a new, empty database with Mariadb on ZFS:

Create the ZFS datasets

sudo zfs create -o mountpoint=/var/lib/mysql tank/mysql   #datasets for InnoDB’s data files
sudo zfs set compression=lz4 atime=off relatime=off recordsize=16k primarycache=metadata logbias=throughput tank/mysql

sudo zfs create -o mountpoint=/var/lib/mysql-log tank/mysql-log   #datasets for log files
sudo zfs set compression=lz4 atime=off relatime=off primarycache=metadata tank/mysql-log

Install Mariadb and set the file permissions and ownership on each dataset

sudo pacman -Syu mariadb

sudo chown -R mysql:mysql /var/lib/{mysql,mysql-log}
sudo chmod -R 700 /var/lib/{mysql,mysql-log}

Move the Mariadb error log to syslog

sudo mkdir -p /etc/systemd/system/mariadb.service.d/

sudo tee /etc/systemd/system/mariadb.service.d/syslog.conf <<EOF
[Service]

StandardOutput=syslog
StandardError=syslog
SyslogFacility=daemon
SysLogLevel=err
EOF

sudo systemctl daemon-reload

create zfs.cnf to tune Mariadb for ZFS

sudo tee /etc/my.cnf.d/zfs.cnf <<EOF
[mariadb]

datadir=/var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql-log
innodb_log_write_ahead_size = 16384

innodb_doublewrite = 0
innodb_checksum_algorithm = none
innodb_use_native_aio = 0
innodb_use_atomic_writes = 0

aria-log-dir-path = /var/lib/mysql-log

log_bin = /var/lib/mysql-log/binlog
relay_log=/var/lib/mysql-log/relay-bin
expire_logs_days=7

pid-file=/var/run/mysqld/mysqld.pid
EOF

Install and secure an empty database

sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

sudo systemctl enable --now mariadb && sudo systemctl status mariadb

sudo mysql_secure_installation

Please let me know any corrections, suggestions, or if I have just done something scary wrong.

Thank you