MySQL8.0 SYSスキーマの確認。 
基本的には、MySQL5.7と変わりは無いですが、ロックを取得するVIEW内部のテーブルがInformation_schemaからPerformance_Schemaに変更になったりしている。但し、VIEW内部のスクリプト変更なので基本的に問題無く、これまで通り利用する事が可能です。

オフィシャルマニュアル: Chapter 26 MySQL sys Schema

確認したバージョン


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

root@localhost [mysql]> 

ドキュメント


SYSスキーマの説明をする機会があったので、改めてMySQL5.7.21でSYSスキーマに関しての概要をまとめたのでご紹介。
Performance_Schema, Information_Schemaを直接確認しないと取得出来無い情報もまだあるけれど、SYSスキーマを利用すれば簡単にMySQLの状態を確認出来、複雑なクエリーを使わないでもロックの状態、メモリーの状態、未使用のインデックス、起動してからの累積値だけれども遅いクエリー等が確認可能です。まだまだ使われていないユーザーも多いけど、便利なので是非活用下さい。

MySQL5.7.21の時点では以下のオブジェクトが存在します。


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

以下のスライドにまとめたので、詳細をご確認下さい。


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

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

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

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

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

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

root@localhost [sys]> 

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

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

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

echo ""

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

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

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

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

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

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

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

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

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

root@localhost [sys]> 

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

(1) 実行スクリプト

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

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

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

(2) 実行と実行結果

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

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

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

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

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

root@localhost [sys]> 

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


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

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

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

root@localhost [sys]> 


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

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

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

root@localhost [sys]>


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

root@localhost [world]> 


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

root@localhost [sys]> 

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

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

root@localhost [world]> 



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

root@localhost [sys]>

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

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

root@localhost [sys]>


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

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

root@localhost [sys]> 

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

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

root@localhost [world]> 

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

root@localhost [sys]>

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

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

root@localhost [sys]>


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

root@localhost [world]> 

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

root@localhost [sys]> 

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

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

root@localhost [world]> 


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

root@localhost [sys]> 

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

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


SYS Schema on MySQLの再確認
SYS Schemaは元々ps_helperという名前だったが、Oracle、MS SQLと同じように
分かり易くする為にSYSにしたとの事。

The MySQL SYS Schema

インストール方法
以下の2つのうちどちらか選択
1) Workbench6.1以上~
2) GitからScriptをダウンロード

root@localhost [sys]>select * from sys.version;
+-------------+-------------------------------------------+
| sys_version | mysql_version                             |
+-------------+-------------------------------------------+
| 1.1.0       | 5.6.22-enterprise-commercial-advanced-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 | FUNCTION    |    11 |
| sys | VIEW        |    78 |
| sys | PROCEDURE   |    22 |
+-----+-------------+-------+
3 rows in set, 3 warnings (0.40 sec)

root@localhost [sys]>

x$はraw view (for tools to poll)
formatted viewはhumans and command lineの為に準備してある。(成形してある)

■■ user / Host Summary View
IO Usage
Stages
Statement Details

root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
                 user: background
           statements: NULL
    statement_latency: NULL
statement_avg_latency: 0 ps
          table_scans: NULL
             file_ios: 1728
      file_io_latency: 2.10 s
  current_connections: 18
    total_connections: 21
         unique_hosts: 0
*************************** 2. row ***************************
                 user: root
           statements: 90
    statement_latency: 1.37 s
statement_avg_latency: 15.22 ms
          table_scans: 6
             file_ios: 72340
      file_io_latency: 2.59 s
  current_connections: 1
    total_connections: 1
         unique_hosts: 1
2 rows in set (0.01 sec)

root@localhost [sys]>

■5.7では、メモリーの確認も出来る。

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

root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
                  user: root
            statements: 171
     statement_latency: 569.90 ms
 statement_avg_latency: 3.33 ms
           table_scans: 5
              file_ios: 1987
       file_io_latency: 00:03:05.67
   current_connections: 1
     total_connections: 1
          unique_hosts: 1
        current_memory: 0 bytes
total_memory_allocated: 0 bytes
*************************** 2. row ***************************
                  user: background
            statements: 0
     statement_latency: 0 ps
 statement_avg_latency: 0 ps
           table_scans: 0
              file_ios: 1940
       file_io_latency: 1.59 s
   current_connections: 21
     total_connections: 23
          unique_hosts: 0
        current_memory: 0 bytes <--- 5.7.5以上で追加された。
