lundi 7 mars 2011

Solaris + ZFS + MySQL + InnoDB performance tweaks

So I've got a relatively cheap ZFS system running in my garage, acting as a MySQL database server for data mining... It's running Solaris 11 Express with MySQL, Apache and PHP on it.

ZFS is a great filesystem, but you can make it better with some performance tweaks (besides using mirrored SSD's for your ZIL and L2ARC).
  • Compress your database pool(s):
  • zfs set compression=lzjb data/mysql
    zfs set compression=lzjb logs/mysql
  • Set the ZFS recordsize to match the InnoDB block size (16KB for InnoDB data files, and 128KB for InnoDB log files) :
  • zfs set recordsize=16K data/mysql
    zfs set recordsize=128K logs/mysql 
  • Limit ARC cache to < 80 % total RAM (in my case, 10GB for 16GB total RAM)
  • vi /etc/system:
         set zfs:zfs_arc_max = 10737418240
Use the following my.cnf directives:
[mysqld]

# use one large data file on one ZFS pool, with large autoextend
# /var/lib/mysql is symlinked to /data/mysql, a ZFS pool
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 128

# put logs in a separate ZFS pool
innodb_log_group_home_dir=/logs/mysql

# use one file per table
innodb_file_per_table=1

# not too high (default=unlimited) to avoid thread trashing
innodb_thread_concurrency=16

# use 2GB because we have 16GB and ZFS needs a lot of RAM. I would use 4GB but MySQL does not accept a total log size > 4GB, which is abnormal for 64-bit...
innodb_buffer_pool_size=2G

# 25% to 50% of the buffer pool size to avoid buffer pool flush on log file overwrite
innodb_log_file_size=1G

# log buffers are flushed every second, so there's no reason to have a big buffer
innodb_log_buffer_size=8M

# not very important, InnoDB will get more RAM from OS if needed but warns in log
innodb_additional_mem_pool_size=256M

# compromise performance for full ACID compliance
innodb_flush_log_at_trx_commit=1

# traditional auto-increment
innodb_autoinc_lock_mode=0

# disable DNS lookups
skip-name-resolve

Aucun commentaire:

Publier un commentaire