社内でMySQLのスレーブを使った水平分散をする話が持ち上がったので、今一度MySQLのレプリケーションを振り返りたいと思います。
以前は、オンプレミス環境でMySQLを構築していたのですが、最近はAWS Aurora等を利用する機会が多くなり、双方の違いを把握するのに一苦労です。
まずは、MySQLのレプリケーションについて振り返りたいと思います。
もともとのMySQLのレプリケーション
MySQLのリファレンス 17章のレプリケーション に詳細が記載されています。
レプリケーションといえばこの話題です。
オンプレミス環境の場合には、こちらのアーキテクチャを利用します。
レプリケーションについて理解
基本的なしくみ
- マスターはバイナリログという長いSQLの書き込み系クエリの実行履歴を保持する
- 最初にスレーブを構築する時は、その時点のデータベースのスナップショットをとり、そのログの位置も一緒にスレーブに保存する
- スレーブのIOスレッドがマスターのバイナリログ位置を読みに行って自分のリレーログに情報を書き込む
- SQLスレッドが暇なときにリレーログからSQLを実行して自分のデータベースを最新化する
そもそもなんでパフォーマンスがあがるのか？
- ロック待ちの軽減 (※InnoDBならそんなに起きない気もする )
- 読み取り専用のサーバーのチューニングができるので、joinバッファやsortバッファーなど読み取りに必要なパラメーターにメモリを沢山割り当てられる
- 複数台の読み取りサーバーを用意することで、1台分のmax connectionよりも接続リクエストに応答できる
参考:
https://gb-j.com/column/rds-performance-turning/
禁忌事項
- スレーブに直接データを書き込んではならない
- マスターとスレーブで整合性がとれなくなるクエリ(不確定的なクエリ)を発行してはならない
具体例としては、DELETE * FROM TABLE NAME WHERE DATE_TIME < =2022/12/01 00:01:12のようなクエリでSQLの実行日時やシステムタイムが1ミリ秒でもずれてしまうと実行結果が変わってしまうので、不確定的といいます
遅延や不整合が発生するという事を念頭においてく
1. MYSQLの性能による不整合
現在は、準同期レプリケーションというレプリケーションが導入されていてスレーブの書き込み処理が低減されているようです。
しかし、マスタクラッシュ時に不整合が起きるなどの問題がありました。
5.7から rpl_semi_sync_master_wait_point オプションが追加されました。
このオプションとAFTER_SYNC（デフォルト）により、障害時にマスターとスレーブ間データの不整合が起きづらくなった模様です。
2. 不確定なSQL発行による不整合
前項で具体例を上げたように特定のSQLを発行することで不整合につながる場合があります。
こちらの記事のNon-deterministicが参考になるので、設計に反映できるといいと思います。
ActiveRecordのような、ORマッパーを使っている場合にはライブラリ側で考慮してくれている場合が多いので、Railsなどを屈指している場合には、あまり意識せずに運用できると思います。
https://qiita.com/Tocyuki/items/c224cef57493f536a941
https://dev.mysql.com/doc/refman/5.6/en/stored-programs-logging.htm
ユーザー権限設定例
| 用途 | 経路 | 権限 | 説明 |
|---|---|---|---|
| 管理用 | Web1〜3 -> master | ALL [PRIVILEGES] | ※ VPNの中のIPに制限した方がいい |
| 書き込み用 | Web1〜3 -> master | SELECT/CREATE /DELETE/INSERT/UPDATE TEMPORARY TABLES DROP ALTER INDEX/REFERENCES LOCK TABLES SHOW DATABASES CREATE VIEW /SHOW VIEW PROCESS REPLICATION CLIENT | ※ CREATE VIEW /SHOW VIEW はビューを利用しないなら不要しなくてもいい ※ LOADDATAとストアドプロシージャ系は省いてある |
| 読み取り用 | Web1〜3 -> slave | SELECT/CREATE /DELETE/INSERT/UPDATE TEMPORARY TABLES CREATE VIEW /SHOW VIEW | |
| レプリケーション用 | slave -> master | REPLICATION SLAVE |
障害対応とフェールオーバー
1. 障害の検知
障害を検知するには、マスターとスレーブの状態を定期的に監視する必要があります。
| コマンド | 用途 | マニュアル |
|---|---|---|
SHOW MASTER STATUS |
マスターの状態を確認 | マスタ管理のSQLステートメント |
SHOW SLAVE STATUS |
スレーブの状態を確認 | スレーブ管理のSQLステートメント |
| ステータス | 内容 |
|---|---|
| Slave_IO_Running | マスタからログを読み込むIOスレッドの監視、ネットワークによるエラーを監視します |
| Slave_SQL_Runnning | マスタから読み込んだSQLを実行するスレッドの監視、SQL起因のエラーを監視します |
2. スレーブの昇格による手動のフェイルオーバー
マスターに障害が発生した場合、スレーブとして稼働していたものをサーバーをマスターに昇格することができます。
スレーブ側にログがすべて読み込まれている場合には、そのまま、マスタを停止できます。
それ以外の場合には、スナップショットの取得からやり直します。
その場合、最初にマスターのログをフラッシュしてサーバーを停止させます
新たにマスターになるものでSTOP SLAVEコマンドをしてSLAVEとしての動作を停止します。
スレーブのままでいるサーバーのMASTER_HOSTを昇格したサーバーに設定しなおします。
3. MySQL Utilitiesを使ってフェイルオーバー
2で記載したオペレーションを機械的に行うためのユーティリティがあります
MySQL Utilities
Amazon Aurora
もともとのMySQLのレプリケーションのアーキテクチャは利用しない方法です。
AWS独自の実装がMySQLをラッピングしています。
マスタとスレーブが同じストレージを共有する方式で、さらにはキャッシュの機能もMySQLのプロセス外にあるようです。
詳しくは、こちらの資料を参照してください
https://aws-ref.s3.amazonaws.com/aurora/Amazon+Aurora.pdf