Changes

Jump to: navigation, search

Performance tuning

684 bytes added, 17:09, 1 February 2019
m
Replace corruption warning with explanation of why ext4 risks corruption without double write protection, but ZFS does not,
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 also writing twice. The double writes are a data integrity feature meant to protect against corruption from partially-written pagesrecords, 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/] WARNING: The source now sayshad advocated using an ext4 configuration where double writes were turned off for a performance gain, "Update: do not do thisbut later recanted it because it caused data corruption. Following a well timed power failure, this has been proven an in place filesystem such as ext4 can have half of a 8KB record be old while the other half be new. This would be the corruption that caused Percona to corrupt 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
301
edits

Navigation menu