Although tuning with ZFS may be considered taboo by some, Neelakanth Nadgir takes on the task and shares how to get even better performance from MySQL Innodb with ZFS that includes fixing some performance bugs.

There are seven tuning tips. The brief rundown of these follows:

1. Match ZFS recordsize with Innodb page size (16KB for Innodb Datafiles, and 128KB for Innodb log files).

2. If working with a write heavy workload, use a Seperate ZFS Intent Log.

3. If the database working set size does not fit in memory, get a big boost by using a SSD as L2ARC.

4. While using storage devices with battery backed caches or while comparing ZFS with other filesystems, turn off the cache flush.

5. Prefer to cache within MySQL/Innodb over the ZFS Adaptive replacement cache (ARC).

6. Disable ZFS prefetch.

7. Disable Innodb double write buffer.

Each of these is explained in detail. Nadgir's provides how to do each of the above listed tasks and offers reasons why performance will be boosted.

More Information

MySQL Innodb ZFS Best Practices - Nadgir's blog entry

ZFS & MySQL/InnoDB Compression Update

Maximizing Performance of MySQL on the Solaris Platform

Tuning the MySQL InnoDB to Maximize Performance on Solaris OS

MySQL - product page

ZFS - product page [...read more...]

		fullsource
Print Email Current Profile Archive Blog Generate newsletter