Skip to content

Instantly share code, notes, and snippets.

@shichi-at-nttr

shichi-at-nttr/README.md

Last active November 22, 2021 04:00
Show Gist options
  • Save shichi-at-nttr/2bf5e0981a6805387d8974636d5658fa to your computer and use it in GitHub Desktop.
Save shichi-at-nttr/2bf5e0981a6805387d8974636d5658fa to your computer and use it in GitHub Desktop.
Download ZIP
ProxySQLにおけるGalera ClusterのFailover挙動観察
Raw
README.md

ProxySQL　ver2.0.0 からサポートされたGalera Clusterサポートの動作を確認する。 ノードの停止/再開の際にReaderノード・Writerノードがどのように変化するかを観察した。 (利用バージョン 2.3.2)

定義ファイル(抜粋)

# defines all the MySQL servers
mysql_servers =
(
        # Writer
        # hostgroup=0

        # Backup-Writer (also Reader) 実態は「プライマリWriteが死ぬまではReaderとして振る舞う待機Writer」
        { hostgroup=1, address="node1.node.mydev" , port=3306 , weight=1 },
        { hostgroup=1, address="node2.node.mydev" , port=3306 , weight=1 },
        { hostgroup=1, address="node3.node.mydev" , port=3306 , weight=1 },
        { hostgroup=1, address="node4.node.mydev" , port=3306 , weight=1 },
        { hostgroup=1, address="node5.node.mydev" , port=3306 , weight=1 },

        # Reader (Writer昇格しないノードはない)
        # hostgroup=2

        # offline_hostgroup
        # hostgroup=3
)

Backup-Writerしか定義しない。

初期状態

$ mysql -u admin -padmin -h 127.0.0.1 -P 6032

mysql> select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 0                | 1                       | 2                | 3                 | 1      | 1           | 2                     | 200                     |         |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

mysql> select hostgroup_id, hostname, status, weight from runtime_mysql_servers order by hostgroup_id, hostname;
+--------------+------------------+--------+--------+
| hostgroup_id | hostname         | status | weight |
+--------------+------------------+--------+--------+
| 1            | node1.node.mydev | ONLINE | 1      |  Backup-Writer
| 1            | node2.node.mydev | ONLINE | 1      |
| 1            | node3.node.mydev | ONLINE | 1      |
| 1            | node4.node.mydev | ONLINE | 1      |
| 1            | node5.node.mydev | ONLINE | 1      |
+--------------+------------------+--------+--------+

/etc/proxysql.cnf で定義したままが表示される

内部的にはこうなっている(便宜上罫線を追記している)：

mysql> select hostgroup_id, hostname, status, weight from runtime_mysql_servers order by hostgroup_id, hostname;

+--------------+------------------+---------+--------+
| hostgroup_id | hostname         | status  | weight |
+--------------+------------------+---------+--------+
| 0            | node1.node.mydev | SHUNNED | 1      |  Writer
| 0            | node2.node.mydev | SHUNNED | 1      |
| 0            | node3.node.mydev | SHUNNED | 1      |
| 0            | node4.node.mydev | SHUNNED | 1      |
| 0            | node5.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 1            | node1.node.mydev | ONLINE  | 1      |  Backup-Writer
| 1            | node2.node.mydev | ONLINE  | 1      |
| 1            | node3.node.mydev | ONLINE  | 1      |
| 1            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 2            | node1.node.mydev | ONLINE  | 1      |  Reader
| 2            | node2.node.mydev | ONLINE  | 1      |
| 2            | node3.node.mydev | ONLINE  | 1      |
| 2            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
  • Backup-Writer(hostgroup_id=1)にリストしたノードは全てWriter(0)にリストされる。
  • Writerは1台だけONLINE(node5)で残りはSHUNNED(停止中、1〜4)となる。
  • 定義していないはずのReader(2)も also-reader=2 なのでリストされる。(ONLINEなWriter以外のnode1〜4)

Readノードの停止

node1を停止してみる。

% ssh node1 sudo systemctl stop mysql

mysql> select hostgroup_id, hostname, status, weight from runtime_mysql_servers order by hostgroup_id, hostname;
+--------------+------------------+---------+--------+
| hostgroup_id | hostname         | status  | weight |
+--------------+------------------+---------+--------+
| 0            | node2.node.mydev | SHUNNED | 1      |  Writer
| 0            | node3.node.mydev | SHUNNED | 1      |
| 0            | node4.node.mydev | SHUNNED | 1      |
| 0            | node5.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 1            | node2.node.mydev | ONLINE  | 1      |  Backup-Writer
| 1            | node3.node.mydev | ONLINE  | 1      |
| 1            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 2            | node2.node.mydev | ONLINE  | 1      |  Reader
| 2            | node3.node.mydev | ONLINE  | 1      |
| 2            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 3            | node1.node.mydev | SHUNNED | 1      |  Offline
+--------------+------------------+---------+--------+
  • Writer(0)のONLINEは変わらず。SHUNNEDのnode1が消えた。
  • Backup-Writer(1)のnode1が消えた。
  • Reader(2)のnode1が消えた。
  • Offline(3)にnode1がリストアップされた。(先にONLINEのまま3になり、さらにしばらくしてからSHUNNEDになる)

