[library] MySQL O_DIRECT mode

[Sergii Golovatiuk]

At the moment Fuel doesn't specify mode for InnoDB engine. It should be parametrized and clearly specified in ERB template.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/126723

[Vladimir Kuklin (vkuklin)]

What is the actual benefit of using this option? Provide links to upstream documentation, please.

[Bogdan Dobrelya (bogdando)]

Perhaps, docs are http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_method

"For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. "

But the one thing with which I agree completely and which is not being addressed here is:
"Always test this parameter with hardware and workload that reflect your production environment."'

[Bartłomiej Piotrowski (bpiotrowski)]

I haven't found many fresh benchmarks regarding the issue. The most relevant link I've seen is [1], which shows better performance of O_DIRECT in SAN, there is also detailed comment written by engineer from Oracle about many factors affecting the results. IMHO it may be better to just expose innodb_flush_method in puppet to let deployment engineers decide and explicitly set it to the default MySQL value – fdatasync.

[1] http://themattreid.com/wordpress/2012/01/06/san-vs-local-disk-innodb_flush_method-performance-benchmarks/

[Bogdan Dobrelya (bogdando)]

It makes sense to enable O_DIRECT as we really don't need a double buffering between the InnoDB buffer pool and the operating system's file system cache.

[Bogdan Dobrelya (bogdando)]

Raised to medium as the double buffering affects the memory consumption for large deployments

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/126723
Committed: https://git.openstack.org/cgit/stackforge/fuel-library/commit/?id=7bb37448784e905d225feafbc63cd5055e00523e
Submitter: Jenkins
Branch: master

commit 7bb37448784e905d225feafbc63cd5055e00523e
Author: Stanislav Makar <email address hidden>
Date: Tue Oct 7 23:36:55 2014 +0300

    Add the parameter innodb_flush_method

    Recommended by Optimizing InnoDB Disk I/O
    (http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html)

    Change-Id: I23acfbe1de0bf50d40b811d8f11b54b78925985e
    Closes-Bug: 1378063

[Bartłomiej Piotrowski (bpiotrowski)]

Verified on MOS 6.1 #435:

root@node-1:~# grep O_DIRECT$ /etc/mysql/conf.d/wsrep.cnf
innodb_flush_method=O_DIRECT