MariaDB on ZFS: Difference between revisions
From Lolly's Wiki
Jump to navigationJump to search
No edit summary |
No edit summary |
||
| Line 1: | Line 1: | ||
[[Kategorie: MySQL|ZFS]] | [[Kategorie: MySQL|ZFS]] | ||
[[Kategorie: MariaDB|ZFS]] | [[Kategorie: MariaDB|ZFS]] | ||
==ZFS parameters== | |||
<source lang=bash> | <source 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 | ||
| Line 12: | Line 13: | ||
</source> | </source> | ||
===If you have innodb_file_per_table=on=== | |||
<source 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== | |||
<source lang=mysql> | <source lang=mysql> | ||
datadir = /MYSQL-DATA/data/mysql | datadir = /MYSQL-DATA/data/mysql | ||
| Line 20: | Line 34: | ||
innodb_flush_log_at_trx_commit = 2 | innodb_flush_log_at_trx_commit = 2 | ||
skip-innodb_doublewrite | skip-innodb_doublewrite | ||
</source> | |||
<source lang=bash> | |||
# /usr/sbin/mysqld --print-defaults | |||
/usr/sbin/mysqld would have been started with the following arguments: | |||
--server_id=42 | |||
--replicate_do_db=mail_db | |||
--replicate_wild_ignore_table=mail_db.bayes_% | |||
--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> | </source> | ||
Revision as of 13:59, 21 October 2016
ZFS parameters
# 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
skip-innodb_doublewrite
# /usr/sbin/mysqld --print-defaults
/usr/sbin/mysqld would have been started with the following arguments:
--server_id=42
--replicate_do_db=mail_db
--replicate_wild_ignore_table=mail_db.bayes_%
--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