total_memory_allocated: 0 bytes <--- 5.7.5以上で追加された。
2 rows in set (0.01 sec)

root@localhost &#91;sys&#93;>

■ユーザ毎にIOを確認出来る

root@localhost [sys]>select * from user_summary_by_file_io_type;
+------------+--------------------------------------+-------+-----------+-------------+
| user       | event_name                           | total | latency   | max_latency |
+------------+--------------------------------------+-------+-----------+-------------+
| background | wait/io/file/innodb/innodb_data_file |   608 | 798.30 ms | 37.70 ms    |
| background | wait/io/file/sql/FRM                 |   910 | 789.77 ms | 195.80 ms   |
| background | wait/io/file/innodb/innodb_log_file  |    18 | 304.28 ms | 172.62 ms   |
| background | wait/io/file/sql/slow_log            |     4 | 65.20 ms  | 65.18 ms    |
| background | wait/io/file/sql/binlog              |    32 | 45.77 ms  | 19.03 ms    |
| background | wait/io/file/myisam/kfile            |    67 | 27.08 ms  | 7.97 ms     |
| background | wait/io/file/mysys/cnf               |     5 | 21.57 ms  | 21.51 ms    |
| background | wait/io/file/sql/binlog_index        |    15 | 20.14 ms  | 14.04 ms    |
| background | wait/io/file/mysys/charset           |     3 | 13.95 ms  | 13.87 ms    |
| background | wait/io/file/sql/ERRMSG              |     5 | 10.38 ms  | 7.82 ms     |
| background | wait/io/file/myisam/dfile            |    42 | 4.91 ms   | 1.40 ms     |
| background | wait/io/file/sql/query_log           |     4 | 1.30 ms   | 1.26 ms     |
| background | wait/io/file/sql/casetest            |    10 | 473.97 us | 275.72 us   |
| background | wait/io/file/sql/pid                 |     3 | 197.44 us | 167.97 us   |
| background | wait/io/file/sql/global_ddl_log      |     2 | 13.67 us  | 11.35 us    |
| root       | wait/io/file/csv/data                | 69443 | 1.35 s    | 277.94 ms   |
| root       | wait/io/file/csv/metadata            |    63 | 535.14 ms | 414.93 ms   |
| root       | wait/io/file/sql/FRM                 |   676 | 379.57 ms | 69.12 ms    |
| root       | wait/io/file/myisam/kfile            |  1458 | 188.04 ms | 49.70 ms    |
| root       | wait/io/file/myisam/dfile            |   532 | 137.41 ms | 66.26 ms    |
| root       | wait/io/file/sql/file_parser         |   156 | 3.52 ms   | 117.48 us   |
| root       | wait/io/file/sql/dbopt               |    16 | 494.32 us | 132.02 us   |
| root       | wait/io/file/innodb/innodb_data_file |     2 | 24.08 us  | 13.14 us    |
+------------+--------------------------------------+-------+-----------+-------------+
23 rows in set (0.00 sec)

root@localhost [sys]>

■STATEMENT_LATENCYなどを確認出来る。FULL_SCANなども確認可能。

root@localhost [sys]>select * from user_summary_by_statement_latency\G
*************************** 1. row ***************************
         user: root
        total: 92
total_latency: 1.37 s
  max_latency: 952.59 ms
 lock_latency: 934.38 ms
    rows_sent: 203
rows_examined: 2968
rows_affected: 0
   full_scans: 8
1 row in set (0.00 sec)

root@localhost [sys]>

■ステートメントのタイプ毎に状況が確認出来る

root@localhost [sys]>select * from user_summary_by_statement_type\G
*************************** 1. row ***************************
         user: root
    statement: select
        total: 10
total_latency: 839.20 ms
  max_latency: 766.46 ms
 lock_latency: 803.75 ms
    rows_sent: 36
rows_examined: 3133
rows_affected: 0
   full_scans: 5
*************************** 2. row ***************************
         user: root
    statement: Field List
        total: 78
total_latency: 463.95 ms
  max_latency: 114.47 ms
 lock_latency: 130.78 ms
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0

■IO処理とProcesslist_idが確認出来る。(Current)

root@localhost [sys]>select * from io_by_thread_by_latency\G
*************************** 1. row ***************************
          user: root@localhost
         total: 72370
 total_latency: 2.59 s
   min_latency: 4.68 ns
   avg_latency: 1.19 ms
   max_latency: 414.93 ms
     thread_id: 21
