MySQL Group Replicationの監視に関しては、Performance_schemaからレプリケーションの状態を確認して、モニタリングする事が可能ですが、MySQL Enterprise Monitor3.4ではGroup ReplicationのトポロジーViewやAdvisor等で、モニタリングを簡素化して、システムの安定稼働と運用負荷を軽減してくれるようになりました。自作でモニタリングツールを作る事も可能ですが、ツールのアップデート等に工数がかかるので、出来るだけ既存のツールを利用したい場合は有用かと思います。

Group Replicationステータスモニタリング用オブジェクト
(Group Replication Status Monitoring MySQL Objects)

performance_schema.replication_group_member_stats
performance_schema.replication_group_members
performance_schema.replication_connection_status
performance_schema.replication_applier_status

ステータスモニタリング例

グループ・レプリケーションのグループメンバーの状況を確認
(Can Confirm Group member status)

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 698f11c8-0397-11e7-aae1-080027d65c57
 MEMBER_HOST: replications
 MEMBER_PORT: 63301
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 713ad572-0397-11e7-aca3-080027d65c57
 MEMBER_HOST: replications
 MEMBER_PORT: 63302
MEMBER_STATE: ONLINE

グループによってコミットされたトランザクション、キューの増加状況、競合の検出数、検査されたトランザクションの数等を確認。
(We can confirm Committed Transaction, Queue, Conflict and so on.)

mysql> SELECT * FROM performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14896410386000092:7
                         MEMBER_ID: 78b1d98a-0397-11e7-aef2-080027d65c57
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 2
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 00000000-1111-2222-3333-123456789abc:1-29
    LAST_CONFLICT_FREE_TRANSACTION: 00000000-1111-2222-3333-123456789abc:29
1 row in set (0.00 sec)

CHANNEL名やグループから受信してアプライアキュー(リレーログ)に入れられたトランザクションを確認
(We can confirm Applier Queue and channel names.)

mysql> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:1-29
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************

Enterprise Monitorでの監視例
(How to monitor group replication by using MySQL Enterprise Monitor)
Group Replicationの状態を可視化して、詳細を管理ノードで一元管理する事が可能。
障害発生時は、メール若しくはSNMPでトラップ送信して知らせる事も可能。

グループトポロジー
(Replication Topology and status under normal condition.)

グループトポロジー(障害発生時)
(Replication Topology and status during system trouble.)

グループレプリケーションステータス
(Group Replication Status over view)

その他、詳細情報
(Group Replication Status and other replication related detail information.)

エラーが発生した場合のログ確認
(Group Replication Error Logs)

Nice to watch for catching up with Group Replication on YouTube.

詳細情報:
https://dev.mysql.com/doc/mysql-monitor/3.4/en/mem-replication.html

https://dev.mysql.com/doc/mysql-monitor/3.4/en/mem-replication-dashboard-ui-ref.html#fig-mem-group-replication-topology-single

MySQL Enterprise 試用版のダウンロード
https://www.mysql.com/jp/trials/


MySQL8.0からのパーティションについての確認
MySQL5.7からは、InnoDB Native Partitioningがサポートされるようになり、多くパーティションを利用している環境で、メモリーの利用率を大幅に下げる事が出来るようになっている。
MySQL8.0からは、Partition Storage Engineがディフォルトでロードされなくなるので注意を促す為に、MySQL5.7.17から”–disable-partition-engine-check”を設定して下さいというWarningがエラーログに出るようになっています。

yoku0825さんが速攻で確認して、ブログを書かれているので確認してみて下さい。

【InnoDB Native Partitioningに関して】
http://mysqlserverteam.com/innodb-native-partitioning-early-access/

【抜粋】

WL#6035: Add native partitioning support to InnoDB
https://dev.mysql.com/worklog/task/?id=6035

WL#8971: Deprecate and remove partitioning storage engine
https://dev.mysql.com/worklog/task/?id=8971

MySQL5.7からは、InnoDB Native Paritioningがサポートされている。
そして、MySQL5.7までは、partition Storage EngineもまだDefaultで含まれている。

MySQL5.7.17で確認すると以下のような感じ。


mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.17-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    -> PLUGIN_NAME as Name,
    -> PLUGIN_VERSION as Version,
    -> PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| InnoDB             | 5.7     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| BLACKHOLE          | 1.0     | DISABLED |
| partition          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| ARCHIVE            | 3.0     | DISABLED |
+--------------------+---------+----------+
11 rows in set (0.00 sec)

mysql> CREATE TABLE T_PARTITION (c1 int)
    -> PARTITION BY RANGE COLUMNS(c1) 
    -> (
    -> PARTITION p1 VALUES LESS THAN (5),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
    -> );

Query OK, 0 rows affected (0.45 sec)

mysql> select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION from information_schema.partitions where table_name = 'T_PARTITION';
+-------------+----------------+------------------+-----------------------+
| TABLE_NAME  | PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+-------------+----------------+------------------+-----------------------+
| T_PARTITION | p1             | RANGE COLUMNS    | 5                     |
| T_PARTITION | p2             | RANGE COLUMNS    | 10                    |
| T_PARTITION | p3             | RANGE COLUMNS    | MAXVALUE              |
+-------------+----------------+------------------+-----------------------+
3 rows in set (0.00 sec)

mysql> 

MySQL5.7までは、まだメタデータのFRMファイルも作成されている。

[oracle@shinya01-mysql-1 mydatabase]$ ls -l T_PART*
-rw-r----- 1 oracle oracle  8556 Apr 20 04:01 T_PARTITION.frm
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p1.ibd
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p2.ibd
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p3.ibd
[oracle@shinya01-mysql-1 mydatabase]$

MySQL8.0.1の段階で、既にpartition storage engineは含まれていない。

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT
    -> PLUGIN_NAME as Name,
    -> PLUGIN_VERSION as Version,
    -> PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| InnoDB             | 8.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| ARCHIVE            | 3.0     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
+--------------------+---------+----------+
10 rows in set (0.00 sec)

mysql> CREATE TABLE T_PARTITION (c1 int)
    -> PARTITION BY RANGE COLUMNS(c1) 
    -> (
    -> PARTITION p1 VALUES LESS THAN (5),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION from information_schema.partitions where table_name = 'T_PARTITION';
+-------------+----------------+------------------+-----------------------+
| TABLE_NAME  | PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+-------------+----------------+------------------+-----------------------+
| T_PARTITION | p1             | RANGE COLUMNS    | 5                     |
| T_PARTITION | p2             | RANGE COLUMNS    | 10                    |
| T_PARTITION | p3             | RANGE COLUMNS    | MAXVALUE              |
+-------------+----------------+------------------+-----------------------+
3 rows in set (0.00 sec)

mysql> 

また、MySQL8.0からはfrmがテーブルに格納されるのでファイルは無い。

[root@DockerHost sakila]# ls -l T_PART*
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p1.ibd
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p2.ibd
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p3.ibd
[root@DockerHost sakila]# 

MySQL8.0でNDB,InnoDB以外でパーティションを作成しようとすると以下のようにエラーになる。

mysql> CREATE TABLE `T_PARTITION_MyISAM` (
    ->   `c1` int(11) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
    -> /*!50500 PARTITION BY RANGE  COLUMNS(c1)
    -> (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM,
    ->  PARTITION p2 VALUES LESS THAN (10) ENGINE = MyISAM,
    ->  PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */;
ERROR 1178 (42000): The storage engine for the table doesn't support native partitioning
mysql>

MySQL8.0から、Descending Indexがサポートされる。
これによりMySQL5.7と比較して、大きなテーブルでの降順(DESC)のデータ参照処理を、高速に実行する事が出来るようになる。小さいテーブルに関しては、既存のMySQL5.7でもインデックスが利用出来るので、それ程差は出ないかと思います。

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXはASCで作成されている)


mysql> select @@version;                                              
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)

