まだ、Lab版ですがMySQL運用上のオプションが増えるので、MySQL Rewrite Pluginを試してみました。
主な、用途としてはApacheのmod_rewriteなどと同じで、アプリケーション側の変更無くrewriteでアクセスがコントロール可能です。
基本的には、mod_rewriteも正規表現等の文字列でコントロールしていたのでこちらも文字列コントロールしているようです。
ソースは見ていないので、検証した結果からの推測です。
短期的なスパンでのアプリケーションの変更が難しいが、即効性のあるソリューションとしてパフォーマンス改善したい場合などには、
DB側でルール作成するだけなので、かなり使えるかもしれません。もちろんGAになってからですが。

Pluginのインストール

root@localhost [mysql]>select @@version;
+------------------------+
| @@version              |
+------------------------+
| 5.7.5-labs-preview-log |
+------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]>select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_DESCRIPTION from information_schema.plugins;
+----------------------------+----------------+---------------+---------------------------------------------------------------------------+
| PLUGIN_NAME                | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_DESCRIPTION                                                        |
+----------------------------+----------------+---------------+---------------------------------------------------------------------------+
| binlog                     | 1.0            | ACTIVE        | This is a pseudo storage engine to represent the binlog in a transaction  |
| mysql_native_password      | 1.0            | ACTIVE        | Native MySQL authentication                                               |
| sha256_password            | 1.0            | ACTIVE        | SHA256 password authentication                                            |
| MyISAM                     | 1.0            | ACTIVE        | MyISAM storage engine                                                     |
| MRG_MYISAM                 | 1.0            | ACTIVE        | Collection of identical MyISAM tables                                     |
| MEMORY                     | 1.0            | ACTIVE        | Hash based, stored in memory, useful for temporary tables                 |
| InnoDB                     | 5.7            | ACTIVE        | Supports transactions, row-level locking, and foreign keys                |
| INNODB_TRX                 | 5.7            | ACTIVE        | InnoDB transactions                                                       |
| INNODB_LOCKS               | 5.7            | ACTIVE        | InnoDB conflicting locks                                                  |
| INNODB_LOCK_WAITS          | 5.7            | ACTIVE        | InnoDB which lock is blocking which                                       |
| INNODB_CMP                 | 5.7            | ACTIVE        | Statistics for the InnoDB compression                                     |
| INNODB_CMP_RESET           | 5.7            | ACTIVE        | Statistics for the InnoDB compression; reset cumulated counts             |
| INNODB_CMPMEM              | 5.7            | ACTIVE        | Statistics for the InnoDB compressed buffer pool                          |
| INNODB_CMPMEM_RESET        | 5.7            | ACTIVE        | Statistics for the InnoDB compressed buffer pool; reset cumulated counts  |
| INNODB_CMP_PER_INDEX       | 5.7            | ACTIVE        | Statistics for the InnoDB compression (per index)                         |
| INNODB_CMP_PER_INDEX_RESET | 5.7            | ACTIVE        | Statistics for the InnoDB compression (per index); reset cumulated counts |
| INNODB_BUFFER_PAGE         | 5.7            | ACTIVE        | InnoDB Buffer Page Information                                            |
| INNODB_BUFFER_PAGE_LRU     | 5.7            | ACTIVE        | InnoDB Buffer Page in LRU                                                 |
| INNODB_BUFFER_POOL_STATS   | 5.7            | ACTIVE        | InnoDB Buffer Pool Statistics Information                                 |
| INNODB_TEMP_TABLE_INFO     | 5.7            | ACTIVE        | InnoDB Temp Table Stats                                                   |
| INNODB_METRICS             | 5.7            | ACTIVE        | InnoDB Metrics Info                                                       |
| INNODB_FT_DEFAULT_STOPWORD | 5.7            | ACTIVE        | Default stopword list for InnDB Full Text Search                          |
| INNODB_FT_DELETED          | 5.7            | ACTIVE        | INNODB AUXILIARY FTS DELETED TABLE                                        |
| INNODB_FT_BEING_DELETED    | 5.7            | ACTIVE        | INNODB AUXILIARY FTS BEING DELETED TABLE                                  |
| INNODB_FT_CONFIG           | 5.7            | ACTIVE        | INNODB AUXILIARY FTS CONFIG TABLE                                         |
| INNODB_FT_INDEX_CACHE      | 5.7            | ACTIVE        | INNODB AUXILIARY FTS INDEX CACHED                                         |
| INNODB_FT_INDEX_TABLE      | 5.7            | ACTIVE        | INNODB AUXILIARY FTS INDEX TABLE                                          |
| INNODB_SYS_TABLES          | 5.7            | ACTIVE        | InnoDB SYS_TABLES                                                         |
| INNODB_SYS_TABLESTATS      | 5.7            | ACTIVE        | InnoDB SYS_TABLESTATS                                                     |
| INNODB_SYS_INDEXES         | 5.7            | ACTIVE        | InnoDB SYS_INDEXES                                                        |
| INNODB_SYS_COLUMNS         | 5.7            | ACTIVE        | InnoDB SYS_COLUMNS                                                        |
| INNODB_SYS_FIELDS          | 5.7            | ACTIVE        | InnoDB SYS_FIELDS                                                         |
| INNODB_SYS_FOREIGN         | 5.7            | ACTIVE        | InnoDB SYS_FOREIGN                                                        |
| INNODB_SYS_FOREIGN_COLS    | 5.7            | ACTIVE        | InnoDB SYS_FOREIGN_COLS                                                   |
| INNODB_SYS_TABLESPACES     | 5.7            | ACTIVE        | InnoDB SYS_TABLESPACES                                                    |
| INNODB_SYS_DATAFILES       | 5.7            | ACTIVE        | InnoDB SYS_DATAFILES                                                      |
| CSV                        | 1.0            | ACTIVE        | CSV storage engine                                                        |
| ARCHIVE                    | 3.0            | ACTIVE        | Archive storage engine                                                    |
| BLACKHOLE                  | 1.0            | ACTIVE        | /dev/null storage engine (anything you write to it disappears)            |
| PERFORMANCE_SCHEMA         | 0.1            | ACTIVE        | Performance Schema                                                        |
| partition                  | 1.0            | ACTIVE        | Partition Storage Engine Helper                                           |
| FEDERATED                  | 1.0            | ACTIVE        | Federated MySQL storage engine                                            |
+----------------------------+----------------+---------------+---------------------------------------------------------------------------+
42 rows in set (0.00 sec)

root@localhost [mysql]>system cat /usr/local/mysql2/lib/plugin/install_rewriter_plugin.sql
CREATE DATABASE IF NOT EXISTS query_rewrite;

CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules (
  pattern VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  pattern_database VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin,
  replacement VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  enabled CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Y',
  message VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_bin
) DEFAULT CHARSET = utf8 ENGINE = INNODB;

GRANT SELECT ON query_rewrite.rewrite_rules TO root@localhost;
GRANT INSERT ON query_rewrite.rewrite_rules TO root@localhost;
GRANT UPDATE ON query_rewrite.rewrite_rules TO root@localhost;

INSTALL PLUGIN rewriter SONAME 'rewriter.so';
CREATE FUNCTION load_rewrite_rules RETURNS STRING
SONAME 'rewriter.so';

DELIMITER //

CREATE PROCEDURE query_rewrite.flush_rewrite_rules()
BEGIN
  COMMIT;
  SELECT load_rewrite_rules() INTO @v;
END //

DELIMITER ;

root@localhost [mysql]>source /usr/local/mysql2/lib/plugin/install_rewriter_plugin.sql
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

root@localhost [mysql]>select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_DESCRIPTION from information_schema.plugins;
+----------------------------+----------------+---------------+---------------------------------------------------------------------------+
| PLUGIN_NAME                | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_DESCRIPTION                                                        |
+----------------------------+----------------+---------------+---------------------------------------------------------------------------+
| binlog                     | 1.0            | ACTIVE        | This is a pseudo storage engine to represent the binlog in a transaction  |
| mysql_native_password      | 1.0            | ACTIVE        | Native MySQL authentication                                               |
| sha256_password            | 1.0            | ACTIVE        | SHA256 password authentication                                            |
| MyISAM                     | 1.0            | ACTIVE        | MyISAM storage engine                                                     |
| MRG_MYISAM                 | 1.0            | ACTIVE        | Collection of identical MyISAM tables                                     |
| MEMORY                     | 1.0            | ACTIVE        | Hash based, stored in memory, useful for temporary tables                 |
| InnoDB                     | 5.7            | ACTIVE        | Supports transactions, row-level locking, and foreign keys                |
| INNODB_TRX                 | 5.7            | ACTIVE        | InnoDB transactions                                                       |
| INNODB_LOCKS               | 5.7            | ACTIVE        | InnoDB conflicting locks                                                  |
| INNODB_LOCK_WAITS          | 5.7            | ACTIVE        | InnoDB which lock is blocking which                                       |
| INNODB_CMP                 | 5.7            | ACTIVE        | Statistics for the InnoDB compression                                     |
| INNODB_CMP_RESET           | 5.7            | ACTIVE        | Statistics for the InnoDB compression; reset cumulated counts             |
| INNODB_CMPMEM              | 5.7            | ACTIVE        | Statistics for the InnoDB compressed buffer pool                          |
| INNODB_CMPMEM_RESET        | 5.7            | ACTIVE        | Statistics for the InnoDB compressed buffer pool; reset cumulated counts  |
| INNODB_CMP_PER_INDEX       | 5.7            | ACTIVE        | Statistics for the InnoDB compression (per index)                         |
| INNODB_CMP_PER_INDEX_RESET | 5.7            | ACTIVE        | Statistics for the InnoDB compression (per index); reset cumulated counts |
| INNODB_BUFFER_PAGE         | 5.7            | ACTIVE        | InnoDB Buffer Page Information                                            |
| INNODB_BUFFER_PAGE_LRU     | 5.7            | ACTIVE        | InnoDB Buffer Page in LRU                                                 |
| INNODB_BUFFER_POOL_STATS   | 5.7            | ACTIVE        | InnoDB Buffer Pool Statistics Information                                 |
| INNODB_TEMP_TABLE_INFO     | 5.7            | ACTIVE        | InnoDB Temp Table Stats                                                   |
| INNODB_METRICS             | 5.7            | ACTIVE        | InnoDB Metrics Info                                                       |
| INNODB_FT_DEFAULT_STOPWORD | 5.7            | ACTIVE        | Default stopword list for InnDB Full Text Search                          |
| INNODB_FT_DELETED          | 5.7            | ACTIVE        | INNODB AUXILIARY FTS DELETED TABLE                                        |
| INNODB_FT_BEING_DELETED    | 5.7            | ACTIVE        | INNODB AUXILIARY FTS BEING DELETED TABLE                                  |
| INNODB_FT_CONFIG           | 5.7            | ACTIVE        | INNODB AUXILIARY FTS CONFIG TABLE                                         |
| INNODB_FT_INDEX_CACHE      | 5.7            | ACTIVE        | INNODB AUXILIARY FTS INDEX CACHED                                         |
| INNODB_FT_INDEX_TABLE      | 5.7            | ACTIVE        | INNODB AUXILIARY FTS INDEX TABLE                                          |
| INNODB_SYS_TABLES          | 5.7            | ACTIVE        | InnoDB SYS_TABLES                                                         |
| INNODB_SYS_TABLESTATS      | 5.7            | ACTIVE        | InnoDB SYS_TABLESTATS                                                     |
| INNODB_SYS_INDEXES         | 5.7            | ACTIVE        | InnoDB SYS_INDEXES                                                        |
| INNODB_SYS_COLUMNS         | 5.7            | ACTIVE        | InnoDB SYS_COLUMNS                                                        |
| INNODB_SYS_FIELDS          | 5.7            | ACTIVE        | InnoDB SYS_FIELDS                                                         |
| INNODB_SYS_FOREIGN         | 5.7            | ACTIVE        | InnoDB SYS_FOREIGN                                                        |
| INNODB_SYS_FOREIGN_COLS    | 5.7            | ACTIVE        | InnoDB SYS_FOREIGN_COLS                                                   |
| INNODB_SYS_TABLESPACES     | 5.7            | ACTIVE        | InnoDB SYS_TABLESPACES                                                    |
| INNODB_SYS_DATAFILES       | 5.7            | ACTIVE        | InnoDB SYS_DATAFILES                                                      |
| CSV                        | 1.0            | ACTIVE        | CSV storage engine                                                        |
| ARCHIVE                    | 3.0            | ACTIVE        | Archive storage engine                                                    |
| BLACKHOLE                  | 1.0            | ACTIVE        | /dev/null storage engine (anything you write to it disappears)            |
| PERFORMANCE_SCHEMA         | 0.1            | ACTIVE        | Performance Schema                                                        |
| partition                  | 1.0            | ACTIVE        | Partition Storage Engine Helper                                           |
| FEDERATED                  | 1.0            | ACTIVE        | Federated MySQL storage engine                                            |
| Rewriter                   | 0.1            | ACTIVE        | A query rewrite plugin that rewrites queries using the parse tree.        |
+----------------------------+----------------+---------------+---------------------------------------------------------------------------+
43 rows in set (0.00 sec)

root@localhost [mysql]>

Installが終わると、データベースとルール管理用のテーブルが用意されてます。

root@localhost [mysql]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| query_rewrite      |
+--------------------+
4 rows in set (0.00 sec)

root@localhost [mysql]>use query_rewrite;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [query_rewrite]>show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules           |
+-------------------------+
1 row in set (0.00 sec)

root@localhost [query_rewrite]>desc rewrite_rules;
+------------------+----------------+------+-----+---------+-------+
| Field            | Type           | Null | Key | Default | Extra |
+------------------+----------------+------+-----+---------+-------+
| pattern          | varchar(10000) | NO   |     | NULL    |       |
| pattern_database | varchar(20)    | YES  |     | NULL    |       |
| replacement      | varchar(10000) | NO   |     | NULL    |       |
| enabled          | char(1)        | NO   |     | Y       |       |
| message          | varchar(1000)  | YES  |     | NULL    |       |
+------------------+----------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

root@localhost [query_rewrite]>select * from rewrite_rules;
Empty set (0.00 sec)

root@localhost [query_rewrite]>show create table rewrite_rules\G
*************************** 1. row ***************************
       Table: rewrite_rules