processlist_id: 1
*************************** 2. row ***************************
          user: main
         total: 1710
 total_latency: 1.85 s
   min_latency: 24.96 ns
   avg_latency: 2.71 ms
   max_latency: 195.80 ms
     thread_id: 1
processlist_id: NULL

■どのファイル処理に時間がかかっているか確認出来る

root@localhost [sys]>select * from io_global_by_file_by_latency limit 2\G
*************************** 1. row ***************************
         file: /usr/local/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/data/mysql/general_log.CSV
        total: 64611
total_latency: 1.21 s
   count_read: 32253
 read_latency: 1.14 s
  count_write: 101
write_latency: 1.18 ms
   count_misc: 32257
 misc_latency: 66.27 ms
*************************** 2. row ***************************
         file: /usr/local/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/data/ibdata1
        total: 532
total_latency: 706.08 ms
   count_read: 525
 read_latency: 696.01 ms
  count_write: 2
write_latency: 87.71 us
   count_misc: 5
 misc_latency: 9.98 ms
2 rows in set (0.00 sec)

root@localhost [sys]>

■■ Analysis Views
Object Overview
Table Usage
Index Usage
Show users only
schema_*

root@localhost [sys]>select * from schema_table_statistics limit 1\G
*************************** 1. row ***************************
     table_schema: mysql
       table_name: plugin
    total_latency: 0 ps
     rows_fetched: 0
    fetch_latency: 0 ps
    rows_inserted: 0
   insert_latency: 0 ps
     rows_updated: 0
   update_latency: 0 ps
     rows_deleted: 0
   delete_latency: 0 ps
 io_read_requests: 10     <---↓ IOの状況も確認可能
          io_read: 1.46 KiB
  io_read_latency: 33.01 ms
io_write_requests: 0
         io_write: 0 bytes
 io_write_latency: 0 ps
 io_misc_requests: 14
  io_misc_latency: 101.93 us
1 row in set (0.02 sec)

root@localhost &#91;sys&#93;>

root@localhost [sys]>select * from schema_index_statistics limit 1\G
*************************** 1. row ***************************
  table_schema: sys
    table_name: sys_config
    index_name: PRIMARY
 rows_selected: 0
select_latency: 0 ps
 rows_inserted: 0
insert_latency: 0 ps
  rows_updated: 0
update_latency: 0 ps
  rows_deleted: 0
delete_latency: 0 ps
1 row in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from schema_tables_with_full_table_scans;
+---------------+-------------+-------------------+
| object_schema | object_name | rows_full_scanned |
+---------------+-------------+-------------------+
| test          | T01_BKUP    |                 5 |
| test          | demo_test   |                 5 |
+---------------+-------------+-------------------+
2 rows in set (0.00 sec)

root@localhost [sys]>

InnoDBはインスタンス、MyISAM I/Oはtemporary table IOでの待ちを示している

root@localhost [sys]>select * from waits_global_by_latency;
+--------------------------------------+-------+---------------+-------------+-------------+
| events                               | total | total_latency | avg_latency | max_latency |
+--------------------------------------+-------+---------------+-------------+-------------+
| wait/io/file/csv/data                | 69530 | 1.35 s        | 19.37 us    | 277.94 ms   |
| wait/io/file/sql/FRM                 |  1664 | 1.17 s        | 704.45 us   | 195.80 ms   |
| wait/io/file/innodb/innodb_data_file |   625 | 855.73 ms     | 1.37 ms     | 37.70 ms    |
| wait/io/file/csv/metadata            |    63 | 535.14 ms     | 8.49 ms     | 414.93 ms   |
| wait/io/file/innodb/innodb_log_file  |    24 | 327.86 ms     | 13.66 ms    | 172.62 ms   |
| wait/io/file/myisam/kfile            |  1591 | 215.86 ms     | 135.68 us   | 49.70 ms    |
| wait/io/file/myisam/dfile            |   604 | 143.02 ms     | 236.79 us   | 66.26 ms    |
| wait/io/file/sql/slow_log            |    10 | 68.33 ms      | 6.83 ms     | 65.18 ms    |
| wait/io/file/sql/binlog              |    46 | 65.30 ms      | 1.42 ms     | 19.03 ms    |
| wait/io/table/sql/handler            |    11 | 30.13 ms      | 2.74 ms     | 29.94 ms    |
| wait/io/file/sql/binlog_index        |    31 | 29.84 ms      | 962.71 us   | 14.04 ms    |
| wait/io/file/mysys/cnf               |     5 | 21.57 ms      | 4.31 ms     | 21.51 ms    |
| wait/io/file/mysys/charset           |     3 | 13.95 ms      | 4.65 ms     | 13.87 ms    |
| wait/io/file/sql/ERRMSG              |     5 | 10.38 ms      | 2.08 ms     | 7.82 ms     |
| wait/io/file/sql/query_log           |    10 | 2.13 ms       | 213.12 us   | 1.26 ms     |
| wait/io/file/sql/file_parser         |    78 | 653.66 us     | 8.38 us     | 21.38 us    |
| wait/io/file/sql/dbopt               |    16 | 494.32 us     | 30.89 us    | 132.02 us   |
| wait/io/file/sql/casetest            |    10 | 473.97 us     | 47.40 us    | 275.72 us   |
| wait/io/file/sql/pid                 |     3 | 197.44 us     | 65.81 us    | 167.97 us   |
| wait/io/file/sql/global_ddl_log      |     2 | 13.67 us      | 6.84 us     | 11.35 us    |
| wait/lock/table/sql/handler          |     8 | 4.71 us       | 588.12 ns   | 1.13 us     |
+--------------------------------------+-------+---------------+-------------+-------------+
21 rows in set (0.01 sec)

