Properties:
| Applicable To | InnoDB on Unix like OS |
| Server Startup Option | --innodb_flush_method=<value> |
| Scope | Global |
| Dynamic | No |
| Possible Values | enum(O_DSYNC, O_DIRECT, <<none>> ) |
| Default Value | <<none>> |
| Category | Performance |
Description:
This variable changes the way InnoDB open files and flush data to disk and is should be considered as very important for InnoDB performance. By default, InnoDB uses fsync() (without O_DSYNC) to flush both log and data files.
Setting this variable to O_DIRECT will result in InnoDB using O_DIRECT while opening files and fsync() to flush both data and log files. O_DIRECT is useful when an application maintains it's own caching mechanism which is very well true for MySQL/InnoDB. O_DIRECT is the option that should be used in most of the cases as it takes the overhead of double buffering and reduces swap pressure. You should be careful if you are not using a battery backed up RAID cache as it may lead to data loss in case of a crash. Also, the MySQL documentation warns against using O_DIRECT when files are located on a SAN, I don't have any idea on that.
O_DSYNC makes InnoDB to use O_SYNC to open and flush the log files, and uses fsync() to flush data files.
For Windows, the flush method is always async_unbuffered.
Recommendation:
If you are not doing anything unusual like SAN storage etc (which otherwise also you should reconsider before doing), always use O_DIRECT for this. This leads to a significant improvement in InnoDB performance by removing double buffering.
Read More:
- MySQL manual entry on innodb_flush_method
- Heikki Tuuri recommends
O_DIRECT: Check Q35 @ mysqlperformanceblog.com man 2 fsyncman 2 open
Hope you enjoyed reading this.
4 comments:
You should be VERY careful using O_DIRECT as on some setups it can cause serious problems. I have seen situations where it causes seg faults of the MySQL daemon and data corruption. Not fun!!
It certainly works in many cases and does do just what you say (eliminate double buffering)but should be used with caution.
Keith,
Thanks for the pointer. Can you share more knowledge on the systems that you have seen problem(s) on?
I agree that O_DIRECT has to be used with utmost care, even seen a little performance degradation (or at least no benefit) while on FreeBSD 4.1. And now that I'm on RHEL, I'm all happy with O_DIRECT :)
--
Parvesh
Keith,
Did the database segfault, or did it InnoDB assert()?
There was a bug (fixed in 5.0.42) that caused an assertion when InnoDB tables were created on a filesystem that didn't support direct io and innodb_flush_method=O_DIRECT.
I personally took down half our production server farm with CREATE TEMPORARY TABLE ... ENGINE=INNODB because it caused the db to crash on machines with tmpdir mounted on tmpfs :D
We run Debian on our servers. They are all running etch. Just recently, after testing (not enough evidently :) ) I added O_DIRECT to some servers. I had tested the different schedulers and it didn't seem to make any significant difference so I was using CFQ (which is default).
We had a number of things going on with the servers that it was rolled out to an it took some time to track it down. I did get SIG 11's and SIG 6's.
Oh, and it 5.0.45.
Post a Comment