tags:

views:

283

answers:

2
+2  Q: 

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?

+3  A: 

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.

Marc B  2010-05-04 07:16:05
When we use o_direct, we will use almost all the physical memory for mysql to cache the data. So it looks like the mysql document assumes we waste some free memory without allocating them to mysql. If we don't reserve free physical memory, then I thought o_direct won't be slower for select. As if block could be cached inside filesystem, then most likely it can also be cached in mysql buffer if we let mysql use all the free memory.
Daniel  2010-05-05 05:18:06
MySQL can and will cache query results, but if any of the queries would require a table scan anyways, then you're off to the disk. With O_DIRECT, there's no disk cache in memory to use, so you're off to the storage medium directly.
Marc B  2010-05-05 20:03:27
A: 

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.

Mark Maunder  2010-05-26 11:35:45

related questions

Remove Quotes and Commas from a String in MySQL
What's the best way of converting a mysql database to a sqlite one?
How do you manage databases in development, test, and production?
Multiple foreign keys?
Add 1 to a field (MySQL)
Issues using MS Access as a front-end to a MySQL database back-end?
Bigger than a char but smaller than a blob
How do I retrieve my MySQL username and password?
Long-time LAMP Developer moving to VB.NET
How do I Concatenate entire result sets in MySQL?
Full complete MySQL database replication? Ideas? What do people do?
SQL: Distribution of table in time
SQLite vs MySQL
How do I create a new Ruby on Rails application using MySQL instead of SQLite?
Multiple Updates in MySQL
MySQL/Apache Error in PHP MySQL query
What all do I need to escape when sending a (My)SQL query?
Auto Generate Database Diagram MySQL
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
Python and MySQL
SQL Server 2005 implementation of MySQL REPLACE INTO?
How to export data from SQL Server 2005 to MySQL
Throw Error In MySQL Trigger
Binary Data in MySQL