Posted inMySQL

MySQL向けProxySQL構築ガイド：Read/Write Splitting、Connection Pooling、Query Cachingの実装

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

背景とProxySQLが必要とされる理由

トラフィックが急増した際、データベース層は最初にボトルネックになりやすいポイントです。最も一般的な対策は、書き込み処理を担う1台のMasterノードと、読み取り負荷を分散する2〜3台のSlaveノードで構成されるMySQL Replicationクラスターを構築することです。

しかし、このアーキテクチャはアプリケーション側に新たな課題をもたらします。アプリ側でどのクエリをMasterに送り、どれをSlaveにルーティングすべきかをどのように判別すればよいでしょうか。開発チームにソースコード内の全Repositoryのリファクタリングを強制すれば、数週間の工数がかかるだけでなく、大規模なリグレッションのリスクに直面します。

さらに深刻なのがコネクション負荷の問題です。フラッシュセールなどのスパイクアクセス時には、数千ものWebワーカープロセスが一斉にMySQLへ接続を試みます。コネクション1本あたり約2MB〜4MBのRAMを消費するため、データベースは即座にmax_connectionsの上限に達し、スレッドスラッシング（thread thrashing）を引き起こしてサービス全体がダウンする原因になります。

ProxySQLはこれらの課題を根本から解決します。インテリジェントなLayer 7リバースプロキシとして機能し、主に3つの重要な価値を提供します。

  • 透過的なRead/Write Splitting：アプリケーションは単一のProxySQLエンドポイントに接続するだけで済みます。SQLの構文解析が自動で行われ、SELECT文はSlaveへ、INSERT/UPDATE/DELETE文はMasterへと適切に振り分けられます。
  • 高パフォーマンスなConnection Pooling：クライアントからの5,000件の接続を、バックエンドのMySQLに対してわずか50〜100件程度の実コネクションプールへと集約します。これにより数ギガバイトのRAMを削減し、頻繁なTCP/TLSハンドシェイクによるレイテンシを排除します。
  • メモリ上のQuery Caching：頻繁に実行される同一の読み取りクエリ結果を、設定したTTLに基づいてProxySQLのメモリ上に直接キャッシュします。MySQLのディスクにアクセスすることなく、1ms未満の超高速レスポンスを実現します。

ただし、レプリケーション遅延（Replication Lag）には十分な注意が必要です。MySQLのレプリケーションは非同期（asynchronous）で行われます。ユーザーが注文を作成した直後にページを再読み込みした際、0.5秒遅延しているSlaveにクエリが送られると、作成したはずの注文が見えないという事象が発生します。ProxySQLでレプリケーション遅延を厳密に監視する設定を行うか、読み書きトランザクションをMaster上に維持する設計を検討してください。

ProxySQLのインストール

ProxySQLは主要なLinuxディストリビューションのほとんどに対応しています。以下はUbuntu 20.04/22.04 LTSにおける標準的なインストール手順です。

# 必要なユーティリティパッケージをインストール
sudo apt-get update && sudo apt-get install -y wget gnupg lsb-release

# ProxySQL公式のGPGキーとリポジトリを追加
wget -qO - https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key | gpg --dearmor | sudo tee /usr/share/keyrings/proxysql-keyring.gpg > /dev/null
echo "deb [signed-by=/usr/share/keyrings/proxysql-keyring.gpg] 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
sudo systemctl status proxysql

起動に成功すると、ProxySQLはデフォルトで以下の2つのポートをリッスンします。

  • Port 6032：SQL構文を用いて管理操作を行うAdmin Interface（デフォルトアカウント: admin/admin）。
  • Port 6033：アプリケーションからのクエリを受け付けるトラフィックポート（従来のポート3306を代替）。

詳細設定

ProxySQLのアーキテクチャは、MEMORY（ランタイムでの編集用）、RUNTIME（実際にトラフィックを処理中）、DISK（SQLiteファイル /var/lib/proxysql/proxysql.db への永続化）の3層構造で動作します。設定はMEMORY上で変更し、RUNTIMEにロードした後にDISKへ永続化するのが基本フローです。

1. バックエンドサーバーの登録

MySQLクライアントから管理用インターフェースに接続します。

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

ここでは、Masterサーバー 192.168.1.10 とSlaveサーバー 192.168.1.11 の構成を想定します。書き込み（Master）用にHostgroup 10、読み取り（Slave）用にHostgroup 20の2つのグループを定義します。

-- MasterをHostgroup 10に登録（最大コネクション数200に設定）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, weight)
VALUES (10, '192.168.1.10', 3306, 200, 1000);

