Changes

Performance tuning

6 bytes added, 17:14, 1 February 2019
m
InnoDB: typo fix
Make separate datasets for InnoDB's data files and log files. Set recordsize=16K on InnoDB's data files to avoid expensive partial record writes and leave recordsize=128K on the log files. Set primarycache=metadata on both to prefer InnoDB's caching.[https://www.patpro.net/blog/index.php/2014/03/09/2617-mysql-on-zfs-on-freebsd/] Set logbias=throughput on the data to stop ZIL from writing twice.
Set skip-innodb_doublewrite in my.cnf to prevent innodb from writing twice. The double writes are a data integrity feature meant to protect against corruption from partially-written records, but those are not possible on ZFS. It should be noted that Percona’s blog[https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/] had advocated using an ext4 configuration where double writes were turned off for a performance gain, but later recanted it because it caused data corruption. Following a well timed power failure, an in place filesystem such as ext4 can have half of a 8KB record be old while the other half would be new. This would be the corruption that caused Percona to recant its advice. However, ZFS’ copy on write design would cause it to return the old correct data following a power failure (no matter what the timing is). That prevents the corruption that the double write feature is intended to prevent from ever happening. The double write feature is therefore unnecessary on ZFS and can be safely turned off for better performance.
On Linux, the driver's AIO implementation is a compatibility shim that just barely passes the POSIX standard. InnoDB performance suffers when using its default AIO codepath. Set innodb_use_native_aio=0 and innodb_use_atomic_writes=0 in my.cnf to disable AIO. Both of these settings must be disabled to disable AIO.
Editor
348
edits