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