Tuesday, September 18, 2012

Automating master failover is possible but needs care

I was asked from a few people about my opinion of the Github's recent service outage. As a creator of MHA, I have lots of MySQL failover experiences.
Here are my points about failover design. Most of them duplicate with Robert's points.


- "Too Many Connections" is not a reason to start automated failover
- Do not repeat failover

I know some unsuccessful failover stories that "1. failover happens because master is unreachable (getting too many connections errors) due to heavy loads 2. failover happens again because the new master is unreachable due to heavy loads 3. failover happens again....". On database servers, newly promoted master is slower because of poor cache hit rate. On traditional active/standby environment, database cache on the new master is empty so you'll suffer from 10x or even worse performance for the time being. On master/slave environment, slave has cache so performance is much better than standby server, but you can't expect better performance than master.
It does not make any sense to repeat failover within short time, and automated failover should not happen just because master is overloaded. If master is overloaded due to H/W problems (i.e. raid battery failure, disk block failure, etc), failover will need to be performed, but I think this can be manually done.

MHA does not start failover if specific error codes are returned (i.e. 1203: ER_TOO_MANY_USER_CONNECTIONS). And MHA does not repeat failover if 1. last failover failed with errors or 2. last failover happened within N minutes (480 minutes by default) ago.


- Do not failover if it is unclear master is dead

This is very important to avoid split brain. In many cases data inconsistency is more problematic than longer downtime. You need to make sure on the master that no mysqld process is running / will not run. Even though master is not reachable via TCP/IP connection attempts, mysqld may be just during crash recovery. Forcing shutdown on the mater (power off) is my favorite approach, but may take long time depending on H/W.
MHA has a helper script to kill (i.e. power off) master. When I developed MHA, I spent long time for investigating how to speed up shutting down machines.


- Prepare tools for manual failover

There are some cases that automating failover is really scary - typical example is a datacenter failure. If the whole datacenter is not reachable, it is not easy to automatically check master's status, and probably remotely shutting down master is not possible. And it would be unclear when the datacenter is recovered. In such cases I think automated failover should not be performed, but manual failover should be done. Proper alerts should be sent immediately, so that DBAs can start analyzing problems and start manual failover quickly. On master/slave environments, slaves' relay log positions might be different each other. Checking all slaves' status and if needed fixing consistency by parsing relay logs is painful. MHA will be helpful in such situations, and actually I have used MHA many more times for manual failover than automated failover.


42 comments:

Robert Hodges said...

Hi Yoshinori, thanks for the reference. I'm completely with you on not automating cross-data center failover. I should have remembered that in my post. Meanwhile, I hope you are having fun with these problems at Facebook. :)

fox diane said...

Thanks for sharing this with us, it's really nice of you! Wish you a nice day.cheap diablo 3 gold

cheng binbin said...

Game gold and items, so you want to safely and quickly speed search MMOGGG.

welcome to http://www.mmoggg.de/
Abnormal favorite online games, such as PC
WoW Gold and Diablo 3 Gold and aion gold and Runescape Gold Are among the most popular game currencies, including the PC and web games.

caton yesiondre said...

From there, the Google research app delivers today?ˉs temperature, your Google windows 7 key Calendar appointments, directions to house or function.

Belkowski Gaylord said...

Thank you for sharing this with us, that's very kind of you! Wish you a nice day
2013 new nike nfl jerseys

Peny Chou said...

This article is fantastic. I really like studying your articles. rs accounts for sale Stay up the great work! Lots of persons are hunting around for this information, you could help them greatly.

Maria Arredondo said...

Ability may get you to the top, but it takes character to keep you there, RuneScape Gold to change your future, alter your decisions today, RS Gold. Life is not measured by the number of breaths we take, but by the moments that take our breath away RS2 Gold.

Maria Arredondo said...

Would you write to please D3 Gold just yourself? Or others? Or yourself by writing for others Cheap D3 Gold? It takes strength to do what must be done when the work is unpleasant and uncomfortable D3 Gold Sale, And of what would you write: Of love? Hate? Fun? Misery? Life? Death?Nothing Everything?

buyGuildwars2gold said...

What gets us into trouble is not what we don't know.It's what we know for sure that just ain't so rs power leveling, Life is like a hot bath. It feels good while you're in it, but the longer you stay in, the more wrinkled you get rs item, life is too short to wake up in the morning with regrets. So, love the people who treat you rs items right and forget about the ones who do not.

buyGuildwars2gold said...

Would you take the pen in hand, plunge right in and just do it, struggling to keep up with the twists and turns of the torrents of words that take you where they take you Buy D3 Gold? Would your plans be so extensive that you never even got to the writing Cheap D3 Gold? But if you do decide to use it, what would you do with it? How would you play the game D3 Gold Sale?

My Family said...

Enjoy Free US Sports Games. Perform golf ball because Jordan, score this excellent dish within american soccer. Play icehockey or maybe hockey recreations.

Vivian Salvatore said...

Cheap D3 Gold The most popular was my game My spouse and i travelled 14-0, GW2 Gold but many of us unfortunately nevertheless dropped by the hairAs the thief were by definition a new glass rule.

Yacht Transport said...

we are the best herbal incense online wholesaler and we have been dealing in Herbal incense online sale from last 10 years. we can provide you excellent quality of Herbal incense and spice incense. Herbal Incense is legal in all states and you will surely enjoy it.

herbalincensehut

Cahaya Mandiri said...

