MySQL Forums
Forum List  »  InnoDB

Re: Write cache
Posted by: James Day
Date: July 18, 2007 02:02PM

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

Options: ReplyQuote

Written By
July 11, 2007 02:50PM
July 12, 2007 05:22AM
July 12, 2007 12:01PM
July 18, 2007 02:05PM
Re: Write cache
July 18, 2007 02:02PM
November 14, 2007 09:53AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.