札幌MySQL勉強会
箇条書きですがメモを載せておきますMySQL MHA
目的
- SPOFをなくしたい
- マスターのSPOFをなくすのは難しい
障害対応の課題
- レプリケーションは非同期
- どのスレーブが最新か判断する必要がある
- スレーブ間のズレを修正する
- これを全自動でやる
アーキテクチャ
- Pure Perl
- MySQL 5.0以降
- binary logのformat versionの都合で5.0から
- MySQL-MasterHA-Manager
- Config::Tiny, Lod::Dispatch, Parallel::PreforkManager, DBD::mysql
- MySQL-MasterHA-Node
- DBD::mysql
内部的な動作
- SQLスレッドが実行を終えるまで待つ
- 会心スレーブのリレーログのヘッダを解析して各スレーブに適用する
特徴
- マスターの稼働監視からフェイルオーバーまでを自動でできる
- フェイルオーバーが秒単位で可能
- 非同期レプリケーションでもスレーブ間の同期が取れる
- 任意のスレーブを新マスターにできる
- いくつかの箇所から外部スクリプトを呼ぶ昨日がある
- 電源OFFやIPアドレスのフェイルオーバーなどに使う
- インストール/アンインストールにあたり現在のmysqldプロセスやレプリケーションを止める必要がない
- MHA自体は追加の負荷をかけない
- ストレージエンジンに依存しない
- バイナリログのフォーマットに依存しない
- statementでもrawでも大丈夫
拡張ポイント
ケーススタディ
- DeNAのサービスに対してMHAを導入
- MySQLは滅多にクラッシュしないけど、OS、H/W障害によって落ちる
- OSダウンによるフェイルオーバーには、ダウン検知に10秒、フェイルオーバーに4秒
- マスターの生死判定
- フェイルオーバー可否判定
- フェイルオーバー処理
デモ
- 3台構成 Master1 Slave2
- master centos6-1
- slave centos6-2
- slave centos6-3
- 設定ファイルでスレーブの中からマスターになる候補を決められる
- masterha実行時にどのサーバーがmasterか自動検知
既存のソリューションに対する優位性
- マスター障害でも整合性をたもってレプリケーションを再開できる
- スタンバイサーバがいらない
- フェイルオーバー時間が高速
- アクティブ/スタンバイ構成のDRBDでは1分単位
- MySQL Cluster/Galeraに対する優位性
- MySQL-MMMは怖い
その他の特徴
- 任意のスレーブを新マスターにできる
- フェイルバックをするのが面倒
- 準同期レプリケーションを併用することで、データ消失をほぼ防げる
サービスの増強と縮退
- ゲームタイトルの人気を正確に見積もることは困難
- 想定外の人気が出た場合
- スレーブを追加
- 水平分割でマスターを増やしていく
- マスタのH/Wを増強する
マスターを別マシンに移行したい
- マスターを別マシンに移す
- メンテナンス時間を設ければ簡単
- ダウンタイムなしでマスターに切り替えるのが理想
- 0.5から3秒くらいでできれば多くの場合は許容範囲
書き込みブロックをどうするか
- アプリケーションのユーザをdropする
- 新規だけエラーになる
- 接続済みはエラーにならない
- 接続済みのセッションが終了しない限り、更新がおわらに
- set global read_only = 1
- OSCのときは3秒で切替だったけど、最適化して1秒くらいになった
MHAでの高速マスター切替
- masterha_master_switchで手動切替もできる
- 切替前と切替後の構成を表示して、確認がある
- webアプリなら一回エラーになっても、もう一回クリックして動けば許される雰囲気
- 今のところ苦情はきてない
質問
- slaveの昇格の設定
- MasterHA自体が落ちた場合は
- pacemakerとかでMasterHA自体をHAする
MySQL開発者向けチューニング
ディスクI/O性能を常に意識する
- HDDは遅い
一部のクエリだけが問題を起こす
- 1%の法則
- 代表的な悪い例
- 大量のレコードをスキャン
- 個々の実行効率は悪くないが大量に実行される
- スロークエリにはでない
- 何かツールを後で紹介
- 長時間ロックを保持
tcpdumpとmk-query-digest
- スロークエリログが有効になっていなくても実行頻度の高いクエリを取れる
クエリの実行計画に注意する
- EXPLAINの読み方を習得する
- MySQLのオプティマイザが判断を謝ることがあるので気をつける
- とくにsortが絡む場合
- FORCE INDEXを使う
- コストベースなので、テスト環境と本番環境で実行計画が異なることがある
- とくにsortが絡む場合
計算量とランダムアクセスを意識する
- SELECT xx FROM t WHERE xxx LIMIT 100000, 20
スレーブの性質を理解して使う
- スレーブはシングルスレッド
- CPU効率の悪い更新処理はスレーブ遅延を招く
- innodb pluginの圧縮とか
- 長時間かかる処理はその実行時間分だけレプリ遅延になる
- テンポラリテーブルとの相性がわるい
トランザクションを意識する
myslowtrancaptureで遅いトランザクションを特定する
- githubにある
- 実行に一定時間以上かかったトランザクションを探し出す
- 誰がロックしているかも表示する
データサイズに常に気を配る
- 必要以上に大きなデータ型を使うべきではない
- datetimeよりtimestamp
- varcharよりint
- intよりもsmallint
- 古いデータをメインテーブルから削除する
- index
INDEX
- ルート→ブランチ→リーフ→実データ
範囲検索を注意して使う
- データを削除するとき
- delete文で削除されるものをslaveで空読みして、メモリに載せてから実際にdeleteしてレプリ遅延を防ぐ
Covering Index(インデックスだけを使う検索)
- SELECT key1 FROM th1 WHERE key1 IN (1,2,3)
- explainでusing indexになっていればOK
マルチカラムインデックスと範囲検索
- カーディナリティに注意
Covering Indexに帰着させる
- SELECT a,b FROM tbl WHERE secondary_key < 100;
- a, bをindexにしておく
- indexを安易に増やすのがいいのか考える
INSERT性能とメモリ量の関係
- リーフブロックにもうエントリーが入らなくなると、追加のブロックが確保される
- ランダムINSERTは虫食い状態になりやすい
- indexサイズが増える
- ディスクI/Oが増える
- インメモリでINSERTを完結させる
- 10000rows/s超える
- 高速ストレージでも実は状況はさほど改善しない
- insert buffer? ランダムinsertでも毎回readしない
- FusionIOでも5000rows/s程度
- 時系列データはレンジパーティション
- indexはパーティション毎に作られる
データブロックがどのように読まれるか
- 全体の3%しかアクセスしなくても、ブロック単位で読み込むので思ったよりも多くメモリが必要
- 剰余ベースは効率がわるい
- 範囲ベースの振り分けは効率がいい
Blogテーブルの扱いを考えてみる
- タイトル、本文
- 大抵の人は一覧しかみない！
- 長いBLOB/TEXTは全クエリを遅延させる
- 本文が別ブロックにはいってしまうことがあるから
- 本文を別テーブルに切り出す 1:1関連
ロック競合
- ロックは必要になるまで確保せず、不要になったらすぐにCOMMIT/ROLLBACKで解放する
- ロックを確保した後に、何秒もかかる可能性のある処理をしない
Version Numberデザイン
- 楽観ロック
- テーブルにVERSION列を追加
SELECT *を多用しない
- 必要な列だけ返すこと
省スペース化
- JSONデータ等でkeyを小さくするとか
データ型の指針
- FLOATを使わない
- NOT NULLを使う
- TEXT/BLOBを使わない
- UNSINGEDを使う
- INT(1)は1バイトじゃない
EXPLAINを見る
- type=indexかつ「Extra: Using index」ではないクエリは使用不可
- 巨大LIMITはダメ
- covering indexにする
LEFT OUTER JOINしない
ストレージエンジンを混在させない
- キューサーバとしてQ4Mを独立して用意するのはあり
重複したインデックスを持たない
- INDEX(c1), INDEX(c1,c2)
LOAD DATAを使わない
- レプリケーション遅延を招く
- LOAD中にマスターがクラッシュすると、フェイルオーバーできないことがある
複数マスター間でのデッドロックに注意
- 異なるmysqldでのデッドロックは自動検出されない
- アプリ側でIDのソート順にロックをかけることで回避できる
0 件のコメント:
コメントを投稿