root@localhost [sys]>

ユーザー毎のファイルIOが確認出来る

root@localhost [sys]>root@localhost [sys]>select * from waits_by_user_by_latency;
+------+--------------------------------------+-------+---------------+-------------+-------------+
| user | event                                | total | total_latency | avg_latency | max_latency |
+------+--------------------------------------+-------+---------------+-------------+-------------+
| root | wait/io/file/csv/data                | 69536 | 1.35 s        | 19.37 us    | 277.94 ms   |
| root | wait/io/file/csv/metadata            |    63 | 535.14 ms     | 8.49 ms     | 414.93 ms   |
| root | wait/io/file/sql/FRM                 |   676 | 379.57 ms     | 561.49 us   | 69.12 ms    |
| root | wait/io/file/myisam/kfile            |  1524 | 188.78 ms     | 123.87 us   | 49.70 ms    |
| root | wait/io/file/myisam/dfile            |   562 | 138.12 ms     | 245.76 us   | 66.26 ms    |
| root | wait/io/table/sql/handler            |    11 | 30.13 ms      | 2.74 ms     | 29.94 ms    |
| root | wait/io/file/innodb/innodb_data_file |    10 | 29.85 ms      | 2.99 ms     | 29.62 ms    |
| root | wait/io/file/sql/binlog              |    14 | 19.53 ms      | 1.40 ms     | 12.48 ms    |
| root | wait/io/file/sql/binlog_index        |    16 | 9.70 ms       | 606.37 us   | 6.12 ms     |
| root | wait/io/file/innodb/innodb_log_file  |     2 | 3.96 ms       | 1.98 ms     | 3.94 ms     |
| root | wait/io/file/sql/file_parser         |   156 | 3.52 ms       | 22.54 us    | 117.48 us   |
| root | wait/io/file/sql/slow_log            |     6 | 3.13 ms       | 521.60 us   | 3.05 ms     |
| root | wait/io/file/sql/query_log           |     6 | 830.11 us     | 138.35 us   | 793.00 us   |
| root | wait/io/file/sql/dbopt               |    16 | 494.32 us     | 30.89 us    | 132.02 us   |
| root | wait/lock/table/sql/handler          |     8 | 4.71 us       | 588.12 ns   | 1.13 us     |
+------+--------------------------------------+-------+---------------+-------------+-------------+
15 rows in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from statement_analysis limit 2\G
*************************** 1. row ***************************
            query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
               db: sys
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 3
    total_latency: 766.46 ms
      max_latency: 766.46 ms
      avg_latency: 766.46 ms
     lock_latency: 760.44 ms
        rows_sent: 3
    rows_sent_avg: 3
    rows_examined: 374
rows_examined_avg: 374
       tmp_tables: 189 <--- Temp Tableの利用が確認出来る
  tmp_disk_tables: 33  <--- Temp Disk Tableの利用が確認出来る
      rows_sorted: 22
sort_merge_passes: 0
           digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
       first_seen: 2015-02-07 22:01:36
        last_seen: 2015-02-07 22:01:36