-- SlaveをHostgroup 20に登録（最大コネクション数500に設定）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, weight)
VALUES (20, '192.168.1.11', 3306, 500, 1000);

-- 設定をRuntimeに反映し、Diskに永続化
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

2. 接続ユーザーの登録

ProxySQLはバックエンドのMySQLへクエリを転送する前に、クライアントからの認証を検証する必要があります。事前に両方のデータベースノードで同一パスワードの app_user アカウントを作成しておいてください。

-- このユーザーのデフォルトルーティングをHostgroup 10（Master）に設定
INSERT INTO mysql_users (username, password, default_hostgroup, active)
VALUES ('app_user', 'MatKhauSieuKho123@', 10, 1);

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

3. Read/Write Splittingの設定

mysql_query_rules テーブルで正規表現（Regex）を用いてクエリの振り分けを行います。ルールの評価はIDの昇順（番号の小さい順）に実行されます。

DELETE FROM mysql_query_rules;

-- ルール1: 不適切なノードでのロックを防ぐため、SELECT ... FOR UPDATEはMasterに転送
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '(?i)^SELECT.*FOR UPDATE', 10, 1);

-- ルール2: 通常のすべてのSELECT文をSlaveへ転送
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '(?i)^SELECT', 20, 1);

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

(?i) フラグにより、大文字と小文字を区別せずに正規表現マッチングを行います。apply = 1 パラメータは、ルールに合致した時点で後続ルールの評価を打ち切る指定です。これにより、FOR UPDATE を含むクエリはルール1で確実に捕捉され、Masterへ直接ルーティングされます。

4. Connection Poolingの最適化

ProxySQLのプーリング機構により、バックエンドへの不要なコネクション再作成を最小限に抑えられます。以下の2つのグローバル変数を用いて、アイドル状態の接続比率とライフサイクルをチューニングできます。

-- 急なトラフィックスパイクに即応できるよう、プール内に最低30%の空きコネクションを維持
SET mysql-free_connections_pct = 30;

-- バックエンドのメモリリークを防ぐため、30分（1800000ms）ごとにコネクションを再作成
SET mysql-connection_max_age_ms = 1800000;

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

5. ProxySQLでのQuery Cachingの有効化

カテゴリー情報やシステム設定など、更新頻度の低いデータに対してProxySQLのインメモリキャッシュを有効化することで、SlaveのCPU負荷を劇的に低減できます。キャッシュ時間は cache_ttl パラメータにミリ秒単位で指定します。

-- categoriesテーブルに対するクエリ結果を10秒間（10000ms）キャッシュ
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, cache_ttl, apply)
VALUES (3, 1, '(?i)^SELECT.*FROM\s+categories', 20, 10000, 1);

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

2,000 QPS規模のカテゴリーテーブル参照において、この設定によりバックエンドへの到達クエリを最大95%削減できます。結果としてSlaveデータベースの負荷を大幅に抑えることが可能です。

動作確認とモニタリング

アプリケーションの設定で、接続先ホストをProxySQLのポート6033に変更します。コマンドラインから直接クエリを実行して、想定通りにルーティングされているか確認できます。

# SELECTを実行してSlaveへのルーティングを確認
mysql -u app_user -pMatKhauSieuKho123@ -h 127.0.0.1 -P 6033 -e "SELECT @@hostname;"

# INSERTを実行してMasterへの書き込みを確認
mysql -u app_user -pMatKhauSieuKho123@ -h 127.0.0.1 -P 6033 -e "INSERT INTO my_db.logs (message) VALUES ('test_proxysql');"

statsデータベースによるメトリクスの監視

ProxySQLは stats データベース内にリアルタイムのメトリクスを保持しています。定期的に確認すべき重要なテーブルは以下の3つです。

-- 1. コネクションプールの状態と各ノードのクエリ処理数を確認
SELECT hostgroup, srv_host, Queries, ConnUsed, ConnFree, ConnOK 
FROM stats.stats_mysql_connection_pool;

-- 2. 各ルーティングルールのヒット数を確認
SELECT rule_id, hits FROM stats.stats_mysql_query_rules;

-- 3. Query Cacheのメモリ使用効率とヒット状況を確認
SELECT * FROM stats.stats_mysql_query_cache;

ConnFree が常に安全な水準を維持し、Query Cacheの hits 数が順調に増加していれば、ピーク時のトラフィックスパイクにも耐えうる安定したシステム基盤が整ったと言えます。

Share:
Tags: