Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql innodb:innodb_flush_method

Tags:

mysql

innodb

in the following link

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

it says: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.

Why O_DIRECT could slow down the select statement?

like image 267
Daniel Avatar asked May 04 '10 03:05

Daniel


2 Answers

O_DIRECT bypasses the OS's cacheing systems. A SAN may be a very fast storage system, but generally it's going to be somewhere else over a network link and proxied/hidden behind various other layers. By using O_DIRECT, which eliminates local cacheing, you force InnoDB to hit the storage system directly every time.

like image 157
Marc B Avatar answered Oct 15 '22 20:10

Marc B


You really need to experiment with the flush method on your hardware to see what works best for you. Setting:

innodb_flush_method = O_DIRECT

Improved our performance by 15% on a Dell 2950 server with 15K RPM SAS drives configured in RAID 1 configuration with Dell's PERC caching controller. We're running Ubuntu 9.04 stock kernel and most of the work is mysql using innodb. Your mileage may vary.

like image 26
Mark Maunder Avatar answered Oct 15 '22 22:10

Mark Maunder