スマートスタイル TECH BLOG

データベース&クラウド技術情報

ProxySQL と Percona XtraDB Cluster でロードバランシングと高可用性を実現する

今回は、MySQLのマルチマスター構成を実現し高い可用性を持つ Percona XtraDB Cluster(以下:PXC)とハイパフォーマンスなSQLプロキシのProxySQLの組み合わせてロードバランシングと高可用性を実現するための方法を検証します。

※ PXCは「Galera Cluster」をベースとした製品です。

ProxySQLとは?

ProxySQLは、ハイパフォーマンスなMySQLのSQLプロキシです。MySQLのフォークであるPercona Server や MariaDB だけでなく、Galera Cluster にも対応しています。
クエリールールによるルーティングなど柔軟な設定変更が可能で、またリスタートを行わずに設定を反映できるなど、高い可用性も持ち合わせています。

主な特徴

  • クエリーキャッシュ
  • クエリールーティング
  • フェイルオーバー連携
  • ファイアーウォール

参考
ProxySQL 公式サイト
ProxySQL – Github

Percona XtraDB Cluster とは?

Percona XtraDB Cluster は Percona社が開発するマルチマスター構成の同期型レプリケーションで、全てのノードに対して書き込み、読み込みが可能で、単一障害点が無い高い可用性と、自動死活監視、自動ノードプロビジョニングなど運用容易性を持ったクラスタリングソリューションです。

参考
Percona XtraDB Cluster
弊社ブログ 関連記事

構成図

今回の検証での構成はProxySQLが1台なので、ここが単一障害点となってしまいますが、通常の運用では多重化したり、複数あるアプリケーションサーバーと同居させることで可用性を担保します。

sysbench 1.0
ProxySQL 1.4.3
Percona XtraDB Cluster 5.7.19-29.22

node1: 172.26.31.202
node2: 172.26.31.203
node3: 172.26.31.204

インスタンスは全てEC2
OSは全て CentOS 7.3

PXCのセットアップ

PXCのセットアップは下記のURLなどを参考にセットアップを行なってください。

Installing Percona XtraDB Cluster on Red Hat Enterprise Linux and CentOS

ProxySQLのセットアップ

ここでは Percona のレポジトリを使ってインストールする方法で行います。

レポジトリの追加を行います。

ProxySQLのインストールします。

PXCの監視やProxySQLのSQL管理インターフェースでログインするために MySQL Client もインストールします。

サービスをスタートします。

ProxySQLの設定

PerconaのパッケージからProxySQLをインストールすると、初期設定が簡単に行える proxysql-admin というツールが同梱されていますが、今回はこのツールを使用せずに設定を行います。

参考:Load balancing with ProxySQL

ProxySQLの設定方法は、同様のSQLプロキシである、MySQL RouterMaxScale のように設定ファイルによる設定方法だけで無く、SQLによる管理インターフェースによって設定することができます。
また、ProxySQLの設定は Multi layer configuration system と呼ばれる下記の図のような3層構造になっています。

  • RUNTIME
    ここに反映されている設定でProxySQLが動作します。
  • MEMORY
    DISKやCONFIG FILEの設定が反映された状態。mysqlコマンドでログインしてSQLで更新することができます。
  • DISK
    MEMORYの設定をディスクに保存します。設定ファイルとは別でこちらの設定のほうが優先されます。
  • CONFIG FILE
    /etc/proxysql.cnfのファイルが読み込まれます。

参考:Multi layer configuration system · sysown/proxysql Wiki · GitHub

設定ファイルによる初期設定

/etc/proxysql.cnf にProxySQLの設定ファイルが配置されるので必要に応じてこのファイルを編集してください。
今回の検証ではデフォルトのまま使用します。

SQLによる管理インターフェースによる設定

ProxySQLのSQL管理インターフェースは通常の mysql コマンドを使ってログインすることができます。
(管理インターフェースのポートはデフォルト6032です)

見た目はMySQLと同じように見えますが、実際のデータベースはSQLite3が使われています。

PXC の監視ユーザーを作成

PXCへの監視用ユーザーは mysql_variables の以下の項目に設定されているので必要に応じて設定してください。

今回はデフォルトの監視用ユーザーをPXCに作成します。必要な権限は USAGE なので、権限付与は不要です。
(PXCなので、どれかのノードでユーザーを作成すれば、全ノードに反映されます)

ロードバランシング

