MariaDB on ZFS: Difference between revisions

From Lolly's Wiki
Jump to navigationJump to search
No edit summary
m (Text replacement - "</source" to "</syntaxhighlight")
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
[[Kategorie: MySQL|ZFS]]
[[Category: MySQL|ZFS]]
[[Kategorie: MariaDB|ZFS]]
[[Category: MariaDB|ZFS]]
 
==ZFS parameters==
==ZFS parameters==
<source lang=bash>
 
<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
</syntaxhighlight>
 
 
<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 11: Line 29:
   128K          all      lz4  1.00x    off  MYSQL-LOG/binlog
   128K          all      lz4  1.00x    off  MYSQL-LOG/binlog
   128K      metadata      lz4  2.17x    off  MYSQL-LOG/ib_log
   128K      metadata      lz4  2.17x    off  MYSQL-LOG/ib_log
</source>
</syntaxhighlight>


===If you have innodb_file_per_table=on===
===If you have innodb_file_per_table=on===
<source lang=bash>
<syntaxhighlight lang=bash>
# mysql -e 'show variables like "innodb_file_per_table";'
# mysql -e 'show variables like "innodb_file_per_table";'
+-----------------------+-------+
+-----------------------+-------+
Line 21: Line 39:
| innodb_file_per_table | ON    |
| innodb_file_per_table | ON    |
+-----------------------+-------+
+-----------------------+-------+
</source>
</syntaxhighlight>
* 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 :-\.*
* 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
* consider setting the initial innodb_data_file_path to smaller value like ibdata1:100M:autoextend


==Database parameters for ZFS==
==Database parameters for ZFS==
<source lang=mysql>
<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
innodb_data_file_path          = ibdata1:2000M:autoextend
innodb_data_file_path          = ibdata1:2000M:autoextend
innodb_log_group_home_dir      = /MYSQL-LOG/ib_log
innodb_log_group_home_dir      = /MYSQL-LOG/ib_log
innodb_flush_method            = O_DIRECT
#innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table          = off
skip-innodb_doublewrite
skip-innodb_doublewrite
</source>
</syntaxhighlight>


<source lang=bash>
<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:
--server_id=42
--server_id=42
--replicate_do_db=mail_db
--replicate_wild_ignore_table=mail_db.bayes_%
--user=mysql
--user=mysql
--pid-file=/var/run/mysqld/mysqld.pid
--pid-file=/var/run/mysqld/mysqld.pid
Line 55: Line 72:
--skip-innodb_doublewrite
--skip-innodb_doublewrite
--tmpdir=/tmp
--tmpdir=/tmp
</source>
</syntaxhighlight>
 
On Linux do not forget to add new directories to apparmor!

Latest revision as of 02:33, 26 November 2021


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!

Retrieved from "https://lars.timmann.de/wiki/index.php?title=MariaDB_on_ZFS&oldid=2496"