TL;DR
- シングルプライマリー構成のInnoDB Clusterにおいて
- プライマリーノードで
ALTER TABLE中の後続のDDL, DMLはプライマリーノードでブロックされない限りはALTER TABLEを追い越してコミットすることができる- コミットされた後続のDDL, DMLはセカンダリーのリレーログに渡ってリプレイされるので(セカンダリーでの実行にかかる時間程度で)データが同期される
- セカンダリーノードで
ALTER TABLEをリプレイ中にプライマリーで実行されたDDL, DMLはその対象に関わらず 「セカンダリーでリプレイ中のALTER TABLEを追い越せない」- よって、セカンダリーノード側で最初に受け取った
ALTER TABLEが終了するまでどんな操作であり遅延する。通常のレプリケーションと一緒っちゃ一緒
- よって、セカンダリーノード側で最初に受け取った
- プライマリーノードで
プライマリーノードにてテキトーな
ALTER TABLE を流す。mysql> ALTER TABLE sbtest1 Engine = InnoDB;
プライマリーでALTER TABLEが走ってる間の3台での
SHOW PROCESSLISTnode1> SHOW PROCESSLIST; -- PRIMARYなのでroot@localhostで "altering table"
+-----+-----------------+-----------+--------+---------+------+--------------------------------------------------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+--------+---------+------+--------------------------------------------------------+-------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 7625 | Waiting on empty queue | NULL |
| 13 | system user | | NULL | Connect | 7315 | waiting for handler commit | Group replication applier module |
| 16 | system user | | NULL | Query | 7315 | Slave has read all relay log; waiting for more updates | NULL |
| 65 | root | localhost | sbtest | Query | 3 | altering table | ALTER TABLE sbtest1 Engine = InnoDB |
| 107 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+-----+-----------------+-----------+--------+---------+------+--------------------------------------------------------+-------------------------------------+
5 rows in set (0.00 sec)
node2> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 7290 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 7290 | waiting for handler commit | Group replication applier module |
| 12 | system user | | NULL | Query | 76 | Slave has read all relay log; waiting for more updates | NULL |
| 80 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
4 rows in set (0.00 sec)
node3> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 7260 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 7260 | waiting for handler commit | Group replication applier module |
| 12 | system user | | NULL | Query | 76 | Slave has read all relay log; waiting for more updates | NULL |
| 65 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
4 rows in set (0.00 sec)
この時にプライマリーで
CREATE DATABASE, CREATE TABLE, INSERTnode1> CREATE DATABASE d1;
Query OK, 1 row affected (0.19 sec)
node1> CREATE TABLE d1.t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.40 sec)
node1> INSERT INTO d1.t1 VALUES (1, 'one');
Query OK, 1 row affected (0.19 sec)
こいつらはALTER TABLEに干渉せずにコミットされ、コミットされた時点でセカンダリーのリレーログに積み上げられてすぐにリプレイされるのでそのまま同期される(ように見える)
node1> SELECT * FROM d1.t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.00 sec)
node2> SELECT * FROM d1.t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.00 sec)
node3> SELECT * FROM d1.t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.00 sec)
さて、プライマリーで
ALTER TABLE が流れ終わり、セカンダリーに適用されている最中の SHOW PROCESSLISTnode1> SHOW PROCESSLIST; -- プライマリーではもうALTER TABLEは終わってる
+-----+-----------------+-----------+--------+---------+------+--------------------------------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+--------+---------+------+--------------------------------------------------------+----------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 7916 | Waiting on empty queue | NULL |
| 13 | system user | | NULL | Connect | 7606 | waiting for handler commit | Group replication applier module |
| 16 | system user | | NULL | Query | 7606 | Slave has read all relay log; waiting for more updates | NULL |
| 65 | root | localhost | sbtest | Sleep | 70 | | NULL |
| 107 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+-----+-----------------+-----------+--------+---------+------+--------------------------------------------------------+----------------------------------+
5 rows in set (0.00 sec)
node2> SHOW PROCESSLIST; --Group replicationのApplierスレッド(12)がaltering table
+----+-----------------+-----------+--------+---------+------+----------------------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+------+----------------------------+-------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 7581 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 7581 | waiting for handler commit | Group replication applier module |
| 12 | system user | | sbtest | Query | 70 | altering table | ALTER TABLE sbtest1 Engine = InnoDB |
| 80 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+--------+---------+------+----------------------------+-------------------------------------+
4 rows in set (0.00 sec)
node3> SHOW PROCESSLIST; -- 同上
+----+-----------------+-----------+--------+---------+------+----------------------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+------+----------------------------+-------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 7551 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 7551 | waiting for handler commit | Group replication applier module |
| 12 | system user | | sbtest | Query | 70 | altering table | ALTER TABLE sbtest1 Engine = InnoDB |
| 65 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+--------+---------+------+----------------------------+-------------------------------------+
4 rows in set (0.00 sec)
この状態でプライマリーに対して
DELETE を打ち込んでみる。node1> DELETE FROM d1.t1 WHERE num = 1;
Query OK, 1 row affected (0.01 sec)
node1> SELECT * FROM d1.t1;
Empty set (0.00 sec)
node2, node3では
node2> SELECT * FROM d1.t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.00 sec)
node3> SELECT * FROM d1.t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.03 sec)
まだ消えてない。
node1> DROP TABLE d1.t1;
Query OK, 0 rows affected (0.02 sec)
node1> SELECT * FROM d1.t1;
ERROR 1146 (42S02): Table 'd1.t1' doesn't exist
node2> SELECT * FROM d1.t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.01 sec)
node3> SELECT * FROM d1.t1;
+-----+------+
| num | val |
+-----+------+
| 1 | one |
+-----+------+
1 row in set (0.00 sec)
DDLも追い越せない。
セカンダリー側で
セカンダリー側で
ALTER TABLE が終わって初めて、 DELETE も DROP TABLE も実行されてデータが追い付く。node2> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 8010 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 8010 | waiting for handler commit | Group replication applier module |
| 12 | system user | | NULL | Query | 416 | Slave has read all relay log; waiting for more updates | NULL |
| 80 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
4 rows in set (0.00 sec)
node2> SELECT * FROM d1.t1;
ERROR 1146 (42S02): Table 'd1.t1' doesn't exist
node3> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 7980 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 7980 | waiting for handler commit | Group replication applier module |
| 12 | system user | | NULL | Query | 416 | Slave has read all relay log; waiting for more updates | NULL |
| 65 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+------+--------------------------------------------------------+----------------------------------+
4 rows in set (0.00 sec)
node3> SELECT * FROM d1.t1;
ERROR 1146 (42S02): Table 'd1.t1' doesn't exist
いわゆるフツーのレプリケーションでフツーに「ALTER TABLEはスレーブでも同じだけ時間がかかるから遅延するよ」というのと同じなので無停止で色々やろうとする時は注意、と。
いわゆる
Seconds_Behind_Master みたいなのは performance_schema.replication_group_member_stats.count_transactions_remote_in_applier_queue にトランザクションの数として現れるので、これで見るのがいいのかな（プライマリーで実行してもセカンダリーで実行しても、グループ全体の値が見える）mysql> SELECT COUNT_TRANSACTIONS_IN_QUEUE AS queue, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS apply_queue, COUNT_TRANSACTIONS_ROWS_VALIDATING AS validating, COUNT_TRANSACTIONS_REMOTE_APPLIED, COUNT_TRANSACTIONS_LOCAL_PROPOSED, TRANSACTIONS_COMMITTED_ALL_MEMBERS AS gtid FROM performance_schema.replication_group_member_stats;
+-------+-------------+------------+-----------------------------------+-----------------------------------+---------------------------------------------+
| queue | apply_queue | validating | COUNT_TRANSACTIONS_REMOTE_APPLIED | COUNT_TRANSACTIONS_LOCAL_PROPOSED | gtid |
+-------+-------------+------------+-----------------------------------+-----------------------------------+---------------------------------------------+
| 0 | 1525 | 2180 | 2556 | 0 | 810ac99f-61b5-11ea-b87d-120fb5068a5b:1-1939 |
| 0 | 0 | 2180 | 4 | 4115 | 810ac99f-61b5-11ea-b87d-120fb5068a5b:1-1939 |
| 0 | 1525 | 2180 | 2546 | 0 | 810ac99f-61b5-11ea-b87d-120fb5068a5b:1-1939 |
+-------+-------------+------------+-----------------------------------+-----------------------------------+---------------------------------------------+
0 件のコメント :
コメントを投稿