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
With reference to the MySQL documentation:
Where InnoDB data and log files are located on a SAN, it has been found that setting
O_DIRECTcan 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.
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.