Difference between revisions of "Performance tuning"

Jump to navigation Jump to search
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,
(Delete MyISAM advice. It is reportedly ineffective due to deficiencies in MyISAM.)
m (Replace corruption warning with explanation of why ext4 risks corruption without double write protection, but ZFS does not,)
Line 167: Line 167:
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.
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 partially-written pages, but those are not possible on ZFS.[https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/] WARNING: The source now says, "Update: do not do this, this has been proven to corrupt data!"
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 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.
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

Navigation menu