Mha and Prm with semi-sync is interesting although it lowers the write bandwidth a bit. I haven’t played much with the new mysql tool for failover in 5.6, it can reconfigure replication but an ha solution is more than that.
Hi Roman, The problems with Myisam are: no durability and no guaranteed recovery. If the primary drbd node crashes with non flushed writes to a large myisam table, it may take hours to recover and data is very likely to be lost. Of course, a read-only myisam table has no issue with drbd but the general use case is no myisam tables. Remember, this is an ha setup, if it can’t recover in a predictible way, what’s the benefit.
Regards,
Yves
LordFPL
10 years ago
Good summary… but i’m disapointed as Roman “DRBD: Only for InnoDB.”… gné ? For information, i made a HA solution with a mix of drbd/pacemaker/slave.
3 nodes with 3 type of environnement (prod/dev/valid).
Example of nominal situation for prod – node 1 = drbd master + mysql master – node 2 = drbd slave – node 3 = mysql slave
I have a sync and async replication… drbd is managed with pacemaker = 15sec to swith to another server. In case of problem, i have the async replication with mysql slave (and of course, some dump each night ;)).
Cost of this = downgrade of write performance due to drbd -> from 3000ops with 64 writers, i fall to 1500… but it’s sufficient for our users (have an average of 500ops).
I don’t know if i made a big mistake with this… but it’s in production since 1 year without any problem 🙂
I have experimented with MySQL 5.6 admin replication scripts and they work ok as long as you know what you are doing and you are disciplined. But you are rigth, this is not HA and I see replication a good solution for managed failover , not automatic. For HA, we have experimented with VmWare HA option and found it could give very good results. Of course, this make sense if there is already a Vm infrastructure in place. Also, using database server on Vm can still suffer some overhead depending on the load put on the database.
Yves and Jay, very nice summary on MySQL HA solutions.
In the future editions I would appreciate you not to restrict yourself to open source only solutions. I would like to see Continuent Tungsten (http://www.continuent.com/solutions/clustering) included.
I agree that there is some merit on open source only approach. After all, Continuent also values open source and contributes to MySQL community our own open source replication solution, Tungsten Replicator, for various advanced multi-master, multi-site and heterogenous (for example MySQL to Oracle, MySQL to Hadoop, or Oracle to MySQL, etc.) replication needs.
But there is also case to be made to offer your readers more balanced view of the world. Especially taking account that commercial Continuent Tungsten solution may not end up costing possible users any more than open source only solutions when taken account the work related to deploy and maintain these solutions.
Additionally, you may not want to limit yourself in HA only. You could also take account DR, which creates whole new set of considerations and requirements. By adding DR in the consideration would give even better guidance which solution actually meets the real world needs.
Last but not least, significant element that impacts down time is scheduled maintenance operations. Maintenance is the single most significant element impacting application availability. Good question to ask is if the chosen solution allows ‘zero downtime maintenance’ for all needed maintenance activities (schema change, database upgrade, hardware upgrade, etc.).
Hi Yves,
I’m curious what you think about using MHA w/semi-sync, or mysqlfailover (from the MySQL Utilities) which is designed to work with Replication+GTIDs?
Mha and Prm with semi-sync is interesting although it lowers the write bandwidth a bit. I haven’t played much with the new mysql tool for failover in 5.6, it can reconfigure replication but an ha solution is more than that.
–Yves
>DRBD: Only for InnoDB.
Guess it’s a mistake?
Hi Roman,
The problems with Myisam are: no durability and no guaranteed recovery. If the primary drbd node crashes with non flushed writes to a large myisam table, it may take hours to recover and data is very likely to be lost. Of course, a read-only myisam table has no issue with drbd but the general use case is no myisam tables. Remember, this is an ha setup, if it can’t recover in a predictible way, what’s the benefit.
Regards,
Yves
Good summary… but i’m disapointed as Roman “DRBD: Only for InnoDB.”… gné ?
For information, i made a HA solution with a mix of drbd/pacemaker/slave.
3 nodes with 3 type of environnement (prod/dev/valid).
Example of nominal situation for prod
– node 1 = drbd master + mysql master
– node 2 = drbd slave
– node 3 = mysql slave
I have a sync and async replication… drbd is managed with pacemaker = 15sec to swith to another server.
In case of problem, i have the async replication with mysql slave
(and of course, some dump each night ;)).
Cost of this = downgrade of write performance due to drbd -> from 3000ops with 64 writers, i fall to 1500… but it’s sufficient for our users (have an average of 500ops).
I don’t know if i made a big mistake with this… but it’s in production since 1 year without any problem 🙂
See my reply to Roman.
–Yves
Hi Yves,
I have experimented with MySQL 5.6 admin replication scripts and they work ok as long as you know what you are doing and you are disciplined. But you are rigth, this is not HA and I see replication a good solution for managed failover , not automatic. For HA, we have experimented with VmWare HA option and found it could give very good results. Of course, this make sense if there is already a Vm infrastructure in place. Also, using database server on Vm can still suffer some overhead depending on the load put on the database.
Regards,
– Marc
Yves and Jay, very nice summary on MySQL HA solutions.
In the future editions I would appreciate you not to restrict yourself to open source only solutions. I would like to see Continuent Tungsten (http://www.continuent.com/solutions/clustering) included.
I agree that there is some merit on open source only approach. After all, Continuent also values open source and contributes to MySQL community our own open source replication solution, Tungsten Replicator, for various advanced multi-master, multi-site and heterogenous (for example MySQL to Oracle, MySQL to Hadoop, or Oracle to MySQL, etc.) replication needs.
But there is also case to be made to offer your readers more balanced view of the world. Especially taking account that commercial Continuent Tungsten solution may not end up costing possible users any more than open source only solutions when taken account the work related to deploy and maintain these solutions.
Additionally, you may not want to limit yourself in HA only. You could also take account DR, which creates whole new set of considerations and requirements. By adding DR in the consideration would give even better guidance which solution actually meets the real world needs.
Last but not least, significant element that impacts down time is scheduled maintenance operations. Maintenance is the single most significant element impacting application availability. Good question to ask is if the chosen solution allows ‘zero downtime maintenance’ for all needed maintenance activities (schema change, database upgrade, hardware upgrade, etc.).
Keep up with the good work!
Good job – thanks for the great work @ Percona.
You could add in the positives/negatives whether it really works in a WAN setup with 100ms latency between data centers.
Does MHA works with MyIsam Storage Engine?
MHA, like PRM, is only using MySQL replication so it works with MyISAM.
This post needs an update!