How to Configure MySQL with ZFS

How to Configure MySQL With ZFS

ZFS is similar to other POSIX file systems so the process of installing and creating MySQL databases on ZFS file systems is identical to setting up MySQL databases on other file systems. However, reviewing some MySQL tuning and ZFS features and configuration can improve the overall MySQL and ZFS experience.

Many excellent resources are already available for configuring MySQL with ZFS. See the following links:

• Mr.Benchmark
• Optimizing MySQL Performance with ZFS
• ZFS Best Practices

This procedure summarizes MySQL and ZFS configuration information from these resources.

Before You Begin

To complete this procedure, you need:

• OpenSolaris release
• Download and install MySQL from an OpenSolaris IPS repository by using these instructions:
  http://wikis.sun.com/display/OpenSolarisInfo/Installing+MySQL%2C+PostgreSQL
  Or, you can download MySQL from this location: http://dev.mysql.com/downloads/

Steps

1. Create a mirrored pool rather than a RAID-Z pool.
   For example:

   # zpool create tank mirror c1t1d0 c2t1d0 mirror c1t2d0 c2t2d0
   

   RAID-Z is not currently recommended for random I/O workloads.

2. Review the following optional ZFS pool configuration recommendations that might match your configuration needs.
   • Consider adding cache (L2ARC) devices to reduce the buffer cache penality if your workload would benefit from this enhancement.

     # zpool add tank cache c1t3d0 c2t3d0
     

   • Consider adding a separate log device to improve synchronous transaction performance if your workload would benefit from this enhancement.

     # zpool add tank log mirror c1t4d0 c2t4d0
     

     Currently, you cannot remove a log device after it is added.

   • Consider creating a separate pool for database log files.

     # zpool create dblog mirror c1t5d0 c2t5d0
     

3. Create a ZFS file system for each database and set needed file system properties.
   • Set the ZFS recordsize to match the database recordsize. For example, Innodb manages data in 16-Kbyte pages.

     # zfs create -o recordsize=16K tank/db1
     

     For improved OLTP performance, match the ZFS recordsize to the storage engine block size.

   • For Innodb, cache only metadata.

     # zfs set primarycache=metadata tank/db1
     

4. Create your databases after the ZFS file systems are created.
   • Keep in mind that if you set recordsize or compression after the data is created, then only new files will be written to the new recordsize or will be compressed.
5. Set database characteristics for your environment.
   For example, consider disabling innodb_doublewritebuffer to remove redundant writes.
6. Consider creating ZFS snapshots of your data. Quiesce the database before taking the snapshot. The process is as follows:
   • Instruct MySQL to flush database tables with read lock
   • Take a ZFS snapshot of the database file system. For example:

     # zfs snapshot tank/db1@0508
     

   • Unlock database tables
7. Consider the following tuning parameters after reviewing the appropriate cautions.
   • zfs_nocacheflush
   • zfs_prefetch_disable

Related Topics

• ZFS Administration Guide
• ZFS Tuning Guide