Re: MySQL & SAN
Posted by:
Rick James
Date: April 08, 2009 11:11PM
Dual-master -- in different physical locations -- is the best (currently) for HA.
SAN, DRBD, hot-swap RAID, etc only protect you against some failure scenarios.
Auto-failover -- dangerous. Consider the case where there is a partial outage of the network. And the master is not dead, but the failover mechanism can't see it so it thinks it is dead. Boom -- you have two masters each thinking it is alone.
Will you be writing to both Masters? Don't. AUTO_INCREMENTS have to be carefully handled. And PRIMARY KEYs, in general, can cause grief.
For write scaling, "shard" the data. That is, have multiple, independent, masters, each with a subset of the data. (Yes, each with a second master for failover.)
Consider having slave(s), too. Here's an ugly case: You have just a dual-master setup, and one of them dies totally. Now you have to take down (or seriously hobble) the other master to take a copy to rebuild the dead box. If the data is 100GB, that will take hours.
But wait... LVM snapshot to the rescue! Plan ahead; create a snapshot LV. Then taking a backup is less than a minute. But...
Do not try to take the snapshot while MySQL is running. Instead, shut it down. InnoDB FLUSH TABLES WITH READ LOCK does not deal with fsyncing the transaction log, nor with writing the 90% dirty blocks in the buffer pool. Sure, the snapshot will recover, but it may have to rollback some of the transactions that you thought you had alread COMMITted.
Still, 1 minute of downtime in the very unlikely event of having a totally dead machine is not bad.