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


データ量が昔と比較して増えてきたからか、最近はパーティションの話を良く聞くので、
簡易的に再確認してみました。いざ確認してみると、色々と忘れている事もあり頭がリフレッシュ出来ました。
パーティショニン種類に関しては、以下のサイトに詳しく出ているので参照してみて下さい。
また、MySQL5.6のマニュアルも日本語化されたので参考にしてみて下さい。

MySQL5.6日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/

19.2. パーティショニングタイプ
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-types.html

——————————————————————————————–
※奥野さんのサイトにも書かれていますが、必ずしもパーティションを利用して、
 パフォーマンスが良くなる事ばかりでは無いです。効率良くIndexが使えていたりデータによっては、
 付けない方が良い事もあるので、先ずは検証環境で確認してみて下さい。

 パーティショニングの使用例 – カーディナリティが低いカラムを使って検索する場合
 http://nippondanji.blogspot.jp/2009/04/1.html
——————————————————————————————–

第19章 パーティション化
https://dev.mysql.com/doc/refman/5.6/ja/partitioning.html

19.6. パーティショニングの制約と制限
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html#partitioning-limitations-subpartitions

確認:バージョン

root@localhost [test]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.24-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> 

プラグインで有効になっているか確認

root@localhost [test]> 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   |
| MEMORY             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| ARCHIVE            | 3.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| InnoDB             | 5.6     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| partition          | 1.0     | ACTIVE   | < --- こちらがACTIVEであれば利用可能
+--------------------+---------+----------+
11 rows in set (0.01 sec)

root@localhost [test]> 

■ 標準的なRANGEパーティショニング

root@localhost [test]> show create table tbl_partition\G
*************************** 1. row ***************************
       Table: tbl_partition
Create Table: CREATE TABLE `tbl_partition` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

root@localhost [test]> explain partitions select * from tbl_partition where year = 2015;
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table         | partitions | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_partition | p2016      | ref  | idx_tbl_partition | idx_tbl_partition | 2       | const |    2 | NULL  |
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 

■ サブパーティション
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-subpartitions.html

サブパーティションに関する問題
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html#partitioning-limitations-subpartitions

サブパーティションに関する問題 サブパーティションは HASH または KEY パーティショニングを使用する必要があります。サブパーティション化できるのは RANGE および LIST パーティションのみです。HASH および KEY パーティションはサブパーティション化できません。 現在のところ、SUBPARTITION BY KEY にはサブパーティショニングカラムを明示的に指定する必要がありますが、PARTITION BY KEY の場合は省略できます (その場合、テーブルの主キーカラムがデフォルトで使用されます)


root@localhost [test]> show create table tbl_sub_partition01\G
*************************** 1. row ***************************
       Table: tbl_sub_partition01
