[MySQL] MySQLのMastar fail over に関して調べてみた

gistfile1.txt

発端はDBAさんとのお話。

前略。
Multi-Master Replication Manager を利用したら良いよ。だからDNSの申請はいらないよ。

どういうこっちゃ？と思って調べてみたのでメモ。
それに続いて、一般的なFail Overの方法ってなんだろう？と気になったのでメモ。

結局、FailOverというか、MySQLの更新の仕組みがよくわかってない、みたいな感じになって実に残念w

とりあえず、調べた限りでは、FailOverの方法はこんな感じがある。

・Master-Slave構成
・Multi-Master Replication Managerを利用
・MySQL MHA

FailOverに関しては、MySQL 5.6 からはGlobal Transaction IDなるものが導入され、FailOverが高速にできるようになるらしい。
（参照：Replication and auto-failover made easy with MySQL Utilities @ Andrew Morgan’s MySQL Cluster Database Blog）

MHAは詳細をみてさえいないので後でみる。

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
参考
■ MySQLレプリケーションを安全に利用するための10のテクニック
http://nippondanji.blogspot.jp/2009/03/mysql10.html
■ 最強のMySQL HA化手法 - Semi-Synchronous Replication
http://nippondanji.blogspot.jp/2009/03/mysql-ha-semi-synchronous-replication.html
■ もしもデータベースサーバがクラッシュしたら
http://nippondanji.blogspot.jp/2009/02/blog-post_23.html

■ レプリケーションを使わないMySQLの冗長化 - yahoo developper network
http://techblog.yahoo.co.jp/database/mysql_1/
■ Announcing MySQL-MHA: "MySQL Master High Availability manager and tools"
http://yoshinorimatsunobu.blogspot.jp/2011/07/announcing-mysql-mha-mysql-master-high.html
■ MySQL Mastar HA Wiki
https://code.google.com/p/mysql-master-ha/wiki/TableOfContents?tm=6
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー




別件だが、以前に 100万件のInsert クエリを発行した際にSlaveでReplication遅延が発生したことがあった。
これも結局は内部の挙動がよくわかっていないために発生していたが、仕組みがわかってくると「そりゃそうだ」的な気分になるw

InnoDBの場合、SQLの実行が終わりcommitされてからBinLogが生成され、Slaveはそれを取得して同じ更新をかけている。
そのため、SlaveはMasterの100万件Insertが終了し、commit されBinLogに出力された後に同じクエリを実行する。
そのため、その実行時間分がまるまる遅延となる。


で、遅延しないためにはどうしたらいいの？
以下、奥野氏の「MySQLにおけるレプリケーション遅延の傾向と対策」から引用。
http://nippondanji.blogspot.jp/2011/12/mysql.html

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
遅延対策のベストプラクティス

エントリのまとめとして、レプリケーションを運用する上で実施するべきことについて列挙しておこう。

まず、巨大なトランザクション（一度に大量の行を更新するもの）は実行しないというのが鉄則である。バッチ処理的なものであれば、可能であればコミットする行数を少しずつに絞り込むようにしよう。大きなテーブルのALTER TABLEのようにいかんともし難い処理は、メンテナンスウィンドウを設けて実施しよう。

スレーブでI/O boundなボトルネックが生じている場合には、innodb_flush_log_at_trx_commit=2の設定を検討しよう。こうすると、スレーブ上ではCOMMIT時にログがディスクへ同期されなくなるが、スレーブはいざとなればバックアップから再構築すれば良いので、特にスレーブが複数あってひとつぐらいダウンしても問題がない場合には、ログの同期をOFFにすることは悪い選択肢ではない。

ワーキングセットがInnoDBバッファプールよりずっと大きい場合、松信さんが紹介していたプリフェッチのテクニックが有効であろう。ただし、そのような状況ではバッファプールを大きくするのも重要である。バッファプールが足りないと、参照の性能にも影響するからだ。

CPUリソースが枯渇している場合には、CPUを増設する（スケールアップ）か、スレーブを追加する（スケールアウト）しかないが、一般的にはスケールアウトのほうが安くつくので好まれる傾向にある。スレーブ数が増えすぎるとNICの帯域が限界に達してネットワークの遅延が生じることになるが、その場合にはマスター上でNICを増設したり、スレーブを多段構成（孫スレーブ）にするなどの工夫が求められる。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

gistfile2.txt

追記

レプリケーションしてるMySQLで、マスタやスレーブが障害停止した場合のリカバリプラン
http://d.hatena.ne.jp/hirose31/20091023/1256259405

実録、ほぼ無停止なMySQLのフェイルオーバ (動画もあるよ)
http://d.hatena.ne.jp/hirose31/20091111/1257942168