CODEWITHSUNDEEP
mysqlinnodb
Daniel
Daniel

Reputation: 641

mysql innodb:innodb_flush_method

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?

Upvotes: 9

Views: 14775

Answers (2)

Mark Maunder
Mark Maunder

Reputation: 430

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.

Upvotes: 4

Marc B
Marc B

Reputation: 360562

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.

Upvotes: 9

Related Questions