Create Table: CREATE TABLE `rewrite_rules` (
  `pattern` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `pattern_database` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `replacement` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `enabled` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Y',
  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [query_rewrite]>

以下で、Sakilaのactorテーブルに対してのQueryに特定のIndexを強制的に使わせるようにしてみました。

root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>ALTER TABLE actor ADD INDEX idx_fl(first_name,last_name);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [sakila]>ALTER TABLE actor ADD index idx_actor_first_name (first_name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`),
  KEY `idx_fl` (`first_name`,`last_name`),
  KEY `idx_actor_first_name` (`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>



root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys               | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_fl,idx_actor_first_name | idx_fl | 137     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@localhost [sakila]>

root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules( pattern, replacement ) VALUES ( 'select * from sakila.actor where first_name = ?', 'select * from sakila.actor force index(idx_actor_first_name) where first_name = ?' );
Query OK, 1 row affected (0.00 sec)

root@localhost [sakila]>select * from  query_rewrite.rewrite_rules;  
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+                                                                                                            
| pattern                                         | pattern_database | replacement                                                                       | enabled | message |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
| select * from sakila.actor where first_name = ? | NULL             | select * from sakila.actor force index(idx_actor_first_name) where first_name = ? | Y       | NULL    |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
1 row in set (0.00 sec)

root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.01 sec)

root@localhost [sakila]>select * from  query_rewrite.rewrite_rules;
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
| pattern                                         | pattern_database | replacement                                                                       | enabled | message |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
| select * from sakila.actor where first_name = ? | NULL             | select * from sakila.actor force index(idx_actor_first_name) where first_name = ? | Y       | NULL    |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
1 row in set (0.00 sec)

root@localhost [sakila]>select * from actor where first_name = 'JOHNNY';
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|       40 | JOHNNY     | CAGE         | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
2 rows in set (0.07 sec)

root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys               | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_fl,idx_actor_first_name | idx_fl | 137     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@localhost [sakila]>
root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules( pattern,pattern_database,replacement ) VALUES ( 'select * from actor where first_name = ?','sakila','select * from actor force index(idx_actor_first_name) where first_name = ?');
Query OK, 1 row affected (0.00 sec)

root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.02 sec)

root@localhost [sakila]>select * from query_rewrite.rewrite_rules;
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+
| pattern                                  | pattern_database | replacement                                                                | enabled | message |
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+
| select * from actor where first_name = ? | sakila           | select * from actor force index(idx_actor_first_name) where first_name = ? | Y       | NULL    |
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+
1 row in set (0.00 sec)

root@localhost [sakila]>


root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';                                                               
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys               | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_fl,idx_actor_first_name | idx_fl | 137     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@localhost [sakila]>

explain-no


上記のように、思ったようにインデックスが使われているかどうか確認してみましたが、Explainを付けると別のQueryと判断されてしまっているようで確認出来ませんでした。
念の為に、Explainを含むQueryを登録して再確認してみました。

root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules(pattern,pattern_database,replacement ) VALUES ('explain select * from actor where first_name = ?','sakila','explain select * from actor force index(idx_actor_first_name) where first_name = ?');
Query OK, 1 row affected (0.00 sec)

root@localhost [sakila]>select * from query_rewrite.rewrite_rules;
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| pattern                                          | pattern_database | replacement                                                                        | enabled | message |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| select * from actor where first_name = ?         | sakila           | select * from actor force index(idx_actor_first_name) where first_name = ?         | Y       | NULL    |
| explain select * from actor where first_name = ? | sakila           | explain select * from actor force index(idx_actor_first_name) where first_name = ? | Y       | NULL    |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
2 rows in set (0.01 sec)

root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.02 sec)

root@localhost [sakila]>select * from query_rewrite.rewrite_rules;                                                                                                               +--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| pattern                                          | pattern_database | replacement                                                                        | enabled | message |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| select * from actor where first_name = ?         | sakila           | select * from actor force index(idx_actor_first_name) where first_name = ?         | Y       | NULL    |
| explain select * from actor where first_name = ? | sakila           | explain select * from actor force index(idx_actor_first_name) where first_name = ? | Y       | NULL    |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
2 rows in set (0.00 sec)

root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_first_name | idx_actor_first_name | 137     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

root@localhost [sakila]>


結論としては、問題無くRewriteは動作しているようです。Explainを含むQueryも適切に書き換えられていて、指定したIndexが使われていました。

Query変更せずに、強制的にIndexなどをコントロールしたい場合は良いですね。
explain-yes
Explain抜きの場合は、Workbenchの実行プランViewで確認出来るかどうか次回確認してみます。

上記確認してみました:GENERAL LOGを確認してみると、きちんとRewriteが有効になっている事が確認出来ました。
final

以下エラーログ (DB指定をQueryで行うか、pattern_databaseにて指定する必要がある。)

root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules( pattern, replacement ) VALUES ( 'select * from actor where first_name = ?', 'select * from actor force index(idx_actor_first_name) where first_name = ?' );
Query OK, 1 row affected (0.00 sec)

root@localhost [sakila]>select * from  query_rewrite.rewrite_rules;
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+
| pattern                                  | pattern_database | replacement                                                                | enabled | message |
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+
| select * from actor where first_name = ? | NULL             | select * from actor force index(idx_actor_first_name) where first_name = ? | Y       | NULL    |
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+
1 row in set (0.00 sec)

root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.27 sec)

root@localhost [sakila]>select * from  query_rewrite.rewrite_rules;
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+--------------------------------------------------+
| pattern                                  | pattern_database | replacement                                                                | enabled | message                                          |
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+--------------------------------------------------+
| select * from actor where first_name = ? | NULL             | select * from actor force index(idx_actor_first_name) where first_name = ? | N       | Parse error in pattern: >>No database selected<< |
+------------------------------------------+------------------+----------------------------------------------------------------------------+---------+--------------------------------------------------+
1 row in set (0.00 sec)

root@localhost &#91;sakila&#93;>truncate table query_rewrite.rewrite_rules;
Query OK, 0 rows affected (0.37 sec)

root@localhost [sakila]>

The Query Rewrite Plugins


【再確認】GTIDを利用したMySQL Replicationにおけるスレーブの追加

新規Slaveのoption fileに以下の設定を追加

# Binary logging and Replication
gtid-mode                      = on
enforce-gtid-consistency       = on
server_id                      = 3
log_bin                        = mysql-bin
log-slave-updates

# master_info_repository        = TABLE
# relay_log_info_repository     = TABLE

#disable-gtid-unsafe-statements
#disable-gtid-unsafe-statementsは,GTIDと互換性のない一部のSQLの実行を無効にする。

GTIDを利用したSlave設定がONになっている事を確認@CentOS03

root@localhost [test]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS03   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]> show tables;
Empty set (0.00 sec)

root@localhost [test]> 

root@localhost [test]> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| enforce_gtid_consistency        | ON        |
| gtid_executed                   |           |
| gtid_mode                       | ON        |
| gtid_next                       | AUTOMATIC |
| gtid_owned                      |           |
| gtid_purged                     |           |
| simplified_binlog_gtid_recovery | OFF       |
+---------------------------------+-----------+
7 rows in set (0.00 sec)

Master側にてデータを取得してデータ作成(スレーブでもOK、また特定DBのみでも問題なければOK)

----------------
[admin@CentOS01 ~]$ /usr/local/mysql/bin/mysqldump -uroot -p --all-databases --single-transaction --triggers --routines --events > GTID_fulldump.sql
Enter password: 
[admin@CentOS01 ~]$ scp  GTID_fulldump.sql admin@192.168.56.112:/home/admin/
admin@192.168.56.112's password: 
GTID_fulldump.sql                                                                                                            100% 4456KB   4.4MB/s   00:00    
[admin@CentOS01 ~]$ 

SlaveにてデータのリストアとReplicationの開始

[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql 
Enter password: 
[admin@CentOS03 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

root@localhost [mysql]> change master to
    -> master_host = '192.168.56.101',
    -> master_port=3306,
    -> master_user='GTID_USER',
    -> master_password='password',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.18 sec)

root@localhost [mysql]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.56.101
                  Master_User: GTID_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: CentOS03-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 151
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204
                Auto_Position: 1
1 row in set (0.00 sec)

root@localhost [mysql]> start slave;
Query OK, 0 rows affected (0.04 sec)

root@localhost [mysql]> SELECT @@global.gtid_executed;
+--------------------------------------------+
| @@global.gtid_executed                     |
+--------------------------------------------+
| 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
+--------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

Initial

Masterでの変更が反映される確認(Table作成)

root@localhost [test]>select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS01   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]>CREATE TABLE `Repli_Demo` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `event` varchar(256) NOT NULL DEFAULT '',
    ->   `description` varchar(256) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.10 sec)

root@localhost [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| Customer_Data  |
| MyISAM_InnoDB  |
| Personal_Info  |
| Personal_Info0 |
| Personal_Info1 |
| Personal_Info2 |
| Repli_Demo     |
| employee_table |
| ex_timestamp   |
| performance    |
+----------------+
10 rows in set (0.00 sec)

root@localhost [test]>

既存Slave

root@localhost [test]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS02   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| BR_TEST        |
| Customer_Data  |
| MyISAM_InnoDB  |
| Personal_Info  |
| Personal_Info0 |
| Personal_Info1 |
| Personal_Info2 |
| Repli_Demo     |
| employee_table |
| ex_timestamp   |
| performance    |
+----------------+
11 rows in set (0.00 sec)

root@localhost [test]> 

新規追加Slave

root@localhost [test]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS03   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| Customer_Data  |
| MyISAM_InnoDB  |
| Personal_Info  |
| Personal_Info0 |
| Personal_Info1 |
| Personal_Info2 |
| Repli_Demo     |
| employee_table |
| ex_timestamp   |
| performance    |
+----------------+
10 rows in set (0.00 sec)

root@localhost [test]> 

マスターにてデータ変更

root@localhost [test]>desc Repli_Demo;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(20)      | NO   | PRI | NULL    | auto_increment |
| event       | varchar(256) | NO   |     |         |                |
| description | varchar(256) | NO   |     |         |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

root@localhost [test]>insert into Repli_Demo(event,description) values('Seminor 20150116','Repli Seminor for beginner');
Query OK, 1 row affected (0.16 sec)

root@localhost [test]>show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000256 |     1536 |              |                  | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]>

既存Slave

root@localhost [test]> select * from Repli_Demo;
+----+------------------+----------------------------+
| id | event            | description                |
+----+------------------+----------------------------+
|  1 | Seminor 20150116 | Repli Seminor for beginner |
+----+------------------+----------------------------+
1 row in set (0.00 sec)