*************************** 2. row ***************************
            query: SELECT `performance_schema` .  ... a` . `accounts` . `HOST` = ...
               db: sys
        full_scan: 
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 114.47 ms
      max_latency: 114.47 ms
      avg_latency: 114.47 ms
     lock_latency: 106.12 ms
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 0
rows_examined_avg: 0
       tmp_tables: 5
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 46f73fffb370a0c58fe74b1f0c71f85b
       first_seen: 2015-02-07 21:55:46
        last_seen: 2015-02-07 21:55:46
2 rows in set (0.00 sec)

root@localhost &#91;sys&#93;>

■SQLステートメントのエラー回数などが確認出来る

root@localhost [sys]>select * from statements_with_errors_or_warnings limit 3\G
*************************** 1. row ***************************
      query: `switch` 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:30:18
  last_seen: 2015-02-07 22:30:18
     digest: 1a34fb3cdd7b61d8f9a688cff4d8ef1b
*************************** 2. row ***************************
      query: FLUSH `logsl` 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:30:43
  last_seen: 2015-02-07 22:30:43
     digest: ff6a6729c6f04b36d5f57c0cf1bacfd0
*************************** 3. row ***************************
      query: SELECT * FROM `statemanet_analysis` LIMIT ? 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:42:18
  last_seen: 2015-02-07 22:42:18
     digest: 577ed5b5d68f1e1cad11c030187cc58c
3 rows in set (0.00 sec)

root@localhost [sys]>

■ SQL StatementのTemp Tableの利用状況が確認出来る。

root@localhost [sys]>select * from statements_with_temp_tables limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
                      db: sys
              exec_count: 1
           total_latency: 766.46 ms
       memory_tmp_tables: 189
         disk_tmp_tables: 33
avg_tmp_tables_per_query: 189
  tmp_tables_to_disk_pct: 17
              first_seen: 2015-02-07 22:01:36
               last_seen: 2015-02-07 22:01:36
                  digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
1 row in set (0.00 sec)

root@localhost [sys]>

■SQLステートメントのFULLスキャンが確認出来る。

root@localhost [sys]>select * from statements_with_full_table_scans limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
                      db: sys
              exec_count: 1
           total_latency: 766.46 ms
     no_index_used_count: 1
no_good_index_used_count: 0
       no_index_used_pct: 100
               rows_sent: 3
           rows_examined: 374
           rows_sent_avg: 3
       rows_examined_avg: 374
              first_seen: 2015-02-07 22:01:36
               last_seen: 2015-02-07 22:01:36
                  digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
1 row in set (0.00 sec)

root@localhost [sys]>

■SHOW PROCESSLISTより多くの情報を確認出来る。

root@localhost [sys]>select * from processlist limit 2\G
*************************** 1. row ***************************
                thd_id: 1
               conn_id: NULL
                  user: sql/main
                    db: NULL
               command: NULL
                 state: System lock
                  time: 3410
     current_statement: INTERNAL DDL LOG RECOVER IN PROGRESS
          lock_latency: NULL
         rows_examined: NULL
             rows_sent: NULL
         rows_affected: NULL
            tmp_tables: NULL
       tmp_disk_tables: NULL
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
*************************** 2. row ***************************
                thd_id: 21
               conn_id: 1
                  user: root@localhost
                    db: sys
               command: Query
                 state: Sending data
                  time: 0
     current_statement: select * from processlist limit 2
          lock_latency: 283.00 us
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 2
       tmp_disk_tables: 0
             full_scan: YES
        last_statement: NULL
last_statement_latency: NULL
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
2 rows in set (0.00 sec)

root@localhost [sys]>

■ Functionsでデータを自動的に読みやすく変換してくれる。

root@localhost [sys]>select format_time(232222345) as time
    -> union select format_time(8323232323)
    -> union select format_time(8323232323234566);
+-----------+
| time      |
+-----------+
| 232.22 us |
| 8.32 ms   |
| 2.31h     |
+-----------+
3 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select format_bytes(23456) as bytes
    -> union select format_bytes(23456789);
+-----------+
| bytes     |
+-----------+
| 22.91 KiB |
| 22.37 MiB |
+-----------+
2 rows in set (0.00 sec)

root@localhost [sys]>

Theadの情報をDUMPする事が可能

root@localhost [sys]>call ps_trace_thread(1,'/tmp/stack_1.dot',60,0.1,true,true,true);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

+--------------------------------------------+
| Info                                       |
+--------------------------------------------+
| Data collection starting for THREAD_ID = 1 |
+--------------------------------------------+
+-----------------------------------------+
| Info                                    |
+-----------------------------------------+
| Stack trace written to /tmp/stack_1.dot |
+-----------------------------------------+
1 row in set (4.07 sec)

+------------------------------------------------+
| Convert to PDF                                 |
+------------------------------------------------+
| dot -Tpdf -o /tmp/stack_1.pdf /tmp/stack_1.dot |
+------------------------------------------------+
1 row in set (4.07 sec)

+------------------------------------------------+
| Convert to PNG                                 |
+------------------------------------------------+
| dot -Tpng -o /tmp/stack_1.png /tmp/stack_1.dot |
+------------------------------------------------+
1 row in set (4.07 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (4.19 sec)

Query OK, 0 rows affected (4.19 sec)

root@localhost [sys]>

——メモ: GraphやPDF化するには以下のソフトが必要—–
PDF作成

[root@misc admin]# dot -Tpdf -o /tmp/stack_2.pdf /tmp/stack_2.dot
[root@misc admin]# ls -l /tmp/
合計 12
srwxrwxrwx. 1 mysql mysql   0  2月  7 21:55 mysql.sock
-rw-rw-rw-. 1 mysql mysql 293  2月  7 23:15 stack_1.dot
-rw-rw-rw-. 1 mysql mysql 293  2月  7 23:21 stack_2.dot
-rw-r--r--. 1 root  root  968  2月  7 23:30 stack_2.pdf
[root@misc admin]# 

以下、インストールログ


[root@misc admin]# wget http://www.graphviz.org/graphviz-rhel.repo
--2015-02-07 23:26:22--  http://www.graphviz.org/graphviz-rhel.repo
www.graphviz.org (www.graphviz.org) をDNSに問いあわせています... 204.178.9.49
www.graphviz.org (www.graphviz.org)|204.178.9.49|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1138 (1.1K) [text/plain]
`graphviz-rhel.repo' に保存中

