2017年2月2日木曜日

MySQL Causal Talk vol.10 で Group Replication について話した

MySQL Causal Talk vol.10 で Group Replication について話してきました。 最近ずっとGroup Replication しか触ってない・・・・

また、 myfinder さんから、MySQL Casual slack の general チャンネルを英語用とし、日本語用のチャンネルを別途作ることが提案されました。
早速、general チャンネルにMySQLの中の人(!)が投稿し、ちょっとした騒ぎになっている様です。

2017年1月28日土曜日

OSC 2017 Osaka で MySQLの冗長構成について発表してきた

OSC Osaka で MySQL の高可用性構成について発表してきました。 会場でフォローして頂いた。sakai さん、ありがとうございました!

2017年1月20日金曜日

Group Replication を試す(4) リカバリ

Group Replication で特定のサーバのデータが吹き飛んでしまった場合の復旧手順を確認。 動いているサーバからdumpを取って、復旧すれば良いのだが、、、
--single-transaction で mysqldump しようとすると、GRではSavepoint がサポートされていないため、 コケる。。。
https://bugs.mysql.com/bug.php?id=81494 で修正の要望が出されている

-bash-4.1$ mysqldump --all-databases --single-transaction -uroot --triggers --routines --events -p > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SAVEPOINT sp': The MySQL server is running with the --transaction-write-set-extraction!=OFF option so it cannot execute this statement (1290)
--single-transactionが使えないので、--lock-all-tables を使う。テーブルがロックされてしまうので、バックアップ中にはバックアップを取得するサーバには更新が行われないようにする必要がある。 3台構成だと、壊れていてるやつが1台、バックアップ取得用に1台ということでサービス用が1台になってしまう。。。
  1. アプリからのアクセスをなくす
  2. STOP GROUP_REPLIACTION (他のノードから更新を受け取らないようにする)
  3. mysqldump
  4. START GROUP_REPLICATION
  5. 追いついたら、アプリからのアクセスを復旧
-bash-4.1$ mysqldump --all-databases --lock-all-tables -uroot --triggers --routines --events -p > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
後は、壊れたやつにダンプしたデータを流しこんで、START GROUP_REPLICATIONすれば復旧できる。
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
 
mysql> source dump.sql
 
mysql> start group_replication;
Query OK, 0 rows affected (2.22 sec)
 
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| group_replication_applier | 0cdd0b6a-cd84-11e6-b223-fa163e0b2b7c | gr02                                  |        3306 | ONLINE       |
| group_replication_applier | 1edf2e1d-cd83-11e6-961b-fa163e83e8e7 | gr01                                  |        3306 | ONLINE       |
| group_replication_applier | a1c37edb-cd89-11e6-a463-fa163e49d992 | gr03                                  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
3 rows in set (0.00 sec)
ちなみにGroup ReplicationのGTIDのUUID部分はgroup_replication_group_name で指定したものが使われる
-bash-4.1$ grep -A2 GTID_PURGED dump.sql
SET @@GLOBAL.GTID_PURGED='1edf2e1d-cd83-11e6-961b-fa163e83e8e7:1,
87e5ed8c-cd83-11e6-bc3c-fa163e83e8e7:1-12947';
 
-bash-4.1$ grep group_name /etc/my.cnf
loose-group_replication_group_name="87e5ed8c-cd83-11e6-bc3c-fa163e83e8e7"

2017年1月13日金曜日

Group Replication を試す(3) 障害時の挙動

group_replication_recovery チャネルは障害後のサーバ間の差分同期のみに使われる。

Group Replication のステータスが ONLINE 状態のときは、Master_HostがNULL、Slave_{IO,SQL}_Running は NO。

