この記事は、ニフクラブログで2022-09-21に公開された記事を移転したものです。
こんにちは、富士通クラウドテクノロジーズの池内です。
ニフクラRDBは、DBサーバーの構築・運用・規模拡大だけではなく、簡単に複数台のリードレプリカの作成を行うことができます。
作成したリードレプリカは個別に利用することも可能ですが、オープンソースのProxySQLを利用することで、複数のリードレプリカをまとめて負荷分散、冗長化構成にできます。
本記事ではProxySQLを利用した、複数リードレプリカの冗長化構成の構築を検証します。
- 前提条件
- 検証概要
- 検証環境
- 構築手順
- 構築の実施
- 1. ニフクラRDB作成
- 2. クライアント兼、ProxySQLサーバー作成
- 3. クライアント兼、ProxySQLサーバーへ作業にMySQL clientをインストール
- 4. クライアント兼、ProxySQLサーバーへ作業にProxySQLをインストール
- 5. ProxySQLを利用するためのデータベース設定
- 6. RDBのリードレプリカ作成
- 7. ProxySQLの設定
- 検証手順
- 検証の実施
- まとめ
- 付録
- 注意事項
前提条件
本ブログは、以下の前提知識がある方を想定しています。
- ニフクラの基本的なコントロールパネルの操作、サービス利用に関する知識
- Linuxの基本的な操作、設定に関する知識
- MySQLの基本的な操作、設定に関する知識
検証概要
ProxySQLを使用して、ニフクラのRDBで複数作成したリードレプリカを冗長化する検証を行います。
本来はProxySQL専用サーバーを設けることが望ましいのですが、今回は検証の簡略化のためクライアントサーバ上に構築します。
構成イメージ
構成イメージは以下となります。
【補足】ProxySQL
ProxySQLとは、オープンソースのMySQL/MariaDB用の高性能で高可用性なSQLプロキシです。
ProxySQLでできることは、
- データベースファイアウォール
- クエリルールの設定(キャッシュ、ルーティング)
- シャーディング&オンザフライでのデータ補正
- フェイルオーバーサポート
など多岐にわたります。
検証環境
RDB
| 項目 | 内容 |
|---|---|
| DBエンジン | MySQL 5.7 |
| サーバータイプ | db.e-small |
| ディスクタイプ | 高速ディスク |
| ディスク容量 | 50GB |
| 冗長化 | シングル構成 |
| リードレプリカ | 2台 |
クライアント兼、ProxySQLサーバー
| 項目 | 内容 |
|---|---|
| ホスト名 | tecveri |
| OS | Rocky Linux8.5 |
| サーバータイプ | h2-medium |
| アプリケーション | ProxySQL 2.4.2 sysbench 1.0.20 |
構築手順
構築の大まかな流れは以下の通りです。
- ニフクラRDB作成
- クライアント兼、ProxySQLサーバー作成
- クライアント兼、ProxySQLサーバーへ作業にMySQL clientをインストール
- クライアント兼、ProxySQLサーバーへ作業にProxySQLをインストール
- ProxySQLを利用するためのデータベース設定
- RDBのリードレプリカ作成
- ProxySQLの設定
構築の実施
作業前に以下情報を確定しておきます。
※ 項番は、手順中にて(A)のように参照しています。
| 項番 | 項目 | 用途 |
|---|---|---|
| A | RDBのユーザー名 | RDB作成時に使用 |
| B | RDBのパスワード | RDB作成時に使用 |
| C | RDBに作成するデータベース名 | 検証で利用 |
| D | ProxySQL用のRDBユーザー | ProxySQLが接続するために利用 |
| E | ProxySQL用のRDBパスワード | ProxySQLが接続するために利用 |
| F | ProxySQLのパスワード | ProxySQL操作時に使用 |
1. ニフクラRDB作成
今回はMySQL 5.7を使用します。
作成時のパラメータは以下としました。
| リソース | DB名 | 冗長化 | 自動バックアップ |
|---|---|---|---|
| MySQL 5.7 | rdbtest | シングル | YES |
作成方法の詳細は以下をご参照ください。
クラウドヘルプ（RDB作成）
2. クライアント兼、ProxySQLサーバー作成
今回はRocky Linux8.5を使用します。
作成方法の詳細は以下をご参照ください。
クラウドヘルプ（SSHキー）
クラウドヘルプ（サーバーの作成）
クラウドヘルプ（ファイアウォールグループの新規作成）
3. クライアント兼、ProxySQLサーバーへ作業にMySQL clientをインストール
RDBサーバーへ設定を実施するため、mysql clientを導入します。
3.1. MySQLのclientをインストールするためにリポジトリを追加します。
# rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm
3.2. クライアント兼、ProxySQLサーバーへMySQLのclientインストール
RDBサーバーへアクセスするためにMySQLのclientをインストールします。
# dnf install -y mysql-community-client # mysql --version mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
mysqlコマンドが実行でき、バージョン番号が表示されていることを確認します。
以上で、MySQLのclientのインストールが完了しました。
4. クライアント兼、ProxySQLサーバーへ作業にProxySQLをインストール
wgetを使用し、公式からProxySQLをインストールします。
合わせてProxySQLの依存アプリケーションもインストールします。
4.1. ProxySQLインストールに必要なパッケージを導入
ProxySQLをインストールするために必要なパッケージを導入しておきます。
# dnf upgrade --refresh -y # dnf install -y perl wget
4.2 ProxySQLのパッケージダウンロードおよびインストール
公式からProxySQLのrpmをダウンロードし、rpmからインストールします。
# wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql-2.4.2-1-centos8.x86_64.rpm # ls proxysql-2.4.2-1-centos8.x86_64.rpm proxysql-2.4.2-1-centos8.x86_64.rpm # rpm -ivh proxysql-2.4.2-1-centos8.x86_64.rpm
4.3. ProxySQLを起動します。
systemctlで起動後、状態を確認します。
# systemctl start proxysql
# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2022-09-01 09:38:42 JST; 4s ago
Process: 4288 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 4291 (proxysql)
Tasks: 24 (limit: 11203)
Memory: 66.3M
CGroup: /system.slice/proxysql.service
├─4291 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─4292 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
Active:の項目がactive(running)となっていることを確認します。
以上で、ProxySQLのインストールが完了しました。
5. ProxySQLを利用するためのデータベース設定
RDBで作成したMySQLへ、ProxySQLで使用するためのユーザー追加と権限の付与を行います。 また、検証に使用するテストデータベース、テーブルも作成します。
5.1. 作成したRDBへ接続
# mysql -h 作成済みのRDBのIP -P 3306 -u RDBのユーザー名(A) -p --ssl-mode=DISABLED password:（RDB作成した際に設定したパスワード） mysql>
無事接続され、mysql>と表示されていることを確認します。
5.2. ProxySQLで利用するためのデータベース・テーブル作成
ProxySQLが監視するのに必要なデータベース・テーブルなどを作成するため、下記SQLを実行します。
設定内容
USE sys;
DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$
CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$
CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result BIGINT DEFAULT 0;
DECLARE colon_pos INT;
DECLARE next_dash_pos INT;
DECLARE next_colon_pos INT;
DECLARE next_comma_pos INT;
SET gtid_set = GTID_NORMALIZE(gtid_set);
SET colon_pos = LOCATE2(':', gtid_set, 1);
WHILE colon_pos != LENGTH(gtid_set) + 1 DO
SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
SET result = result +
SUBSTR(gtid_set, next_dash_pos + 1,
LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
ELSE
SET result = result + 1;
END IF;
SET colon_pos = next_colon_pos;
END WHILE;
RETURN result;
END$$
CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$
CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
DELIMITER ;
5.3. ProxySQL用ユーザーの作成
5.3.1. ProxySQLが操作するために使用するユーザー(D)を作成します。
mysql> CREATE USER 'ユーザー名(D)'@'%' IDENTIFIED BY 'パスワード'(E); mysql> GRANT ALL PRIVILEGES on データベース名(C).* to 'ユーザー名(D)'@'%';
5.3.2. ProxySQLが監視するためのユーザーmonitorを作成します。
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor'; mysql> GRANT SELECT on sys.* to 'monitor'@'%';
5.3.3. MySQLに設定した内容を保存します。
mysql> FLUSH PRIVILEGES;
以上で、ProxySQLから利用するための準備は完了しました。
5.4 検証で利用するテーブルの作成
RDB作成時に指定したデータベースにテーブルを作成し、テスト用データを入力しておきます。
mysql> use rdbtest;
mysql> CREATE table rdbtest.testtb(seat_id varchar(10), office_id varchar(2));
mysql> INSERT INTO testtb(seat_id,office_id) VALUE ('06Z002',01);
mysql> SELECT * FROM testtb;
|seat_id|office_id|
|-------|---------|
|06Z002 | 01|
insertしたデータが表示されている事を確認したら、mysqlへの接続を終了します。
mysql> exit
6. RDBのリードレプリカ作成
今回はリードレプリカを2台作成します。
作成方法の詳細は以下をご参照ください。
クラウドユーザーガイド(RDBリードレプリカの作成)
7. ProxySQLの設定
ProxySQLへデータベースを組み込むため、以下の設定を行います。
- 7.1. ProxySQL管理用のユーザーとパスワード設定
- 7.2. ProxySQL監視用のユーザーとパスワード設定
- 7.3. 振り分け先サーバーの追加
- 7.4. MySQL操作に使用するユーザーとパスワード設定
- 7.5. 振り分け設定
- 7.6. ProxySQLからMySQLの疎通と操作確認
7.1.ProxySQL管理用のユーザーとパスワード設定
ProxySQL管理用のユーザーとパスワードを設定し、設定の反映と保存を実施します
# mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> ' Enter password: admin (初期パスワードはadminです。） ProxySQLAdmin> UPDATE global_variables SET variable_value='admin:パスワード(F)' WHERE variable_name='admin-admin_credentials'; ProxySQLAdmin> LOAD ADMIN VARIABLES TO RUNTIME; ProxySQLAdmin> SAVE ADMIN VARIABLES TO DISK;
7.2. ProxySQL監視用のユーザーとパスワード設定
ProxySQL監視用のユーザーとパスワードを設定し、設定の反映と保存を実施します
ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME; ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
7.3.振り分け先サーバーの追加
振り分け先サーバーグループの作成と、MySQLサーバーの登録を実施していきます。 今回は、書き込みと読み込みを分割するため、select文以外を実施するグループと、select文のみを実施するグループの2つを作成します。
7.3.1 select文以外を行うホストグループの作成
select文以外を行うホストグループにRDBのマスターを追加し、設定を反映、保存します。
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 'マスターのIPアドレス', 3306); ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME; ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
7.3.2 select文を振り分けるホストグループの作成
select文を振り分けるホストグループにすべてのサーバーを追加し、設定を反映、保存します。
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 'マスターのIPアドレス', 3306); ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 'リードレプリカ1のIPアドレス', 3306); ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 'リードレプリカ2のIPアドレス', 3306); ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME; ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
7.3.3 設定内容の確認
設定した内容を確認します。
ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers; +--------------+-----------------------------+--------+ | hostgroup_id | hostname | status | +--------------+-----------------------------+--------+ | 2 | リードレプリカ1のIPアドレス | ONLINE | | 2 | リードレプリカ2のIPアドレス | ONLINE | | 2 | マスターのIPアドレス | ONLINE | | 1 | マスターのIPアドレス | ONLINE | +--------------+-----------------------------+--------+ 4 rows in set (0.05 sec)
以下となっているか確認します。
- hostgroup_idが1となっている行が1行だけ、マスターのIPアドレスのみが表示されていること
- hostgroup_idが2となっている行が3行、マスターおよびリードレプリカのIPアドレス合計3つが表示されていること
- statusがONLINEであること
7.4.RDBのMySQL操作に使用するユーザーとパスワード設定
ProxySQLがMySQLに接続するためのユーザー(D)、パスワード(E)を設定、有効化、保存します。
ProxySQLAdmin> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('ユーザー名(D)', 'パスワード(E)', 1);
ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
mysql_usersテーブルで設定するdefault_hostgroupは「7.5. 振り分け設定」で設定する振り分けに一致しないクエリが来た場合に、処理する振り分け先として「7.3. 振り分け先サーバーの追加」で設定したhostgroup_idの番号を設定しています。
今回はdefault_hostgroupを1にしているので、「7.3.3. 設定内容の確認」のhostgroup_idが1のRDBのマスターのIPアドレスに振り分ける設定になっています。
7.5.振り分け設定
以下二つの振り分け設定を実施します。
| グループID | ルール |
|---|---|
| 1 | SELECT.*FOR UPDATE |
| 2 | SELECTのみ |
ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1, 1,'^SELECT.*FOR UPDATE$',1,1), (2,1,'^SELECT',2,1); ProxySQLAdmin> SELECT rule_id,match_digest,destination_hostgroup FROM mysql_query_rules WHERE active = 1 ORDER BY rule_id; +---------+----------------------+-----------------------+ | rule_id | match_digest | destination_hostgroup | +---------+----------------------+-----------------------+ | 1 | ^SELECT.*FOR UPDATE$ | 1 | | 2 | ^SELECT | 2 | +---------+----------------------+-----------------------+ 2 rows in set (0.00 sec) ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME; ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
想定したとおりのルールが設定されていることを確認します。
設定完了後、セッションを終了させます。
ProxySQLAdmin> exit
7.6.ProxySQLからMySQLの疎通と操作確認
疎通と操作確認にはコンソールを２個使用するため、事前に準備してください。
7.6.1. ProxySQLに対して、RDBへのログインユーザー(A)、パスワード(B)でログインします
# mysql -u ログインユーザ(A) -p -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> ' Enter password: パスワード(B)入力 ProxySQLClient> SELECT * FROM rdbtest.testtb; +---------+-----------+ | seat_id | office_id | +---------+-----------+ | 06Z002 | 1 | +---------+-----------+
「5.4 検証で利用するテーブルの作成」と同じデータが表示されていることを確認します。
7.6.2. データ更新テストの実施
UPDATE文を実行し、正しくデータが更新され、参照できることを確認します。
ProxySQLClient> UPDATE rdbtest.testtb SET office_id = 02 WHERE seat_id = '06Z002'; ProxySQLClient> SELECT * FROM rdbtest.testtb; +---------+-----------+ | seat_id | office_id | +---------+-----------+ | 06Z002 | 2 | +---------+-----------+ ProxySQLClient> exit
office_id が 2 に更新されていることを確認します。
また、select文を複数回確認を実施し、バックエンドのRDBが切り替わっても同じ値が返却されていることを確認します。
7.6.3. アクセスの振り分け先情報の初期化
「stats_mysql_connection_pool」でアクセスがどのhostに振り分けされたのか確認できます。
テーブルの最後に「_reset」と付けることでテーブル情報の初期化が行えます。
今回はselect文が分散されているか確認するために初期化します。
# mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> ' Enter password: パスワード(F)入力 ProxySQLAdmin> SELECT hostgroup,srv_host,status,ConnOK,ConnERR,Queries,Bytes_data_sent FROM stats_mysql_connection_pool_reset; ProxySQLAdmin> SELECT hostgroup,srv_host,status,ConnOK,ConnERR,Queries,Bytes_data_sent FROM stats_mysql_connection_pool; +-----------+------------------------+--------+--------+---------+---------+-----------------+ | hostgroup | srv_host | status | ConnOK | ConnERR | Queries | Bytes_data_sent | +-----------+------------------------+--------+--------+---------+---------+-----------------+ | 2 | リードレプリカ1アドレス | ONLINE | 0 | 0 | 0 | 0 | | 2 | リードレプリカ2アドレス | ONLINE | 0 | 0 | 0 | 0 | | 2 | マスターアドレス | ONLINE | 0 | 0 | 0 | 0 | | 1 | マスターアドレス | ONLINE | 0 | 0 | 0 | 0 | +-----------+----------------+--------+--------+---------+---------+-----------------+
ConnOK、ConnERR、Queries、Bytes_data_sentの項目が0になっていることを確認します。
7.6.4. watchコマンドでselect文を2秒毎に実行します。
2個目のコンソールを使用します。
# watch --differences "mysql -uProxySQL用のRDBユーザー(D) -pProxySQL用のRDBパスワード(E) -h 127.0.0.1 -P 6033 -e'SELECT * FROM rdbtest.testtb;'" seat_id office_id 06Z002 2
7.6.5. select文が分散されているか確認
watchコマンド実行し、10秒後に振り分け先情報のテーブルを参照しました。
ProxySQLAdmin> SELECT hostgroup,srv_host,status,ConnOK,ConnERR,Queries,Bytes_data_sent FROM stats_mysql_connection_pool; +-----------+------------------------+--------+--------+---------+---------+-----------------+ | hostgroup | srv_host | status | ConnOK | ConnERR | Queries | Bytes_data_sent | +-----------+------------------------+--------+--------+---------+---------+-----------------+ | 2 | リードレプリカ1アドレス | ONLINE | 0 | 0 | 1 | 28 | | 2 | リードレプリカ2アドレス | ONLINE | 0 | 0 | 2 | 58 | | 2 | マスターアドレス | ONLINE | 0 | 0 | 2 | 56 | | 1 | マスターアドレス | ONLINE | 0 | 0 | 0 | 0 | +-----------+----------------+--------+--------+---------+---------+-----------------+ 4 rows in set (0.00 sec)
hostgroupが2に属している3台すべてのQueriesがそれぞれ加算されていることを確認します。
動作確認が完了したら、セッションを終了させます。
ProxySQLAdmin> exit
以上で、検証環境の構築が完了しました。
検証手順
検証として、select文を実施しながらリードレプリカを1台再起動し、問題なくデータの取得が継続できることを確認します。
- クライアントサーバー上で2秒ごとにselect文を実施する様に設定
- ProxySQL上のカウンタを確認し、リクエスト振り分け状況を確認
- リードレプリカ1台について、コントロールパネルより再起動を実施
- 1)のコンソールを確認、リクエストへの応答がエラーにならないことを確認
- 2)のコンソールを確認、障害中のリクエスト振り分け状況を確認
- 2)のコンソールを確認、リードレプリカ再起動後のリクエスト振り分け状況を確認
検証の実施
検証にはコンソールを２個使用するため、事前に準備してください。
1. クライアントサーバ上ーで2秒ごとにselect文を実施する様に設定
# watch --differences "mysql -uProxySQL用のRDBユーザー(D) -pProxySQL用のRDBパスワード(E) -h 127.0.0.1 -P 6033 -e'SELECT * FROM rdbtest.testtb;'" seat_id office_id 06Z002 2
コンソールは閉じず、そのままの状態にしておきます。
2. ProxySQL上のカウンタを確認し、リクエスト振り分け状況を確認
2個目のコンソールを使用します。
hostgroupが2に属している3台すべてのQueriesがそれぞれ加算されていくことを確認します。
# watch -n 1 --differences "mysql -uadmin -pパスワード(F) -h 127.0.0.1 -P 6032 -e'SELECT hostgroup,srv_host,status,ConnOK,ConnERR,Queries,Bytes_data_sent FROM stats_mysql_connection_pool;'" hostgroup srv_host status ConnOK ConnERR Queries Bytes_data_sent 2 リードレプリカ1アドレス ONLINE 0 0 51 1428 2 リードレプリカ2アドレス ONLINE 0 0 70 1960 2 マスターアドレス ONLINE 0 0 63 1764 1 マスターアドレス ONLINE 0 0 0 0
3. リードレプリカ1台について、コントロールパネルより再起動を実施
3.1. リードレプリカを1台選択し、メニューより「DBサーバー再起動」を選択します。
3.2. リードレプリカを再起動します。
再起動オプションで「DB通常再起動」を選択し、OKボタンを押します。
4. 1)のコンソールを確認、リクエストへの応答がエラーにならないことを確認
リードレプリカの再起動中に応答エラーにならず、select文の結果が表示されていることを確認します。
seat_id office_id 06Z002 2
5. 2)のコンソールを確認、障害中のリクエスト振り分け状況を確認
hostgroup srv_host status ConnOK ConnERR Queries Bytes_data_sent 2 リードレプリカ1アドレス SHUNNED 0 1 61 1708 2 リードレプリカ2アドレス ONLINE 0 0 140 3920 2 マスターアドレス ONLINE 0 0 123 3444 1 マスターアドレス ONLINE 0 0 0 0
以下となっているか確認します。
- 再起動したリードレプリカのstatusがSHUNNEDと表示されていること
- 再起動したリードレプリカのConnERRが加算されていること
- 再起動したリードレプリカのQueriesは加算されないこと
- 再起動したリードレプリカ以外のhostgroupが2のマスターおよびリードレプリカのQueriesが加算されていくこと
6. 2)のコンソールを確認、リードレプリカ再起動完了後のリクエスト振り分け状況を確認
hostgroup srv_host status ConnOK ConnERR Queries Bytes_data_sent 2 リードレプリカ1アドレス ONLINE 1 1 71 1988 2 リードレプリカ2アドレス ONLINE 0 0 163 4564 2 マスターアドレス ONLINE 0 0 158 4424 1 マスターアドレス ONLINE 0 0 0 0
以下となっているか確認します。
- 再起動したリードレプリカのstatusがONLINEになっていること
- 再起動したリードレプリカのConnOKが加算されていること
- 再起動したリードレプリカのQueriesが加算されていること
以上で、検証が完了しました。
まとめ
今回はProxySQLを使用した、ニフクラRDBのリードレプリカの冗長化方法についてご紹介しました。
ProxySQLを使用することでリードレプリカを複数利用する場合に、簡単に冗長化することができ、リードレプリカ障害時にも、サービスが継続することがご理解いただけたのではないでしょうか。
ここまで読んでいただきありがとうございました！
付録
ProxySQLによるロードバランスによって性能向上も期待できるため、簡単にベンチマークを実施してみました。
検証方法
sysbencyhのoltp_read_writeルールを利用し、transactionsの数値を確認します。
検証環境は以下の2パターンとします。
- ProxySQL経由でマスター＋リードレプリカ2台の3台構成へベンチマーク
- マスターデータベースへ直接ベンチマーク
ベンチマークは3回実施し、平均値を参照します。
検証時パラメータ
以下のパラメータにて実施しています。
# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-db=rdbbtest --db-driver=mysql --table-size=10000000 --mysql-host=マスターDBまたはProxySQLのアドレス --mysql-user=[USER] --mysql-password=[PASSWORD] --time=180 --threads=64 --mysql-port=6032 --db-ps-mode=disable run
検証結果
結果を表にまとめました。
| transactions | 平均transactions |
|---|---|
| ProxySQL経由 | 37042 |
| マスター直 | 17711 |
結果、「マスター直」よりも「ProxySQL経由」の方がtransactionsの数値が約2倍上がり、より高い処理性能が発揮できると確認できました。
今回はリードレプリカ2台の構成ですが、今後の展望としてリードレプリカの台数を増やした場合の性能影響を検証できればと考えています。
注意事項
- 本記事で記載した各サービス/ニフクラの機能等は、2022年9月時点の情報です。ご利用の際は、各サービスの最新情報をご確認ください。
- 本記事については検証結果の1つとなります。実際に構成を検討されるときは、それぞれの要件を鑑みて十分に検証を実施してください。
- 本記事ではOS上の操作についても記載していますが、ニフクラではOS以上はご利用者様の責任範囲となりますのでご留意ください。
