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.

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?

.

share|improve this question

1 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.

share|improve this answer

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.