Bug #21947 innodb_flush_method = O_DIRECT may degrade performance of InnoDB on SAN
Submitted: 31 Aug 2006 13:22 Modified: 6 Sep 2006 16:36
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[31 Aug 2006 13:22] Valeriy Kravchuk
Description:
Our manual does not contain any warnings agains usage of O_DIRECT, neither at:

http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html

nor at:

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html

"- innodb_flush_method

If set to fdatasync (the default), InnoDB uses fsync() to flush both the data and log files. If set to O_DSYNC, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the data files. If O_DIRECT is specified (available on some GNU/Linux versions), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB uses fsync() instead of fdatasync(), and it does not use O_DSYNC by default because there have been problems with it on many varieties of Unix. This variable is relevant only for Unix. On Windows, the flush method is always async_unbuffered and cannot be changed."

While on on a EMC SAN connected via Fibre Channel through a QLogic FC adapter (qla2300), for example, using O_DIRECT can slow down simple SELECTs  3 times.

How to repeat:
Try to use O_DIRECT when InnoDB data files and logs are on SAN

Suggested fix:
Add warnings to the manual
[6 Sep 2006 16:36] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Note added to innodb_flush_method description:

Different values of this variable can have a marked effect on InnoDB
performance. For example, on some systems where InnoDB data and log
files are located on a SAN, it has been found that setting
innodb_flush_method to O_DIRECT can degrade performance of simple
SELETE statements by a factor of three.