mysql>  select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| group_replication_applier | 0cdd0b6a-cd84-11e6-b223-fa163e0b2b7c | gr02                                  |        3306 | ONLINE       |
| group_replication_applier | 1edf2e1d-cd83-11e6-961b-fa163e83e8e7 | gr01                                  |        3306 | ONLINE       |
| group_replication_applier | a1c37edb-cd89-11e6-a463-fa163e49d992 | gr03                                  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
3 rows in set (0.00 sec)
 
 
mysql> show slave status for channel 'group_replication_recovery' \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 
                  Master_User: rpl_user
                  Master_Port: 0
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: gr02-group_replication_recovery.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
## snip ##

障害によって他のサーバと差分が生じ、差分同期中(RECOVERING) になると、Group Replication Plugin が自動的にマスターを指定し、差分を同期する。

mysql>  select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| group_replication_applier | 0cdd0b6a-cd84-11e6-b223-fa163e0b2b7c | gr02                                  |        3306 | RECOVERING   |
| group_replication_applier | 1edf2e1d-cd83-11e6-961b-fa163e83e8e7 | gr01                                  |        3306 | ONLINE       |
| group_replication_applier | a1c37edb-cd89-11e6-a463-fa163e49d992 | gr03                                  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
3 rows in set (0.00 sec)
 
mysql> show slave status for channel 'group_replication_recovery' \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: gr01
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 65574674
               Relay_Log_File: gr02-group_replication_recovery.000003
                Relay_Log_Pos: 31852573
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

## snip ##

binary log がすでにロストしてた場合

galera ではIST(差分同期)をあきらめて、SST(バックアップ取得→同期) になるケース。 Group Replication では、バイナリログを探し続けて、試行回数が一定に達すると、起動できずに終了する。

2016-12-30T01:54:26.017573Z 14 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 5/10'
2016-12-30T01:55:26.030228Z 14 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host
='gr02', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2016-12-30T01:55:26.045140Z 14 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 0cdd0b6a-cd84-11e6-b223-fa163e0b2b7c at gr02 port: 3306.'
2016-12-30T01:55:26.046151Z 23 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for STA
RT SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2016-12-30T01:55:26.059113Z 23 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'rpl_user@gr02:3306',replication started in log 'FIRST' at position 4
2016-12-30T01:55:26.061461Z 24 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './gr03-group_replication_recovery.000001' position: 4
2016-12-30T01:55:26.066858Z 23 [ERROR] Error reading packet from server for channel 'group_replication_recovery': The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)
2016-12-30T01:55:26.066904Z 23 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
2016-12-30T01:55:26.066917Z 23 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2016-12-30T01:55:26.066983Z 14 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2016-12-30T01:55:26.067037Z 24 [Note] Error reading relay log event for channel 'group_replication_recovery': slave SQL thread was killed
2016-12-30T01:55:26.079298Z 14 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='gr02', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2016-12-30T01:55:26.091463Z 14 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 6/10'
 
## snip ##
 
2016-12-30T02:00:22.345848Z 0 [Note] InnoDB: Starting shutdown...
2016-12-30T02:00:22.446097Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2016-12-30T02:00:22.447044Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 161230 11:00:22
2016-12-30T02:00:23.959156Z 0 [Note] InnoDB: Shutdown completed; log sequence number 280039404
2016-12-30T02:00:23.962273Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2016-12-30T02:00:23.962291Z 0 [Note] Shutting down plugin 'MyISAM'
2016-12-30T02:00:23.962302Z 0 [Note] Shutting down plugin 'CSV'
2016-12-30T02:00:23.962308Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-12-30T02:00:23.962346Z 0 [Note] Shutting down plugin 'sha256_password'
2016-12-30T02:00:23.962352Z 0 [Note] Shutting down plugin 'mysql_native_password'
2016-12-30T02:00:23.963043Z 0 [Note] Shutting down plugin 'binlog'
2016-12-30T02:00:23.963818Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

スプリットブレイン時

