MySQL Bugs: #54306: Docs issue - Advice on innodb_flush

Bug #54306	Docs issue - Advice on innodb_flush_method can be misinterpreted
Submitted:	7 Jun 2010 19:26	Modified:	2 Sep 2011 5:06
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1, 5.4, 5.5	OS:	Any
Assigned to:	John Russell	CPU Architecture:	Any

Description:
I have an issue with this manual page:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method

"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 SELECT statements by a factor of three. "

I think this paragraph is worded awkwardly, because it makes it sound like if you have a SAN or hardware RAID you should not be using O_DIRECT. My experience has been that:

- If you don't have hardware raid, you probably do *not* want O_DIRECT, because you still need some of the request merging that a good IO scheduler can provide.

- If you have a hardware raid controller you probably *do* want O_DIRECT, since you avoid double buffering, and the RAID controller does an intelligent job of merging requests when battery backed.

- If you have a SAN device you probably *do* want O_DIRECT.

I can understand that there was probably a back-story that led to this exception being documented as it is, but the problem is that with the current wording it's too easy to interpret the exception as the rule.

How to repeat:
Run any sysbench on a system with a Hardware raid controller, decent scheduler option (deadline, noop) and try with O_DIRECT/without.

Suggested fix:
Try for something like the following:

"Depending on hardware configuration, setting innodb_flush_method to O_DIRECT can either have either a positive or negative effect on performance. In the typical case a system with a hardware RAID controller and battery backed write cache O_DIRECT should contribute positively to performance, and avoid double buffering between the innodb_buffer_pool_size and the Operating System's filesystem cache. However, 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 SELECT statements by a factor of three. You should benchmark your choice of hardware before drawing conclusions as to which setting is ideal."

Morgan, the current one was changed based on read performance, while you seem to be writing more about write performance. You're right that the general case is test it and see which is best for a particular hardware combination and workload so we've been discussing just what to write. I expect we'll change something, if we can settle on some reasonable "try this first but try all options" wording.

James - the problem with the current version is it is always misinterpreted.  I have many people tell me "setting innodb_flush_method is bad".  They glance over this manual page - they get it wrong.

If you want to have one paragraph for read performance, and one for write, that's fine by me as well.  Please don't leave it how it is though ;)

Morgan, if you want something official to point to temporarily, you could always point them here to illustrate that we say try them all to see which works best for the system and workload. A more full signature to help with that if it's useful sometime:

James Day, MySQL Principal Support Engineer, Oracle UK

Any movement on this?  I just found another my.cnf file with this in it:

# with a san, o_direct slow down queries
# http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_method
#innodb_flush_method=O_DIRECT

It is unfortunate when those who RTFM get mislead.  It is normally an excellent source to point to.

Replacing the paragraph in question with the following wording, in 5.6 Ref Man first in case it needs another iteration or two before closing the bug.  Please confirm if this captures all the nuances.

Depending on hardware configuration, setting innodb_flush_method to
O_DIRECT can either have either a positive or negative effect on
performance. Benchmark your particular configuration to decide which
setting to use. The mix of read and write operations in your workload
can also affect which setting performs better for you. For example,
on a system with a hardware RAID controller and battery-backed write
cache, O_DIRECT can help to avoid double buffering between the InnoDB
buffer pool and the operating system's filesystem cache. On some
systems where InnoDB data and log files are located on a SAN, the
default value or O_DSYNC might be faster for a read-heavy workload
with mostly SELECT statements. Always test this parameter with the
same type of hardware and workload that reflects your production
environment.

Much better wording.  Thank you John!

Applied same wording to 5.5 and 5.1 manuals also.