root@localhost [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: GTID_SSL_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000256
          Read_Master_Log_Pos: 1536
               Relay_Log_File: CentOS02-relay-bin.000111
                Relay_Log_Pos: 1746
        Relay_Master_Log_File: mysql-bin.000256
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1536
              Relay_Log_Space: 2350
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /usr/local/mysql/ssl/sql-ssl-cert.pem
           Master_SSL_CA_Path: /usr/local/mysql/ssl/
              Master_SSL_Cert: /usr/local/mysql/ssl/sql-gtid-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /usr/local/mysql/ssl/sql-ssl-repl-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:122-209
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209,
cf80b01f-364f-11e4-aa59-0800270e2d1e:1-11
                Auto_Position: 1
1 row in set (0.00 sec)

root@localhost [test]> 

新規追加Slave

root@localhost [test]> select * from Repli_Demo;
+----+------------------+----------------------------+
| id | event            | description                |
+----+------------------+----------------------------+
|  1 | Seminor 20150116 | Repli Seminor for beginner |
+----+------------------+----------------------------+
1 row in set (0.00 sec)

root@localhost [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: GTID_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000256
          Read_Master_Log_Pos: 1536
               Relay_Log_File: CentOS03-relay-bin.000002
                Relay_Log_Pos: 1706
        Relay_Master_Log_File: mysql-bin.000256
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1536
              Relay_Log_Space: 1913
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:205-209
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209
                Auto_Position: 1
1 row in set (0.00 sec)

root@localhost [test]> 

データ追加
final
GTIDの確認
final-2
Binary Logの確認
GTID
Binary Log確認時のGTID確認
GTID-Confirm

ここら辺は、Enterprise版のMySQL Enterprise Monitorでレプリケーションを統合監視しても良いかもしれません。

その他:メモ
—————————————————————————————————-
以前のReplicationの設定が残っていてエラーになる場合の対応  ERROR 1840 (HY000)
—————————————————————————————————-


[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql Enter password: ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. [admin@CentOS03 ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]> SELECT @@global.gtid_executed;
+——————————————–+
| @@global.gtid_executed |
+——————————————–+
| 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
+——————————————–+
1 row in set (0.00 sec)

root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%GTID%’;
+———————————+——————————————–+
| Variable_name | Value |
+———————————+——————————————–+
| enforce_gtid_consistency | ON |
| gtid_executed | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-203 |
| simplified_binlog_gtid_recovery | OFF |
+———————————+——————————————–+
6 rows in set (0.00 sec)

root@localhost [(none)]> RESET MASTER;
Query OK, 0 rows affected (0.03 sec)

root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%GTID%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+———————————+——-+
6 rows in set (0.00 sec)

root@localhost [(none)]> exit
Bye
[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql Enter password: [/SQL] GTIDを利用するにあたり、読んでおいた方が良さそうなページ http://nippondanji.blogspot.jp/2014/12/mysqlgtid.html
http://yakst.com/ja/posts/896
http://www.percona.com/live/mysql-conference-2014/sites/default/files/slides/FacebookGTIDPerconaLive2014.pdf


MySQL Replicationは読み込みをスケールアウトする為の方法として多くのサイトで使われてきましたが、
マスターに関してもMySQL Fabricでスケールアウト出来るようになったので動作確認してみました。

特定のデータを分散する為に、Sharding Groupの作成
Shard 1

mysqlfabric sharding create_definition RANGE global
# mysqlfabric sharding list_definitions

mysqlfabric group create shard1
mysqlfabric group add shard1 127.0.0.1:63304
mysqlfabric group add shard1 127.0.0.1:63305
mysqlfabric group add shard1 127.0.0.1:63306
mysqlfabric group promote shard1
mysqlfabric group activate shard1

Shard2

mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group add shard2 127.0.0.1:63308
mysqlfabric group add shard2 127.0.0.1:63309
mysqlfabric group promote shard2
mysqlfabric group activate shard2

Shardingの定義を追加
test.T_shardingテーブルのid列で100番を境にマスターを分ける場合

mysqlfabric sharding add_table 1 test.T_sharding id
mysqlfabric sharding add_shard 1 shard1/1,shard2/100 --state=enabled

shardingの状況確認

-bash-4.2$ mysql -uroot -p -h127.0.0.1 -P63300 -e 'select * from fabric.shards'
+----------+----------+---------+
| shard_id | group_id | state   |
+----------+----------+---------+
|        1 | shard1   | ENABLED |
|        2 | shard2   | ENABLED |
+----------+----------+---------+
-bash-4.2$ 

-bash-4.2$ mysqlfabric sharding list_tables RANGE
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name      table_name global_group column_name
---------- --------- --------------- ------------ -----------
         1     RANGE test.T_sharding       global          id


-bash-4.2$ 

fabricグループ確認

-bash-4.2$ mysqlfabric group lookup_servers shard1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4aca246d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63304   PRIMARY READ_WRITE    1.0
4b631544-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4bff6f2a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric group lookup_servers shard2
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4cb7ea8d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63307   PRIMARY READ_WRITE    1.0
4d80f9e7-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
57f4b83a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

test.T_shardingをベースとしたsharding情報を確認

-bash-4.2$ mysqlfabric dump sharding_information
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

schema_name table_name column_name lower_bound shard_id type_name group_id global_group
----------- ---------- ----------- ----------- -------- --------- -------- ------------
       test T_sharding          id           1        1     RANGE   shard1       global
       test T_sharding          id         100        2     RANGE   shard2       global


-bash-4.2$ 

-bash-4.2$ mysqlfabric sharding lookup_servers test.T_sharding 99
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4aca246d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63304   PRIMARY READ_WRITE    1.0
4b631544-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4bff6f2a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric sharding lookup_servers test.T_sharding 100
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
4cb7ea8d-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63307   PRIMARY READ_WRITE    1.0
4d80f9e7-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
57f4b83a-8d0a-11e4-9fe4-080027d65c57 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

実際に分散されたデータを確認してみる

-bash-4.2$ mysql -uroot -p -P63304 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63305 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63306 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
95      John:95 Doe
96      John:96 Doe
97      John:97 Doe
98      John:98 Doe
99      John:99 Doe
-bash-4.2$ mysql -uroot -p -P63307 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ mysql -uroot -p -P63308 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ mysql -uroot -p -P63309 -h127.0.0.1 -se "select * from test.T_sharding" | tail -n 5
Enter password: 
300     John:300        Doe
301     John:301        Doe
302     John:302        Doe
303     John:303        Doe
304     John:304        Doe
-bash-4.2$ 

マスターデータの分散がされていて、書き込みも分散出来る事が確認出来た。
これまでのMySQLレプリケーションは読み込みが中心のスケールアウト手法でしたが、
書き込みもFabricでスケールアウトするという選択肢が増えた。

connector


mysqlfabricにてslaveを追加した場合に、新しいSlaveのバックグラウンド処理の確認。

Slave追加前

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0

スレーブ追加
mysqlfabric group add global 127.0.0.1:63304

スレーブ追加後

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
8e43506d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0

新しいSlaveではリストア同期が行われ、アプリアカウント、テーブル、データのリストア同期が行われている。

-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 | egrep -v ^# | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
BINLOG '
jledVA8EAAAAdAAAAHgAAAABAAQANS42LjIyLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQbR
bZs=
'/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:1'/*!*/;
SET TIMESTAMP=1419597327/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET PASSWORD FOR 'root'@'localhost'='*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:2'/*!*/;
SET TIMESTAMP=1419597331/*!*/;
GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:3'/*!*/;
use `test`/*!*/;
SET TIMESTAMP=1419597503/*!*/;
DROP TABLE IF EXISTS `employees` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:4'/*!*/;
SET TIMESTAMP=1419597503/*!*/;
CREATE TABLE employees (   emp_no INT,    first_name CHAR(40),    last_name CHAR(40))
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:5'/*!*/;
SET TIMESTAMP=1419597505/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597505/*!*/;
INSERT INTO employees VALUES (0, 'John:0', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:6'/*!*/;
SET TIMESTAMP=1419597506/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597506/*!*/;
INSERT INTO employees VALUES (1, 'John:1', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:7'/*!*/;
--続ける--
-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 |  egrep -i GTID | tail -n 10
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:417'/*!*/;
#141226 21:42:16 server id 1  end_log_pos 118585 CRC32 0x55ca8db9       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:418'/*!*/;
#141226 21:42:17 server id 1  end_log_pos 118870 CRC32 0x19ecc662       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:419'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119155 CRC32 0x63c53321       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:420'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119440 CRC32 0xee427af3       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:421'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
-bash-4.2$ 

スレーブ追加時のデータ同期については、GTIDモードのレプリケーションに依存するので此方を参照下さい。
http://variable.jp/2015/02/26/mysqlfabric-group-add%E5%AE%9F%E8%A1%8C%E6%99%82%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E5%90%8C%E6%9C%9F/


MySQL WorkbenchからMySQL5.7に対して、SYSスキーマを作成した場合。
5.6の場合よりもViewの数が多い事が確認出来る。

英語ですが、こちらのページに各Viewの詳細が説明されております。
https://github.com/MarkLeith/mysql-sys

Workbenchから作成する場合とScriptから作成した場合の差分は以下の4つのViewの様です。
innodb_lock_waits
memory_by_thread_by_current_bytes
memory_by_user_by_current_bytes
sys_config

root@localhost [sys]>select * from sys.innodb_lock_waits;
Empty set (0.00 sec)

root@localhost [sys]>select * from sys.memory_by_thread_by_current_bytes;
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user                            | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|         7 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         8 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         9 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        10 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        11 | innodb/io_ibuf_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        12 | innodb/io_log_thread            |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        13 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        15 | innodb/srv_error_monitor_thread |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        16 | innodb/srv_monitor_thread       |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        17 | innodb/srv_master_thread        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        18 | innodb/srv_lock_timeout_thread  |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        19 | innodb/srv_purge_thread         |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        20 | innodb/buf_dump_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        21 | innodb/dict_stats_thread        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        22 | sql/signal_handler              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        23 | root@localhost                  |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         1 | sql/main                        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         2 | sql/thread_timer_notifier       |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         3 | innodb/page_cleaner_thread      |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         4 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         5 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         6 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
22 rows in set (0.07 sec)

root@localhost [sys]>select * from sys.memory_by_user_by_current_bytes;
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user       | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| root       |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
| background |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
2 rows in set (0.00 sec)

root@localhost [sys]>select * from sys.sys_config;
+------------------------+-------+---------------------+----------------+
| variable               | value | set_time            | set_by         |
+------------------------+-------+---------------------+----------------+
| statement_truncate_len | 64    | 2014-12-23 08:50:02 | root@localhost |
+------------------------+-------+---------------------+----------------+
1 row in set (0.00 sec)

root@localhost [sys]>

sys.innodb_lock_waitsに関しては、実際にLockを発生させてみると現在Lockになっている状況が確認出来ました。

lock-wait

root@localhost [sys]>select * from sys.innodb_lock_waits;
+----------------+----------------+------------------------------------------------------+-----------------+-------------------+-------------------+--------------------+--------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+
| waiting_trx_id | waiting_thread | waiting_query                                        | waiting_lock_id | waiting_lock_mode | waiting_lock_type | waiting_lock_table | waiting_lock_index | blocking_trx_id | blocking_thread | blocking_query | blocking_lock_id | blocking_lock_mode | blocking_lock_type | blocking_lock_table | blocking_lock_index |
+----------------+----------------+------------------------------------------------------+-----------------+-------------------+-------------------+--------------------+--------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+
| 42525          |              3 | update store set address_id = 101 where store_id = 2 | 42525:109:3:3   | X                 | RECORD            | `sakila`.`store`   | PRIMARY            | 42524           |               2 | NULL           | 42524:109:3:3    | X                  | RECORD             | `sakila`.`store`    | PRIMARY             |
+----------------+----------------+------------------------------------------------------+-----------------+-------------------+-------------------+--------------------+--------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

root@localhost [sys]>

WorkBenchから作成した場合


root@localhost [sys]>select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.7.5-labs-http-log |
+---------------------+
1 row in set (0.00 sec)

root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+-------------+-------+
| db  | object_type | count |
+-----+-------------+-------+
| sys | PROCEDURE   |    22 |
| sys | FUNCTION    |    11 |
| sys | VIEW        |    84 |
+-----+-------------+-------+
3 rows in set (0.08 sec)

root@localhost [sys]>show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_allocated            |
| memory_global_total                           |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_allocated          |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_index_statistics                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
84 rows in set (0.01 sec)

root@localhost [sys]>

スクリプトをダウンロードして作成した場合

[admin@Labs01 mysql-sys-master]$ mysql -u root -p < sys_57.sql 
Enter password: 
[admin@Labs01 mysql-sys-master]$ 

root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | FUNCTION      |    11 |
| sys | PROCEDURE     |    22 |
| sys | VIEW          |    91 |
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
| sys | TRIGGER       |     2 |
+-----+---------------+-------+
6 rows in set (0.07 sec)

root@localhost [sys]>

root@localhost [sys]>show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_host_by_current_bytes               |
| memory_by_thread_by_current_bytes             |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_allocated            |
| memory_global_total                           |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| sys_config                                    |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$innodb_lock_waits                           |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_host_by_current_bytes             |
| x$memory_by_thread_by_current_bytes           |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_allocated          |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_index_statistics                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
92 rows in set (0.00 sec)

root@localhost [sys]>

MySQLのSYS Schemaから色々な情報を確認する事が出来ます。

それぞれのObjectの数

root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+-------------+-------+
| db  | object_type | count |
+-----+-------------+-------+
| sys | FUNCTION    |    11 |
| sys | VIEW        |    78 |
| sys | PROCEDURE   |    22 |
+-----+-------------+-------+
3 rows in set, 1 warning (0.06 sec)

Latencyのhistogram確認用のSP

root@localhost [sys]>CALL sys.ps_statement_avg_latency_histogram()\G
*************************** 1. row ***************************
Performance Schema Statement Digest Average Latency Histogram: 

  . = 1 unit
  * = 2 units
  # = 3 units

(0 - 82ms)       103 | ##################################
(82 - 164ms)     2   | ..
(164 - 245ms)    1   | .
(245 - 327ms)    0   | 
(327 - 409ms)    0   | 
(409 - 491ms)    0   | 
(491 - 572ms)    0   | 
(572 - 654ms)    0   | 
(654 - 736ms)    0   | 
(736 - 818ms)    0   | 
(818 - 899ms)    0   | 
(899 - 981ms)    0   | 
(981 - 1063ms)   0   | 
(1063 - 1145ms)  0   | 
(1145 - 1226ms)  0   | 
(1226 - 1308ms)  0   | 

  Total Statements: 107; Buckets: 16; Bucket Size: 82 ms;

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

root@localhost [sys]>


こちらのViewは、Workbenchから作成したものです。


root@localhost [sys]>select * from host_summary;
+----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| host           | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_hosts |
+----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| 192.168.56.108 |         17 | 13.79 ms          | 810.91 us             |           3 |       55 | 757.59 us       |                   0 |                 1 |            1 |
| CentOS02       |          9 | 4.44 ms           | 493.35 us             |           2 |     3482 | 1.62 s          |                   1 |                 1 |            1 |
| localhost      |        296 | 7.60 s            | 25.66 ms              |          28 |     9338 | 5.28 s          |                   1 |                 3 |            1 |
+----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
3 rows in set (0.02 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_file_io;  
+----------------+------+------------+
| host           | ios  | io_latency |
+----------------+------+------------+
| NULL           | 2168 | 9.36 s     |
| localhost      | 9344 | 5.28 s     |
| CentOS02       | 3482 | 1.62 s     |
| 192.168.56.108 |   55 | 757.59 us  |
+----------------+------+------------+
4 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [sys]>select * from host_summary_by_file_io_type;
+----------------+--------------------------------------+-------+-----------+-------------+
| host           | event_name                           | total | latency   | max_latency |
+----------------+--------------------------------------+-------+-----------+-------------+
| NULL           | wait/io/file/innodb/innodb_data_file |   623 | 3.07 s    | 362.56 ms   |
| NULL           | wait/io/file/sql/binlog              |   355 | 1.32 s    | 208.15 ms   |
| NULL           | wait/io/file/innodb/innodb_log_file  |    22 | 1.14 s    | 394.59 ms   |
| NULL           | wait/io/file/sql/FRM                 |   910 | 964.35 ms | 134.84 ms   |
| NULL           | wait/io/file/sql/binlog_index        |    21 | 810.08 ms | 411.41 ms   |
| NULL           | wait/io/file/sql/ERRMSG              |     5 | 771.84 ms | 711.30 ms   |
| NULL           | wait/io/file/sql/slow_log            |     4 | 428.92 ms | 428.90 ms   |
| NULL           | wait/io/file/myisam/kfile            |    67 | 286.50 ms | 175.43 ms   |
| NULL           | wait/io/file/sql/query_log           |     4 | 230.20 ms | 230.18 ms   |
| NULL           | wait/io/file/mysys/cnf               |     5 | 138.75 ms | 138.68 ms   |
| NULL           | wait/io/file/sql/relaylog            |    75 | 75.81 ms  | 75.44 ms    |
| NULL           | wait/io/file/mysys/charset           |     3 | 69.91 ms  | 69.73 ms    |
| NULL           | wait/io/file/myisam/dfile            |    44 | 38.47 ms  | 35.59 ms    |
| NULL           | wait/io/file/sql/relaylog_index      |     9 | 13.24 ms  | 13.18 ms    |
| NULL           | wait/io/file/sql/casetest            |    10 | 1.01 ms   | 601.72 us   |
| NULL           | wait/io/file/sql/pid                 |     3 | 109.28 us | 92.89 us    |
| NULL           | wait/io/file/csv/data                |     6 | 29.28 us  | 13.07 us    |
| NULL           | wait/io/file/sql/global_ddl_log      |     2 | 11.91 us  | 10.12 us    |
| 192.168.56.108 | wait/io/file/csv/data                |    54 | 747.85 us | 38.46 us    |
| 192.168.56.108 | wait/io/file/sql/dbopt               |     1 | 9.75 us   | 9.75 us     |
| CentOS02       | wait/io/file/csv/data                |  3405 | 825.63 ms | 282.11 ms   |
| CentOS02       | wait/io/file/csv/metadata            |    21 | 774.72 ms | 367.48 ms   |
| CentOS02       | wait/io/file/sql/FRM                 |    39 | 17.67 ms  | 17.53 ms    |
| CentOS02       | wait/io/file/sql/binlog              |    17 | 84.64 us  | 23.62 us    |
| localhost      | wait/io/file/sql/FRM                 |   963 | 2.35 s    | 288.10 ms   |
| localhost      | wait/io/file/myisam/kfile            |  4497 | 1.27 s    | 325.61 ms   |
| localhost      | wait/io/file/innodb/innodb_data_file |   115 | 590.69 ms | 210.05 ms   |
| localhost      | wait/io/file/csv/metadata            |    49 | 417.85 ms | 234.59 ms   |
| localhost      | wait/io/file/myisam/dfile            |  2271 | 269.27 ms | 65.60 ms    |
| localhost      | wait/io/file/sql/file_parser         |   202 | 220.82 ms | 98.15 ms    |
| localhost      | wait/io/file/sql/partition           |    20 | 80.27 ms  | 79.51 ms    |
| localhost      | wait/io/file/csv/data                |  1200 | 74.32 ms  | 39.83 ms    |
| localhost      | wait/io/file/innodb/innodb_log_file  |     2 | 14.11 ms  | 14.08 ms    |
| localhost      | wait/io/file/sql/dbopt               |    28 | 1.41 ms   | 892.22 us   |
| localhost      | wait/io/file/sql/binlog              |     2 | 44.76 us  | 23.05 us    |
| localhost      | wait/io/file/archive/data            |     1 | 11.00 us  | 11.00 us    |
+----------------+--------------------------------------+-------+-----------+-------------+
36 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_stages;
Empty set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_statement_latency;
+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host           | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost      |   300 | 7.63 s        | 5.41 s      | 978.76 ms    |       535 |         29778 |             1 |         32 |
| 192.168.56.108 |    17 | 13.79 ms      | 7.03 ms     | 1.77 ms      |        29 |            26 |             0 |          3 |
| CentOS02       |     9 | 4.44 ms       | 2.65 ms     | 698.00 us    |         5 |             2 |             0 |          2 |
+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_statement_type;
+----------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host           | statement         | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+----------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| 192.168.56.108 | show_tables       |     2 | 6.41 ms       | 3.24 ms     | 634.00 us    |        18 |            18 |             0 |          2 |
| 192.168.56.108 | Field List        |     9 | 3.46 ms       | 568.37 us   | 369.00 us    |         0 |             0 |             0 |          0 |
| 192.168.56.108 | show_databases    |     1 | 1.83 ms       | 1.83 ms     | 326.00 us    |         8 |             8 |             0 |          1 |
| 192.168.56.108 | select            |     3 | 1.22 ms       | 524.43 us   | 303.00 us    |         3 |             0 |             0 |          0 |
| 192.168.56.108 | Init DB           |     1 | 682.28 us     | 682.28 us   | 89.00 us     |         0 |             0 |             0 |          0 |
| 192.168.56.108 | Quit              |     1 | 190.70 us     | 190.70 us   | 50.00 us     |         0 |             0 |             0 |          0 |
| CentOS02       | show_variables    |     2 | 2.48 ms       | 1.78 ms     | 331.00 us    |         2 |             2 |             0 |          2 |
| CentOS02       | set_option        |     3 | 925.25 us     | 325.51 us   | 162.00 us    |         0 |             0 |             0 |          0 |
| CentOS02       | select            |     3 | 856.66 us     | 370.57 us   | 205.00 us    |         3 |             0 |             0 |          0 |
| CentOS02       | Register Slave    |     1 | 176.49 us     | 176.49 us   | 0 ps         |         0 |             0 |             0 |          0 |
| localhost      | select            |    33 | 5.47 s        | 4.68 s      | 398.01 ms    |       162 |         30071 |             0 |         21 |
| localhost      | Field List        |   244 | 2.04 s        | 614.23 ms   | 561.31 ms    |         0 |             0 |             0 |          0 |
| localhost      | show_databases    |     4 | 48.88 ms      | 45.59 ms    | 1.04 ms      |        32 |            32 |             0 |          4 |
| localhost      | call_procedure    |     2 | 38.89 ms      | 23.06 ms    | 214.00 us    |         0 |             0 |             0 |          0 |
| localhost      | Init DB           |     4 | 19.30 ms      | 19.02 ms    | 19.02 ms     |         0 |             0 |             0 |          0 |
| localhost      | insert            |     1 | 14.91 ms      | 14.91 ms    | 426.00 us    |         0 |             0 |             1 |          0 |
| localhost      | show_tables       |     7 | 5.63 ms       | 1.62 ms     | 2.45 ms      |       342 |           342 |             0 |          7 |
| localhost      | Quit              |     2 | 1.68 ms       | 1.56 ms     | 103.00 us    |         0 |             0 |             0 |          0 |
| localhost      | drop_table        |     1 | 1.60 ms       | 1.60 ms     | 97.00 us     |         0 |             0 |             0 |          0 |
| localhost      | show_fields       |     1 | 981.28 us     | 981.28 us   | 633.00 us    |         2 |             2 |             0 |          1 |
| localhost      | show_create_table |     1 | 180.04 us     | 180.04 us   | 47.00 us     |         0 |             0 |             0 |          0 |
| localhost      | error             |     1 | 92.56 us      | 92.56 us    | 0 ps         |         0 |             0 |             0 |          0 |
+----------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
22 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| sakila        | 320.00 KiB | 58.39 KiB |    20 |           20 |        20 |        1330 |
| InnoDB System | 176.00 KiB | 35.59 KiB |    11 |           11 |        11 |         101 |
| mysql         | 112.00 KiB | 21.92 KiB |     7 |            7 |         7 |         238 |
| test          | 80.00 KiB  | 2.26 KiB  |     5 |            5 |         5 |          34 |
| audit_test    | 16.00 KiB  | 7.07 KiB  |     1 |            1 |         1 |          57 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
5 rows in set (0.09 sec)

root@localhost [sys]>

root@localhost [sys]>select * from innodb_buffer_stats_by_table;
+---------------+----------------------+-----------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name          | allocated | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+----------------------+-----------+------------+-------+--------------+-----------+-------------+
| InnoDB System | SYS_FOREIGN          | 48.00 KiB | 3.73 KiB   |     3 |            3 |         3 |          22 |
| mysql         | innodb_index_stats   | 48.00 KiB | 19.98 KiB  |     3 |            3 |         3 |         207 |
| InnoDB System | SYS_TABLES           | 32.00 KiB | 4.38 KiB   |     2 |            2 |         2 |          40 |
| sakila        | address              | 32.00 KiB | 8.56 KiB   |     2 |            2 |         2 |         117 |
| sakila        | city                 | 32.00 KiB | 13.38 KiB  |     2 |            2 |         2 |         389 |
| sakila        | customer             | 32.00 KiB | 10.43 KiB  |     2 |            2 |         2 |         136 |
| sakila        | film                 | 32.00 KiB | 3.76 KiB   |     2 |            2 |         2 |          36 |
| sakila        | inventory            | 32.00 KiB | 1.27 KiB   |     2 |            2 |         2 |          52 |
| sakila        | payment              | 32.00 KiB | 3.21 KiB   |     2 |            2 |         2 |         113 |
| sakila        | rental               | 32.00 KiB | 4.92 KiB   |     2 |            2 |         2 |         149 |
| audit_test    | EE_log               | 16.00 KiB | 7.07 KiB   |     1 |            1 |         1 |          57 |
| InnoDB System | SYS_COLUMNS          | 16.00 KiB | 14.45 KiB  |     1 |            1 |         1 |         228 |
| InnoDB System | SYS_DATAFILES        | 16.00 KiB | 1.79 KiB   |     1 |            1 |         1 |          36 |
| InnoDB System | SYS_FIELDS           | 16.00 KiB | 3.06 KiB   |     1 |            1 |         1 |          71 |
| InnoDB System | SYS_FOREIGN_COLS     | 16.00 KiB | 1.56 KiB   |     1 |            1 |         1 |          22 |
| InnoDB System | SYS_INDEXES          | 16.00 KiB | 4.86 KiB   |     1 |            1 |         1 |          69 |
| InnoDB System | SYS_TABLESPACES      | 16.00 KiB | 1.76 KiB   |     1 |            1 |         1 |          36 |
| mysql         | innodb_table_stats   | 16.00 KiB | 1.94 KiB   |     1 |            1 |         1 |          31 |
| mysql         | slave_master_info    | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| mysql         | slave_relay_log_info | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| mysql         | slave_worker_info    | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| sakila        | actor                | 16.00 KiB | 7.33 KiB   |     1 |            1 |         1 |         200 |
| sakila        | category             | 16.00 KiB | 487 bytes  |     1 |            1 |         1 |          16 |
| sakila        | country              | 16.00 KiB | 3.61 KiB   |     1 |            1 |         1 |         109 |
| sakila        | language             | 16.00 KiB | 396 bytes  |     1 |            1 |         1 |           9 |
| sakila        | staff                | 16.00 KiB | 1021 bytes |     1 |            1 |         1 |           2 |
| sakila        | store                | 16.00 KiB | 52 bytes   |     1 |            1 |         1 |           2 |
| test          | Customer_Data        | 16.00 KiB | 1.12 KiB   |     1 |            1 |         1 |           6 |
| test          | MyISAM_InnoDB        | 16.00 KiB | 156 bytes  |     1 |            1 |         1 |           3 |
| test          | Personal_Info        | 16.00 KiB | 990 bytes  |     1 |            1 |         1 |          24 |
| test          | Personal_Info0       | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| test          | Personal_Info1       | 16.00 KiB | 29 bytes   |     1 |            1 |         1 |           1 |
+---------------+----------------------+-----------+------------+-------+--------------+-----------+-------------+
32 rows in set (0.07 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_by_thread_by_latency;
+------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| user                   | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| main                   |  2125 | 8.33 s        | 25.73 ns    | 27.46 ms    | 711.30 ms   |         1 |           NULL |
| root@localhost         |  8637 | 4.30 s        | 25.73 ns    | 2.73 ms     | 325.61 ms   |        25 |              5 |
| GTID_SSL_USER@CentOS02 |  3482 | 1.62 s        | 25.73 ns    | 9.40 ms     | 367.48 ms   |        21 |              1 |
| io_handler_thread      |     3 | 783.70 ms     | 5.93 ms     | 261.23 ms   | 394.59 ms   |         3 |           NULL |
| srv_master_thread      |     6 | 188.64 ms     | 25.73 us    | 31.44 ms    | 183.65 ms   |        15 |           NULL |
| io_handler_thread      |     3 | 46.71 ms      | 2.75 ms     | 15.57 ms    | 22.17 ms    |         8 |           NULL |
| page_cleaner_thread    |     8 | 13.47 ms      | 12.35 us    | 1.68 ms     | 8.87 ms     |        19 |           NULL |
| srv_purge_thread       |    14 | 354.94 us     | 4.19 us     | 25.35 us    | 118.80 us   |        18 |           NULL |
| signal_handler         |     3 | 109.28 us     | 7.23 us     | 36.43 us    | 92.89 us    |        20 |           NULL |
+------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
9 rows in set (0.02 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_global_by_file_by_bytes limit 0,10;
+--------------------------------------------------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
| file                                                                                       | count_read | total_read | avg_read  | count_write | total_written | avg_write | total      | write_pct |
+--------------------------------------------------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ibdata1                        |        448 | 8.97 MiB   | 20.50 KiB |           5 | 112.00 KiB    | 22.40 KiB | 9.08 MiB   |      1.20 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/general_log.CSV          |       2023 | 7.90 MiB   | 4.00 KiB  |         339 | 32.32 KiB     | 98 bytes  | 7.93 MiB   |      0.40 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/proc.MYD                 |        482 | 738.98 KiB | 1.53 KiB  |           0 | 0 bytes       | 0 bytes   | 738.98 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slow_log.CSV             |        144 | 571.86 KiB | 3.97 KiB  |          22 | 3.03 KiB      | 141 bytes | 574.89 KiB |      0.53 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/innodb_index_stats.ibd   |          9 | 144.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 144.00 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/sakila/actor.ibd               |          8 | 128.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 128.00 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/innodb_table_stats.ibd   |          7 | 112.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 112.00 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/test/Personal_Info.ibd         |          4 | 64.00 KiB  | 16.00 KiB |           1 | 16.00 KiB     | 16.00 KiB | 80.00 KiB  |     20.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slave_relay_log_info.ibd |          5 | 80.00 KiB  | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 80.00 KiB  |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slave_master_info.ibd    |          5 | 80.00 KiB  | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 80.00 KiB  |      0.00 |
+--------------------------------------------------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_global_by_file_by_latency limit 0,10;
+----------------------------------------------------------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                                                                                                     | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+----------------------------------------------------------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ibdata1                                      |   460 | 1.96 s        |        448 | 1.93 s       |           5 | 161.09 us     |          7 | 37.89 ms     |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/general_log.CSV                        |  4395 | 840.16 ms     |       2025 | 824.34 ms    |         341 | 5.72 ms       |       2029 | 10.10 ms     |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ib_logfile0                                  |    13 | 806.86 ms     |          4 | 23.05 ms     |           3 | 83.95 us      |          6 | 783.72 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/general_log.CSM                        |    21 | 774.72 ms     |          3 | 1.17 ms      |           3 | 44.64 us      |         15 | 773.50 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/share/english/errmsg.sys                          |     5 | 771.84 ms     |          3 | 771.60 ms    |           0 | 0 ps          |          2 | 239.28 us    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/slow.log                                     |     4 | 428.92 ms     |          0 | 0 ps         |           1 | 428.90 ms     |          3 | 20.07 us     |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slow_log.CSM                           |    49 | 417.85 ms     |          7 | 1.02 ms      |           7 | 106.03 us     |         35 | 416.73 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql-bin.index                              |     5 | 411.43 ms     |          0 | 0 ps         |           0 | 0 ps          |          5 | 411.43 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ib_logfile1                                  |    11 | 349.60 ms     |          2 | 146.93 ms    |           3 | 87.66 us      |          6 | 202.59 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/sys/x@0024host_summary_by_statement_type.frm |     6 | 288.36 ms     |          2 | 288.11 ms    |           0 | 0 ps          |          4 | 251.39 us    |
+----------------------------------------------------------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_global_by_wait_by_bytes limit 0,10;
+-------------------------+-------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
| event_name              | total | total_latency | min_latency | avg_latency | max_latency | count_read | total_read | avg_read  | count_write | total_written | avg_written | total_requested |
+-------------------------+-------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
| innodb/innodb_data_file |   738 | 3.66 s        | 0 ps        | 4.95 ms     | 362.56 ms   |        578 | 11.00 MiB  | 19.49 KiB |           6 | 128.00 KiB    | 21.33 KiB   | 11.13 MiB       |
| myisam/dfile            |  2796 | 313.68 ms     | 0 ps        | 112.19 us   | 65.60 ms    |        998 | 5.26 MiB   | 5.40 KiB  |         207 | 4.49 MiB      | 22.23 KiB   | 9.75 MiB        |
| csv/data                |  4734 | 902.26 ms     | 0 ps        | 190.59 us   | 282.11 ms   |       2174 | 8.49 MiB   | 4.00 KiB  |         368 | 36.21 KiB     | 101 bytes   | 8.52 MiB        |
| sql/FRM                 |  1997 | 3.33 s        | 0 ps        | 1.67 ms     | 288.10 ms   |        969 | 485.91 KiB | 513 bytes |           0 | 0 bytes       | 0 bytes     | 485.91 KiB      |
| myisam/kfile            |  4722 | 1.55 s        | 0 ps        | 329.24 us   | 325.61 ms   |        380 | 78.97 KiB  | 213 bytes |        3155 | 64.15 KiB     | 21 bytes    | 143.11 KiB      |
| innodb/innodb_log_file  |    24 | 1.16 s        | 0 ps        | 48.19 ms    | 394.59 ms   |          6 | 68.00 KiB  | 11.33 KiB |           6 | 3.50 KiB      | 597 bytes   | 71.50 KiB       |
| sql/ERRMSG              |     5 | 771.84 ms     | 0 ps        | 154.37 ms   | 711.30 ms   |          3 | 58.14 KiB  | 19.38 KiB |           0 | 0 bytes       | 0 bytes     | 58.14 KiB       |
| mysys/charset           |     3 | 69.91 ms      | 0 ps        | 23.30 ms    | 69.73 ms    |          1 | 17.30 KiB  | 17.30 KiB |           0 | 0 bytes       | 0 bytes     | 17.30 KiB       |
| sql/binlog              |   374 | 1.32 s        | 0 ps        | 3.54 ms     | 208.15 ms   |        140 | 11.04 KiB  | 81 bytes  |           5 | 687 bytes     | 137 bytes   | 11.72 KiB       |
| sql/file_parser         |   117 | 216.52 ms     | 0 ps        | 1.85 ms     | 98.15 ms    |          8 | 3.83 KiB   | 490 bytes |           0 | 0 bytes       | 0 bytes     | 3.83 KiB        |
+-------------------------+-------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
10 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [sys]>select * from io_global_by_wait_by_latency limit 0,10;
+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
| event_name              | total | total_latency | avg_latency | max_latency | read_latency | write_latency | misc_latency | count_read | total_read | avg_read  | count_write | total_written | avg_written |
+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
| innodb/innodb_data_file |   738 | 3.66 s        | 4.95 ms     | 362.56 ms   | 3.59 s       | 184.81 us     | 64.68 ms     |        578 | 11.00 MiB  | 19.49 KiB |           6 | 128.00 KiB    | 21.33 KiB   |
| sql/FRM                 |  1997 | 3.33 s        | 1.67 ms     | 288.10 ms   | 3.25 s       | 0 ps          | 87.60 ms     |        969 | 485.91 KiB | 513 bytes |           0 | 0 bytes       | 0 bytes     |
| myisam/kfile            |  4722 | 1.55 s        | 329.24 us   | 325.61 ms   | 526.23 ms    | 7.89 ms       | 1.02 s       |        380 | 78.97 KiB  | 213 bytes |        3155 | 64.15 KiB     | 21 bytes    |
| sql/binlog              |   374 | 1.32 s        | 3.54 ms     | 208.15 ms   | 1.10 s       | 87.02 us      | 221.08 ms    |        140 | 11.04 KiB  | 81 bytes  |           5 | 687 bytes     | 137 bytes   |
| csv/metadata            |    70 | 1.19 s        | 17.04 ms    | 367.48 ms   | 2.19 ms      | 150.67 us     | 1.19 s       |         10 | 350 bytes  | 35 bytes  |          10 | 350 bytes     | 35 bytes    |
| innodb/innodb_log_file  |    24 | 1.16 s        | 48.19 ms    | 394.59 ms   | 169.98 ms    | 171.61 us     | 986.31 ms    |          6 | 68.00 KiB  | 11.33 KiB |           6 | 3.50 KiB      | 597 bytes   |
| csv/data                |  4740 | 902.33 ms     | 190.37 us   | 282.11 ms   | 885.27 ms    | 6.21 ms       | 10.85 ms     |       2176 | 8.49 MiB   | 4.00 KiB  |         370 | 36.43 KiB     | 101 bytes   |
| sql/binlog_index        |    20 | 809.93 ms     | 40.50 ms    | 411.41 ms   | 4.24 us      | 0 ps          | 809.92 ms    |          2 | 513 bytes  | 257 bytes |           0 | 0 bytes       | 0 bytes     |
| sql/ERRMSG              |     5 | 771.84 ms     | 154.37 ms   | 711.30 ms   | 771.60 ms    | 0 ps          | 239.28 us    |          3 | 58.14 KiB  | 19.38 KiB |           0 | 0 bytes       | 0 bytes     |
| sql/slow_log            |     4 | 428.92 ms     | 107.23 ms   | 428.90 ms   | 0 ps         | 428.90 ms     | 20.07 us     |          0 | 0 bytes    | 0 bytes   |           1 | 242 bytes     | 242 bytes   |
+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from latest_file_io;
Empty set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from processlist limit 0,10;
+--------+---------+---------------------------------+------+------------------+------------------------------------------------------------------+------+--------------------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
| thd_id | conn_id | user                            | db   | command          | state                                                            | time | current_statement                    | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement | last_statement_latency | last_wait | last_wait_latency | source |
+--------+---------+---------------------------------+------+------------------+------------------------------------------------------------------+------+--------------------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
|      1 |    NULL | sql/main                        | NULL | NULL             | System lock                                                      | 4087 | INTERNAL DDL LOG RECOVER IN PROGRESS | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     21 |       1 | GTID_SSL_USER@CentOS02          | NULL | Binlog Dump GTID | Master has sent all binlog to slave; waiting for binlog to be up | 4081 | NULL                                 | 45.00 us     |             0 |         0 |             0 |          0 |               0 | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     25 |       5 | root@localhost                  | sys  | Query            | Sending data                                                     |    0 | select * from processlist limit 0,10 | 1.43 ms      |             0 |         0 |             0 |          2 |               0 | YES       | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     17 |    NULL | innodb/srv_lock_timeout_thread  | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|      4 |    NULL | innodb/io_handler_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     15 |    NULL | innodb/srv_master_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|      3 |    NULL | innodb/io_handler_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     14 |    NULL | innodb/srv_monitor_thread       | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|      2 |    NULL | innodb/io_handler_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     13 |    NULL | innodb/srv_error_monitor_thread | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
+--------+---------+---------------------------------+------+------------------+------------------------------------------------------------------+------+--------------------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from ps_check_lost_instrumentation;
Empty set (0.00 sec)

root@localhost [sys]>select * from schema_index_statistics limit 0,10;
+--------------+---------------------------+--------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name                | index_name         | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+---------------------------+--------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| sakila       | store                     | idx_unique_manager |             3 | 235.88 us      |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone_transition_type | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone                 | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone_name            | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone_leap_second     | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | proxies_priv              | Grantor            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | proxies_priv              | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | db                        | User               |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | db                        | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| test         | Personal_Info1            | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
+--------------+---------------------------+--------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
10 rows in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from schema_object_overview;
+--------------------+------------------+-------+
| db                 | object_type      | count |
+--------------------+------------------+-------+
| audit_test         | BASE TABLE       |     2 |
| audit_test         | INDEX (BTREE)    |     1 |
| information_schema | SYSTEM VIEW      |    59 |
| mysql              | BASE TABLE       |    29 |
| mysql              | INDEX (BTREE)    |    64 |
| performance_schema | BASE TABLE       |    52 |
| sakila             | BASE TABLE       |    17 |
| sakila             | FUNCTION         |     3 |
| sakila             | INDEX (BTREE)    |    48 |
| sakila             | INDEX (FULLTEXT) |     2 |
| sakila             | PROCEDURE        |     3 |
| sakila             | TRIGGER          |     6 |
| sakila             | VIEW             |     7 |
| sys                | FUNCTION         |    11 |
| sys                | PROCEDURE        |    22 |
| sys                | VIEW             |    78 |
| test               | BASE TABLE       |     9 |
| test               | INDEX (BTREE)    |     6 |
| test               | PROCEDURE        |     1 |
+--------------------+------------------+-------+
19 rows in set (0.06 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_table_statistics limit 0,10;
+--------------+--------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+-----------+------------------+------------------+-----------------+
| table_schema | table_name         | total_latency | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read    | io_read_latency | io_write_requests | io_write  | io_write_latency | io_misc_requests | io_misc_latency |
+--------------+--------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+-----------+------------------+------------------+-----------------+
| test         | Personal_Info      | 316.26 us     |           25 | 54.22 us      |             2 | 262.03 us      |            0 | 0 ps           |            0 | 0 ps           |               11 | 67.53 KiB  | 81.17 ms        |                 2 | 32.00 KiB | 46.31 us         |               13 | 25.25 ms        |
| sakila       | store              | 235.88 us     |            3 | 235.88 us     |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               12 | 80.71 KiB  | 22.87 ms        |                 0 | 0 bytes   | 0 ps             |               11 | 277.67 us       |
| mysql        | help_category      | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                9 | 1.27 KiB   | 141.42 ms       |                 0 | 0 bytes   | 0 ps             |               14 | 150.86 us       |
| mysql        | innodb_index_stats | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               16 | 148.61 KiB | 86.00 ms        |                 0 | 0 bytes   | 0 ps             |               11 | 325.14 us       |
| mysql        | db                 | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               10 | 3.85 KiB   | 11.69 ms        |                 0 | 0 bytes   | 0 ps             |               17 | 86.13 us        |
| mysql        | slow_log           | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |              179 | 607.52 KiB | 74.22 ms        |                36 | 4.23 KiB  | 565.28 us        |              208 | 417.58 ms       |
| mysql        | time_zone          | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               11 | 1.29 KiB   | 2.14 ms         |                 0 | 0 bytes   | 0 ps             |               19 | 117.24 us       |
| sakila       | city               | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               12 | 80.80 KiB  | 173.95 ms       |                 0 | 0 bytes   | 0 ps             |               11 | 121.46 us       |
| mysql        | columns_priv       | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                9 | 2.08 KiB   | 10.09 ms        |                 0 | 0 bytes   | 0 ps             |               14 | 112.87 us       |
| sakila       | film               | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               15 | 85.20 KiB  | 125.65 ms       |                 0 | 0 bytes   | 0 ps             |               17 | 361.29 us       |
+--------------+--------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+-----------+------------------+------------------+-----------------+
10 rows in set (0.06 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_table_statistics_with_buffer limit 0,10;
+--------------------+-------------------------------------------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+-----------+-----------------+-------------------+-----------+------------------+------------------+-----------------+-------------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| table_schema       | table_name                                      | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read   | io_read_latency | io_write_requests | io_write  | io_write_latency | io_misc_requests | io_misc_latency | innodb_buffer_allocated | innodb_buffer_data | innodb_buffer_pages | innodb_buffer_pages_hashed | innodb_buffer_pages_old | innodb_buffer_rows_cached |
+--------------------+-------------------------------------------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+-----------+-----------------+-------------------+-----------+------------------+------------------+-----------------+-------------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| test               | Personal_Info                                   |           50 | 108.45 us     |            50 | 524.06 us      |            0 | 0 ps           |            0 | 0 ps           |               11 | 67.53 KiB | 81.17 ms        |                 2 | 32.00 KiB | 46.31 us         |               13 | 25.25 ms        |                   32768 |               2040 |                   2 |                          2 |                       2 |                        50 |
| sakila             | store                                           |            6 | 471.77 us     |             6 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               12 | 80.71 KiB | 22.87 ms        |                 0 | 0 bytes   | 0 ps             |               11 | 277.67 us       |                   65536 |                132 |                   4 |                          4 |                       4 |                         4 |
| mysql              | time_zone_transition_type                       |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               11 | 1.53 KiB  | 175.82 ms       |                 0 | 0 bytes   | 0 ps             |               19 | 774.47 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_waits_current                            |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 4.11 KiB  | 134.86 ms       |                 0 | 0 bytes   | 0 ps             |                6 | 103.84 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_waits_summary_global_by_event_name       |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 1.14 KiB  | 38.64 ms        |                 0 | 0 bytes   | 0 ps             |                6 | 126.24 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | rwlock_instances                                |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 1.07 KiB  | 13.49 ms        |                 0 | 0 bytes   | 0 ps             |                6 | 63.51 us        |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | table_lock_waits_summary_by_table               |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 6.50 KiB  | 582.50 us       |                 0 | 0 bytes   | 0 ps             |                6 | 111.13 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_stages_summary_by_host_by_event_name     |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 1.35 KiB  | 547.39 us       |                 0 | 0 bytes   | 0 ps             |                6 | 30.26 us        |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_statements_summary_by_host_by_event_name |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 2.62 KiB  | 1.19 ms         |                 0 | 0 bytes   | 0 ps             |                6 | 25.85 us        |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| sakila             | rental                                          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               15 | 81.76 KiB | 415.46 ms       |                 0 | 0 bytes   | 0 ps             |               17 | 213.30 us       |                   98304 |              15117 |                   6 |                          6 |                       6 |                       447 |
+--------------------+-------------------------------------------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+-----------+-----------------+-------------------+-----------+------------------+------------------+-----------------+-------------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
10 rows in set (0.10 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_tables_with_full_table_scans;
+---------------+----------------+-------------------+
| object_schema | object_name    | rows_full_scanned |
+---------------+----------------+-------------------+
| test          | Personal_Info  |                25 |
| test          | performance    |                 7 |
| test          | Personal_Info0 |                 1 |
+---------------+----------------+-------------------+
3 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_unused_indexes ;
+---------------+----------------+-----------------------------+
| object_schema | object_name    | index_name                  |
+---------------+----------------+-----------------------------+
| audit_test    | EE_log         | PRIMARY                     |
| sakila        | actor          | PRIMARY                     |
| sakila        | actor          | idx_actor_last_name         |
| sakila        | actor          | idx_fl                      |
| sakila        | actor          | idx_actor_first_name        |
| sakila        | address        | idx_fk_city_id              |
| sakila        | address        | PRIMARY                     |
| sakila        | category       | PRIMARY                     |
| sakila        | city           | PRIMARY                     |
| sakila        | city           | idx_fk_country_id           |
| sakila        | country        | PRIMARY                     |
| sakila        | customer       | idx_last_name               |
| sakila        | customer       | idx_fk_address_id           |
| sakila        | customer       | idx_fk_store_id             |
| sakila        | customer       | PRIMARY                     |
| sakila        | film           | idx_fk_original_language_id |
| sakila        | film           | idx_fk_language_id          |
| sakila        | film           | idx_title                   |
| sakila        | film           | PRIMARY                     |
| sakila        | film_actor     | PRIMARY                     |
| sakila        | film_actor     | idx_fk_film_id              |
| sakila        | film_category  | PRIMARY                     |
| sakila        | film_category  | fk_film_category_category   |
| sakila        | film_text      | idx_title_description       |
| sakila        | film_text      | PRIMARY                     |
| sakila        | inventory      | PRIMARY                     |
| sakila        | inventory      | idx_fk_film_id              |
| sakila        | inventory      | idx_store_id_film_id        |
| sakila        | language       | PRIMARY                     |
| sakila        | payment        | fk_payment_rental           |
| sakila        | payment        | idx_fk_customer_id          |
| sakila        | payment        | idx_fk_staff_id             |
| sakila        | payment        | PRIMARY                     |
| sakila        | rental         | idx_fk_staff_id             |
| sakila        | rental         | idx_fk_customer_id          |
| sakila        | rental         | idx_fk_inventory_id         |
| sakila        | rental         | rental_date                 |
| sakila        | rental         | PRIMARY                     |
| sakila        | staff          | PRIMARY                     |
| sakila        | staff          | idx_fk_store_id             |
| sakila        | staff          | idx_fk_address_id           |
| sakila        | store          | idx_fk_address_id           |
| sakila        | store          | PRIMARY                     |
| test          | Customer_Data  | PRIMARY                     |
| test          | Customer_Data  | idx_name                    |
| test          | MyISAM_InnoDB  | PRIMARY                     |
| test          | Personal_Info  | PRIMARY                     |
| test          | Personal_Info0 | PRIMARY                     |
| test          | Personal_Info1 | PRIMARY                     |
+---------------+----------------+-----------------------------+
49 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statement_analysis limit 0,10;
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| query                                                             | db   | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest                           | first_seen          | last_seen           |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  | *         |          4 |         3 |          1 | 5.23 s        | 4.68 s      | 1.31 s      | 173.34 ms    |         3 |             1 |           499 |               125 |        579 |             102 |          77 |                 0 | 5170dc5f93bc1119d5ae4e33c35b9a9a | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 |
| SELECT `performance_schema` .  ... a` . `accounts` . `HOST` = ... | sys  |           |          3 |         0 |          0 | 619.11 ms     | 614.23 ms   | 206.37 ms   | 540.72 ms    |         0 |             0 |             0 |                 0 |         15 |               0 |           0 |                 0 | 46f73fffb370a0c58fe74b1f0c71f85b | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 |
| SELECT `information_schema` .  ... TICS` . `INDEX_TYPE` UNION ... | sys  |           |          3 |         0 |          0 | 367.43 ms     | 328.81 ms   | 122.48 ms   | 109.00 us    |         0 |             0 |             0 |                 0 |         21 |               9 |           0 |                 0 | ae52e0db739083c9787386574a466db2 | 2014-12-23 06:52:12 | 2014-12-23 07:26:43 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |           |          3 |         0 |          0 | 330.62 ms     | 256.40 ms   | 110.21 ms   | 117.00 us    |         0 |             0 |             0 |                 0 |          6 |               3 |           0 |                 0 | 97b93c349004ed5d8774479aecc83eda | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 |
| SELECT IF ( `isnull` ( `inform ... tory_long` . `THREAD_ID` = ... | sys  |           |          3 |         0 |          0 | 123.40 ms     | 99.50 ms    | 41.13 ms    | 150.00 us    |         0 |             0 |             0 |                 0 |          6 |               3 |           0 |                 0 | 6a3616ea2e897af54c2cd67e897c1026 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.48 ms     | 101.48 ms   | 101.48 ms   | 101.42 ms    |        10 |            10 |          9210 |              9210 |          7 |               3 |         186 |                 0 | b7b460a53f2b7adaa1ef906ab252ffcf | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.04 ms     | 101.04 ms   | 101.04 ms   | 100.96 ms    |       109 |           109 |          9309 |              9309 |          7 |               3 |         186 |                 0 | 118fe5e749cef7bb98721f42b27272cd | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  | *         |          1 |         0 |          0 | 84.68 ms      | 84.68 ms    | 84.68 ms    | 84.64 ms     |        32 |            32 |          8364 |              8364 |          4 |               3 |         108 |                 0 | 904e7bec1adda724acb5246c40a7250e | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  | *         |          1 |         0 |          0 | 84.26 ms      | 84.26 ms    | 84.26 ms    | 84.21 ms     |         5 |             5 |          8251 |              8251 |          4 |               3 |          49 |                 0 | 7f118a1b7ade086fc5721e0c52d67e8d | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 |
| SELECT * FROM `innodb_buffer_s ...  `ibp` . `COMPRESSED_SIZE` ... | sys  | *         |          1 |         0 |          0 | 76.98 ms      | 76.98 ms    | 76.98 ms    | 76.83 ms     |        32 |            32 |          8332 |              8332 |          4 |               3 |          76 |                 0 | 3e176b8aec7493370bac9a0140121550 | 2014-12-23 07:48:05 | 2014-12-23 07:48:05 |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_errors_or_warnings;
+-------------------------------------------------------------------+------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | errors | error_pct | warnings | warning_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  |          4 |      3 |   75.0000 |        1 |     25.0000 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| <                                                                 | NULL |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:04:10 | 2014-12-23 07:04:10 | 4c4154c90f9c70d2c865fbba17469831 |
| SELECT * FROM `sys` . `schema_overview` WHERE `db` = ?            | sys  |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:08:09 | 2014-12-23 07:08:09 | 9c0f4109fbd431777e669b0024381584 |
| SELECT * FROM `federated_table`                                   | test |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:11:40 | 2014-12-23 07:11:40 | 4efc47cc0a3b062fe8ecd4f6dc751c98 |
| DESC `Personal_Info` +                                            | test |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:54:50 | 2014-12-23 07:54:50 | 1fecc49ac0c1257680c5d3b14c27a4a5 |
| SELECT * FROM PROCESSLIST :                                       | sys  |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:56:40 | 2014-12-23 07:56:40 | 0a161211bcfa223ac5355844c9b8f45e |
| SELECT * FROM `sys` . `user_su ... atency` `stmt` ON ( ( IF ( ... | sys  |          1 |      0 |    0.0000 |        4 |    400.0000 | 2014-12-23 07:30:54 | 2014-12-23 07:30:54 | b8cc646b95da6a9fe467debd187bba98 |
+-------------------------------------------------------------------+------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
7 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_full_table_scans limit 0,10;
+-------------------------------------------------------------------+------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | total_latency | no_index_used_count | no_good_index_used_count | no_index_used_pct | rows_sent | rows_examined | rows_sent_avg | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `ps_check_lost_i ... s` . `VARIABLE_VALUE` > ? ) )  | sys  |          1 | 955.72 us     |                   1 |                        0 |               100 |         0 |            23 |             0 |                23 | 2014-12-23 07:58:03 | 2014-12-23 07:58:03 | 4e4d87490ae3897ea806bd076116593d |
| SELECT * FROM `latest_file_io` ... ( ( `performance_schema` . ... | sys  |          3 | 9.19 ms       |                   3 |                        0 |               100 |         0 |            62 |             0 |                21 | 2014-12-23 07:53:38 | 2014-12-23 07:55:10 | 55e76ed25f3c3206f0f0c3905be1c7d7 |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  |          1 | 84.68 ms      |                   1 |                        0 |               100 |        32 |          8364 |            32 |              8364 | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 | 904e7bec1adda724acb5246c40a7250e |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          1 | 84.26 ms      |                   1 |                        0 |               100 |         5 |          8251 |             5 |              8251 | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 | 7f118a1b7ade086fc5721e0c52d67e8d |
| SELECT * FROM `innodb_buffer_s ...  `ibp` . `COMPRESSED_SIZE` ... | sys  |          1 | 76.98 ms      |                   1 |                        0 |               100 |        32 |          8332 |            32 |              8332 | 2014-12-23 07:48:05 | 2014-12-23 07:48:05 | 3e176b8aec7493370bac9a0140121550 |
| SELECT * FROM `io_global_by_wa ... ( ( `performance_schema` . ... | sys  |          1 | 7.91 ms       |                   1 |                        0 |               100 |        10 |            56 |            10 |                56 | 2014-12-23 07:52:01 | 2014-12-23 07:52:01 | fd3b6f36113a46d3865a75837fa0c790 |
| SELECT * FROM `sys` . `user_su ... mt` ON ( ( IF ( `isnull` ( ... | sys  |          1 | 7.21 ms       |                   1 |                        0 |               100 |         4 |          1842 |             4 |              1842 | 2014-12-23 07:30:59 | 2014-12-23 07:30:59 | dde89d4792dc23edcf2ff3ea77d8f28d |
| SELECT * FROM `sys` . `user_su ... atency` `stmt` ON ( ( IF ( ... | sys  |          7 | 66.60 ms      |                   7 |                        0 |               100 |         7 |         11033 |             1 |              1576 | 2014-12-23 07:29:37 | 2014-12-23 07:30:51 | 769b46157ec1a6441a48da07d1ccf7b2 |
| SELECT * FROM `io_global_by_fi ... `file_summary_by_instance` ... | sys  |          1 | 62.30 ms      |                   1 |                        0 |               100 |       375 |           750 |           375 |               750 | 2014-12-23 07:50:57 | 2014-12-23 07:50:57 | 18f8431093bdf755abe8470a130a7e5b |
| SELECT * FROM `host_summary_by ... ` , `performance_schema` . ... | sys  |          1 | 6.76 ms       |                   1 |                        0 |               100 |        36 |          1184 |            36 |              1184 | 2014-12-23 07:42:48 | 2014-12-23 07:42:48 | c3fe3613ac65b56475622798e793bae6 |
+-------------------------------------------------------------------+------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_runtimes_in_95th_percentile;
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  | *         |          4 |         3 |          1 | 5.23 s        | 4.68 s      | 1.31 s      |         3 |             1 |           499 |               125 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| SELECT `performance_schema` .  ... a` . `accounts` . `HOST` = ... | sys  |           |          3 |         0 |          0 | 619.11 ms     | 614.23 ms   | 206.37 ms   |         0 |             0 |             0 |                 0 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 46f73fffb370a0c58fe74b1f0c71f85b |
| SELECT `information_schema` .  ... TICS` . `INDEX_TYPE` UNION ... | sys  |           |          3 |         0 |          0 | 367.43 ms     | 328.81 ms   | 122.48 ms   |         0 |             0 |             0 |                 0 | 2014-12-23 06:52:12 | 2014-12-23 07:26:43 | ae52e0db739083c9787386574a466db2 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |           |          3 |         0 |          0 | 330.62 ms     | 256.40 ms   | 110.21 ms   |         0 |             0 |             0 |                 0 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 97b93c349004ed5d8774479aecc83eda |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.48 ms     | 101.48 ms   | 101.48 ms   |        10 |            10 |          9210 |              9210 | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 | b7b460a53f2b7adaa1ef906ab252ffcf |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.04 ms     | 101.04 ms   | 101.04 ms   |       109 |           109 |          9309 |              9309 | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 | 118fe5e749cef7bb98721f42b27272cd |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  | *         |          1 |         0 |          0 | 84.68 ms      | 84.68 ms    | 84.68 ms    |        32 |            32 |          8364 |              8364 | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 | 904e7bec1adda724acb5246c40a7250e |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  | *         |          1 |         0 |          0 | 84.26 ms      | 84.26 ms    | 84.26 ms    |         5 |             5 |          8251 |              8251 | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 | 7f118a1b7ade086fc5721e0c52d67e8d |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
8 rows in set (0.05 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_sorting limit 0,10;
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | total_latency | sort_merge_passes | avg_sort_merges | sorts_using_scans | sort_using_range | rows_sorted | avg_rows_sorted | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  |          4 | 5.23 s        |                 0 |               0 |                12 |                0 |          77 |              19 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.48 ms     |                 0 |               0 |                 3 |                0 |         186 |             186 | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 | b7b460a53f2b7adaa1ef906ab252ffcf |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.04 ms     |                 0 |               0 |                 3 |                0 |         186 |             186 | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 | 118fe5e749cef7bb98721f42b27272cd |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  |          1 | 84.68 ms      |                 0 |               0 |                 3 |                0 |         108 |             108 | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 | 904e7bec1adda724acb5246c40a7250e |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          1 | 84.26 ms      |                 0 |               0 |                 2 |                0 |          49 |              49 | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 | 7f118a1b7ade086fc5721e0c52d67e8d |
| SELECT * FROM `innodb_buffer_s ...  `ibp` . `COMPRESSED_SIZE` ... | sys  |          1 | 76.98 ms      |                 0 |               0 |                 2 |                0 |          76 |              76 | 2014-12-23 07:48:05 | 2014-12-23 07:48:05 | 3e176b8aec7493370bac9a0140121550 |
| SELECT * FROM `sys` . `user_su ... atency` `stmt` ON ( ( IF ( ... | sys  |          7 | 66.60 ms      |                 0 |               0 |                21 |                0 |          65 |               9 | 2014-12-23 07:29:37 | 2014-12-23 07:30:51 | 769b46157ec1a6441a48da07d1ccf7b2 |
| SELECT * FROM `io_global_by_fi ... `file_summary_by_instance` ... | sys  |          1 | 62.30 ms      |                 0 |               0 |                 1 |                0 |         375 |             375 | 2014-12-23 07:50:57 | 2014-12-23 07:50:57 | 18f8431093bdf755abe8470a130a7e5b |
| SELECT * FROM `io_global_by_fi ... ile_summary_by_instance` . ... | sys  |          1 | 58.35 ms      |                 0 |               0 |                 1 |                0 |         375 |             375 | 2014-12-23 07:49:45 | 2014-12-23 07:49:45 | 3ba99c6174dec8f0c634305296abb19b |
| SELECT * FROM `schema_object_o ... MA` , `information_schema` ... | sys  |          1 | 56.56 ms      |                 0 |               0 |                 6 |                0 |          38 |              38 | 2014-12-23 07:59:21 | 2014-12-23 07:59:21 | ccc857fb69a9f151a1b8cb8687697b1a |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_temp_tables limit 0,10;
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | total_latency | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  |          4 | 5.23 s        |               579 |             102 |                      145 |                     18 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| SELECT * FROM `schema_object_o ... MA` , `information_schema` ... | sys  |          1 | 56.56 ms      |               189 |              33 |                      189 |                     17 | 2014-12-23 07:59:21 | 2014-12-23 07:59:21 | ccc857fb69a9f151a1b8cb8687697b1a |
| SELECT `information_schema` .  ... TICS` . `INDEX_TYPE` UNION ... | sys  |          3 | 367.43 ms     |                21 |               9 |                        7 |                     43 | 2014-12-23 06:52:12 | 2014-12-23 07:26:43 | ae52e0db739083c9787386574a466db2 |
| SELECT `stmts` . `DIGEST_TEXT` ... M ( `performance_schema` . ... | sys  |          3 | 35.45 ms      |                12 |               3 |                        4 |                     25 | 2014-12-23 06:52:13 | 2014-12-23 07:26:43 | f3ccd31f72c22e9419a309de00822ff5 |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.04 ms     |                 7 |               3 |                        7 |                     43 | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 | 118fe5e749cef7bb98721f42b27272cd |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.48 ms     |                 7 |               3 |                        7 |                     43 | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 | b7b460a53f2b7adaa1ef906ab252ffcf |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          3 | 14.25 ms      |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 5aae34a9612abcf6604a68de054cbb85 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          3 | 330.62 ms     |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 97b93c349004ed5d8774479aecc83eda |
| SELECT IF ( `isnull` ( `inform ... tory_long` . `THREAD_ID` = ... | sys  |          3 | 123.40 ms     |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 6a3616ea2e897af54c2cd67e897c1026 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          3 | 31.08 ms      |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:13 | 2014-12-23 07:26:43 | 544388f8ee4e7f53375a9b186533fb4a |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary;
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| user          | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_hosts |
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| GTID_SSL_USER |          9 | 4.44 ms           | 493.35 us             |           2 |     3485 | 1.62 s          |                   1 |                 1 |            1 |
| admin         |         86 | 163.00 ms         | 1.90 ms               |          16 |      397 | 96.51 ms        |                   1 |                 3 |            1 |
| background    |       NULL | NULL              | 0 ps                  |        NULL |     2179 | 9.42 s          |                  18 |                22 |            0 |
| root          |        340 | 8.50 s            | 25.01 ms              |          71 |    12468 | 5.36 s          |                   1 |                 3 |            1 |
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
4 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_file_io;
+---------------+-------+------------+
| user          | ios   | io_latency |
+---------------+-------+------------+
| background    |  2179 | 9.42 s     |
| root          | 12474 | 5.36 s     |
| GTID_SSL_USER |  3485 | 1.62 s     |
| admin         |   397 | 96.51 ms   |
+---------------+-------+------------+
4 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_file_io_type limit 0,10;
+---------------+--------------------------------------+-------+-----------+-------------+
| user          | event_name                           | total | latency   | max_latency |
+---------------+--------------------------------------+-------+-----------+-------------+
| GTID_SSL_USER | wait/io/file/csv/data                |  3405 | 825.63 ms | 282.11 ms   |
| GTID_SSL_USER | wait/io/file/csv/metadata            |    21 | 774.72 ms | 367.48 ms   |
| GTID_SSL_USER | wait/io/file/sql/FRM                 |    39 | 17.67 ms  | 17.53 ms    |
| GTID_SSL_USER | wait/io/file/sql/binlog              |    20 | 152.98 us | 56.58 us    |
| admin         | wait/io/file/myisam/kfile            |    90 | 68.84 ms  | 42.58 ms    |
| admin         | wait/io/file/innodb/innodb_log_file  |     2 | 21.94 ms  | 21.90 ms    |
| admin         | wait/io/file/csv/data                |   264 | 4.28 ms   | 548.74 us   |
| admin         | wait/io/file/myisam/dfile            |    29 | 968.23 us | 203.67 us   |
| admin         | wait/io/file/innodb/innodb_data_file |     7 | 408.68 us | 167.94 us   |
| admin         | wait/io/file/sql/binlog              |     1 | 39.46 us  | 39.46 us    |
+---------------+--------------------------------------+-------+-----------+-------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_stages;
Empty set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_statement_latency;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user          | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| root          |   346 | 8.53 s        | 5.41 s      | 1.84 s       |      2348 |         88008 |             1 |         77 |
| admin         |    86 | 163.00 ms     | 88.86 ms    | 13.77 ms     |       154 |           144 |             1 |         16 |
| GTID_SSL_USER |     9 | 4.44 ms       | 2.65 ms     | 698.00 us    |         5 |             2 |             0 |          2 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_statement_type limit 0,10;
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user          | statement      | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| GTID_SSL_USER | show_variables |     2 | 2.48 ms       | 1.78 ms     | 331.00 us    |         2 |             2 |             0 |          2 |
| GTID_SSL_USER | set_option     |     3 | 925.25 us     | 325.51 us   | 162.00 us    |         0 |             0 |             0 |          0 |
| GTID_SSL_USER | select         |     3 | 856.66 us     | 370.57 us   | 205.00 us    |         3 |             0 |             0 |          0 |
| GTID_SSL_USER | Register Slave |     1 | 176.49 us     | 176.49 us   | 0 ps         |         0 |             0 |             0 |          0 |
| admin         | Field List     |    51 | 97.22 ms      | 47.42 ms    | 2.31 ms      |         0 |             0 |             0 |          0 |
| admin         | insert         |     1 | 25.18 ms      | 25.18 ms    | 628.00 us    |         0 |             0 |             1 |          0 |
| admin         | select         |    14 | 12.20 ms      | 2.90 ms     | 1.96 ms      |        18 |             8 |             0 |          3 |
| admin         | show_tables    |     8 | 11.54 ms      | 3.24 ms     | 2.10 ms      |       102 |           102 |             0 |          8 |
| admin         | show_databases |     4 | 7.10 ms       | 2.40 ms     | 1.62 ms      |        32 |            32 |             0 |          4 |
| admin         | show_fields    |     1 | 5.29 ms       | 5.29 ms     | 4.31 ms      |         2 |             2 |             0 |          1 |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from version;
+-------------+-------------------------------------------+
| sys_version | mysql_version                             |
+-------------+-------------------------------------------+
| 1.1.0       | 5.6.21-enterprise-commercial-advanced-log |
+-------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from wait_classes_global_by_avg_latency;
+-----------------+-------+---------------+-------------+-------------+-------------+
| event_class     | total | total_latency | min_latency | avg_latency | max_latency |
+-----------------+-------+---------------+-------------+-------------+-------------+
| wait/io/file    | 18586 | 16.49 s       | 0 ps        | 887.44 us   | 711.30 ms   |
| wait/io/table   |    38 | 1.26 ms       | 219.49 ns   | 33.20 us    | 665.72 us   |
| wait/lock/table |    12 | 365.26 us     | 684.61 ns   | 30.44 us    | 349.01 us   |
+-----------------+-------+---------------+-------------+-------------+-------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from wait_classes_global_by_latency;
+-----------------+-------+---------------+-------------+-------------+-------------+
| event_class     | total | total_latency | min_latency | avg_latency | max_latency |
+-----------------+-------+---------------+-------------+-------------+-------------+
| wait/io/file    | 18592 | 16.49 s       | 0 ps        | 887.16 us   | 711.30 ms   |
| wait/io/table   |    38 | 1.26 ms       | 219.49 ns   | 33.20 us    | 665.72 us   |
| wait/lock/table |    12 | 365.26 us     | 684.61 ns   | 30.44 us    | 349.01 us   |
+-----------------+-------+---------------+-------------+-------------+-------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from waits_by_host_by_latency limit 0,10;
+----------------+--------------------------------------+-------+---------------+-------------+-------------+
| host           | event                                | total | total_latency | avg_latency | max_latency |
+----------------+--------------------------------------+-------+---------------+-------------+-------------+
| 192.168.56.108 | wait/io/file/myisam/kfile            |    90 | 68.84 ms      | 764.84 us   | 42.58 ms    |
| 192.168.56.108 | wait/io/file/innodb/innodb_log_file  |     2 | 21.94 ms      | 10.97 ms    | 21.90 ms    |
| 192.168.56.108 | wait/io/file/csv/data                |   264 | 4.28 ms       | 16.22 us    | 548.74 us   |
| 192.168.56.108 | wait/io/table/sql/handler            |    12 | 1.07 ms       | 89.53 us    | 665.72 us   |
| 192.168.56.108 | wait/io/file/myisam/dfile            |    29 | 968.23 us     | 33.39 us    | 203.67 us   |
| 192.168.56.108 | wait/io/file/innodb/innodb_data_file |     7 | 408.68 us     | 58.38 us    | 167.94 us   |
| 192.168.56.108 | wait/lock/table/sql/handler          |     8 | 359.52 us     | 44.94 us    | 349.01 us   |
| 192.168.56.108 | wait/io/file/sql/binlog              |     1 | 39.46 us      | 39.46 us    | 39.46 us    |
| 192.168.56.108 | wait/io/file/sql/dbopt               |     4 | 36.28 us      | 9.07 us     | 12.14 us    |
| CentOS02       | wait/io/file/csv/data                |  3405 | 825.63 ms     | 242.48 us   | 282.11 ms   |
+----------------+--------------------------------------+-------+---------------+-------------+-------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from waits_by_user_by_latency limit 0,10;
+---------------+--------------------------------------+-------+---------------+-------------+-------------+
| user          | event                                | total | total_latency | avg_latency | max_latency |
+---------------+--------------------------------------+-------+---------------+-------------+-------------+
| GTID_SSL_USER | wait/io/file/csv/data                |  3405 | 825.63 ms     | 242.48 us   | 282.11 ms   |
| GTID_SSL_USER | wait/io/file/csv/metadata            |    21 | 774.72 ms     | 36.89 ms    | 367.48 ms   |
| GTID_SSL_USER | wait/io/file/sql/FRM                 |    39 | 17.67 ms      | 453.10 us   | 17.53 ms    |
| GTID_SSL_USER | wait/io/file/sql/binlog              |    20 | 152.98 us     | 7.65 us     | 56.58 us    |
| admin         | wait/io/file/myisam/kfile            |    90 | 68.84 ms      | 764.84 us   | 42.58 ms    |
| admin         | wait/io/file/innodb/innodb_log_file  |     2 | 21.94 ms      | 10.97 ms    | 21.90 ms    |
| admin         | wait/io/file/csv/data                |   264 | 4.28 ms       | 16.22 us    | 548.74 us   |
| admin         | wait/io/table/sql/handler            |    12 | 1.07 ms       | 89.53 us    | 665.72 us   |
| admin         | wait/io/file/myisam/dfile            |    29 | 968.23 us     | 33.39 us    | 203.67 us   |
| admin         | wait/io/file/innodb/innodb_data_file |     7 | 408.68 us     | 58.38 us    | 167.94 us   |
+---------------+--------------------------------------+-------+---------------+-------------+-------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from waits_global_by_latency limit 0,10;
+--------------------------------------+-------+---------------+-------------+-------------+
| events                               | total | total_latency | avg_latency | max_latency |
+--------------------------------------+-------+---------------+-------------+-------------+
| wait/io/file/innodb/innodb_data_file |   752 | 3.69 s        | 4.91 ms     | 362.56 ms   |
| wait/io/file/sql/FRM                 |  1997 | 3.33 s        | 1.67 ms     | 288.10 ms   |
| wait/io/file/myisam/kfile            |  6022 | 1.67 s        | 277.65 us   | 325.61 ms   |
| wait/io/file/sql/binlog              |   378 | 1.32 s        | 3.50 ms     | 208.15 ms   |
| wait/io/file/innodb/innodb_log_file  |    30 | 1.20 s        | 40.04 ms    | 394.59 ms   |
| wait/io/file/csv/metadata            |    70 | 1.19 s        | 17.04 ms    | 367.48 ms   |
| wait/io/file/csv/data                |  5190 | 910.17 ms     | 175.37 us   | 282.11 ms   |
| wait/io/file/sql/binlog_index        |    20 | 809.93 ms     | 40.50 ms    | 411.41 ms   |
| wait/io/file/sql/ERRMSG              |     5 | 771.84 ms     | 154.37 ms   | 711.30 ms   |
| wait/io/file/sql/slow_log            |     4 | 428.92 ms     | 107.23 ms   | 428.90 ms   |
+--------------------------------------+-------+---------------+-------------+-------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

GITからのダウンロード。
英語ですが、こちらのページに各Viewの詳細が説明されております。
https://github.com/MarkLeith/mysql-sys

上記Gitからダウンロードした方がObjectの数が多いようです。

[admin@CentOS01 mysql-sys-master]$ mysql -u root -p < sys_56.sql 
Enter password: 
[admin@CentOS01 mysql-sys-master]$ 


root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
| sys | TRIGGER       |     2 |
| sys | FUNCTION      |    11 |
| sys | PROCEDURE     |    22 |
| sys | VIEW          |    81 |
+-----+---------------+-------+
6 rows in set (0.06 sec)

root@localhost [sys]>

SYS Schemaに関しては、こちらが参考になるかと思います。
https://oracleus.activeevents.com/2014/connect/fileDownload/session/72527FD42DFF7B2148314B9E72BE7B6A/CON3751_Leith-mysql_sys_schema_oow_2014.pdf


「USE INDEX 」と「FORCE INDEX 」の違い
USE INDEXでは、possible_keysにインデックスがリストアップされない場合に、
リストに加えるために利用します。ただ、それが実際に使われる(keyに表示される)かどうかは
オプティマイザの判断にゆだねられます。
それに対して、FORCE INDEXは実際に使われる(keyに表示される)ように強制するものです。

以下ケースでは、インデックスに対応するデータがある場合はヒント通りに実行されている。

root@localhost [sakila]>select * from actor limit 0,10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>ALTER TABLE actor ADD INDEX idx_fl(first_name,last_name);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [sakila]>ALTER TABLE actor ADD index idx_actor_first_name (first_name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`),
  KEY `idx_fl` (`first_name`,`last_name`),
  KEY `idx_actor_first_name` (`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>



root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys               | key    | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_fl,idx_actor_first_name | idx_fl | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor use index(idx_actor_first_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_actor_first_name | idx_actor_first_name | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor force index(idx_actor_first_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_actor_first_name | idx_actor_first_name | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>


root@localhost [sakila]>explain select * from actor use index(idx_actor_last_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor force index(idx_actor_last_name) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@localhost [sakila]>

root@localhost [sakila]>select * from actor where last_name = 'CAGE';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       11 | ZERO       | CAGE      | 2006-02-15 04:34:33 |
|       40 | JOHNNY     | CAGE      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

root@localhost [sakila]>explain select * from actor where last_name = 'CAGE';
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_actor_last_name | idx_actor_last_name | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor use index(idx_fl) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_fl        | idx_fl | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>explain select * from actor force index(idx_fl) where first_name = 'JOHNNY';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | actor | ref  | idx_fl        | idx_fl | 137     | const |    2 | Using index condition |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>

その他、パフォーマンス関連の確認に関してはSYSスキーマを利用すると良いでしょう。

GITからのダウンロード。
英語ですが、こちらのページに各Viewの詳細が説明されております。
https://github.com/MarkLeith/mysql-sys

SYS Schemaに関しては、こちらが参考になるかと思います。
https://oracleus.activeevents.com/2014/connect/fileDownload/session/72527FD42DFF7B2148314B9E72BE7B6A/CON3751_Leith-mysql_sys_schema_oow_2014.pdf


MySQL 5.7: オプティマイザ

追加要素にてコストを設定可能
ディスクI/O処理性能
メモリ処理性能

コスト変更前

root@localhost [nyosm]>select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.7.5-labs-http-log |
+---------------------+
1 row in set (0.00 sec)

root@localhost [nyosm]>

Configurable Costs
root@localhost [nyosm]>SELECT * FROM mysql.engine_cost;
+-------------+-------------+--------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name          | cost_value | last_update         | comment |
+-------------+-------------+--------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost |       NULL | 2014-10-09 18:51:46 | NULL    |
+-------------+-------------+--------------------+------------+---------------------+---------+
1 row in set (0.00 sec)

root@localhost [nyosm]>SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2014-10-09 18:51:46 | NULL    |
| disk_temptable_row_cost      |       NULL | 2014-10-09 18:51:46 | NULL    |
| key_compare_cost             |       NULL | 2014-10-09 18:51:46 | NULL    |
| memory_temptable_create_cost |       NULL | 2014-10-09 18:51:46 | NULL    |
| memory_temptable_row_cost    |       NULL | 2014-10-09 18:51:46 | NULL    |
| row_evaluate_cost            |       NULL | 2014-10-09 18:51:46 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.00 sec)

root@localhost [nyosm]>

コスト設定変更前(Default)

root@localhost [nyosm]>EXPLAIN FORMAT=JSON SELECT 'node' as type, id
    -> FROM nodetags WHERE k='amenity' and v='cafe' UNION SELECT 'way' as
    -> type, id FROM waytags WHERE k='amenity' and v='cafe' UNION
    -> SELECT 'relation' as type,id FROM relationtags WHERE
    -> k='amenity' and v='cafe'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "table_name": "<union1,2,3>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "452.40"
            },
            "table": {
              "table_name": "nodetags",
              "access_type": "ref",
              "possible_keys": [
                "i_nodekeys",
                "i_nodevalues"
              ],
              "key": "i_nodevalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 377,
              "rows_produced_per_join": 22,
              "filtered": 6.0989,
              "cost_info": {
                "read_cost": "377.00",
                "eval_cost": "4.60",
                "prefix_cost": "452.40",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`nodetags`.`v` = 'cafe') and (`nyosm`.`nodetags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "37.20"
            },
            "table": {
              "table_name": "waytags",
              "access_type": "ref",
              "possible_keys": [
                "i_waykeys",
                "i_wayvalues"
              ],
              "key": "i_wayvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 31,
              "rows_produced_per_join": 0,
              "filtered": 0.1613,
              "cost_info": {
                "read_cost": "31.00",
                "eval_cost": "0.01",
                "prefix_cost": "37.20",
                "data_read_per_join": "15"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`waytags`.`v` = 'cafe') and (`nyosm`.`waytags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 3,
            "cost_info": {
              "query_cost": "1.20"
            },
            "table": {
              "table_name": "relationtags",
              "access_type": "ref",
              "possible_keys": [
                "i_relationkeys",
                "i_relationvalues"
              ],
              "key": "i_relationvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": 5,
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.01",
                "prefix_cost": "1.20",
                "data_read_per_join": "16"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`relationtags`.`v` = 'cafe') and (`nyosm`.`relationtags`.`k` = 'amenity'))"
            }
          }
        }
      ]
    }
  }
}
1 row in set, 1 warning (0.39 sec)

root@localhost [nyosm]>

explainnull

コスト設定変更

root@localhost [nyosm]>UPDATE mysql.server_cost SET cost_value = 10;
Query OK, 6 rows affected (0.19 sec)
Rows matched: 6  Changed: 6  Warnings: 0

root@localhost [nyosm]>SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |         10 | 2014-12-18 17:26:13 | NULL    |
| disk_temptable_row_cost      |         10 | 2014-12-18 17:26:13 | NULL    |
| key_compare_cost             |         10 | 2014-12-18 17:26:13 | NULL    |
| memory_temptable_create_cost |         10 | 2014-12-18 17:26:13 | NULL    |
| memory_temptable_row_cost    |         10 | 2014-12-18 17:26:13 | NULL    |
| row_evaluate_cost            |         10 | 2014-12-18 17:26:13 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.00 sec)

root@localhost [nyosm]>

root@localhost [nyosm]>FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.00 sec)

root@localhost [nyosm]>

コストの設定変更後

root@localhost [nyosm]>EXPLAIN FORMAT=JSON SELECT 'node' as type, id
    -> FROM nodetags WHERE k='amenity' and v='cafe' UNION SELECT 'way' as
    -> type, id FROM waytags WHERE k='amenity' and v='cafe' UNION
    -> SELECT 'relation' as type,id FROM relationtags WHERE
    -> k='amenity' and v='cafe'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "table_name": "<union1,2,3>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "452.40"
            },
            "table": {
              "table_name": "nodetags",
              "access_type": "ref",
              "possible_keys": [
                "i_nodekeys",
                "i_nodevalues"
              ],
              "key": "i_nodevalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 377,
              "rows_produced_per_join": 22,
              "filtered": 6.0989,
              "cost_info": {
                "read_cost": "377.00",
                "eval_cost": "4.60",
                "prefix_cost": "452.40",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`nodetags`.`v` = 'cafe') and (`nyosm`.`nodetags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "37.20"
            },
            "table": {
              "table_name": "waytags",
              "access_type": "ref",
              "possible_keys": [
                "i_waykeys",
                "i_wayvalues"
              ],
              "key": "i_wayvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 31,
              "rows_produced_per_join": 0,
              "filtered": 0.1613,
              "cost_info": {
                "read_cost": "31.00",
                "eval_cost": "0.01",
                "prefix_cost": "37.20",
                "data_read_per_join": "15"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`waytags`.`v` = 'cafe') and (`nyosm`.`waytags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 3,
            "cost_info": {
              "query_cost": "1.20"
            },
            "table": {
              "table_name": "relationtags",
              "access_type": "ref",
              "possible_keys": [
                "i_relationkeys",
                "i_relationvalues"
              ],
              "key": "i_relationvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": 5,
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.01",
                "prefix_cost": "1.20",
                "data_read_per_join": "16"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`relationtags`.`v` = 'cafe') and (`nyosm`.`relationtags`.`k` = 'amenity'))"
            }
          }
        }
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

root@localhost [nyosm]>

explain10

manual:
http://dev.mysql.com/doc/refman/5.7/en/cost-model.html

MySQL 5.6

root@localhost [(none)]>SHOW VARIABLES like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.01 sec)

MySQL5.7

root@localhost [(none)]>

root@localhost [nyosm]>SHOW VARIABLES like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on
1 row in set (0.04 sec)

root@localhost [nyosm]>

データロード中のパフォーマンス確認メモ
検証データ

データ量

root@localhost [nyosm]>select
    ->  table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
    ->  floor((data_length+index_length)/1024/1024) as 'ToTal(MB)',
    ->  floor((data_length)/1024/1024) as 'DATA(MB)',
    ->  floor((index_length)/1024/1024) as 'INDEX(MB)'
    ->  from information_schema.tables
    ->  where table_schema=database()
    ->  order by (data_length+index_length) desc;
+-----------------+--------+----------+------+-----------+----------+-----------+
| table_name      | engine | tbl_rows | rlen | ToTal(MB) | DATA(MB) | INDEX(MB) |
+-----------------+--------+----------+------+-----------+----------+-----------+
| nodes           | InnoDB |  5145519 |  116 |       671 |      571 |       100 |
| waytags         | InnoDB |  4751170 |   57 |       630 |      262 |       367 |
| waynodes        | InnoDB |  6761106 |   49 |       454 |      319 |       134 |
| nodetags        | InnoDB |   651766 |   60 |        93 |       37 |        55 |
| ways            | InnoDB |   866342 |   89 |        91 |       73 |        17 |
| relationtags    | InnoDB |    28886 |   91 |         7 |        2 |         4 |
| relationmembers | InnoDB |    72882 |   64 |         6 |        4 |         1 |
| relations       | InnoDB |     4748 |   89 |         0 |        0 |         0 |
+-----------------+--------+----------+------+-----------+----------+-----------+
8 rows in set (0.19 sec)

root@localhost [nyosm]>

HTOPにてシステムリソース確認
htop

データロード中


root@localhost [sys]>show full processlist;
+----+------+-----------+-------+---------+------+------------------+----------------------------------------------------------------+
| Id | User | Host      | db    | Command | Time | State            | Info                                                           |
+----+------+-----------+-------+---------+------+------------------+----------------------------------------------------------------+
|  7 | root | localhost | sys   | Query   |    0 | System lock      | show full processlist                                          |
|  9 | root | localhost | nyosm | Query   |   48 | Reading from net | LOAD DATA LOCAL INFILE '/tmp/waynodes.txt' INTO TABLE waynodes |
+----+------+-----------+-------+---------+------+------------------+----------------------------------------------------------------+
2 rows in set (0.13 sec)

root@localhost [sys]>select * from sys.innodb_buffer_stats_by_table order by data desc limit 0,10;
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name        | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| nyosm         | waynodes           | 91.73 MiB | 85.43 MiB |  5871 |         5871 |      5871 |     1382935 |
| mysql         | innodb_index_stats | 16.00 KiB | 11.64 KiB |     1 |            1 |         1 |         126 |
| mysql         | innodb_table_stats | 16.00 KiB | 1.95 KiB  |     1 |            1 |         1 |          32 |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
3 rows in set (0.90 sec)

root@localhost [sys]>

データLOAD終了
INFO: finished after 0 hours, 23 minutes and 2 seconds

root@localhost [sys]>show full processlist;
+----+------+-----------+------+---------+------+-------------+-----------------------+
| Id | User | Host      | db   | Command | Time | State       | Info                  |
+----+------+-----------+------+---------+------+-------------+-----------------------+
|  7 | root | localhost | sys  | Query   |    0 | System lock | show full processlist |
+----+------+-----------+------+---------+------+-------------+-----------------------+
1 row in set (0.03 sec)

root@localhost [sys]>select * from sys.innodb_buffer_stats_by_table order by data desc limit 0,10;
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name        | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| nyosm         | waynodes           | 79.41 MiB  | 73.97 MiB  |  5082 |         5082 |      5082 |     1204526 |
| InnoDB System | SYS_INDEXES        | 16.00 KiB  | 7.85 KiB   |     1 |            1 |         1 |         113 |
| InnoDB System | SYS_TABLES         | 32.00 KiB  | 7.81 KiB   |     2 |            2 |         2 |          66 |
| InnoDB System | SYS_FIELDS         | 16.00 KiB  | 5.59 KiB   |     1 |            1 |         1 |         131 |
| nyosm         | relationmembers    | 5.69 MiB   | 5.24 MiB   |   364 |          364 |       364 |       73607 |
| nyosm         | relations          | 544.00 KiB | 449.98 KiB |    34 |           34 |        34 |        4770 |
| nyosm         | relationtags       | 4.08 MiB   | 3.11 MiB   |   261 |          261 |       261 |       28638 |
| InnoDB System | SYS_DATAFILES      | 16.00 KiB  | 3.01 KiB   |     1 |            1 |         1 |          57 |
| InnoDB System | SYS_TABLESPACES    | 16.00 KiB  | 2.96 KiB   |     1 |            1 |         1 |          57 |
| mysql         | innodb_table_stats | 16.00 KiB  | 2.14 KiB   |     1 |            1 |         1 |          35 |
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
10 rows in set (0.09 sec)

root@localhost [sys]>

MySQL5.6では、Performance SchemaはDefaultでONになっている。
このスキーマを利用して、色々とMySQLの状態を確認する事が出来る。

例)ストレージエンジン,クライアント種類,効率の悪いIndex,時間のかかっているQuery

root@localhost [mysql]>SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

root@localhost [mysql]>SELECT * FROM information_schema.ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

root@localhost [mysql]>

root@localhost [performance_schema]>select * from session_connect_attrs;
+----------------+-----------------+----------------+------------------+
| PROCESSLIST_ID | ATTR_NAME       | ATTR_VALUE     | ORDINAL_POSITION |
+----------------+-----------------+----------------+------------------+
|              1 | _os             | linux-glibc2.5 |                0 |
|              1 | _client_name    | libmysql       |                1 |
|              1 | _pid            | 2638           |                2 |
|              1 | _client_version | 5.6.21         |                3 |
|              1 | _platform       | x86_64         |                4 |
|              3 | _os             | linux-glibc2.5 |                0 |
|              3 | _client_name    | libmysql       |                1 |
|              3 | _pid            | 2882           |                2 |
|              3 | _client_version | 5.6.21         |                3 |
|              3 | _platform       | x86_64         |                4 |
|              3 | program_name    | mysql          |                5 |
+----------------+-----------------+----------------+------------------+
11 rows in set (0.00 sec)

root@localhost [performance_schema]>

root@localhost [performance_schema]>SELECT object_schema, object_name, index_name
    ->  FROM performance_schema.table_io_waits_summary_by_index_usage
    ->  WHERE index_name IS NOT NULL
    ->  AND count_star = 0
    ->  AND OBJECT_SCHEMA = 'test'
    ->  ORDER BY object_schema, object_name;
+---------------+-----------------+------------+
| object_schema | object_name     | index_name |
+---------------+-----------------+------------+
| test          | federated_table | PRIMARY    |
| test          | federated_table | name       |
| test          | MyISAM_InnoDB   | PRIMARY    |
| test          | Personal_Info   | PRIMARY    |
| test          | Personal_Info0  | PRIMARY    |
| test          | Personal_Info1  | PRIMARY    |
+---------------+-----------------+------------+
6 rows in set (0.01 sec)
root@localhost [performance_schema]>

root@localhost [performance_schema]>SELECT PROCESSLIST_ID AS id, 
    ->         PROCESSLIST_USER AS user, 
    ->         PROCESSLIST_HOST AS host, 
    ->         PROCESSLIST_DB AS db, 
    ->         PROCESSLIST_COMMAND AS command, 
    ->         PROCESSLIST_TIME AS time, 
    ->         PROCESSLIST_STATE AS state, 
    ->         LEFT(PROCESSLIST_INFO, 80) AS info
    ->  FROM performance_schema.threads
    ->  WHERE PROCESSLIST_ID IS NOT NULL
    ->  AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
    ->  ORDER BY PROCESSLIST_TIME ASC;
+------+---------------+-----------+--------------------+------------------+------+------------------------------------------------------------------+----------------------------------------------------------------------------------+
| id   | user          | host      | db                 | command          | time | state                                                            | info                                                                             |
+------+---------------+-----------+--------------------+------------------+------+------------------------------------------------------------------+----------------------------------------------------------------------------------+
|    3 | root          | localhost | performance_schema | Query            |    0 | Creating sort index                                              | SELECT PROCESSLIST_ID AS id,PROCESSLIST_USER AS user,PROCESS                     |
|    1 | GTID_SSL_USER | CentOS02  | NULL               | Binlog Dump GTID | 4743 | Master has sent all binlog to slave; waiting for binlog to be up | NULL                                                                             |
+------+---------------+-----------+--------------------+------------------+------+------------------------------------------------------------------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [performance_schema]>


MySQL待機イベントの確認方法
performance_schemaはそれなりにメモリーも使うので、すべてのデータ取得を常にしている訳ではありません。
特定の値を調べたい場合は、setup_consumersの値をONにして調査して終了後に再度NOにすると良いでしょう。


root@localhost [performance_schema]>select * from performance_schema.setup_consumers WHERE name like 'events_waits%';
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| events_waits_current      | NO      |
| events_waits_history      | NO      |
| events_waits_history_long | NO      |
+---------------------------+---------+
3 rows in set (0.00 sec)

root@localhost [performance_schema]>UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

root@localhost [performance_schema]>select * from performance_schema.setup_consumers WHERE name like 'events_waits%';
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| events_waits_current      | YES     |
| events_waits_history      | YES     |
| events_waits_history_long | YES     |
+---------------------------+---------+
3 rows in set (0.00 sec)

root@localhost [performance_schema]>


root@localhost [performance_schema]>select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name
    -> where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 10;
Empty set (0.01 sec)

root@localhost [performance_schema]>select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name 
    -> where COUNT_STAR > 0 and event_name not in ('idle') order by sum_timer_wait desc limit 10;
+--------------------------------------+------------+----------------+
| event_name                           | count_star | sum_timer_wait |
+--------------------------------------+------------+----------------+
| wait/io/file/innodb/innodb_data_file |        636 |  2851389865555 |
| wait/io/file/sql/FRM                 |       1197 |   582446988970 |
| wait/io/file/sql/binlog              |        756 |   389156664435 |
| wait/io/file/innodb/innodb_log_file  |         24 |   280608815795 |
| wait/io/file/csv/data                |        866 |   133876420370 |
| wait/io/file/myisam/kfile            |        157 |   118736606065 |
| wait/io/file/csv/metadata            |         28 |    79348938130 |
| wait/io/file/sql/slow_log            |          4 |    63380202270 |
| wait/io/file/sql/binlog_index        |         30 |    45983365505 |
| wait/io/file/sql/query_log           |          4 |    38931406360 |
+--------------------------------------+------------+----------------+
10 rows in set (0.01 sec)

root@localhost [performance_schema]>

root@localhost [performance_schema]>UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE name like 'events_waits%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

root@localhost [performance_schema]>select * from performance_schema.setup_consumers WHERE name like 'events_waits%';
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| events_waits_current      | NO      |
| events_waits_history      | NO      |
| events_waits_history_long | NO      |
+---------------------------+---------+
3 rows in set (0.00 sec)

root@localhost [performance_schema]>