MariaDB on ZFS

From Lolly's Wiki
Jump to navigationJump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.


ZFS parameters

zfs set atime=off             MYSQL-DATA
zfs set compression=lz4       MYSQL-DATA

zfs set atime=off             MYSQL-LOG
zfs set compression=lz4       MYSQL-LOG

zfs set recordsize=8k         MYSQL-DATA/data

zfs set recordsize=16k        MYSQL-DATA/InnoDB
zfs set primarycache=metadata MYSQL-DATA/InnoDB

zfs set primarycache=metadata MYSQL-LOG/ib_log


# zfs list -o recordsize,primarycache,compression,compressratio,atime,name -r MYSQL-DATA -r MYSQL-LOG
RECSIZE  PRIMARYCACHE  COMPRESS  RATIO  ATIME  NAME
   128K           all       lz4  1.06x    off  MYSQL-DATA
    16K      metadata       lz4  2.81x    off  MYSQL-DATA/InnoDB
     8K           all       lz4  1.05x    off  MYSQL-DATA/data
   128K           all       lz4  2.15x    off  MYSQL-LOG
   128K           all       lz4  1.00x    off  MYSQL-LOG/binlog
   128K      metadata       lz4  2.17x    off  MYSQL-LOG/ib_log

If you have innodb_file_per_table=on

# mysql -e 'show variables like "innodb_file_per_table";'
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
  • If you have only InnoDB-Tables or the only productive ones are InnoDB then consider setting the blocksize of MYSQL-DATA/data to 16k because all Innodb-Datafiles (*.ibd) will be written there :-\.*
  • consider setting the initial innodb_data_file_path to smaller value like ibdata1:100M:autoextend

Database parameters for ZFS

datadir                         = /MYSQL-DATA/data/mysql
innodb_data_home_dir            = /MYSQL-DATA/InnoDB
innodb_data_file_path           = ibdata1:2000M:autoextend
innodb_log_group_home_dir       = /MYSQL-LOG/ib_log
#innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = off
skip-innodb_doublewrite
# /usr/sbin/mysqld --print-defaults
/usr/sbin/mysqld would have been started with the following arguments:
--server_id=42
--user=mysql
--pid-file=/var/run/mysqld/mysqld.pid
--socket=/var/run/mysqld/mysqld.sock
--port=3306
--basedir=/usr
--datadir=/MYSQL-DATA/data/mysql
--innodb_data_home_dir=/MYSQL-DATA/InnoDB
--innodb_data_file_path=ibdata1:100M:autoextend
--innodb_log_group_home_dir=/MYSQL-LOG/ib_log
--innodb_flush_method=O_DIRECT
--innodb_flush_log_at_trx_commit=2
--skip-innodb_doublewrite
--tmpdir=/tmp

On Linux do not forget to add new directories to apparmor!