2台構成で、1台を落とし、スプリットブレイン状態を再現。

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| group_replication_applier | 0cdd0b6a-cd84-11e6-b223-fa163e0b2b7c | gr02                                  |        3306 | UNREACHABLE  |
| group_replication_applier | 1edf2e1d-cd83-11e6-961b-fa163e83e8e7 | gr01                                  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
2 rows in set (0.00 sec)
 
 
mysql> select * from test.t limit 2;
+----+---------------------+
| pk | col1                |
+----+---------------------+
|  1 | 2016-12-29 15:23:07 |
|  8 | 2016-12-29 15:23:07 |
+----+---------------------+
2 rows in set (0.00 sec)
 
 
mysql> INSERT INTO test.t (col1) VALUES(NOW());
(戻ってこない)
mysql> SHOW PROCESSLIST;
+------+-------------+-----------+------+---------+-------+--------------------------------------------------------+-----------------------------------------+
| Id   | User        | Host      | db   | Command | Time  | State                                                  | Info                                    |
+------+-------------+-----------+------+---------+-------+--------------------------------------------------------+-----------------------------------------+
|    7 | system user |           | NULL | Connect | 75875 | executing                                              | NULL                                    |
|   10 | system user |           | NULL | Connect | 75875 | Slave has read all relay log; waiting for more updates | NULL                                    |
| 5482 | root        | localhost | NULL | Query   |    53 | query end                                              | INSERT INTO test.t (col1) VALUES(NOW()) |
| 5786 | root        | localhost | NULL | Query   |     0 | starting                                               | SHOW PROCESSLIST                        |
+------+-------------+-----------+------+---------+-------+--------------------------------------------------------+-----------------------------------------+
4 rows in set (0.00 sec)

SELECTはできるが、更新はブロックされ、ハングしたような状態になる。スプリットブレイン時も整合性は保たれる。スプリットブレインが解消されるとINSERT文が成功する。 Lab 版ではスプリットブレインの状態をうまくハンドリングできてなかったが、GAではちゃんとハンドリングできるようになってた。

2台だと1台死んだだけでスプリットブレインになってしまうため、Group Replicationを構成する場合は3台以上が推奨される。

2017年1月6日金曜日

Group Replication を試す(2) ロックの挙動

テスト用のテーブル

mysql> CREATE TABLE grplt.tbl (pk SERIAL, col1 int, who_update varchar(10));
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO grplt.tbl (col1, who_update) VALUES (1, 'node1') , (2, 'node1'), (3, 'node1');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql>  SELECT * FROM grplt.tbl;
+----+------+------------+
| pk | col1 | who_update |
+----+------+------------+
|  1 |    1 | node1      |
|  8 |    2 | node1      |
| 15 |    3 | node1      |
+----+------+------------+
3 rows in set (0.00 sec)

いつものデッドロック

同じサーバに対して、入れ子の行ロックを取る2つのトランザクションを実行するパターン。
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction が発生し、発生したデッドロックの情報は SHOW ENGINE INNODB STATUS で確認できる。

TIME T1 T2
1 > BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE grplt.tbl SET col1 = 10, who_update = 'node1' WHERE pk = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2 > BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE grplt.tbl SET col1 = 10, who_update = 'node1' WHERE pk = 15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3 T2 が pk = 15 を開放するのを待つ。
> UPDATE grplt.tbl SET col1 = 10, who_update = 'node1' WHERE pk = 15;
4 T2 が pk = 1 を要求したため、T1は強制ロールバックしpk = 1 を開放
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
> UPDATE grplt.tbl SET col1 = 10, who_update = 'node1' WHERE pk = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SHOW ENGINE INNODB STATUS\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-12-29 16:02:59 0x7f539018a700
*** (1) TRANSACTION:
TRANSACTION 6102, ACTIVE 42 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3838, OS thread handle 139996877612800, query id 11406 localhost root updating
UPDATE grplt.tbl  SET col1 = 10, who_update = 'node1' WHERE pk = 15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 3 n bits 72 index pk of table `grplt`.`tbl` trx id 6102 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 000000000000000f; asc         ;;
 1: len 6; hex 0000000017d7; asc       ;;
 2: len 7; hex 30000001880233; asc 0     3;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 5; hex 6e6f646531; asc node1;;