PXCではマルチマスターのため、全てのノードに対して書き込みが可能で、どのノードのデータも同一になります。
ただし、PXCの仕様として同じレコードに対する更新トランザクションが別々のノードで同時に行われた場合、後から更新されたトランザクションはロールバックされエラーとなります。この場合、アプリケーション側でエラー処理としてリトライなどの処理を追加する必要があります。

また、更新データが他のノードに伝播してから反映されるのに若干のラグが発生する場合があります。

これらの制約を理解した上で、ロードバランシングの方法を決定します。

シングルプライマリ

非常に厳格なデータ整合性が必要な場合や、マスタースレーブ構成で運用中の既存のアプリケーションをPXCに載せ替えたい場合は、更新処理を1ノードに限定することで従来のマスタースレーブ構成と同等の動きを実現できます。

アプリケーションからの接続ポートに応じて、接続するノードを切り替えることもできますが、今回は接続ユーザーでノードを切り替える設定を行います。

ProxySQLでシングルプライマリの設定をする場合

まず、バックエンドのMySQLの設定は mysql_servers テーブルを操作します。

hostgroup_id フィールドはバックエンドのMySQLをグルーピングすることができ、IDは自由に付与することができます。今回は更新系は0、参照系は1とします。
PXCの場合、どのノードからも更新も参照もできるため、3つのノードはどちらのグループにも所属させるようにします。

status はバックエンドのMySQLの状態です。
更新については1ノードに限定し、該当ノードがダウンしたら別のノードに切り替えたいのでグループ0のstatus はノード1を除いてSOFT_OFFLINEの設定にします。
参照については全ノード分散させたいので、全てONLINEに設定します。

設定は以下のようになります。

次にPXC側に書き込み用と読み込み用のユーザーを作成します。

ProxySQL側では書き込み用ユーザーは更新系グループの0へ、読み込み用ユーザーは参照系グループの1に接続が向くように設定します。

PXCの監視用スクリプトを有効にします。

スクリプトに渡す引数の意味は以下の通りです。

引数 説明
arg1 書き込みノードの hostgroup ID を指定
arg2 読み込みノードの hostgroup ID を指定
arg3 書き込みノードの最大数
arg4 0に設定すると、書き込みノードとしてONLINEになると、読み込みノードではONLINEになりません
arg5 ログファイル

ProxySQL経由でバックエンドのノードに接続します。(デフォルトのポートは6033です)
書き込みユーザーで接続する

読み込みユーザーで接続する

マルチプライマリ

書き込み、読み込み共に全てのノードに分散されるのでシンプルでわかりやすい構成です。

ProxySQLでマルチプライマリの設定をする場合

マルチプライマリの場合は hostgroup_id を更新系と参照系に分ける必要がないため、一つのグループに全てのノードが所属するよう設定します。

status についても全てのノードに更新も参照も分散させるため、全てONLINEとなります。

マルチプライマリの設定は以下になります。

PXC側のユーザーを作成します。
(シングルプライマリのように書き込み用と読み込み用でユーザーを分ける必要はありません)

ProxySQL側は以下のような設定になります。

ProxySQL経由でバックエンドのノードに接続します。

障害発生時の挙動

PXCでは1ノードがダウンした場合でも、残りの2ノードで継続してサービスを継続することができます。

例えば、従来のマスタースレーブ構成のような、マスターがダウンした場合、スレーブをマスターに昇格させるようなフェイルオーバーの処理も必要ありません。
また、PXCでは更新トランザクションは全てのノードに伝播しているため、従来の非同期レプリケーションのようにデータロスも発生しません。

そのため、PXCでノードがダウンしても、ProxySQL側ではダウンしたノードを単純に切り離すだけでフェイルオーバーは完了するというシンプルなものとなります。

シングルプライマリで書き込みノードがダウンした場合

書き込みノードがダウンした場合、ProxySQLは障害を検知して、残り2ノードのうち、どちらかを新しい書き込みノードに変更してサービスを継続させます。

マルチプライマリで1ノードがダウンした場合

1ノードがダウンした場合、単純にダウンしたノードが切り離され、サービスは継続されます。

まとめ

  • PXCとProxySQLを組み合わせることで、運用の手間を軽減しつつ、高い可用性を実現できます。
  • PXCはSQLレベルでMySQLと同じため、シングルプライマリにすれば従来のマスタースレーブレプリケーションと同じように使えるので既存のアプリケーションからの乗り換えも可能です。
  • ProxySQLの設定方法は他のSQLプロキシと比較しても特殊なので少し慣れが必要かもしれません。

Percona

 

Return Top