ProxySQLによるMySQLのRead/Write Splitting設定：アプリ改修なしで読み書き負荷を分散 - ITFROMZERO - Share tobe shared! MySQL MasterのCPUが100%に達しているのにReplicaは遊休状態？ProxySQLを使用した自動Read/Write Splitting（読み書き分離）の設定手順をステップバイステップで解説。アプリ改修なしで本番環境の負荷を即座に解消します。

Table of Contents

深夜2時15分、鳴り響くPagerDuty：Replicaが「ただの飾り」になっていた夜

深夜2時15分、PagerDutyのアラートでスマートフォンが激しく震えました。Grafanaダッシュボードは警告の赤一色。MySQL MasterノードのCPU使用率は100%に張り付き、Threads_runningは15から一気に350以上へと急上昇。API Gatewayからは504 Gateway Timeoutエラーが大量に吐き出されていました。その一方で、隣に並ぶ8コア・32GB RAMのReplicaノード2台は、CPU使用率が3%にも満たず、完全に遊休状態（宝の持ち腐れ）だったのです。

これは実によくある光景です。Master-Slave構成のレプリケーションクラスタは完璧に構築されているものの、すべてのマイクロサービスがMasterの単一の接続文字列（connection string）を指したままになっているケースです。読み取りクエリも書き込みクエリもすべて1箇所に集中していました。今夜中に読み書き分離（Read/Write Splitting）を実施しなければ、翌朝9時の大型セールでシステムが完全に崩壊するのは目に見えていました。

Read/Write分離における3つのアプローチを比較

この課題を解決するには、主に3つのアプローチが存在します。

1. アプリケーション層でのコネクションプール分離（Application Routing）

バックエンド側で書き込み用のdb_masterと読み取り用のdb_replicaという2つのDataSourceを定義します。開発者が手動でクエリを振り分けるか、アノテーションを設定する必要があります（Spring Bootの@Transactional(readOnly = true)や、Laravel/DjangoのRead Replicaプール設定など）。

2. HAProxyによるポート単位の分離

HAProxyを導入し、ポート3306をMasterへの転送用、ポート3307をReplicaクラスタへのロードバランス用として開放します。ただし、アプリケーション側で用途に応じた適切なポートを呼び出すロジックを管理し続ける必要があります。

3. インテリジェントなDatabase Proxy（ProxySQL）の導入

ProxySQLを単一のエントリポイント（ポート6033）として配置します。Layer 7でリアルタイムにSQL構文を解析し、SELECTならReplicaへルーティングし、INSERT/UPDATEならMasterへ維持します。アプリケーション側はバックエンドの変更を一切意識する必要がありません。

本番環境の緊急障害対応でProxySQLが選ばれる理由

どのソリューションもシステムのフェーズに応じて一長一短があります。

アプリケーションコードの改修： 長期的には理想的なアプローチです。しかし、深夜に15個ものマイクロサービスのコネクションプールを改修し、トランザクションコンテキストを検証してデプロイするのは自殺行為に等しいと言えます。トランザクション内の書き込み直後の読み取り（read-your-own-writes）を1箇所でも見落とせば、即座にデータ不整合が発生します。
HAProxy： 非常に軽量で高負荷にも耐えられます（Layer 4）。しかし残念ながら、HAProxyはSQL文の中身を解読できません。SELECTとUPDATEを判別できないため、ルーティングの責任はすべてアプリケーション側に押し戻されてしまいます。
ProxySQL： Layer 7でMySQLプロトコルをネイティブに理解します。正規表現による自動ルーティングに加え、強力なコネクションプーリング、オンメモリでのクエリキャッシュ、レプリケーション遅延の自動検知機能を備えています。何より決定的なのは、アプリケーション側はホストとポートをたった1行変更するだけで済む点です。

アーキテクチャ構成

本記事で想定するインフラ環境は以下の3ノード構成です。

