Re: Write cache

[James Day]

You MUST disable the write cache on the hard drives if you want to be sure of reliable InnoDB recovery after power failures. Not optional: do it or you're risking your data integrity and may have to recover from backup and binary log replay.

Disk controllers with battery backup and write cache are fine. However, you cannot trust that the disk controller will disable the hard drive's write cache. If it doesn't it'll merrily lose the data it wrote just before the power loss and your data will be corrupt in spite of the battery.

Test it. Carefully. Including really turning off the power while the server is doing heavy insert activity.

Losing most of your database servers because your battery backed up write caching disk controllers neglected to turn off the hard drive write caches is not fun. Been there, done that, suffered the outage when two different brands with battery backup, one SATA, the other SCSI, both got it wrong.

Today there is an alternative. Only the InnoDB log files and binary log, if used, need to be on the cached drive. You could use a battery backed RAM card or a solid state drive for just those files and avoid the hassles of a RAID controller, using OS RAID instead. Do verify correct operation with real power off tests as well.

A UPS will not protect you. Look up "emergency power off" or EPO switch. US data centers are generally required by fire regulations to disconnect UPS and generator power when it's used, to remove the source of a possible electrical fire and protect firefighters.

Most people can get away without paying attention to caching because their loads are low. Those who have heavy and continuous loads won't get away with it.

Once the drive caches are off and your tests are passed it really does work as advertised and correctly survive power loss. I've been there more times than I like and the InnoDB part just works once the controllers and drives are telling the truth.

James Day, Support Engineer, MySQL AB