*** (2) TRANSACTION:
TRANSACTION 6103, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3841, OS thread handle 139996876547840, query id 11407 localhost root updating
UPDATE grplt.tbl  SET col1 = 10, who_update = 'node1' WHERE pk = 1

Group Replication でサーバ間でロックが競合

Group Replication で複数のサーバで同時に同じレコードを更新した場合の挙動。 サーバ間のロックのアルゴリズムは「楽観的ロック」になっている。
コミット時に、そのレコードが他のサーバで既に更新済みだった場合、そのトランザクションは強制ロールバックされる。
要するに「先勝ち」

pk = 1 のレコードを別々のサーバで更新。エラーメッセージが異なり通常のデッドロックと区別することができる。
※ ちなみに、Galera Cluster の場合、通常のデッドロックと、複数サーバ間で競合しロールバックされた場合で同じエラーメッセージが出力されるため区別が付かない。

しかし、もう少しわかりやすいメッセージにできなかったんだろうか。
SHOW ENGINE INNODB STATUS に情報は出力されない。

TIME T1 T2
1 > BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE grplt.tbl SET col1 = 10, who_update = 'node1' WHERE pk = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2 > BEGIN;
Query OK, 0 rows affected (0.00 sec)

> UPDATE grplt.tbl SET col1 = 10, who_update = 'node2' WHERE pk = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3 pk = 1 の行をノード1で更新確定する
> COMMIT;
Query OK, 0 rows affected (0.00 sec)
(この時点でT1が更新したことが伝わってくる)
4 > COMMIT;
ERROR 1180 (HY000): Got error 149 during COMMIT
mysql> pager grep DEAD
PAGER set to 'grep DEAD'
 
mysql> SHOW ENGINE INNODB STATUS\G
1 row in set (0.00 sec)

2016年12月30日金曜日

Group Replication を試す(1) 導入編

MySQL 8.0 で入ると噂されていた、Group ReplicationがMySQL 5.7.17 でリリースされました。
5.7 GAとは何だったのか。。。 とはいえ、早く使えるようになったのは、うれしいです。
※ Group Replication は追加プラグインとして提供されているため、明示的に使わない限り影響を及ぼすことはありません。
-bash-4.1$ ls /usr/lib64/mysql/plugin/group_replication.so
/usr/lib64/mysql/plugin/group_replication.so

Group Replication とは

MySQLのマスターサーバの冗長化を目的とした機能です。

  • マルチマスター(マルチライター)のレプリケーション構成がとれる
  • 複数サーバに同時に書き込みが可能。HAProxy などで分散。
  • 冗長化の仕組みがMySQL本体に組み込まれており、簡単に扱える

相互にデータはレプリケーションされるため、1台あたりの更新量はサーバ台数を増やしても変化しない点に注意
(更新性能をスケールさせるためのソリューションではない。


http://mysqlhighavailability.com/mysql-group-replication-transaction-life-cycle-explained/
より引用

Group Replication 構成のマスターにスレーブをぶら下げることも可能です。


Group Replication を設定してみる

今回は3台構成のGroup Replication を設定してみました。


1. レプリケーションユーザの作成

全DBサーバで、root のパスワード設定と、レプリケーション用ユーザを作成しておきます。 この時点ではbinlogは無効化しておく必要があります。
mysql> ALTER USER root@'localhost' identified by 'Pass_1234';
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE USER rpl_user IDENTIFIED BY 'rpl_Pass1234';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rpl_user'@'%';
Query OK, 0 rows affected (0.00 sec)

2. 最初の1台の設定

以下の内容をmy.cnf に追加する。Group Replication を利用するにはGTIDが必須になります。
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
 
# Group Replication の設定
transaction_write_set_extraction=XXHASH64
 
# SELECT UUID() で生成した任意のUUIDを指定
loose-group_replication_group_name="87e5ed8c-cd83-11e6-bc3c-fa163e83e8e7"
loose-group_replication_start_on_boot=off
 
# 自分のIPアドレス
loose-group_replication_local_address= "172.21.134.26:24901"
 
# すべてのサーバを並べる
loose-group_replication_group_seeds= "172.21.134.26:24901,172.21.134.27:24901,172.21.134.28:24901"
 
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
 
# サーバ間の通信に利用するネットワークを許可する
loose-group_replication_ip_whitelist = 172.21.134.0/23
MySQLを再起動し、CHANGE MASTER を実行。Group Replication 用の group_replication_recovery チャンネルに先ほど作成したユーザを指定する。
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_Pass1234' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Group Replication プラグインの有効化
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0,01 sec)
 