MySQL Master (Writer): 192.168.1.10:3306
MySQL Replica (Reader): 192.168.1.11:3306
ProxySQL Node: 192.168.1.5（クライアントポート: 6033、管理ポート: 6032）

ProxySQLによるRead/Write Splitting構築手順

ステップ1：プロキシサーバーへのProxySQLインストール

Ubuntu/DebianノードにProxySQL 2.5.xをインストールします。

sudo apt-get update
sudo apt-get install -y lsb-release curl apt-transport-https
curl -fsSL https://repo.proxysql.com/repo/proxysql.repo.gpg | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/proxysql.gpg
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt-get update
sudo apt-get install -y proxysql
sudo systemctl enable --now proxysql

ステップ2：MySQL Masterでの監視用ユーザー作成

ProxySQLがヘルスチェックおよびレプリケーション遅延（Replication Lag）を監視するための専用ユーザーが必要です。Masterで以下のコマンドを実行します（Replicaノードへも自動的にレプリケーションされます）。

CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

ステップ3：ProxySQLでのバックエンドサーバーとHostgroup設定

ProxySQLの管理インターフェースにログインします（デフォルトのユーザー／パスワードはポート6032のadmin:adminです）。

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

サーバーを2つのグループに分割します。Writer（Master）用のHostgroup 10と、Reader（Replica）用のHostgroup 20です。

-- 1. 監視用ユーザーの設定
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonitorPass123!' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 2. データベースノードをHostgroupに追加
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, weight, max_replication_lag) VALUES 
(10, '192.168.1.10', 3306, 1000, 1, 0),
(20, '192.168.1.11', 3306, 1000, 1, 10);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

設定パラメータのmax_replication_lag = 10は非常に重要です。Replicaの遅延がMasterに対して10秒を超えた場合、ProxySQLは古いデータの読み取りを防ぐため、該当ノードへの読み取りトラフィックを即座に遮断します。

ステップ4：アプリケーションユーザーのProxySQLへの登録

バックエンドがデータベース接続に使用しているアカウントを登録します。

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) 
VALUES ('app_user', 'SecretAppPass', 10, 1);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

フラグtransaction_persistent = 1の設定は極めて重要です。これにより、アクティブなトランザクション（BEGIN ... COMMIT）内のすべてのクエリ（SELECTを含む）がMaster上に固定されます。その結果、レプリケーション遅延によるダーティリードや競合状態（Race Condition）を完全に防止できます。

ステップ5：自動ルーティング用Query Rulesの設定

ここがトラフィック分散メカニズムの要となる部分です。優先順位（rule_id）がデータの整合性を左右します。

-- Rule 1: 行ロックを安全に維持するため、SELECT ... FOR UPDATEはMasterに留める
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- Rule 2: 残りのすべてのSELECTクエリをReplicaへ転送
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT', 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Rule 1における属性apply = 1は決定的な役割を果たします。クエリがFOR UPDATEパターンに一致した場合、ProxySQLは直ちにそれをHostgroup 10へ割り当て、後続のルールの評価を停止します。これにより、対象クエリがRule 2によって誤ってReplicaへルーティングされるのを確実に防ぎます。

ステップ6：動作検証と負荷確認

バックエンドの接続文字列を旧MasterのIPから192.168.1.5:6033に変更します。ProxySQL管理ターミナルを開き、クエリが意図通り分離されているかを確認します。

SELECT hostgroup, schemaname, digest_text, count_star 
FROM stats_mysql_query_digest 
ORDER BY count_star DESC LIMIT 10;

hostgroupカラムを確認すると、SELECT文が着実にHostgroup 20へ分散されている一方、INSERT/UPDATEはHostgroup 10へと直接送られていることが分かります。読み取り負荷の約75%がReplicaノードへと綺麗にオフロードされました。MasterノードのCPU使用率は100%から約25%へと急速に低下し、Threads_runningも安全圏内の18スレッドまで激減しました。アプリケーションのコードを1行も触ることなく、システムの安定稼働を取り戻すことができました。