mysql> show create table rental\G                                     
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3; 
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXをDESCで作成し直した場合)
インデックスを降順に作成したので、先程とはEXTRAが反対になっている事が確認出来る。

mysql> alter table rental add unique key rental_date_desc (`rental_date` desc,`inventory_id` desc,`customer_id` desc);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;                                                
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;                                               
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> 

詳細は、以下のMySQL Server Teamのブログを確認下さい。
MySQL 8.0 Labs – Descending Indexes in MySQL
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

MySQL5.5 ~ MySQL5.7までは昇順(ASC)で格納されたインデックスデータを利用してASC, DESC共に参照処理を行う
MySQL 5.7までは、インデックスのデータはASCで格納されています。こちらは、DESCで格納したインデックスと比べて、降順のデータの参照パフォーマンスは遅いが、
クエリーにDESCオプションを付けて、直近のデータを参照するとASCで作成したINDEXを利用してデータを参照する為、インデックスを利用した後方参照処理になる為、
インデックスが無い場合と比べても高速なレスポンスで後方参照処理を行う事が出来る。以下のマニュアルは、若干分かり難いがASC、DESCを付けてインデックスを作成しても、
MySQL5.5以降のMySQLであれば昇順(ASC)で作成されたINDEXでASCもDESCも処理出来る為、高速に参照する事が可能です。MySQL8.0で処理がより高速になる。

5.7 英語マニュアル
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

5.6 日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/create-index.html
index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

MySQL5.7.18の実行プランは以下のような感じです。インデックスを利用して降順(DESC)参照が行われている事が確認出来る。
MySQL8.0RCがリリースされた頃に、大きなテーブルでレスポンスの差を確認して見たいと思います。


mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table rental\G
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select * from rental limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental limit 3;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16005 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` limit 3

mysql> select * from rental order by rental_date asc limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` limit 3

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` desc limit 3
mysql>  


MySQL5.7からslave_parallel_workersを調整してスレーブの遅延が対応出来る事は、
色々な資料やブログ等にも書かれているので詳細はそちらを確認してみて下さい。
Oracle MySQL Cloud Service(OC3 = 2vCPU)の環境でSQLSLAPで負荷をかけてみて、
マスターとスレーブで遅延がどれだけ解消できるか?若しくはどこまで調整すれば良いか確認してみました。
slave_parallel_workersを1,2,4,8,16,32と変更して確認した中では、slave_parallel_workers=8が安定していました。
但し、slave_parallel_workersが多いからと言ってCPUが少ないインスタンスより上がる訳では無く、全体的なシステムのバランスが重要なようです。
スレーブのCPUや実行されているQuery等を確認して、適宜最適な値を調整出来ると良いですね。

環境
Oracle Cloud
MySQL Service
Instance: OC3 (2vCPU)
Monitoring: MySQL Enterprise Monitor

slave_parallel_workers=1

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL SLAVE_PARALLEL_WORKERS=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'SLAVE_PARALLEL_WORKERS';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 1     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> 

[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 38.711 seconds
        Minimum number of seconds to run all queries: 38.711 seconds
        Maximum number of seconds to run all queries: 38.711 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[oracle@study01-mysql-1 ~]$ 

slave_parallel_workers=8

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL SLAVE_PARALLEL_WORKERS=8;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'SLAVE_PARALLEL_WORKERS';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 8     |
+------------------------+-------+
1 row in set (0.01 sec)

mysql> 
[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 38.466 seconds
        Minimum number of seconds to run all queries: 38.466 seconds
        Maximum number of seconds to run all queries: 38.466 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[oracle@study01-mysql-1 ~]$ 

以下、MySQL Enterprise Monitorで確認したグラフです。スレーブの遅延の状況が分かり易いですね。

slave_parallel_workers=2とslave_parallel_workers=8の差です。
1) Master -> 2) slave_parallel_workers=8 -> slave_parallel_workers=2の順にデータベース内での処理が終わっています。

slave_parallel_workers=2とslave_parallel_workers=32の差です。
この検証では、マスターとスレーブがほぼ同時に終了しています。

数回CPUの負荷を取得してみましたが、今回の環境ではThreadが少ない方がCPU負荷が上がっていました。何処に差があるかはまた別途確認してみますが、Threadが多いからと言って必ずしも少ないインスタンスよりCPU負荷が上がる訳では無さそうです。

[oracle@study02-mysql-1 ~]$ 
01:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:10:01 PM     all      0.97      0.08      0.18      0.80      0.07     97.90
01:20:01 PM     all      4.08      0.32      0.31      4.32      0.08     90.89
01:30:01 PM     all      0.78      2.31      7.91      8.56      0.12     80.33
01:40:01 PM     all      0.57      2.46      7.94     10.96      0.13     77.93
01:50:01 PM     all      0.61      0.14      0.09      0.66      0.06     98.44
02:00:01 PM     all      0.74      0.14      0.09      0.63      0.07     98.33
Average:        all      0.10      0.10      0.22      0.69      0.06     98.83


[oracle@study03-mysql-1 ~]$ 
01:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:10:01 PM     all      0.79      0.15      0.09      0.55      0.06     98.35
01:20:01 PM     all      0.68      0.34      0.16      3.82      0.09     94.91
01:30:01 PM     all      0.56      1.75      0.70      6.41      0.10     90.48
01:40:01 PM     all      0.58      1.75      0.75      8.11      0.12     88.69
01:50:01 PM     all      0.52      0.13      0.09      0.43      0.08     98.75
02:00:01 PM     all      0.55      0.13      0.09      0.86      0.07     98.30
Average:        all      0.23      0.09      0.08      0.61      0.07     98.93

大目に負荷をかけて、もう少し長いスパンで影響を確認してみました

[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 185.845 seconds
        Minimum number of seconds to run all queries: 185.845 seconds
        Maximum number of seconds to run all queries: 185.845 seconds
        Number of clients running queries: 10
        Average number of queries per client: 50000

[oracle@study01-mysql-1 ~]$

以下の、グラフを確認してみるとslave_parallel_workersを適切に調節すれば、レプリケーションの遅延、余計なCPU負荷の削減なども出来る事が確認出来ます。

補足: SHOW SLAVE STATUSによるマスターからの遅延状況の確認

slave_parallel_workersが8と16では殆ど変りが無い事が分かります。
サーバースペック、ネットワーク、インスタンスの負荷等によっても変わってくるので適宜調整下さい。


MySQL Cluster7.5からPK参照以外の参照性能が向上している。
テーブル作成時にREAD_BACKUPオプションを利用するか、ndb_read_backupオプションを設定しておくとPRIMARYレプリカだけでなく、SECONDARYレプリカからもデータが参照する事が出来るようになる。
MySQL7.4までは常に、PRIMARYレプリカからデータを参照していたので、ローカルにPRIMARYデータが無い場合は、PRIMARYデータを持っているノードまで取得しにいく必要があった。

イメージ

cluster

参照:
14.1.18.7 Setting NDB_TABLE options in table comments

抜粋: 
READ_BACKUP: Setting this option to 1 has the same effect as though ndb_read_backup were enabled; enables reading from any replica.
Setting this option to 1 automatically sets FRAGMENT_COUNT_TYPE to ONE_PER_LDM_PER_NODE_GROUP (see next item).
Starting with MySQL Cluster NDB 7.5.3, you can set READ_BACKUP for an existing table online,
using an ALTER TABLE statement similar to one of those shown here:


ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1";
ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";

【結論】
結果としては、READ_BACKUPをONにした方が、3割程パフォーマンスが向上していました。
非常に非力な、仮想環境での検証なので皆さんの環境でも是非検証してみて下さい。
大幅にパフォーマンスが改善する可能性があります。

READ_BACKUP=0 (OFF)
transactions: 5650 (93.91 per sec.)
transactions: 5718 (94.91 per sec.)
transactions: 5698 (94.71 per sec.)
transactions: 5490 (91.24 per sec.)

READ_BACKUP=1 (ON)
transactions: 7234 (120.17 per sec.)
transactions: 7403 (123.00 per sec.)
transactions: 7419 (123.32 per sec.)
transactions: 7264 (120.77 per sec.)

※ マシーン自体は非力なのでTPSは少ないですが、どちらも全く同じ状況で検証しています。

■ 簡単にSYSBENCH0.5のOLTP READで、参照パフォーマンスの確認を行ってみました。
メモリーが無いので、データは1万件しかいれてません。(1万 X 4テーブル)


mysql> select @@version;
+----------------------------------+
| @@version                        |
+----------------------------------+
| 5.7.13-ndb-7.5.3-cluster-gpl-log |
+----------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1            |
| sbtest2            |
| sbtest3            |
| sbtest4            |
+--------------------+
4 rows in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

mysql> 

SYSBENCHのスレッド数と実行しているQUERY
‐ 32スレッドで60秒程実行しています。 
‐ Queryの殆どは、BETWEEN等を利用しているのでPKで特定のデータをピンポイントで持ってくる処理ではありません。


/bin/sysbench \
  --test=../lua/db/oltp.lua \
  --rand-init=on \
  --db-driver=mysql \
  --oltp-table-size=${TSIZE} \
  --rand-type=uniform \
  --oltp-read-only=on \
  --oltp-tables-count=${NTBLE}\
  --mysql-db=sysbench \
  --mysql-host=${ENDPOINT} \
  --mysql-user=bench_user \
  --mysql-password=password \
  --max-time=60 \
  --max-requests=0 \
  --num-threads=32 \




[root@CL-SLAVE01 sys03]# cat ../lua/db/oltp.lua | egrep -i select 
   for i=1, oltp_point_selects do
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
      rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
[root@CL-SLAVE01 sys03]# 

上記のテーブルとデータでDEFAULTの状態(READ_BACKUPをOFF)で検証してみます。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            79100
        write:                           0
        other:                           11300
        total:                           90400
    transactions:                        5650   (93.91 per sec.)
    read/write requests:                 79100  (1314.76 per sec.)
    other operations:                    11300  (187.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1630s
    total number of events:              5650
    total time taken by event execution: 1919.4297s
    response time:
         min:                                 89.72ms
         avg:                                339.72ms
         max:                                617.21ms
         approx.  95 percentile:             442.32ms

Threads fairness:
    events (avg/stddev):           176.5625/2.45
    execution time (avg/stddev):   59.9822/0.11

[root@CL-SLAVE01 sys03]# 




[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            80052
        write:                           0
        other:                           11436
        total:                           91488
    transactions:                        5718   (94.91 per sec.)
    read/write requests:                 80052  (1328.77 per sec.)
    other operations:                    11436  (189.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.2452s
    total number of events:              5718
    total time taken by event execution: 1924.6034s
    response time:
         min:                                161.28ms
         avg:                                336.59ms
         max:                                646.96ms
         approx.  95 percentile:             441.26ms

Threads fairness:
    events (avg/stddev):           178.6875/2.28
    execution time (avg/stddev):   60.1439/0.07

[root@CL-SLAVE01 sys03]# 

READ_BACKUPをONにしています。ALTERテーブルでテーブル毎に設定変更可能です。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPオプションをONにしたので、同じテストを実行してみます。


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            101276
        write:                           0
        other:                           14468
        total:                           115744
    transactions:                        7234   (120.17 per sec.)
    read/write requests:                 101276 (1682.33 per sec.)
    other operations:                    14468  (240.33 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1997s
    total number of events:              7234
    total time taken by event execution: 1919.6510s
    response time:
         min:                                 61.99ms
         avg:                                265.37ms
         max:                                515.43ms
         approx.  95 percentile:             350.01ms

Threads fairness:
    events (avg/stddev):           226.0625/0.97
    execution time (avg/stddev):   59.9891/0.10

[root@CL-SLAVE01 sys03]# 



[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            103642
        write:                           0
        other:                           14806
        total:                           118448
    transactions:                        7403   (123.00 per sec.)
    read/write requests:                 103642 (1722.05 per sec.)
    other operations:                    14806  (246.01 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1852s
    total number of events:              7403
    total time taken by event execution: 1923.2176s
    response time:
         min:                                143.96ms
         avg:                                259.79ms
         max:                                421.40ms
         approx.  95 percentile:             328.68ms

Threads fairness:
    events (avg/stddev):           231.3438/0.59
    execution time (avg/stddev):   60.1006/0.05

[root@CL-SLAVE01 sys03]# 

■ 再テストでREAD_BACKUPをOFFにしてみます。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPをOFFにした状態で再度検証してみると、TPSは落ちています。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            79772
        write:                           0
        other:                           11396
        total:                           91168
    transactions:                        5698   (94.71 per sec.)
    read/write requests:                 79772  (1325.97 per sec.)
    other operations:                    11396  (189.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1611s
    total number of events:              5698
    total time taken by event execution: 1920.7135s
    response time:
         min:                                118.55ms
         avg:                                337.09ms
         max:                                600.45ms
         approx.  95 percentile:             440.47ms

Threads fairness:
    events (avg/stddev):           178.0625/1.71
    execution time (avg/stddev):   60.0223/0.09

[root@CL-SLAVE01 sys03]# 


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            76860
        write:                           0
        other:                           10980
        total:                           87840
    transactions:                        5490   (91.24 per sec.)
    read/write requests:                 76860  (1277.31 per sec.)
    other operations:                    10980  (182.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1734s
    total number of events:              5490
    total time taken by event execution: 1922.5091s
    response time:
         min:                                171.24ms
         avg:                                350.18ms
         max:                                801.92ms
         approx.  95 percentile:             478.98ms

Threads fairness:
    events (avg/stddev):           171.5625/1.32
    execution time (avg/stddev):   60.0784/0.06

[root@CL-SLAVE01 sys03]# 

READ_BACKUP設定を再度ONにしています。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

もう一度、READ_BACKUPをONにしてパフォーマンスを確認してみました。
やはり、ONにした方が今回の環境ではパフォーマンスが良いようです。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            103866
        write:                           0
        other:                           14838
        total:                           118704
    transactions:                        7419   (123.32 per sec.)
    read/write requests:                 103866 (1726.47 per sec.)
    other operations:                    14838  (246.64 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1609s
    total number of events:              7419
    total time taken by event execution: 1920.0195s
    response time:
         min:                                 91.51ms
         avg:                                258.80ms
         max:                                463.14ms
         approx.  95 percentile:             351.79ms

Threads fairness:
    events (avg/stddev):           231.8438/0.83
    execution time (avg/stddev):   60.0006/0.13

[root@CL-SLAVE01 sys03]#


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            101696
        write:                           0
        other:                           14528
        total:                           116224
    transactions:                        7264   (120.77 per sec.)
    read/write requests:                 101696 (1690.77 per sec.)
    other operations:                    14528  (241.54 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1476s
    total number of events:              7264
    total time taken by event execution: 1921.1800s
    response time:
         min:                                 77.60ms
         avg:                                264.48ms
         max:                                454.03ms
         approx.  95 percentile:             343.88ms

Threads fairness:
    events (avg/stddev):           227.0000/0.79
    execution time (avg/stddev):   60.0369/0.06

[root@CL-SLAVE01 sys03]# 

MySQL Cluster 7.5はMySQL5.7ベースのMySQLが利用可能なので、JSONが利用出来たり色々な改善が加わっています。
これまでより、多くのケースで活用出来る場面が増えてくるかと思います。詳細は以下のURLを参照下さい。
19.1.4 What is New in MySQL Cluster NDB 7.5

例) MySQL Cluster7.5のndbinfoから,こちらのようにテーブルの情報も確認出来るようになってます。


mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name IN ('NodeId', 'HostName','DataMemory', 'IndexMemory');
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| Name        | Node | Type     | Default   | Minimum | Maximum       | Required | Current        |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| NodeId      |    1 | unsigned |           | 1       | 48            | Y        | 1              |
| HostName    |    1 | string   | localhost |         |               | N        | 192.168.56.114 |
| DataMemory  |    1 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    1 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
| NodeId      |    2 | unsigned |           | 1       | 48            | Y        | 2              |
| HostName    |    2 | string   | localhost |         |               | N        | 192.168.56.115 |
| DataMemory  |    2 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    2 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
8 rows in set (0.09 sec)

mysql> 

MySQL Clusterに関しても、セミナーで紹介していたりするので都合が付けば是非参加下さい。
https://www-jp.mysql.com/news-and-events/events/


MySQL Clusterの検証をIBM(Softlayer)さん、株式会社インフィニットループさんと共同で行って、
結果を先日のCEDECで発表した時の資料です。インフィニットループさんがOpenSourceのゲームで検証してくれているので、内容的には参考になるかと思います。

1. MySQL Clusterを稼働させるにはネットワークが非常に重要
2. MySQL Clusterを稼働させる場合、仮想環境のCommit Ratioがコントロール出来ない場合はベアメタルが良い。
3. 仮想、ベアメタル共にSSDを利用していてもISCSIよりもやはりローカルの方が断然良い。
4. MaxNoOfExecutionThreadsより、ThreadConfigで丁寧に調整した方がパフォーマンス良い(この検証では1.2倍)
5. 今回のSYSBENCH0.5での検証では、データの偏りがあったので思うようにパフォーマンスが出なかった。(パーティションで対応可能)
6・ Sysbench0.5のOLTP R/Wの処理は、5割程がMySQL Cluster向きで残りの5割はInnDB向きの処理。(データノードを跨る処理が多かった)

今回は2週間でしたが、もう少し時間があればより最適なパラメータ構成を設定出来たかと思います。
皆さんも利用する場合は、事前に最適なパラメータ確認を実施して下さい。

MySQL Cluster Community Edition Download
http://dev.mysql.com/downloads/cluster/

Commercial Edition追加ツールとサポート
https://www-jp.mysql.com/products/cluster/


SYSスキーマのstatement_analysisとperformance_schemaのevents_statements_historyを利用して、MySQLで簡単にSQLのパフォーマンス確認。

statement_analysis / x$statement_analysis
Description:
Lists a normalized statement view with aggregated statistics,
mimics the MySQL Enterprise Monitor Query Analysis view, ordered by the total execution time per normalized statement
https://github.com/mysql/mysql-sys

events_statements_history
events_statements_history テーブルには、スレッドごとの最新の N ステートメントイベントが格納されます。
N の値はサーバー起動時に自動サイズ設定されます。テーブルサイズを明示的に設定するには、サーバー起動時にperformance_schema_events_statements_history_sizeシステム変数を設定します。
ステートメントイベントは終了するまでテーブルに追加されません。新しいイベントが追加されたときに、テーブルがいっぱいである場合、古いイベントが破棄されます。
https://dev.mysql.com/doc/refman/5.6/ja/events-statements-history-table.html

以下、幾つかテストしてみました。データも十分に入っていないので、十分なテストでは無いですが、
挙動は把握出来るかと思いますので、実際の検証環境で確認して見てください。

【1】 JSONデータのFULLスキャンの状況確認
(1) 先ずは、既存のsys.statement_analysisの状況を確認してみます。

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis;
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db                 | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:16:47 | SHOW TABLES                                                       | sys                | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | sys                | *         |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SHOW SCHEMAS                                                      | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SHOW TABLES                                                       | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SYSTEM_USER , HOST , `d ... != ? AND `state` NOT IN (...)  | performance_schema | *         |          2 |               2 |                 0 |
| 2016-06-03 22:16:47 | SHOW SCHEMAS                                                      | sys                | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SCHEMA ( )                                                 | performance_schema |           |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SCHEMA ( )                                                 | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SYSTEM_USER ( )                                            | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT @@`version_comment` LIMIT ?                                | NULL               |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
10 rows in set (0.00 sec)