mysql> SHOW PLUGINS;
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.01 sec)
プラグインをインストールすると、Group Replication 用のユーザが自動で作成される。 このアカウントはアカウントロックされているため、例えパスワードがわかっても利用することはできない。 Group Replication Plugin 内部でこのユーザを使ってSQLを実行している模様。
mysql> SHOW GRANTS FOR _gr_user@'localhost';
+------------------------------------------------------------------------------------------------+
| Grants for _gr_user@localhost                                                                  |
+------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO '_gr_user'@'localhost'                                                   |
| GRANT SELECT ON `performance_schema`.`replication_connection_status` TO '_gr_user'@'localhost' |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql>  SHOW CREATE USER _gr_user@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for _gr_user@localhost                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER '_gr_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*7CF5CA9067EC647187EB99FCC27548FBE4839AE3' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
次に、Group Replication を開始。最初の1台のみ group_replication_bootstrap_group を有効にした状態でSTART GROUP REPLICATION を実行する必要がある。
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
 
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.76 sec)
 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
ステータス確認。MEMBER_STATEがONLINEになれば成功。
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| group_replication_applier | 1edf2e1d-cd83-11e6-961b-fa163e83e8e7 | gr01                                  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
1 row in set (0.00 sec)

3. 2台目以降

server-id、group_replication_local_address をサーバにあわせたものにし、my.cnf に設定を追記して、再起動後、1台目と同じような流れで進める。
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_Pass1234' FOR CHANNEL 'group_replication_recovery';
 
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
 
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.76 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
| group_replication_applier | 0cdd0b6a-cd84-11e6-b223-fa163e0b2b7c | gr02                                  | 3306        | ONLINE       |
| group_replication_applier | 1edf2e1d-cd83-11e6-961b-fa163e83e8e7 | gr01                                  | 3306        | ONLINE       |
+---------------------------+--------------------------------------+---------------------------------------+-------------+--------------+
2 rows in set (0.00 sec)
Group Replication ロックの挙動に続く・・・(予定)

2016年12月23日金曜日

performance_schema を top っぽく見るツール ps-top

とある企業のDBA @yoku0825 さんに ps-top というものを教えてもらったので試してみました。 ps は 「Performance Schema」のps です。MySQL の パフォーマンス統計である performance_schema の情報をtopコマンドのように表示してくれる。

インストール

ps-top は go で書かれている。 自分の管理外のサーバだと、ソフトウェアをインストールするのに躊躇するので、ポータブルな(バイナリをコピーすれば動く)goなのはありがたいですね。
$ export GOPATH=$HOME/go
$ mkdir $GOPATH
$ go get -u github.com/sjmudd/ps-top/cmd/ps-top

使い方

