8.2 MySQL Performance Standard Rules

The following are the MySQL Performance Standard compliance rules:

• InnoDB Flush Method May Not Be Optimal

• InnoDB Log Buffer Flushed To Disk After Each Transaction

InnoDB Flush Method May Not Be Optimal

Description Different values for innodb_flush_method can have a marked effect on InnoDB performance. In some versions of GNU/Linux and Unix, flushing files to disk by invoking fsync() (which InnoDB uses by default) or other similar methods, can be surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DIRECT or O_DSYNC.

Severity Minor Warning

Advice Review your setting of the innodb_flush_method variable based on your application, operating system, and storage environment. It is currently set to %flush_method% . The default ( fdatasync ) may be best. O_DIRECT can be good for I/O, especially within "local filesystems", as it also avoids double-write buffering. However, O_DIRECT is bad for network attached storage such as SAN/NFS. O_DSYNC can cause extra overhead above the default of fdatasync and there have been problems with it on many varieties of Unix. However, at least one user has reported that using O_DSYNC on NetBSD makes a huge difference.

InnoDB Log Buffer Flushed To Disk After Each Transaction

Description By default, InnoDB's log buffer is written out to the log file at each transaction commit and a flush-to-disk operation is performed on the log file, which enforces ACID compliance. In the event of a crash, if you can afford to lose a second's worth of transactions, you can achieve better performance by setting innodb_flush_log_at_trx_commit to either 0 or 2. If you set the value to 2, then only an operating system crash or a power outage can erase the last second of transactions. This can be very useful on slave servers, where the loss of a second's worth of data can be recovered from the master server if needed.

Severity Minor Warning

Advice Set innodb_flush_log_at_trx_commit=2 in your my.cnf/my.ini file and restart your MySQL server. WARNING: A value of 1 is required for ACID compliance. If you set the value to 2, then an operating system crash or a power outage may erase the last second of transactions. This may not be critical for your application or environment, though, especially if this is a slave server and the loss of a second's worth of data can be recovered from the master.