Create Table: CREATE TABLE `tbl_sub_partition01` (
  `member_id` int(11) DEFAULT NULL,
  `platform` varchar(10) NOT NULL,
  `purchased` date DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
SUBPARTITIONS 12
(PARTITION sub_y2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION sub_y2011 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION sub_y2012 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION sub_y2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION sub_y2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION sub_y2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION sub_y2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION sub_y2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION sub_y2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION sub_y2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION sub_y2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION sub_y_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

timestamp型などを利用すると、
”ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”というエラーになります。

root@localhost [test]> select * from tbl_sub_partition01;
+-----------+----------+------------+---------------------+
| member_id | platform | purchased  | updated_at          |
+-----------+----------+------------+---------------------+
|         1 | iphone   | 2015-06-28 | 2015-06-30 11:53:32 |
|         2 | iphone   | 2015-06-29 | 2015-06-30 11:53:39 |
|         3 | iphone   | 2015-06-30 | 2015-06-30 11:53:47 |
|         4 | iphone   | 2015-07-01 | 2015-06-30 12:08:13 |
|         5 | iphone6  | 2015-07-02 | 2015-06-30 12:08:26 |
|         6 | iphone6  | 2015-07-03 | 2015-06-30 12:10:11 |
|         7 | iphone3s | 2015-07-04 | 2015-06-30 12:10:27 |
|         8 | iphone4s | 2015-07-05 | 2015-06-30 12:10:37 |
|         9 | iphone4s | 2015-07-06 | 2015-06-30 12:10:46 |
|        10 | iphone6s | 2015-07-07 | 2015-06-30 12:10:59 |
+-----------+----------+------------+---------------------+
10 rows in set (0.00 sec)

root@localhost [test]> explain partitions select * from tbl_sub_partition01 where purchased = '2015-07-01';
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | partitions             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_sub_partition01 | sub_y2016_sub_y2016sp5 | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@localhost [test]> 

パーティションにDATA DIRECTORYなどを指定する場合。
MyISAMで且つ、テーブル全体では無くSubpartition毎に指定しています。

root@localhost [test]> show create table tbl_sub_partition_dir\G
*************************** 1. row ***************************
       Table: tbl_sub_partition_dir
Create Table: CREATE TABLE `tbl_sub_partition_dir` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
(PARTITION p0_dir VALUES LESS THAN (1990)
 (SUBPARTITION s0 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s1 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM),
 PARTITION p1_dir VALUES LESS THAN (2000)
 (SUBPARTITION s2 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s3 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM),
 PARTITION p2_dir VALUES LESS THAN MAXVALUE
 (SUBPARTITION s4 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s5 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM)) */
1 row in set (0.01 sec)

root@localhost [test]> system ls /home/mysql/data/
tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD  tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD  tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD  test
tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD  tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD  tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD
root@localhost [test]> system ls /home/mysql/index/
tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI  tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI  tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI
tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI  tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI  tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI
root@localhost [test]> 

sub_myisam

DATAディレクトリーにシンボリックリンクが作成されています。

[root@GA01 test]# pwd
/usr/local/mysql/data/test
[root@GA01 test]# ls -l tbl_sub_partition_dir*
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI
-rw-rw----. 1 mysql mysql 8596  6月 30 13:31 tbl_sub_partition_dir.frm
-rw-rw----. 1 mysql mysql  104  6月 30 13:31 tbl_sub_partition_dir.par
[root@GA01 test]# 

data foler

19.6.4. パーティショニングとロック
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations-locking.html

MySQL 5.6.6 はパーティションロックプルーニングを実装し、これによって多くの場合に不必要なロックが排除されます。
MySQL 5.6.6 以降では、パーティション化された MyISAM テーブルに対して読み取りまたは更新を行うほとんどのステートメントで、影響を受けるパーティションのみがロックされます。たとえば、MySQL 5.6.6 より前は、パーティション化 MyISAM テーブルからのSELECT でテーブル全体がロックされました。MySQL 5.6.6 以降は、SELECT ステートメントの WHERE 条件を満たす行を実際に含むパーティションのみがロックされます。これには、パーティション化された MyISAM テーブルに対する同時操作の速度および効率を向上させる効果があります。この改善は、多く (32 以上) のパーティションを持つ MyISAM テーブルを操作するときに特に顕著になります。

DATA DIRECTORY、INDEX DIRECTORY
http://dev.mysql.com/doc/refman/5.6/ja/create-table.html

InnoDB では、DATA DIRECTORY=’directory’ オプションを使用すると、MySQL データディレクトリ以外の場所に新しいInnoDB file-per-table テーブルスペースを作成できます。MySQL は、指定されたディレクトリ内にデータベース名に対応するサブディレクトリを作成し、さらにその中に新しいテーブルの .ibd ファイルを作成します。InnoDB テーブルで DATA DIRECTORY オプションを使用するには、innodb_file_per_table 構成オプションを有効にする必要があります。このディレクトリは、ディレクトリへの (相対パスではなく) フルパス名である必要があります。
詳細は、「テーブルスペースの位置の指定」を参照してください。
MyISAM テーブルを作成する場合は、DATA DIRECTORY=’directory’ 句、INDEX DIRECTORY=’directory’ 句、またはその両方を使用できます。これらは、それぞれ MyISAM テーブルのデータファイルとインデックスファイルを配置する場所を指定します。
InnoDB テーブルとは異なり、DATA DIRECTORY または INDEX DIRECTORY オプションで MyISAM テーブルを作成する場合、MySQL はデータベース名に対応するサブディレクトリを作成しません。各ファイルは、指定されたディレクトリ内に作成されます。

重要
https://bugs.mysql.com/bug.php?id=32091
テーブルレベルの DATA DIRECTORY および INDEX DIRECTORY オプションは、パーティション化されたテーブルでは無視されます。(Bug #32091)

■InnoDBで、SUBPARTITIONを指定せずに実行した場合


root@localhost [test]> CREATE TABLE `tbl_partition_innodb` (
    ->   `member_id` varchar(40) NOT NULL,
    ->   `platform` varchar(10) NOT NULL,
    ->   `year` smallint(5) unsigned NOT NULL,
    ->   `month` tinyint(2) unsigned NOT NULL,
    ->   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`member_id`,`year`,`month`),
    ->   KEY `idx_tbl_partition` (`year`),
    ->   KEY `idx_tbl_partition_id` (`member_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY = '/home/mysql/data';
Query OK, 0 rows affected (0.02 sec)

root@localhost [test]> show create table tbl_partition_innodb\G
*************************** 1. row ***************************
       Table: tbl_partition_innodb
Create Table: CREATE TABLE `tbl_partition_innodb` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY='/home/mysql/data/'
1 row in set (0.00 sec)

ALTERテーブルでパーティション化すると、DATA DIRECTORYは無効になりファイルは削除されます。

root@localhost [test]> Alter table tbl_partition_innodb PARTITION BY RANGE (`year`)
    -> (PARTITION p2013_innodb VALUES LESS THAN (2013) ENGINE = InnoDB,
    ->  PARTITION p2014_innodb VALUES LESS THAN (2014) ENGINE = InnoDB,
    ->  PARTITION p2015_innodb VALUES LESS THAN (2015) ENGINE = InnoDB,
    ->  PARTITION p2016_innodb VALUES LESS THAN (2016) ENGINE = InnoDB,
    ->  PARTITION p2017_innodb VALUES LESS THAN (2017) ENGINE = InnoDB,
    ->  PARTITION p2018_innodb VALUES LESS THAN (2018) ENGINE = InnoDB,
    ->  PARTITION p2019_innodb VALUES LESS THAN (2019) ENGINE = InnoDB,
    ->  PARTITION p2020_innodb VALUES LESS THAN (2020) ENGINE = InnoDB,
    ->  PARTITION pmax_innodb VALUES LESS THAN MAXVALUE ENGINE = InnoDB) ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table tbl_partition_innodb\G
*************************** 1. row ***************************
       Table: tbl_partition_innodb
Create Table: CREATE TABLE `tbl_partition_innodb` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013_innodb VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014_innodb VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015_innodb VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016_innodb VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017_innodb VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018_innodb VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019_innodb VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020_innodb VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax_innodb VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

※ Alter Tableでパーティショニングすると、
  DATA DIRECTORY=’/home/mysql/data/’に存在していたibdファイルは削除されます。

file_innodb_alter_update

■圧縮テーブルとパーティショニング
パーティション毎にストレージエンジンンが指定出来ますが、圧縮はパーティション毎に指定出来ません。
圧縮する場合はテーブル毎に指定してください。

root@localhost [test]> show create table tbl_partition_sample_zip\G
*************************** 1. row ***************************
       Table: tbl_partition_sample_zip
Create Table: CREATE TABLE `tbl_partition_sample_zip` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

補足メモ:

パーティションでは無いですが、パスを変更してデータを配置する方法。
MYSQL DATA DIRECTORY と INDEX DIRECTORYの指定

5.7からは複数テーブルを配置出来る、GENERAL TABLESPACEが利用可能になります。
InnoDB General Tablespace


root@localhost [test]> CREATE TABLE `tbl_DATA_DIRECTORY` (
    ->   `member_id` varchar(40) NOT NULL,
    ->   `platform` varchar(10) NOT NULL,
    ->   `year` smallint(5) unsigned NOT NULL,
    ->   `month` tinyint(2) unsigned NOT NULL,
    ->   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`member_id`,`year`,`month`),
    ->   KEY `idx_tbl_DATA_DIRECTORY` (`year`),
    ->   KEY `idx_tbl_DATA_DIRECTORY_id` (`member_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY = '/home/mysql/data';
Query OK, 0 rows affected (0.03 sec)

root@localhost [test]> 

■テーブル定義はDefault Dataディレクトリーで,データ用テーブルスペースが指定したディレクトリーに作成されます。


[root@GA01 test]# ls -l /usr/local/mysql/data/test/tbl_DATA*
-rw-rw----. 1 mysql mysql 8712  6月 30 15:05 /usr/local/mysql/data/test/tbl_DATA_DIRECTORY.frm
-rw-rw----. 1 mysql mysql   44  6月 30 15:05 /usr/local/mysql/data/test/tbl_DATA_DIRECTORY.isl
[root@GA01 test]# ls -l /home/mysql/data/test/
合計 192
-rw-rw----. 1 mysql mysql 131072  6月 30 15:05 tbl_DATA_DIRECTORY.ibd
[root@GA01 test]# 

基本ファイル構成