It is amazing posting and incredible work, It has suitable information, I presently wanted to say that you have really so motivating and very informative post. Thanks for wonderful posting
tips cara agar cepat hamil l CARA BELAJAR BAHASA INGGRIS l the best acne treatment l how to lose weight fast easy
margahayuland l BELAJAR BISNIS ONLINE l tips cepat hamil l how to get rid of acne home remedies l
home remedies for acne l how to cure acne fast l
baju batik modern l toko sepatu online l grosir jam tangan online l
jual jam tangan l toko jam tangan murah peluang usaha online l is acne no more for you l how to get rid of acne naturally
how to clear acne l cure acne naturally
best natural remedies for acne l acne no more l tempat belajar bisnis online
peluang usaha rumahan l cara mendapatkan uang dari internet
makanan sehat agar cepat hamil l penyebab tidak bisa hamil lcara agar cepat hamil


steve7876 said...

This article is fantastic. I really like studying your articles. rs accounts for sale Stay up the great work! Lots of persons are hunting around for this information, you could help them greatly.Buy online cars

Lisa Parkar said...

This is an excellent post i seen.I have to thanks to you to share it. It is really what I wanted to see hope in future you will continue for sharing such a excellent post modern dinette sets

Lisa Parkar said...

This article gives good ideas and concepts. This was very interesting to read such a Great blog post modern dining room set

wow9gamer said...

be different each other. Checking all slaves' status and if needed fixing consistency by parsing relay logs is painful. MHA will be helpful in such situationsWorld of Warcraft Gold Billig
World of Warcraft Gold Kaufen

harley dexter said...

Thanks a lot for sharing it, I really enjoy to read articles that have good information and ideas to share to each reader.
pay for essay | uk essays

hannah william said...

not just simple care but special care is needed.

write my essay by essayavenue.co.uk || essay editing services

Charlie Jonathan said...

Hi this information is exactly what i needed. Thanks.
assignment help by assignmentvilla | write my assignment

shahbaz said...

Your article has helped me to understand this subject on a different level. I would like to appreciate your efforts for exploring this issue. Thank you for your information.Wholesale yacon Syrup

steve7876 said...

Would you take the pen in hand, plunge right in and just do it, struggling to keep up with the twists and turns of the torrents of words that take you where they take youweight loss supplements

Helen Dina said...
This comment has been removed by the author.
Martin berk said...

It is useful informative blog. I really enjoy to read articles that have good information.coursework writers | online coursework | write my coursework


Andrew Alpert said...

I found this article very informative and to the point. coursework writing service

Jerukukuk said...

great article , nice information very usefull, thankyou
jam tangan alexandre christie
jam tangan couple
jam tangan original

Danny Scott said...

Thanks.you article very informative, I really enjoy to read articles that have good information and share to each reader.
do my essay for me | custom essay writing

steve7876 said...

Nice post and it would be great for everyone, Thanks a lot for spreading this information here.Id card passbook passes

Buy Essay said...

Buy Essay
The first several months of my site there were no comments; just give it time; now they come in like crazy every day! Thanks.

Buy Essay said...

Animation Service Provider
The first several months of my site there were no comments; just give it time; now they come in like crazy every day! Thanks.

Buy Essay said...

Company Logo Design
The first several months of my site there were no comments; just give it time; now they come in like crazy every day! Thanks.

Buy Essay said...

Content Writing Services
The first several months of my site there were no comments; just give it time; now they come in like crazy every day! Thanks.

Buy Essay said...

Animated Explainer VideoThe first several months of my site there were no comments; just give it time; now they come in like crazy every day! Thanks.

panca-samudera said...

The article posted was very informative and useful
thanks for sharing.
jaring futsal , jaring futsal murah , jual jaring futsal , jaring golf , jual jaring golf , jaring golf murah ,
jaring polynet , tangga darurat , jaring truk , jaring cargo , jaring outbound , jual rumput futsal murah ,
jual rumput sintetis murah , tali tambang , cargo net , jaring tanaman , jaring kassa , jaring proyek , jaring bangunan ,
jaring gedung , jaring pengaman proyek , jaring pengaman bangunan , jaring pengaman gedung , jaring peneduh , jaring waring , kasa hijau , tangga darurat , jaring gawang futsal ,
jual jaring gawang futsal murah , jaring peneduh , jaring truk , tali tambang nylon , jaring safety , safety net , jaring , waring , polynet

Herman Alvarez said...

You described it very beautifuly . Your writing style grabbed my intentions, You can also visit me to buy essays online & Professional Essay Writing Help. Would love to read more about it…

Nathan Todd said...

Superb one! I have never read such a nice article; topic and the comments are good. I have learned many things from all your post and this one was the best post for me. I really enjoyed reading it and craving more tips from you… thank you for such an engaging article. Essay writing help | Custom Essay writing

Bryan Parker said...

I bookmarked this web. I will come back to continue learning about your web design.I’m glad Yahoo pointed me to it. I was able to get the know-how I was searching so badly for days now.Thank You very much for your really good web page. Have a good day.

Professional Degree I Logo Design

Bryan Parker said...

You have a real ability for writing unique content. I like how you think and the way you represent your views in this article. I agree with your way of thinking.

Life Experience Degree I Online Bachelor Degree


Naveen Chaudhary said...

valentine wish
valentine quotes for girlfriend
valentine day wishes for girlfriend
valentine day poems for girlfriend

lolyta said...

Nice blog and article, thanks for sharing. However want to statement on few common issues, The website taste is great, the articles is in point of fact excellent. Vimax IZON Canada Original | Obat Pembesar Penis
vimax izon

vimax izon canada

vimax izon asli

harga vimax izon

jual vimax izon

vimax

Assignment Help said...

Assignment Help
my assignment help

Post a Comment