root@localhost [sys]> 

(2) こちらのJSONデータを確認するQueryを実行してみます。

echo "INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。"
echo "SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル"
echo ""

echo "【TEXT型】"
time /usr/local/mysql/bin/mysql -u demo_user -ppassword -e "SELECT distinct json_extract(feature,'$.type') as feature FROM NEW57.features_txt;"

echo ""

echo "【JSON型】"
time /usr/local/mysql/bin/mysql -u demo_user -ppassword -e "SELECT distinct json_extract(feature,'$.type') as feature FROM NEW57.features_json;"

(3) 実行 (やっぱりJSONデータ型は、TEXT型と比較するとバイナリーで早いですね:206,000件のデータ参照)

[admin@misc01 SOD2015]$ ./json_and_text_without_index.sh 
INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。
SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル

【TEXT型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m8.147s
user    0m0.007s
sys     0m0.004s

【JSON型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m1.379s
user    0m0.009s
sys     0m0.005s
[admin@misc01 SOD2015]$ 

(4) 実行結果
INDEXも無い、206,000件のデータなのでどちらも、FULLスキャン,temp table,そしてディスクのtemp tableへ変換されてますね。

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis;
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db                 | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:19:02 | SELECT DISTINCTROW `json_extra ... FROM `NEW57` . `features_txt`  | NULL               | *         |          1 |               1 |                 0 | ※
| 2016-06-03 22:19:02 | SELECT DISTINCTROW `json_extra ... ROM `NEW57` . `features_json`  | NULL               | *         |          1 |               1 |                 0 | ※
| 2016-06-03 22:19:02 | SHOW TABLES                                                       | sys                | *         |          2 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | sys                | *         |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SHOW SCHEMAS                                                      | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SYSTEM_USER ( )                                            | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SHOW SCHEMAS                                                      | sys                | *         |          2 |               0 |                 0 |
| 2016-06-03 22:19:02 | SHOW TABLES                                                       | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SYSTEM_USER , HOST , `d ... != ? AND `state` NOT IN (...)  | performance_schema | *         |          2 |               2 |                 0 |
| 2016-06-03 22:19:02 | SELECT @@`version_comment` LIMIT ?                                | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SCHEMA ( )                                                 | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SCHEMA ( )                                                 | performance_schema |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
12 rows in set (0.01 sec)

root@localhost [sys]> 

【2】 JSONデータにGENERATE COLUMN(生成列)でINDEXを付与した場合の状況確認

(1) 実行スクリプト

echo "JSONデータ型のサンプルを入れたテーブル"
/usr/local/mysql/bin/mysql -u demo_user -ppassword -e "select * from NEW57.features where feature_street = '\"MARKET\"' limit 1\G"

read -p "Press [Enter] key to resume."
echo "JSONデータ型とGenerated Columnを利用したテーブル"
/usr/local/mysql/bin/mysql -u demo_user -ppassword -e "show create table NEW57.features\G"

read -p "Press [Enter] key to resume."
echo "JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where feature_street = '\"MARKET\"'"
/usr/local/mysql/bin/mysql -u demo_user -ppassword -e "explain select feature from NEW57.features where feature_street = '\"MARKET\"'\G"

(2) 実行と実行結果

[admin@misc01 SOD2015]$ ./json_and_generated_column_index.sh 
JSONデータ型のサンプルを入れたテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            id: 12250
       feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
  feature_type: "Feature"
feature_street: "MARKET"
Press [Enter] key to resume.

JSONデータ型とGenerated Columnを利用したテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_type` (`feature_type`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4
Press [Enter] key to resume.

JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where feature_street = '"MARKET"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: idx_feature_street
          key: idx_feature_street
      key_len: 123
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
[admin@misc01 SOD2015]$ 

(3) 実行後のsys.statement_analysisを確認すると、full_scanでも無く,temp tableでの処理も無いことが確認出来る。

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis;
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db                 | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:22:03 | SELECT DISTINCTROW `json_extra ... FROM `NEW57` . `features_txt`  | NULL               | *         |          1 |               1 |                 0 |
| 2016-06-03 22:22:03 | SELECT DISTINCTROW `json_extra ... ROM `NEW57` . `features_json`  | NULL               | *         |          1 |               1 |                 0 |
| 2016-06-03 22:22:03 | SELECT * FROM `NEW57` . `featu ... _extract` ( `feature` , ? ) )  | NULL               |           |          0 |               0 |                 0 | ※
| 2016-06-03 22:22:03 | SHOW TABLES                                                       | sys                | *         |          3 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | sys                | *         |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW SCHEMAS                                                      | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SYSTEM_USER ( )                                            | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW SCHEMAS                                                      | sys                | *         |          3 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT @@`version_comment` LIMIT ?                                | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW TABLES                                                       | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SYSTEM_USER , HOST , `d ... != ? AND `state` NOT IN (...)  | performance_schema | *         |          2 |               2 |                 0 |
| 2016-06-03 22:22:03 | EXPLAIN SELECT `feature` FROM  ... _extract` ( `feature` , ? ) )  | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW CREATE TABLE `NEW57` . `f ... _extract` ( `feature` , ? ) )  | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SCHEMA ( )                                                 | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SCHEMA ( )                                                 | performance_schema |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
15 rows in set (0.00 sec)