Readerノードの復帰

node1のMySQLを起動。

% ssh node1 sudo systemctl start mysql

mysql> select hostgroup_id, hostname, status, weight from runtime_mysql_servers order by hostgroup_id, hostname;
+--------------+------------------+---------+--------+
| hostgroup_id | hostname         | status  | weight |
+--------------+------------------+---------+--------+
| 0            | node1.node.mydev | SHUNNED | 1      |  Writer
| 0            | node2.node.mydev | SHUNNED | 1      |
| 0            | node3.node.mydev | SHUNNED | 1      |
| 0            | node4.node.mydev | SHUNNED | 1      |
| 0            | node5.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 1            | node1.node.mydev | ONLINE  | 1      |  Backup-Writer
| 1            | node2.node.mydev | ONLINE  | 1      |
| 1            | node3.node.mydev | ONLINE  | 1      |
| 1            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 2            | node1.node.mydev | ONLINE  | 1      |  Reader
| 2            | node2.node.mydev | ONLINE  | 1      |
| 2            | node3.node.mydev | ONLINE  | 1      |
| 2            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+

元に戻った。

Writeの停止

node5を停止してみる。

% ssh node5 sudo systemctl stop mysql

mysql> select hostgroup_id, hostname, status, weight from runtime_mysql_servers order by hostgroup_id, hostname;
+--------------+------------------+---------+--------+
| hostgroup_id | hostname         | status  | weight |
+--------------+------------------+---------+--------+
| 0            | node1.node.mydev | SHUNNED | 1      |  Writer
| 0            | node2.node.mydev | SHUNNED | 1      |
| 0            | node3.node.mydev | SHUNNED | 1      |
| 0            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 1            | node1.node.mydev | ONLINE  | 1      |  Backup-Writer
| 1            | node2.node.mydev | ONLINE  | 1      |
| 1            | node3.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 2            | node1.node.mydev | ONLINE  | 1      |  Reader
| 2            | node2.node.mydev | ONLINE  | 1      |
| 2            | node3.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 3            | node5.node.mydev | SHUNNED | 1      |  Offline
+--------------+------------------+---------+--------+

  • Writer(0)からnode5が消え、別のノードがONLINEになった　(Writerのfailover)

  • Backup-Writer(1)から新しいWriterであるnode4が消えた

  • Readerから新しいWriterであるnode4が消えた。

  • Offline(3)にnode5がリストアップされた

  • 新しいWriterノードの選定ロジックはここに記載あり。

Writerの復帰

node5のMySQLを起動。

% ssh node5 sudo systemctl start mysql

mysql> select hostgroup_id, hostname, status, weight from runtime_mysql_servers;
+--------------+------------------+---------+--------+
| hostgroup_id | hostname         | status  | weight |
+--------------+------------------+---------+--------+
| 0            | node1.node.mydev | SHUNNED | 1      |  Writer
| 0            | node2.node.mydev | SHUNNED | 1      |
| 0            | node3.node.mydev | SHUNNED | 1      |
| 0            | node4.node.mydev | SHUNNED | 1      |
| 0            | node5.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 1            | node1.node.mydev | ONLINE  | 1      |  Backup-Writer
| 1            | node2.node.mydev | ONLINE  | 1      |
| 1            | node3.node.mydev | ONLINE  | 1      |
| 1            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
| 2            | node1.node.mydev | ONLINE  | 1      |  Reader
| 2            | node2.node.mydev | ONLINE  | 1      |
| 2            | node3.node.mydev | ONLINE  | 1      |
| 2            | node4.node.mydev | ONLINE  | 1      |
+--------------+------------------+---------+--------+
  • Writer(0)がまたnode5がONLINEになった。(node4が継続かと思ったが実際の挙動は違った。前掲の選出ロジック通り)
  • Backup-Writer(1)にWriterから外れたnode4が戻った
  • Reader(2)にnode4が戻った

ステータス遷移スピード

デフォルト値

mysql> select * from global_variables where variable_name like '%monitor_galera%';
+----------------------------------------------------+----------------+
| variable_name                                      | variable_value |
+----------------------------------------------------+----------------+
| mysql-monitor_galera_healthcheck_interval          | 5000           |
| mysql-monitor_galera_healthcheck_timeout           | 800            |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3              |
+----------------------------------------------------+----------------+

5秒かかる。遷移完了まで10秒かかるイメージ。

mysql> update global_variables set variable_value=100 where variable_name="mysql-monitor_galera_healthcheck_interval";
mysql> update global_variables set variable_value=100 where variable_name="mysql-monitor_galera_healthcheck_timeout";
mysql> select * from global_variables where variable_name like '%monitor_galera%';
+----------------------------------------------------+----------------+
| variable_name                                      | variable_value |
+----------------------------------------------------+----------------+
| mysql-monitor_galera_healthcheck_interval          | 100            |
| mysql-monitor_galera_healthcheck_timeout           | 100            |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3              |
+----------------------------------------------------+----------------+

mysql> LOAD MYSQL SERVERS TO RUNTIME;

hostgroupの切り替えタイミングが早くなる。ステータスがSHUNNEDになる時間は変わらない様子。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment