MariaDB on ZFS: Difference between revisions
No edit summary |
m (Text replacement - "<source" to "<syntaxhighlight") |
||
| Line 4: | Line 4: | ||
==ZFS parameters== | ==ZFS parameters== | ||
< | <syntaxhighlight lang=bash> | ||
zfs set atime=off MYSQL-DATA | zfs set atime=off MYSQL-DATA | ||
zfs set compression=lz4 MYSQL-DATA | zfs set compression=lz4 MYSQL-DATA | ||
| Line 20: | Line 20: | ||
< | <syntaxhighlight lang=bash> | ||
# zfs list -o recordsize,primarycache,compression,compressratio,atime,name -r MYSQL-DATA -r MYSQL-LOG | # zfs list -o recordsize,primarycache,compression,compressratio,atime,name -r MYSQL-DATA -r MYSQL-LOG | ||
RECSIZE PRIMARYCACHE COMPRESS RATIO ATIME NAME | RECSIZE PRIMARYCACHE COMPRESS RATIO ATIME NAME | ||
| Line 32: | Line 32: | ||
===If you have innodb_file_per_table=on=== | ===If you have innodb_file_per_table=on=== | ||
< | <syntaxhighlight lang=bash> | ||
# mysql -e 'show variables like "innodb_file_per_table";' | # mysql -e 'show variables like "innodb_file_per_table";' | ||
+-----------------------+-------+ | +-----------------------+-------+ | ||
| Line 44: | Line 44: | ||
==Database parameters for ZFS== | ==Database parameters for ZFS== | ||
< | <syntaxhighlight lang=mysql> | ||
datadir = /MYSQL-DATA/data/mysql | datadir = /MYSQL-DATA/data/mysql | ||
innodb_data_home_dir = /MYSQL-DATA/InnoDB | innodb_data_home_dir = /MYSQL-DATA/InnoDB | ||
| Line 55: | Line 55: | ||
</source> | </source> | ||
< | <syntaxhighlight lang=bash> | ||
# /usr/sbin/mysqld --print-defaults | # /usr/sbin/mysqld --print-defaults | ||
/usr/sbin/mysqld would have been started with the following arguments: | /usr/sbin/mysqld would have been started with the following arguments: | ||
Revision as of 23:12, 25 November 2021
ZFS parameters
<syntaxhighlight lang=bash> 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 </source>
<syntaxhighlight lang=bash>
- 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
</source>
If you have innodb_file_per_table=on
<syntaxhighlight lang=bash>
- mysql -e 'show variables like "innodb_file_per_table";'
+-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ </source>
- 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
<syntaxhighlight lang=mysql> 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 </source>
<syntaxhighlight lang=bash>
- /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 </source>
On Linux do not forget to add new directories to apparmor!
