Nines , Damn Nines and More Nines
Funny how different experiences lead to different evaluations of tools. The MySQL HA solutions the MySQL Performanceblog list, are almost listed in the complete opposited order of what my impressions are.
Ok agreed, I should probably not put my MySQL NDB experiences from 2-3 years ago with multiple Query of deaths and more problems than you into account anymore , but back then went in the list Less stable than a single node. I've had NDB POC setups going down for much more than 05:16 minutes
Ndb comes with a lot of restrictions, there are
As for MySQL on DRBD, I've said this before , I love DRBD, but having to wait for a long InnoDB recovery after a failover just kills your uptime ,
I remember being called by a customer during Fred last holiday who was waiting over 20 minutes for recovery , twice, so putting the DRBD/San setup second would not be my preference. But agreed .. it's only listed at 99.9% meaning almost 9 hours of downtime per year are allowed.
On the other hand we've seen database uptime of MySQL MultiMaster setups with Heartbeat reaching better figures than 99.99% Heck I've seen single nodes achieve better than 99.99% :)
So what does this teach us ... there is no golden rule for HA, lots of situations are different, it's the preferences of the customer, the size of the database, the kind of application , and much
more .. you always need to think and evaluate the environment ...
Comments
#1 Anonymous : I've recently set up
I've recently set up mysql-mmm on a customers set up to handle their database requirements. Two master DBs, 3 slaves.
I like it though it's no guarantee of 100% data accuracy. Performance overhead is negligible too.
#2 Anonymous : To avoid innodb recovery time
To avoid innodb recovery time on DRBD, run two nodes with XEN and the domU's on DRBD so when the primary dom0 fails over to the secondary, the domU's live migrate resulting in no downtime to MySQL which runs inside a domU instance.
Then add the same setup in another geographically diverse datacenter and setup circular replication between the two domU's running MySQL so the two datacenters are connected.
This setup also protects you from significant outages and routing mistakes made by the clowns working at datacenters, who are the ones responsible for the most downtime in any of my services.
#3 Kris Buytaert : Crash & Live Migrate
Dear Anonymous,
When the primary dom0 fails, its usually to late to live migrate.
When the primary domU should fail it is also to late to live migrate.
Please explain how your fairy tail could ever work .
#4 Anonymous : My fairy tail as you put it
My fairy tail as you put it can work with either of these 2 solutions;
http://www.citrix.com/English/ps2/products/feature.asp?contentID=1686940
or
http://dsg.cs.ubc.ca/remus/
#5 Kris Buytaert : Fairytale vs Production Ready
I keep calling it a fairy tail tail till either Remus or Kemari are production state.
Or did I overlook that ?
#6 Eric Eisenhart : Ignoring cost of complexity?
It's important to differentiate between planned and unplanned downtime (downtime 3am local time that was announced ahead of time might be much less costly than unexpected "prime time" downtime).
But it's also very important to take into account the complexity cost of different solutions. A lot of the solutions that claim more 9's actually fare quite poorly because they're somewhat fragile. If you get good hardware with a lot of internal redundancy (RAID, redundant power supplies, UPS+generator, redundant fans, etc) and maybe also one of those expensive 2-hour support contracts (or a supply of spare parts) you can get to about 99.8% uptime without any special complexity. Anything in excess of 99.8% uptime probably requires multiple data centers.
To get some of the "HA" solutions to meet that you'll probably have to spend a lot of time learning every little detail of how they work, test them thoroughly, etc. Otherwise it's likely that the "HA" itself will cause problems and get you lower uptime than a single resilient host could achieve.
#7 Yves : InnoDB recovery with DRBD
How can InnoDB recovery kill your uptime? It is fairly well known that you need a small InnoDB log file size, something like 128MB, for a short recovery. With a pair of such log files, recovery is usually under one minute, which is not a problem. Of course, a smaller log file means a lower write performance, but in most cases, it is fairly decent at 128MB.
Yves
#8 lefred : innodb recovery time
just some more details : the db is >2TB and we have the innodb log_file_size of 1G ! (still for historical reasons).
The setup is not on drbd but on a SAN and this DB should never crash... but... we had a bug with the SAN that put the lun in read only... then the db crashed... the recovery time could then reach 3h ! We have a huge amount of complex transaction per second (>17k)
Of course on our DRBD-MySQL setup (for radius and dedicated JMS) we have a smaller log_file_size.
So the hard thing is to find the best solution: performance, longer undo, longer recovery ?