consequences of using “innodb_flush_method = O_DIRECT” without having a battery backed write cache? or on a KVM guest?

Question

Mysql 5.5.29 Innodb- 128GB Ram - 32 cores - Raid 10 SSD.

Our server which is a dedicated KVM guest on a 'baremetal' is hosting our heavy read-write DB server. Everything is file-per-table. innodb_Buffer_pool is 96GB with 1GBx2 log_file_size with about 20 minutes of writes to fill up those logs at peak time.

How bad of a situation would it be if O_DIRECT (currently running on the default) was enabled during a high work load without a battery backed write cache and a total crash were to occur on the OS, parent host or the power was cut?

Does a battery backed write cache make a difference if the server is a vm guest of the parent anyway?

.

Answer 1

Back on March 4, 2011, I wrote my answer to Clarification on MySQL innodb_flush_method variable

Basically, I mentioned that O_DIRECT is slower than its default but more data consistent. In essence, O_DIRECT assumes more responsibility for writing that doing O_DSYNC (since O_DSYNC is more reliant on the OS for flushing data changes).

Concerning a Battery-Backed Cache, I would expect O_DIRECT to be

• going nominally slower without it
• going significantly slower with a dead battery

A hard crash could potentially lose up one-second's worth of transactions. It would be as if innodb_flush_log_at_trx_commit were set to 0 or 2 and a crash would happen.

In light of this, your best option is simply to leave innodb_flush_log_at_trx_commit set to 1 so the database can be brought up in a consistent state after a crash.

If the Battery-Backed Cache is for the sake of the VM, you will need it indeed. If a dead battery on a Disk Controller can greatly slow down InnoDB, I can easily see a VM without a Battery-Backed Cache performing a little better but the danger is exactly the same. Why? Not only is the DB at its mercy, but so are all the moving parts of the VM. Using O_DIRECT or not using it would be the least of your worries.