$ $HOME/go/bin/ps-top --help
pstop - Copyright (C) 2014-2015 Simon J Mudd 
Top-like program to show MySQL activity by using information collected
from performance_schema.
Usage: pstop <options>
Options:
--anonymise=<true|false>                 Anonymise hostname, user, db and table names
--count=<count>                          Set the number of times to watch
--defaults-file=/path/to/defaults.file   Connect to MySQL using given defaults-file
--help                                   Show this help message
--host=<hostname>                        MySQL host to connect to
--interval=<seconds>                     Set the default poll interval (in seconds)
--limit=<rows>                           Limit the number of lines of output (excluding headers)
--password=<password>                    Password to use when connecting
--port=<port>                            MySQL port to connect to
--socket=<path>                          MySQL path of the socket to connect to
--user=<user>                            User to connect with
--use-environment                        Connect to MySQL using a go dsn collected from MYSQL_DSN e.g. MYSQL_DSN='test_user:test_pass@tcp(127.0.0.1:3306)/performance_schema'
--version                                Show the version
--view=<view>                            Determine the view you want to see when pstop starts (default: table_io_latency
                                         Possible values: table_io_latency table_io_ops file_io_latency table_lock_latency user_latency mutex_latency stages_latency
--userが無視されるのに惑わされました。。。 --host もしくは --socket を指定しないと、--user や --port が無視される仕様のようです。
// --user は無視される・・・
$ $HOME/go/bin/ps-top --user=perf
2016/12/20 15:37:59 Error 1045: Access denied for user 'mita2'@'localhost' (using password: NO)

// --host を指定すると--userが有効になる。
$ $HOME/go/bin/ps-top --user=perf --host localhost.localdomain
2016/12/20 15:37:56 Error 1045: Access denied for user 'perf'@'localhost.localdomain' (using password: NO)
MySQL5.7 以降だとカジュアルにSIGSEGVで落ちます。。。原因はおいておいて、とりあえず、show_compatibility_56 をONにすれば動く。
$ $HOME/go/bin/ps-top --socket /var/lib/mysql/mysql.sock --user root --password password
panic: runtime error: invalid memory address or nil pointer dereference
        panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x20 pc=0x4c86c6]
goroutine 1 [running]:
mysql> SET GLOBAL show_compatibility_56=on;
Query OK, 0 rows affected (0.00 sec)

操作方法

タブで項目切り替え。

Table (table_io_waits_summary_by_table)

IO待ちが大きいテーブル一覧
ps-top 0.7.5 - 16:35:40 localhost / 5.7.15-9, up 16h 52m 14s      [REL] 3 seconds
Table  (table_io_waits_summary_by_table) 1 rows
   Latency      %| Fetch Insert Update Delete|Table Name
    1.16 s 100.0%| 72.9%   5.0%  19.8%   2.3%|sbtest.sbtest
                 |                           |
                 |                           |
                 |                           |
                 |                           |

File I/O Latency (file_summary_by_instance)

IO待ちの大きいテーブル
ps-top 0.7.5 - 16:36:29 localhost / 5.7.15-9, up 16h 53m 3s       [REL] 52 seconds
File I/O Latency (file_summary_by_instance)    6 row(s)
   Latency      %|  Read  Write   Misc|Rd bytes Wr bytes|     Ops  R Ops  W Ops  M Ops|Table Name
   34.28 s  73.8%|         0.9%  99.1%|          35.06 M| 17.39 k         50.0%  50.0%|
    9.47 s  20.4%|       100.0%       |          25.56 M|294.00 k        100.0%       |/localhost.log
    1.46 s   3.1%|  3.1%  28.6%  68.3%| 21.16 M 148.86 M| 10.74 k  12.3%  86.6%   1.1%|sbtest.sbtest
 868.59 ms   1.9%|        72.0%  28.0%|         169.17 M|     244         50.0%  50.0%|/xb_doublewrite
 392.50 ms   0.8%|        18.9%  81.1%|          20.31 M|  1.32 k         96.5%   3.5%|
 113.28 us       |       100.0%       |          48.00 k|       3        100.0%       |

Locks by Table Name (table_lock_waits_summary_by_table)

lock の大きいテーブル
ps-top 0.7.5 - 16:37:12 localhost / 5.7.15-9, up 16h 53m 46s      [REL] 95 seconds
Locks by Table Name (table_lock_waits_summary_by_table)
   Latency      %|  Read  Write|S.Lock   High  NoIns Normal Extrnl|AlloWr CncIns    Low Normal Extrnl|Table Name
 719.38 ms 100.0%| 69.6%  30.4%|                             69.6%|                             30.4%|sbtest.sbtest