root@localhost [sys]> 

(4) 強制的にデータのソート処理を発生させて、sys.statement_analysisにてsort_merge_passesが確認出来るか念の為確認。
group byの処理により、tmp_disk_tables、sort_merge_passes共に発生しています。


root@localhost [NEW57]> SELECT json_extract(feature,'$.properties.STREET'),count(json_extract(feature,'$.properties.STREET')) as feature FROM NEW57.features group by json_extract(feature,'$.properties.STREET');
<SNIP>
1717 rows in set (4.61 sec)

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'NEW57';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-04 08:40:56 | SELECT `json_extract` ( `featu ... on_extract` ( `feature` , ? )  | NEW57 | *         |          1 |               1 |                 1 | ※DISK処理発生
| 2016-06-04 08:40:56 | SELECT `json_extract` ( `featu ... re` FROM `NEW57` . `features`  | NEW57 | *         |          0 |               0 |                 0 |
| 2016-06-04 08:40:56 | SHOW SCHEMAS                                                      | NEW57 | *         |          1 |               0 |                 0 |
| 2016-06-04 08:40:56 | SHOW TABLES                                                       | NEW57 | *         |          1 |               0 |                 0 |
| 2016-06-04 08:40:56 | SELECT `json_extract` ( `featu ... re` FROM `NEW57` . `features`  | NEW57 |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
5 rows in set (0.01 sec)

root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| SQL_TEXT                                                                                                                                                                                  | t (ms)    | ROWS_EXAMINED |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| NULL                                                                                                                                                                                      |    0.0197 |             0 |
| NULL                                                                                                                                                                                      |    0.0080 |             0 |
| NULL                                                                                                                                                                                      |    0.0048 |             0 |
| NULL                                                                                                                                                                                      |    0.0039 |             0 |
| select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'NEW57'                                                               |    3.2530 |            25 |
| SELECT json_extract(feature,'$.properties.STREET'),count(json_extract(feature,'$.properties.STREET')) as feature FROM NEW57.features group by json_extract(feature,'$.properties.STREET') | 4606.0133 |        209994 |
| SELECT json_extract(feature,'$.properties.STREET') as feature FROM NEW57.features                                                                                                         |  559.5791 |        206560 |
| SELECT json_extract(feature,'$.properties.STREET'),count(json_extract(feature,'$.properties.STREET')) as feature FROM NEW57.features                                                      |    0.2148 |             0 |
| NULL                                                                                                                                                                                      |    0.0981 |             0 |
| SET @slave_uuid= 'b1f2d384-009f-11e6-9aa6-0800275fa837'                                                                                                                                   |    0.0996 |             0 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
10 rows in set (0.00 sec)

root@localhost [sys]> 


【3】 ここからは、MySQLのオフィシャルサンプルDB(World)を利用してMySQLの挙動とパフォーマンスを確認してみます。

パターン1) Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+---------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query         | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+---------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:25:41 | SHOW TABLES   | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:25:41 | SHOW SCHEMAS  | world | *         |          1 |               0 |                 0 |
+---------------------+---------------+-------+-----------+------------+-----------------+-------------------+
2 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [world]> SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+---------------------------+----------+
| Language                  | COUNT(1) |
+---------------------------+----------+
| Abhyasi                   |        1 |
| Acholi                    |        1 |
| Adja                      |        1 |
<SNIP>
| Zhuang                    |        1 |
| Zulu                      |        3 |
| [South]Mande              |        1 |
+---------------------------+----------+
457 rows in set (0.04 sec)

root@localhost [world]> 


root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:25:52 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(1回目)
| 2016-06-03 22:25:52 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:25:52 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
3 rows in set (0.00 sec)

root@localhost [sys]> 

上記、Queryの実行プランと実行時間を確認してみます。

root@localhost [world]> explain SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL    | PRIMARY             | NULL        | NULL    | NULL                  |  239 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | City            | NULL       | eq_ref | PRIMARY             | PRIMARY     | 4       | world.Country.Capital |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref    | PRIMARY,CountryCode | CountryCode | 3       | world.Country.Code    |    4 |   100.00 | Using index                                  |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

root@localhost [world]> 



root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SQL_TEXT                                                                                                                                                                    | t (ms) | ROWS_EXAMINED |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.3615 |          2368 |(1回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.5577 |             2 |
| truncate table performance_schema.events_statements_history                                                                                                                 | 0.9153 |             0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

パターン2) CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:28:15 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:28:15 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:28:15 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:28:15 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
4 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [world]> SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+---------------------------+----------+
| Language                  | COUNT(1) |
+---------------------------+----------+
| Abhyasi                   |        1 |
| Acholi                    |        1 |
| Adja                      |        1 |
<SNIP>
| Zhuang                    |        1 |
| Zulu                      |        3 |
| [South]Mande              |        1 |
+---------------------------+----------+
457 rows in set (0.01 sec)

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:29:13 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(1回目)
| 2016-06-03 22:29:13 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |(2回目)
| 2016-06-03 22:29:13 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:29:13 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:29:13 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
5 rows in set (0.01 sec)

root@localhost [sys]> 

上記、Queryの実行プランと実行時間を確認してみます。
行数は、多いですがFULL SCANは発生していません、また時間も若干早いです。(但し、何回も実施していないので確かではありません、但しこのサイズではそれ程大きく変わらない事は確かです。)

root@localhost [world]> explain SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                               | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | CountryLanguage | NULL       | index  | PRIMARY,CountryCode | CountryCode | 3       | NULL                              |  984 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | Country         | NULL       | eq_ref | PRIMARY             | PRIMARY     | 3       | world.CountryLanguage.CountryCode |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | City            | NULL       | eq_ref | PRIMARY             | PRIMARY     | 4       | world.Country.Capital             |    1 |   100.00 | Using index                                  |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

root@localhost [world]> 

root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SQL_TEXT                                                                                                                                                                    | t (ms) | ROWS_EXAMINED |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.0868 |          3865 |(2回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 1.5986 |             6 |
| SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.3615 |          2368 |(1回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.5577 |             2 |
| truncate table performance_schema.events_statements_history                                                                                                                 | 0.9153 |             0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
5 rows in set (0.00 sec)

root@localhost [sys]>

パターン3) Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code
ヒント無しなので、オプティマイザー次第

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:33:16 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:33:16 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
6 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [world]> SELECT Language, COUNT(1) FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+---------------------------+----------+
| Language                  | COUNT(1) |
+---------------------------+----------+
| Abhyasi                   |        1 |
| Acholi                    |        1 |
| Adja                      |        1 |
<SNIP>
| Zhuang                    |        1 |
| Zulu                      |        3 |
| [South]Mande              |        1 |
+---------------------------+----------+
457 rows in set (0.01 sec)

root@localhost [world]> 

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:33:29 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(1回目)
| 2016-06-03 22:33:29 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |(2回目)
| 2016-06-03 22:33:29 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(3回目)
| 2016-06-03 22:33:29 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:33:29 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:33:29 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |
| 2016-06-03 22:33:29 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
7 rows in set (0.01 sec)

root@localhost [sys]> 

上記、Queryの実行プランと実行時間を確認してみます。(今回はヒントを付けてませんが、1回目と同じ実行プランになりました)
よって、EXPLAINも時間も殆ど同じです。

root@localhost [world]> explain SELECT Language, COUNT(1) FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL    | PRIMARY             | NULL        | NULL    | NULL                  |  239 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | City            | NULL       | eq_ref | PRIMARY             | PRIMARY     | 4       | world.Country.Capital |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref    | PRIMARY,CountryCode | CountryCode | 3       | world.Country.Code    |    4 |   100.00 | Using index                                  |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

root@localhost [world]> 


root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SQL_TEXT                                                                                                                                                                    | t (ms) | ROWS_EXAMINED |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SELECT Language, COUNT(1) FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE          | 7.3393 |          2368 |(3回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.8451 |            10 |
| SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.0868 |          3865 |(2回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 1.5986 |             6 |
| SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.3615 |          2368 |(1回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.5577 |             2 |
| truncate table performance_schema.events_statements_history                                                                                                                 | 0.9153 |             0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
7 rows in set (0.00 sec)

root@localhost [sys]> 

【参考】
■ この動画とPDFは参考になる良い資料です。
https://community.oracle.com/docs/DOC-997564

■ こちらのツールを利用すると複数MySQLのシステムとSQLを監視する事が出来ます。
簡単にGUIベースで確認して管理コスト削減したいたい場合はお勧めします。
https://www-jp.mysql.com/products/enterprise/monitor.html


先日は、mysqlxプラグインをMySQL Shellから処理して基本動作確認しました。
mysqlxとMySQL Shell初期設定と基本動作確認
本日は、mysqlxに対応しているConnector/Node.jsからMySQLにJSONデータを処理を確認してみました。
基本的には、JohannesさんがMySQL Server Blogに書いた記事をベースに検証しています。
http://mysqlserverteam.com/mysql-5-7-12-part-5-connectornode-js/

【mysqlxとConnector/Node.jsを利用する為の前提条件】
MySQL 5.7.12 or higher, with the X plugin enabled
Node.JS 4.2

Download Connector/Node.js
~概要~
Connector/Node.js is the official Node.js driver for MySQL.
MySQL Connector/Node.js is an native asychronous promise-based client library for the of
MySQL 5.7.12+ providing New CRUD APIs for Document and Relational development

http://dev.mysql.com/downloads/connector/nodejs/

設定前の各種バージョン確認


[root@misc01 nodejs]# node -v
v4.4.3
[root@misc01 nodejs]# npm -v
2.15.1
[root@misc01 nodejs]# 

admin@192.168.56.113 [NEW57]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.12-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.01 sec)

admin@192.168.56.113 [NEW57]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE_VERSION from information_schema.plugins
    -> where PLUGIN_NAME = 'mysqlx';
+-------------+----------------+---------------+---------------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE_VERSION |
+-------------+----------------+---------------+---------------------+
| mysqlx      | 1.0            | ACTIVE        | 50712.0             |
+-------------+----------------+---------------+---------------------+
1 row in set (0.01 sec)

admin@192.168.56.113 [NEW57]> 

ダウンロード
node.jsもMySQLもコネクター対応している事が確認出来たので、コネクターをダウンロードしてインストールしてみます。


[root@misc01 nodejs]# wget http://dev.mysql.com/get/Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz
--2016-04-18 11:00:12--  http://dev.mysql.com/get/Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz
dev.mysql.com (dev.mysql.com) をDNSに問いあわせています... 137.254.60.11
dev.mysql.com (dev.mysql.com)|137.254.60.11|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: http://cdn.mysql.com//Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz [続く]
--2016-04-18 11:00:18--  http://cdn.mysql.com//Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz
cdn.mysql.com (cdn.mysql.com) をDNSに問いあわせています... 104.78.21.123
cdn.mysql.com (cdn.mysql.com)|104.78.21.123|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 121705 (119K) [application/x-tar-gz]
`mysql-connector-nodejs-1.0.2.tar.gz' に保存中
100%[==============================================================================================>] 121,705      157KB/s 時間 0.8s   
2016-04-18 11:00:19 (157 KB/s) - `mysql-connector-nodejs-1.0.2.tar.gz' へ保存完了 [121705/121705]
[root@misc01 nodejs]# 

npmを使用してインストール
http://dev.mysql.com/doc/dev/connector-nodejs/
http://dev.mysql.com/doc/dev/connector-nodejs/tutorial-Getting_Started.html


[root@misc01 nodejs]# npm install mysql-connector-nodejs-1.0.2.tar.gz
mysqlx@1.0.2 node_modules/mysqlx
[root@misc01 nodejs]# 

シェルのサンプルを利用した基本動作確認

[root@misc01 nodejs]# cat sample_node_X_API.js 
const mysqlx = require('mysqlx');

mysqlx.getSession({
    host: 'localhost',
    port: 33060,
    dbUser: 'demo_user',
    dbPassword: 'password'
}).then(function (session) {
    return session.createSchema("test_schema").then(function (schema) {
        return schema.createCollection("myCollection");
    }).then(function (collection) {
        return Promise.all([
            collection.add(
                {baz: { foo: "bar"}},
                {foo: { bar: "baz"}}
            ).execute(),
            collection.find("$.baz.foo == 'bar'").execute(function (row) {
                console.log("Row: %j", row);
            }).then(function (res) {
                console.log("Collection find done!");
            }),
            collection.remove("($.foo.bar) == 'baz'").execute().then(function () {
                console.log("Document deleted");
            }),
            collection.drop()
        ]);
    }).then(function () {
        return session.dropSchema("test_schema");
    }).then(function () {
        return session.close();
    });
}).catch(function (err) {
    console.log(err.stack);
    process.exit();
});

上記スクリプトを実行


[root@misc01 nodejs]# node sample_X_API.js 
Row: {"_id":"d0ef5b1d-86e6-34b2-5794-3ebcab02","baz":{"foo":"bar"}}
Collection find done!
Document deleted
[root@misc01 nodejs]# 

実行すると、以下のオブジェクトとデータがmysqlx経由(33060)でjavascriptから登録されている

admin@192.168.56.113 [test_schema]> show tables;
+-----------------------+
| Tables_in_test_schema |
+-----------------------+
| myCollection          |
+-----------------------+
1 row in set (0.01 sec)

admin@192.168.56.113 [test_schema]> select * from myCollection;
+--------------------------------------------------------------------+----------------------------------+
| doc                                                                | _id                              |
+--------------------------------------------------------------------+----------------------------------+
| {"_id": "21dddfdd-2d4e-3cf1-fc5f-05a0e9ea", "foo": {"bar": "baz"}} | 21dddfdd-2d4e-3cf1-fc5f-05a0e9ea |
| {"_id": "7a7a7497-95c8-b7e7-fa15-064a8aba", "baz": {"foo": "bar"}} | 7a7a7497-95c8-b7e7-fa15-064a8aba |
+--------------------------------------------------------------------+----------------------------------+
2 rows in set (0.00 sec)

admin@192.168.56.113 [test_schema]> 

MySQLXのClassは以下のページを参照してください。
http://dev.mysql.com/doc/dev/connector-nodejs/NodeSession.html

その他、幾つかのパターンを検証してみましたが、node.js経由でJSONドキュメントデータをプラグイン経由で処理する事が可能なので、
JSONドキュメント処理をXプロトコル経由で高速に処理出来そうです。ただ、SQL処理とSQLX経由の処理に関しては、自分の方では実際のベンチマークは行っていないので、
Dimitriさんがブログで結果を発表してくれるのを待とうかと思います。
まだリリース間もないので、色々と情報を集めて紹介出来ればと思います。


レプリケーションフレームワークのMySQL Fabricについては、昨年概要をブログに書いたので本日は、
MySQL FabricをMySQL Routerの連携による可用性や柔軟なスケールアウト方法について確認して見ます。

MySQL Fabricは便利なフレームワークですが、Fabric対応コネクターを利用しないと利用出来ず、
アプリケーションの書き換え労力が必要な為、あまりまだ広まっていませんでしたが、
MySQL Routerのリリースにより今後利用されるケースも増えてくるかと思います。

1. 障害発生時にマスターとスレーブ(新マスター)を自動切り替えし、アプリケーション接続変更は必要が無い。
2. アクセスが増えSlaveサーバーを追加した場合に、アプリケーションを変更せずに動的に新Slaveは参照用Round-Robinに追加される。
3. その他、サーバー入れ替え等のメンテナンスもFabricを利用する事で柔軟性が大幅に向上。

実際に障害発生した場合は、自動的に切り替えを行ってくれるので。
マスター障害発生時も機会損失を最小限にすると同時に緊急対応が不要にする事も可能なので運用負荷を軽減する事が可能です。

router_2

メモ
※ MySQL RouterはMySQL Fabricと連携しないでも利用する事は可能ですが、Fabricを利用した方がより柔軟な対応が可能です。
※ Fabricを利用しない場合は、設定ファイルのread-write、read-only項目にそれぞれアクセスするホストを書きます。マスター障害発生時は、記載されているホストを左から順に接続、スレーブはラウンドロビンです。

MySQL Fabricについて
http://variable.jp/2014/12/05/mysql-fabric%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/

復習:MySQL Fabricの管理ノードではレプリケーションステータスを管理しています。(上記、過去ログ抜粋)
Fabric2

MySQL Router概要
https://www-jp.mysql.com/products/enterprise/router.html

MySQL Routerドキュメント
https://dev.mysql.com/doc/mysql-router/en/

MySQL Router FAQ
https://dev.mysql.com/doc/mysql-router/en/mysql-router-faq.html

MySQL Routerダウンロード
https://dev.mysql.com/downloads/router/
Oracle Linux, RedHat, CentOS6でも利用出来ますが、必要なソフトを事前にインストールしてコンパイルする必要があります。
ちなみに、検証で利用したところ7系のコンパイル済みTarをそのまま展開して6系のLinux上で動かしたところ動きました。

インストール必要条件
An operating system with a compiler that supports C++11.
Example systems that include this support are Ubuntu 14.04 and later, Oracle Linux 7, and OS X 10.10 and later.
Oracle Linux 6 works as well, but you have to install the Software Collection Library 1.2. For RedHat and CentOS, see Docs and Downloads.
MySQL Client Libraries development packages. For example, on Ubuntu this is the libmysqlclient-dev package.
CMake 2.8.9 or later.

MySQL Routerのインストールがおわったら、MySQL Router設定ファイルを作成して実行してください。
MySQL Routerの設定ファイルはシンプルです。
詳細はこちらのサンプルを参照下さい。

■ Static
https://dev.mysql.com/doc/mysql-router/en/mysql-router-configuration-file-example.html
■ Fabric利用
https://dev.mysql.com/doc/mysql-router/en/mysql-router-use-cases.html
■ Static & Fabric利用
http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/

[MySQL Router起動例]
/home/mysql/mysql-router/bin/mysqlrouter –config=/etc/mysql/mysqlrouter.ini

設定ファイルを指定しないで立ち上げると以下のロケーションからファイルを読み込みます。
Generic Linux (standalone‐layout) : ./mysqlrouter.ini
Default, installing under /usr/local : /usr/local/etc/mysqlrouter.ini
RPM and Debian : /etc/mysqlrouter/mysqlrouter.ini

RouterからFabricに接続してマスター、スレーブの切り替わりなどを以下のようなSQLで確認してみる事が可能です。
ここでは、localhost:7001がマスター、localhost:7002がスレーブになっています。


[admin@Fabric01 mysql-router]$ cat mysql_router_balancing.sh 
#!/bin/sh

echo "[Current Slave Connection]"
/usr/local/mysql/bin/mysql -h 127.0.0.1 -P 7002 -u root --password=root -e "SELECT '現在のSlave',@@hostname,@@port,count(*) FROM test.employees"

echo "[Current Master Server]"
# read -p "Press [Enter] key to resume."

# Port 7001 is defined as read/write master server
/usr/local/mysql/bin/mysql -h 127.0.0.1 -P 7001 -u root --password=root -e "select '現在のMaster',@@hostname,@@port"

### Fail Over Demo ###
# /usr/local/mysql/bin/mysqladmin -h 127.0.0.1 -P63301 -u root -p shutdown

実際に実行してみると以下のようにスレーブへのアクセスが実行する度に、ラウンドロビンされている事が確認出来ます。
date

参照:
MySQL Router

補足:
サポートやコンサルテーティブサポートが必要な場合は、Enterprise Editionでサポートを受ける事も可能。
https://www-jp.mysql.com/products/


10月19日にMySQL5.7がGAになったので、新規追加されたJSONデータ型の確認を行いました。
JSONはXMLと同じように、スマートフォンアプリ用のAPIやB2BやB2C連携でJSON APIが多く使われているようです。

基本的な動作検証
1. 基本的な構文バリデーション機能
2. JSONデータ型とTEXT型のパフォーマンス差
3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
4. JSONドキュメントの部分アップデート

ちなみに、ザックリとGoogleで検索してみると以下のように多くのサイトがJSON用のAPIを提供していました。
Twitter
https://dev.twitter.com/rest/reference/get/statuses/user_timeline
原子力規制委員会
http://radioactivity.nsr.go.jp/data/ja/real/area_24000/2401_trend.json
Google API
https://storage.googleapis.com/maps-devrel/google.json
ぐるナビ
http://api.gnavi.co.jp/api/tools/?apitype=ver1_RestSearchAPI
シカゴ市
https://data.cityofchicago.org/resource/alternative-fuel-locations.json?$limit=100&$offset=50

1. 基本的な構文バリデーション機能

検証テーブル (TEXT型)

CREATE TABLE `employees_txt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

検証テーブル (JSON型)

CREATE TABLE `employees_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

それぞれのテーブルに、構文ミスしたJSONデータを入れてみると以下のようになる。
JSONデータ型はINSERT時のバリデーションが正常に効いている。


root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 2, "name": "Joe"');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> SELECT * FROM NEW57.employees_txt;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
|  2 | {"id": 2, "name": "Joe"   |
+----+---------------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 2, "name": "Joe"');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value (or column) '{"id": 2, "name": "Joe"'.
root@localhost [NEW57]> SELECT * FROM NEW57.employees_json;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
+----+---------------------------+
1 row in set (0.00 sec)

root@localhost [NEW57]> 

2. JSONデータ型とTEXT型のパフォーマンス差
セミナーでも実施しましたが、以下のようなシンプルなテーブルに対して、
ダウンロードしてきた、20万6千件のJSONデータをインサートして参照レスポンス比較。

それぞれの参照クエリにて同じコマンドと同じデータでレスポンス比較
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_txt;
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_json;

結果としては、JSON型の方がTEXT型に比べて圧倒的に早い事が確認出来る。

[root@misc01 SOD2015]# ./json_and_text_without_index.sh 
INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。
SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル

【TEXT型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m9.724s
user    0m0.005s
sys     0m0.002s

【JSON型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m1.506s
user    0m0.004s
sys     0m0.004s
[root@misc01 SOD2015]# 

3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
JSONドキュメントのオブジェクトの中からSTREET名の部分からデータを抽出して列を作成しIndexを付与。

列:      json_extract(feature,’$.properties.STREET’))
インデックス: KEY `feature_street` (`feature_street`)

 

JSONデータ型のサンプルを入れたテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            id: 12250
       feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
  feature_type: "Feature"
feature_street: "MARKET"
Press [Enter] key to resume.

JSONデータ型とGenerated Columnを利用したテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `feature_type` (`feature_type`),
  KEY `feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4
Press [Enter] key to resume.

JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where json_extract(feature,'$.properties.STREET') = '"MARKET"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: feature_street
          key: feature_street
      key_len: 123
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
[root@misc01 SOD2015]# 

4. JSONドキュメントの部分アップデート
JSONドキュメントの一部をUPDATEする場合の処理方法確認。
TEXT型だと、全ての列データを入れ替える必要がありますが、JSONだと一部のみ変更可能です。

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+------------------------------------------------------------------------+---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}| 50000   |
+----+------------------------------------------------------------------------+---------+

update T_JSON_DOC set T_JSON_DOC.body = JSON_REPLACE(body,"$.price",15000) 
where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+----------------------------------------------------------------------- +---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 15000, "Conditions": ["NEW", 2015]}| 15000   |
+----+------------------------------------------------------------------------+---------+

文字入れ替えに便利なJSONファンクション(例)
JSON_SET() 既存の値を置き換え、存在しない値を追加
JSON_INSERT() 既存の値を置き換えずに値を挿入
JSON_REPLACE() 既存の値のみを置き換えます

参照:
11.6 The JSON Data Type
https://dev.mysql.com/doc/refman/5.7/en/json.html

MySQL5.7セミナー資料
https://www-jp.mysql.com/news-and-events/seminar/downloads.html