Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster.
We have 125G main memory with 24G InnoDB buffer pool occupied by instance1 & 12G occupied by instance 2 which is not associated with the RedHat cluster. Both the data and transaction logs are located on the LVM disk partition with an ext3 file system.

For a performance boost and better I/O throughput I have decided to change innodb_flush_method to O_DIRECT.

With reference to the MySQL documentation:

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.

Referring to High performance MySQL Ver 2 and 3, it stated that InnoDB developers found bugs with using innodb_flush_method=O_DSYNC.O_SYNC and O_DSYNC are similar to fysnc() and fdatasync(): O_SYNC syncs both data and metadata, whereas O_DSYNC syncs data only.If that all seemed like a lot of explanation with no advice, here’s the advice: if you use a Unix-like operating system and your RAID controller has a battery-backed writecache, we recommend that you use O_DIRECT. If not, either the default or O_DIRECT will probably be the best choice, depending on your application

By googling,I got this benchmark report: on O_DSYNC vs O_DIRECT

Bench Mark Report :
===================
1B Row Complex Transactional Test, 64 threads

 *   SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
 *   SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
 *   SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
 *   Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
 *   Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
 *   Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.

However, O_DIRECT disables the OS level cache, where double caching can be disabled which show some better I/O throughput.

Is it good to go with O_DIRECT rather than O_DSYNC? These two options are bit confusing. Which option can show some better I/O throughput and enhancement in the performance with out any impact on the data, reads/writes especially in production? Any better suggestions based on your personal experience?

I could see Rolando Update in the post : == Clarification on MySQL innodb_flush_method variable ==

    Still there is slight confusion on both of these parametres. Where i could see most 
of the production config templates using O_DIRECT, i havent seen any where recommonding
O_DSYNC.
share|improve this question
    
    
What version of MySQL are you using ??? –  RolandoMySQLDBA Sep 10 '12 at 18:10
    
MySQL 5.1.51-enterprise-gpl-pro-log,o/s : Red Hat Enterprise Linux Server release 5.5, Hardware DELL DRAC with Raid Controller having a battery write back cache 512MB, Dell PERC controllers H700 with a battery back-up unit (BBU). –  Gopinath Sep 13 '12 at 4:22
    
What is your innodb_read_io_threads, innodb_write_io_threads, innodb_thread_concurrency ??? –  RolandoMySQLDBA Sep 13 '12 at 4:50
    
innodb_thread_concurrency :96 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 142761 OS file reads, 12191807 OS file writes, 1327207 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.62 writes/s, 1.22 fsyncs/s –  Gopinath Sep 13 '12 at 10:34
show 3 more comments

2 Answers

Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com

As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would be good solution.

As of yet, I know of no one having touched this issue. O_DSYNC as a default is not an appealing prospect but does accommodate faster writes that are not really verified. That why there is so much hype around O_DIRECT.

UPDATE 2012-09-14 23:25 EDT

From your comments, I can tell you do not have the InnoDB Plugin installed. With the InnoDB Plugin, several variables should exist.

You should upgrade InnoDB in one of two weays:

Once you have done so, you can enhance InnoDB to

  • access more CPUs and Cores
  • increase read and write I/O threads
  • scale the I/O capacity (this is especially needed for different storage media)

Here are my past posts on the settings you can change for this:

share|improve this answer
    
Thanks Rolando for your recommondations. Will look into it. –  Gopinath Sep 17 '12 at 16:57
add comment

I was always under the impression that O_DIRECT is better for performance as it prevents double buffering. Also, provided you have hardware RAID with battery-backed write cache. Of course, it also depends on the workload, whether you're READ or WRITE heavy.

Also, question for the experts: do the extra calls to fsync() for O_DIRECT cause a noticeable degradation in overall performance, or is it negligible? I have yet to see benchmarks show this.

Also, note that Percona has enabled the ability to set innodb_flush_method=ALL_O_DIRECT, which provides direct I/O not only on InnoDB data files, but also on InnoDB transaction logs at the same time.

share|improve this answer
    
-1 because your answer does not provide a definitive, tested, answer to the question. Try not to answer questions with more questions. –  Max Vernon Sep 14 '12 at 3:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.