Activity by Username (processlist)
ユーザごとのアクティビティ(SELECT数など)
ps-top 0.7.5 - 16:38:29 localhost / 5.7.15-9, up 16h 55m 3s
Activity by Username (processlist) 2 rows
Run Time      %|Sleeping      %|Conn Actv|Hosts DBs|Sel Ins Upd Del Oth|User
               |00:00:03 100.0%|   1     |    1    |                   |perf
               |               |   6    6|    1   2|  4                |root

Mutex Latency (events_waits_summary_global_by_event_name)

mutex 一覧
ps-top 0.7.5 - 16:39:02 localhost / 5.7.15-9, up 16h 55m 36s      [REL] 205 seconds
Mutex Latency (events_waits_summary_global_by_event_name) 27 rows
   Latency   MtxCnt        %|Mutex Name
 874.87 ms 121.15 k    28.0%|log_sys_write_mutex
 630.11 ms   8.84 M    20.2%|trx_mutex
 462.33 ms 958.76 k    14.8%|lock_mutex
 444.83 ms   1.03 M    14.2%|log_sys_mutex
 335.51 ms 942.34 k    10.7%|fil_system_mutex
 258.67 ms 562.83 k     8.3%|trx_sys_mutex
  33.28 ms 281.07 k     1.1%|trx_undo_mutex
  26.75 ms 224.85 k     0.9%|redo_rseg_mutex
  16.67 ms  56.21 k     0.5%|purge_sys_pq_mutex
  15.09 ms  86.95 k     0.5%|buf_pool_LRU_list_mutex
   5.65 ms  60.98 k     0.2%|flush_list_mutex
   5.43 ms  52.51 k     0.2%|srv_sys_mutex

SQL Stage Letency

ps-top 0.7.5 - 16:23:43 localhost / 5.7.15-9, up 16h 40m 17s      [REL] 28 seconds
SQL Stage Latency (events_stages_summary_global_by_event_name) 16 rows
   Latency      %  Counter|Stage Name
  93.97 ms  59.6%       73|Sending data
  22.59 ms  14.3%      157|starting
  17.45 ms  11.1%       73|executing
   7.55 ms   4.8%      115|Opening tables
   7.19 ms   4.6%       31|freeing items
   2.26 ms   1.4%       73|init
   1.60 ms   1.0%       73|statistics
   1.26 ms   0.8%       73|preparing
 877.32 us   0.6%      115|checking permissions
 747.29 us   0.5%       73|optimizing
 622.61 us   0.4%       73|System lock
 546.47 us   0.3%       85|removing tmp table
 404.06 us   0.3%       73|query end
 269.76 us   0.2%       73|closing tables
 231.15 us   0.1%      157|cleaning up
 136.90 us   0.1%       73|end

Memory Usage

ps-top 0.7.5 - 16:24:42 localhost / 5.7.15-9, up 16h 41m 16s      [REL] 9223372037 seconds
Memory Usage (memory_summary_global_by_event_name)
CurBytes         %  High Bytes|MemOps          %|CurAlloc       %  HiAlloc|Memory Area
   13.66 M   10.0%     13.66 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_history_long
    9.77 M    7.1%      9.77 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_history_long.sqltext
    9.77 M    7.1%      9.77 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_history_long.tokens
    9.77 M    7.1%      9.77 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_digest.tokens
    9.03 M    6.6%      9.03 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_thread_by_event_name
    7.03 M    5.1%      7.03 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/memory_summary_by_thread_by_event_name
    4.88 M    3.6%      4.88 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_digest
    4.51 M    3.3%      4.51 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_account_by_event_name
    4.51 M    3.3%      4.51 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_host_by_event_name
    4.51 M    3.3%      4.51 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_user_by_event_name
    4.00 M    2.9%      4.00 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/table_shares
    3.52 M    2.6%      3.52 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/memory_summary_by_account_by_event_name