In one of my production environments, 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 instance2, 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
SELECTstatements by a factor of three.
Referring to High performance MySQL Ver 2 and 3, it stated that InnoDB developers found bugs with using
O_DSYNC are similar to
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_DIRECTwill probably be the best choice, depending on your application.
By googling, I got this benchmark report: on
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 a bit confusing. Which option can show some better I/O throughput and enhancement in the performance without 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:
Still there is slight confusion on both of these parameters. Where i could see most of the production config templates using
O_DIRECT, i haven't seen any where recommending
- MySQL 5.1.51-enterprise-gpl-pro-log
- Red Hat Enterprise Linux Server release 5.5
- DELL DRAC with Raid Controller having a battery write back cache 512MB
- Dell PERC controllers H700 with a battery back-up unit (BBU).
mysql> show variables like 'innodb_thread_concurrency'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_thread_concurrency | 96 | +---------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_read_io_threads'; Empty set (0.00 sec) mysql> show variables like 'innodb_write_io_threads'; Empty set (0.00 sec)
We are using the default plugin, so I have posted the info from InnoDB status:
mysql> SELECT * FROM Plugins WHERE PLUGIN_NAME LIKE '%innodb%' AND PLUGIN_TYPE LIKE 'STORAGE ENGINE'\G *************************** 1. row *************************** PLUGIN_NAME: InnoDB PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 50151.0 PLUGIN_LIBRARY: NULL PLUGIN_LIBRARY_VERSION: NULL PLUGIN_AUTHOR: Innobase OY PLUGIN_DESCRIPTION: Supports transactions, row-level locking, and foreign keys PLUGIN_LICENSE: GPL 1 row in set (0.00 sec)