100%[==============================================================================================================>] 1,138       --.-K/s 時間 0.003s  

2015-02-07 23:26:25 (321 KB/s) - `graphviz-rhel.repo' へ保存完了 [1138/1138]

[root@misc admin]# ls -l
合計 320
drwx------. 4 root  root     92  1月 24 22:59 backup
drwx------. 4 root  root     92  1月 25 21:15 backup0125manual
-rwxr-xr-x. 1 root  root 299008  1月  5 20:32 cpanm
-rw-r--r--. 1 root  root   1138  2月 17  2012 graphviz-rhel.repo
-rwxr-xr-x. 1 root  root    642  1月  5 14:18 memcached_fast.pl
-rwxr-xr-x. 1 root  root    316  1月  5 21:03 memcached_mysql.pl
drwxrwxrwx. 4 mysql root     92  1月 24 23:37 mysqlbackup
-rw-r--r--. 1 root  root  12582  3月 21  2013 rpmforge-release-0.5.3-1.el5.rf.x86_64.rpm

[root@misc admin]# yum install 'graphviz*'
読み込んだプラグイン:fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.riken.jp
 * extras: ftp.riken.jp
 * rpmforge: ftp.kddilabs.jp
 * updates: ftp.riken.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ graphviz.x86_64 0:2.30.1-18.el7 を インストール
--> 依存性の処理をしています: urw-fonts のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: librsvg-2.so.2()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: libpng15.so.15()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: libpangoft2-1.0.so.0()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64


[root@misc admin]# rpm -qa |grep -i graphviz
graphviz-2.30.1-18.el7.x86_64
graphviz-perl-2.30.1-18.el7.x86_64
graphviz-ocaml-2.30.1-18.el7.x86_64
graphviz-gd-2.30.1-18.el7.x86_64
graphviz-php-2.30.1-18.el7.x86_64
graphviz-guile-2.30.1-18.el7.x86_64
graphviz-graphs-2.30.1-18.el7.x86_64
graphviz-devel-2.30.1-18.el7.x86_64
graphviz-lua-2.30.1-18.el7.x86_64
graphviz-ruby-2.30.1-18.el7.x86_64
graphviz-doc-2.30.1-18.el7.x86_64
graphviz-tcl-2.30.1-18.el7.x86_64
graphviz-python-2.30.1-18.el7.x86_64
graphviz-java-2.30.1-18.el7.x86_64
[root@misc admin]# 

Sys Schema for MySQL 5.6 and MySQL 5.7

http://wiki.ducca.org/wiki/graphviz_%E3%81%AE%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB


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