ProxySQL

ProxySQL

弊社ではDBバックエンドへの接続にMaxScaleを使っていますが、別のソリューションも欲しかったのでProxySQLを試した時のドキュメントです。何かお役に立てば幸いです。

少し概念がややこしいので、ドキュメントは充実していますので、ちゃんと使いたい人はきちんと読みましょう。

ちなみに、CentOS7でしか試してないので悪しからず！

Install

公式の通りです。yumでインストールできるので楽勝ですね。

Getting started

起動

service proxysql start

面白いのは、ProxySQL自体がMySQLのようなインタラクティブShellを持っていて、SQLっぽい構文で命令を実行できます。

ドキュメントでいうと、この辺

インタラクティブShellに接続するためにはMySQL Clientが必要です。入ってなかったらインストールしてね〜

Configuration

さて、設定ですが、ProxySQLの設定がどのように反映されるかのアーキテクチャを理解していないとわかりにくいかもしれません。

次の図のように設定にレイヤーを持っており、インタラクティブShellから各々命令を発行して設定を反映させます。

Multi layer configuration system

+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+

コンフィグファイルを修正させてMemoryにロードさせるもよし、再起動時に設定を保持したければDiskに書き込むもよし、インタラクティブに設定をするもよし。

configから各設定をloadする手順は次の5つのコマンドがあります。。。もっとあるかもしれないが気にしない！

• LOAD MYSQL USERS FROM CONFIG
• LOAD MYSQL SERVERS FROM CONFIG
• LOAD MYSQL QUERY RULES FROM CONFIG
• LOAD MYSQL VARIABLES FROM CONFIG
• LOAD ADMIN VARIABLES FROM CONFIG

例を挙げると次のような感じ！

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# user, serverの情報をconfigからload
Admin> LOAD MYSQL USERS FROM CONFIG;
Admin> LOAD MYSQL SERVERS FROM CONFIG;
# user, serverの情報をdbにsave
Admin> SAVE MYSQL USERS FROM MEMORY;
Admin> SAVE MYSQL SERVERS FROM MEMORY;
Admin> PROXYSQL RESTART;
# user, serverの情報をruntimeにload
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> LOAD MYSQL SERVERS TO RUNTIME;

Backendにmonitor用ユーザ作成

# read_only Global variableを変更する必要があるのでSUPERのみ権限付与
GRANT SUPER ON *.* TO proxysql@'%' IDENTIFIED BY 'proxysql';

Read/Write Split

MaxScaleのようにRead/Writeのクエリをノード別に振り分ける設定をしたい場合、アプリケーション側の実装に依存して次の方法を選択可能となっている！

• ユーザ
• ポート
• Query Rule

MaxScaleと違ってQuery Ruleで分けれるのがポイントで、細かいルールを引けば可能性無限大？

Query RuleでRead/Write Split

というわけで、Query Ruleで分ける設定を試してみました。

ここからはドキュメントとか見ながら頑張って設定してみてね！

サーバ設定は次の通り。

Admin> select * from mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 500          | pxc001   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc002   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc003   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

バックエンドは弊社ではおなじみのPercona XtraDB Clusterを構成しています。

ユーザの設定は次の通り。

Admin> select username,password,active,default_hostgroup,transaction_persistent from mysql_users where username='sysbench';
+----------+----------+--------+-------------------+------------------------+
| username | password | active | default_hostgroup | transaction_persistent |
+----------+----------+--------+-------------------+------------------------+
| sysbench | sysbench | 1      | 500               | 0                      |
+----------+----------+--------+-------------------+------------------------+
1 row in set (0.00 sec)

ユーザ設定のポイントは次の通り。

• default_hostgroupをWriteのhostgroupに設定しておく
• transaction_persistentを0に設定しておかないとQuery Ruleが効かない

用意するQuery Rule は次の通り。

Admin> select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+------------------------+-----------------------+-------+
| rule_id | active | match_pattern          | destination_hostgroup | apply |
+---------+--------+------------------------+-----------------------+-------+
| 10      | 1      | ^SELECT .* FOR UPDATE$ | 500                   | 1     |
| 20      | 1      | ^SELECT                | 501                   | 1     |
+---------+--------+------------------------+-----------------------+-------+
2 rows in set (0.00 sec)

ここが残念ポイントで、上記設定でRead/Write Splitは実現可能だが、Failoverは実現できない。

Replication Host Group

というわけでFailoverを実現させるためにRHG(Replication Host Group)という概念を利用します。

サーバ設定を次のように設定する。

Admin> select * from mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 500          | pxc001   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 500          | pxc002   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 500          | pxc003   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc001   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc002   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc003   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 8500         | pxc001   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 8500         | pxc002   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 8500         | pxc003   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
9 rows in set (0.00 sec)

うわぁ、なんか冗長だしやな感じ〜。でもFailoverさせるためだ、我慢しよう。

RHGを次のように設定!

Admin> INSERT INTO mysql_replication_hostgroups VALUES(500,501,'');
Query OK, 1 row affected (0.00 sec)

Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+---------+
| 500              | 501              |         |
+------------------+------------------+---------+
1 row in set (0.00 sec)

RHGを設定すると、read_onlyの状態を監視しONならwriter_hostgroupのhostをBackendから外してくれます。

上記例でpxc002,pxc003をread_onlyに設定すると次のような状態になります！

Admin> select * from mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 500          | pxc001   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc001   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc002   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 501          | pxc003   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 8500         | pxc001   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 8500         | pxc002   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
| 8500         | pxc003   | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
7 rows in set (0.00 sec)

この状態での挙動は次のようになります。

• pxc001がcrashするとhostgroup 500のホストが存在しなくなりWriteが停滞
• pxc002またはpxc003のread_onlyをOFFに設定することでFailover可能

pxc001復帰時にread_onlyをONに設定する必要があるのでmy.cnfでread_onlyを設定しておくこと！

Failoverの自動化

便利なもの作ってくれた人がいました〜！

galera_check.plというツールを利用してFailoverを自動化することができます。。。

これをProxySQLのスケジューラ機能を使って…

delete from scheduler where id=10;
INSERT INTO scheduler (id,active,interval_ms,filename,arg1) VALUES
(10,0,2000,
"/var/lib/proxysql/galera_check.pl",
"-u=admin -p=admin -h=127.0.0.1 -H=500:W,501:R -P=6032 --execution_time=1 --retry_down=2 --retry_up=1 --main_segment=1 --active_failover=1 --debug=0 --log=/var/log/proxysql/galeraLog");
LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK;

update scheduler set active=1 where id=10;
LOAD SCHEDULER TO RUNTIME;

では、バイバイ〜！

---

株式会社grasys(グラシス)は、技術が好きで一緒に夢中になれる仲間を募集しています。

grasysは、大規模・高負荷・高集積・高密度なシステムを多く扱っているITインフラの会社です。Google Cloud (GCP)、Amazon Web Services (AWS)、Microsoft Azureの最先端技術を活用してクラウドインフラやデータ分析基盤など、ITシステムの重要な基盤を設計・構築し、改善を続けながら運用しています。

お客様の課題解決をしながら技術を広げたい方、攻めのインフラ技術を習得したい方、とことん技術を追求したい方にとって素晴らしい環境が、grasysにはあります。
お気軽にご連絡ください。

株式会社grasys | 採用情報