MySQL5.7までのSHOW SLAVE STATUSだけでは分からない事が多かったけど、MySQL8.0のSHOW SLAVE STATUSは少し改善されていた。
マスター側で負荷をかけて、スレーブの状態を確認した時にスレーブ側で”Systetm lock”という状態になっていて、詳細を確認する為にPerformance Schemaを確認してみた。
MySQL8.0からはPerformance_Schemaを確認しなくても”Slave_SQL_Running_State”で状態が確認出来るようになっている。
以下、MySQL8.0で確認したログですが、MySQL5.7では”System lock”だった状態が、MySQL8.0では”Applying batch of row changes (update)”になっています。

[admin@misc02 ~]$ cat repli_log | grep Slave_SQL_Running_State
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Applying batch of row changes (update)
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
[admin@misc02 ~]$

詳細: WL#7364: RBR: Enhanced Applier Thread Progress Details
https://dev.mysql.com/worklog/task/?id=7364

以下、MySQL5.7のケース
マスター側でmysqlslapで負荷をかけると、スレーブ側がsystem lockになっている。
スレーブ側でトランザクション実行して、UPLOCKかけて参照しても、同じsystem lockになる。
これだと、I/O Threadに遅延が無いことは分かるが、Applier Threadの状態が良く分からない。

[ON MASTER] – MySQL5.7

root@localhost [REPLI]> show create table T_SLAP01\G
*************************** 1. row ***************************
       Table: T_SLAP01
Create Table: CREATE TABLE `T_SLAP01` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `n_time` varchar(30) DEFAULT NULL,
  `s_time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [REPLI]> 

[root@misc01 admin]# cat mysqlslap_exsisting_table.sh 
/usr/local/mysql/bin/mysqlslap -u root -p -S /tmp/mysql.sock --create-schema=REPLI --no-drop -c 10 -i 1000 -q 'INSERT INTO REPLI.T_SLAP01(n_time,s_time) values(now(3),sysdate(6))' 
[root@misc01 admin]# 

[root@misc01 admin]# ./mysqlslap_exsisting_table.sh 
Enter password: 
Benchmark
        Average number of seconds to run all queries: 0.046 seconds
        Minimum number of seconds to run all queries: 0.018 seconds
        Maximum number of seconds to run all queries: 0.135 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

[root@misc01 admin]# 

[ON SLAVE]
上記マスターで負荷をかけた場合の、スレーブ側の状態。 “System lock”という状態になっている。ただし、この状態はApplier Threadが止まっている訳では無く、何らかの処理を行っている状態の場合もある。


root@localhost [REPLI]> show processlist; show engine innodb status\G show slave status\G
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
| Id  | User            | Host         | db    | Command | Time | State                            | Info             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
|   1 | system user     |              | NULL  | Connect |  888 | Waiting for master to send event | NULL             |
|   2 | system user     |              | NULL  | Connect |   -1 | System lock                      | NULL             |
|   3 | event_scheduler | localhost    | NULL  | Daemon  |  887 | Waiting on empty queue           | NULL             |
|  13 | root            | localhost    | REPLI | Query   |    0 | starting                         | show processlist |
|  29 | admin           | Labs01:44555 | mysql | Sleep   |    3 |                                  | NULL             |
| 763 | admin           | Labs01:47755 | mysql | Sleep   |   55 |                                  | NULL             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
6 rows in set (0.00 sec)

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2018-03-23 15:32:58 0x7fcdb65f0700 INNODB MONITOR OUTPUT
=====================================
<SNIP>
------------
TRANSACTIONS
------------
Trx id counter 756832
Purge done for trx's n:o < 756831 undo n:o < 0 state: running but idle
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421997346109728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346107904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 756831, ACTIVE (PREPARED) 0 sec
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 140521468139264, query id 69415 System lock

<SNIP>

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=8340, Main thread ID=140522077779712, state: sleeping
Number of rows inserted 31953, updated 30756, deleted 0, read 32044
72.99 inserts/s, 72.99 updates/s, 0.00 deletes/s, 72.99 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002848
          Read_Master_Log_Pos: 59249068
               Relay_Log_File: misc02-relay-bin.000766
                Relay_Log_Pos: 12937379
        Relay_Master_Log_File: mysql-bin.002848
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
<SNIP>
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
<SNIP>
1 row in set (0.00 sec)

root@localhost [REPLI]> 

SlaveのPerfomance_Schemaの状態。
Replication SlaveのIDで確認してみると、statement/sql/beginという状態である事が分かる。

root@localhost [REPLI]> SELECT  event_id, event_name, timer_wait, lock_time FROM performance_schema.events_statements_history_long WHERE thread_id =  @sql_thread;
+----------+---------------------+------------+-----------+
| event_id | event_name          | timer_wait | lock_time |
+----------+---------------------+------------+-----------+
|    10003 | statement/sql/begin |  175076000 |         0 |
+----------+---------------------+------------+-----------+
1 row in set (0.01 sec)


root@localhost [REPLI]> SELECT event_name, count_star, sum_timer_wait FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE thread_id =  @sql_thread AND sum_timer_wait > 0 ORDER BY sum_timer_wait DESC;
+--------------------------------------+------------+----------------+
| event_name                           | count_star | sum_timer_wait |
+--------------------------------------+------------+----------------+
| wait/io/table/sql/handler            |      10002 | 52235951612404 |
| wait/io/file/innodb/innodb_log_file  |      20012 | 24423316510586 |
| wait/io/file/sql/binlog              |      10014 |   372515866246 |
| wait/io/file/innodb/innodb_data_file |         29 |   158702658238 |
| wait/io/file/sql/relaylog            |      12641 |   144454610798 |
| wait/io/file/sql/binlog_index        |         30 |    62816598186 |
| wait/lock/table/sql/handler          |      10003 |    51676533966 |
| wait/io/file/sql/misc                |          3 |      116404090 |
+--------------------------------------+------------+----------------+

SLAVE側のSystem Lockの状態はRelay Logの適用から発生するトランザクションでした。

備考:以下のように、スレーブ側で参照ロックをかけた場合の状況もSystem Lock。
補足:トランザクションでロックをかけたセッションで、SYSスキーマを利用した為、sys.innodb_lock_waitsの値がおかしい。

違うセッションであれば、sys.innodb_lock_waitsも問題無い。

上記はバグでは無いですが、分かり難いのでスクリプト変更しました。(MySQL8.0では利用出来ません)
https://bugs.mysql.com/file.php?id=26694&bug_id=72131

Please Enjoy Replication with MySQL8.0 soon…..


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)

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


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

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

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

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

root@localhost [mysql]>

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

root@localhost [performance_schema]>

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

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

root@localhost [performance_schema]>


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


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

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

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

root@localhost [performance_schema]>


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

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

root@localhost [performance_schema]>

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

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

root@localhost [performance_schema]>


ps_helperはperformance_schemaやinformation_schemaのテーブルを使用して、
FUNCTION 8つ、 PROCEDURE 12つ、VIEW 53つ作成して、
直接、自分で集計するよりも簡単に把握出来るようにしてくれている。

GITからdbahelperを取得させて頂く。


variable.user@myPC /c/git (master)
$ git clone https://github.com/MarkLeith/dbahelper.git dbahelper
Cloning into ‘dbahelper’…
remote: Counting objects: 224, done.
Receiving objecemote: Total 224 (delta 0), reused 0 (delta 0)ts: 71% (160/224),
R
Receiving objects: 100% (224/224), 96.80 KiB | 139.00 KiB/s, done.
Resolving deltas: 100% (92/92), done.
Checking connectivity… done.

variable.user@myPC /c/git (master)
$ cd dbahelper/

variable.user@myPC /c/git/dbahelper (master)
$ “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql” -u root -p < ps_helper_56.sql Enter password: ********* variable.user@myPC /c/git/dbahelper (master) $ [/SHELL] ps_helperデータベースが出来ている事を確認

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| copy_test          |
| mysql              |
| performance_schema |
| ps_helper          |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
9 rows in set (0.00 sec)

mysql> SELECT ROUTINE_SCHEMA AS db, ROUTINE_TYPE AS object_type, COUNT(*) AS count FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'ps_helper' GROUP BY ROUTINE_SCHEMA, ROUTINE_TYPE
    ->  UNION
    -> SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*) FROM INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA = 'ps_helper' GROUP BY TABLE_SCHEMA, TABLE_TYPE
    ->  UNION
    -> SELECT TABLE_SCHEMA, CONCAT('INDEX (', INDEX_TYPE, ')'), COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'ps_helper' GROUP BY TABLE_SCHEMA, INDEX_TYPE
    ->  UNION
    -> SELECT TRIGGER_SCHEMA, 'TRIGGER', COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = 'ps_helper' GROUP BY TRIGGER_SCHEMA
    ->  UNION
    -> SELECT EVENT_SCHEMA, 'EVENT', COUNT(*) FROM INFORMATION_SCHEMA.EVENTS where EVENT_SCHEMA = 'ps_helper' GROUP BY EVENT_SCHEMA ORDER BY DB,OBJECT_TYPE;
+-----------+-------------+-------+
| db        | object_type | count |
+-----------+-------------+-------+
| ps_helper | FUNCTION    |     8 |
| ps_helper | PROCEDURE   |    12 |
| ps_helper | VIEW        |    53 |
+-----------+-------------+-------+
3 rows in set (0.09 sec)

mysql>

mysql> show tables;
+-------------------------------------------------+
| Tables_in_ps_helper                             |
+-------------------------------------------------+
| _digest_95th_percentile_by_avg_us               |
| _digest_avg_latency_by_avg_us                   |
| check_lost_instrumentation                      |
| innodb_buffer_stats_by_schema                   |
| innodb_buffer_stats_by_schema_raw               |
| innodb_buffer_stats_by_table                    |
| innodb_buffer_stats_by_table_raw                |
| io_by_thread_by_latency                         |
| io_by_thread_by_latency_raw                     |
| io_global_by_file_by_bytes                      |
| io_global_by_file_by_bytes_raw                  |
| io_global_by_file_by_latency                    |
| io_global_by_file_by_latency_raw                |
| io_global_by_wait_by_bytes                      |
| io_global_by_wait_by_bytes_raw                  |
| io_global_by_wait_by_latency                    |
| io_global_by_wait_by_latency_raw                |
| latest_file_io                                  |
| latest_file_io_raw                              |
| processlist                                     |
| processlist_raw                                 |
| schema_index_statistics                         |
| schema_index_statistics_raw                     |
| schema_object_overview                          |
| schema_table_statistics                         |
| schema_table_statistics_raw                     |
| schema_table_statistics_with_buffer             |
| schema_table_statistics_with_buffer_raw         |
| schema_tables_with_full_table_scans             |
| schema_unused_indexes                           |
| statement_analysis                              |
| statement_analysis_raw                          |
| statements_with_errors_or_warnings              |
| statements_with_full_table_scans                |
| statements_with_runtimes_in_95th_percentile     |
| statements_with_runtimes_in_95th_percentile_raw |
| statements_with_sorting                         |
| statements_with_temp_tables                     |
| user_summary                                    |
| user_summary_by_stages                          |
| user_summary_by_stages_raw                      |
| user_summary_by_statement_type                  |
| user_summary_by_statement_type_raw              |
| user_summary_raw                                |
| version                                         |
| wait_classes_global_by_avg_latency              |
| wait_classes_global_by_avg_latency_raw          |
| wait_classes_global_by_latency                  |
| wait_classes_global_by_latency_raw              |
| waits_by_user_by_latency                        |
| waits_by_user_by_latency_raw                    |
| waits_global_by_latency                         |
| waits_global_by_latency_raw                     |
+-------------------------------------------------+
53 rows in set (0.00 sec)

mysql>

schema_unused_indexesビューを確認すると、アクセスされていないインデックスを確認可能
performance_schema.table_io_waits_summary_by_index_usageから情報を取得


mysql> desc schema_unused_indexes;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| object_schema | varchar(64) | YES  |     | NULL    |       |
| object_name   | varchar(64) | YES  |     | NULL    |       |
| index_name    | varchar(64) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create view schema_unused_indexes\G
*************************** 1. row ***************************
                View: schema_unused_indexes
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW 
`schema_unused_indexes` AS select `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` AS `object_schema`,
`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME` AS `object_name`,
`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` AS `index_name` 
from `performance_schema`.`table_io_waits_summary_by_index_usage` 
where ((`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` is not null) 
and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_STAR` = 0) 
and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` <> 'mysql')) 
order by `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA`,
`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>

mysql> select * from schema_unused_indexes;
+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| test          | language    | PRIMARY    |
+---------------+-------------+------------+
1 row in set (0.01 sec)

mysql>

Innodbのスキーマのバッファー状態が確認出来る。
innodb_buffer_pageテーブルから情報を取得している。
Explainで見ると以下のような感じで実行情報が表示される。

mysql> show create view innodb_buffer_stats_by_schema\G
*************************** 1. row ***************************
                View: innodb_buffer_stats_by_schema
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW 
`innodb_buffer_stats_by_schema` AS select if((locate('.',`ibp`.`TABLE_NAME`) = 0),
'InnoDB System',replace(substring_index(`ibp`.`TABLE_NAME`,'.',1),'`','')) AS `object_schema`,
`format_bytes`(sum(if((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPRESSED_SIZE`))) AS `allocated`,
`format_bytes`(sum(`ibp`.`DATA_SIZE`)) AS `data`,count(`ibp`.`PAGE_NUMBER`) AS `pages`,
count(if((`ibp`.`IS_HASHED` = 'YES'),1,0)) AS `pages_hashed`,count(if((`ibp`.`IS_OLD` = 'YES'),1,0)) AS `pages_old`,
round((sum(`ibp`.`NUMBER_RECORDS`) / count(distinct `ibp`.`INDEX_NAME`)),0) AS `rows_cached` 
from `information_schema`.`innodb_buffer_page` `ibp` where (`ibp`.`TABLE_NAME` is not null) 
group by `object_schema` order by sum(if((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPRESSED_SIZE`)) desc
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>

mysql> select * from innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| test          | 32.64 MiB  | 29.93 MiB |  2089 |         2089 |      2089 |      672443 |
| InnoDB System | 144.00 KiB | 26.48 KiB |     9 |            9 |         9 |          78 |
| mysql         | 112.00 KiB | 18.68 KiB |     7 |            7 |         7 |         205 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
3 rows in set (0.08 sec)

mysql>

mysql> explain select * from innodb_buffer_stats_by_schema;
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL                                         |
|  2 | DERIVED     | ibp        | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain format=JSON select * from innodb_buffer_stats_by_schema;
+--------------------------------------------------------------------------------------------------------------------------------------
| EXPLAIN
+--------------------------------------------------------------------------------------------------------------------------------------
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "innodb_buffer_stats_by_schema",
      "access_type": "ALL",
      "rows": 2,
      "filtered": 100,
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "ordering_operation": {
            "using_temporary_table": true,
            "using_filesort": true,
            "grouping_operation": {
              "using_filesort": true,
              "table": {
                "table_name": "ibp",
                "access_type": "ALL",
                "attached_condition": "(`ibp`.`TABLE_NAME` is not null)"
              }
            }
          }
        }
      }
    }
  }
} |
+--------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)

mysql>


mysql> select * from latest_file_io;
+------------------------+----------------------------------------+-----------+-----------+-----------+
| thread                 | file                                   | latency   | operation | requested |
+------------------------+----------------------------------------+-----------+-----------+-----------+
| root@localhost:50115:1 | @@datadir/MyPC.log                   | 27.60 us  | write     | 57 bytes  |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 85.66 us  | open      | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 18.51 us  | read      | 64 bytes  |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 26.79 us  | open      | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 7.29 us   | read      | 3.39 KiB  |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 13.99 us  | close     | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 7.22 us   | close     | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 641.05 us | create    | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 518.82 us | create    | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 38.61 us  | write     | 176 bytes |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 22.86 us  | write     | 100 bytes |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 16.76 us  | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 8.39 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 8.04 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 8.14 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.61 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.69 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.57 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.53 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.85 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 44.70 us  | chsize    | 1.00 KiB  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 102.88 us | close     | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 115.22 us | close     | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 38.44 us  | open      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 6.68 us   | read      | 24 bytes  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 789.17 ns | seek      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 3.02 us   | read      | 339 bytes |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 73.41 us  | open      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 1.18 us   | seek      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 5.41 us   | read      | 20 bytes  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 2.59 us   | read      | 1.25 KiB  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 1.04 us   | seek      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 3.95 us   | read      | 20 bytes  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 2.15 us   | read      | 1.51 KiB  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 958.79 ns | seek      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 4.00 us   | read      | 20 bytes  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 2.00 us   | read      | 1.17 KiB  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 35.29 us  | write     | 96 bytes  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 1.25 us   | tell      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 2.87 us   | seek      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 255.84 ns | seek      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 4.21 us   | read      | 96 bytes  |
+------------------------+----------------------------------------+-----------+-----------+-----------+
42 rows in set (0.04 sec)

mysql>

performance_schemaからIOヒストリー情報を取得している。

mysql> show create view latest_file_io_raw\G
*************************** 1. row ***************************
                View: latest_file_io_raw
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `latest_file_io_raw` AS select if(isnull(`information_schema`.`processlist`.`ID`),
concat(substring_index(`performance_schema`.`threads`.`NAME`,'/',-(1)),':',`performance_schema`.`events_waits_history_long`.`THREAD_ID`),
concat(`information_schema`.`processlist`.`USER`,'@',`information_schema`.`processlist`.`HOST`,':',`information_schema`.`processlist`.`ID`)) AS `thread`,
`performance_schema`.`events_waits_history_long`.`OBJECT_NAME` AS `file`,
`performance_schema`.`events_waits_history_long`.`TIMER_WAIT` AS `latency`,`performance_schema`.`events_waits_history_long`.`OPERATION` AS `operation`,
`performance_schema`.`events_waits_history_long`.`NUMBER_OF_BYTES` AS `requested` from ((`performance_schema`.`events_waits_history_long` join `performance_schema`.`threads` 
on((`performance_schema`.`events_waits_history_long`.`THREAD_ID` = `performance_schema`.`threads`.`THREAD_ID`))) left join `inf
ormation_schema`.`processlist` on((`performance_schema`.`threads`.`PROCESSLIST_ID` = `information_schema`.`processlist`.`ID`))) 
where ((`performance_schema`.`events_waits_history_long`.`OBJECT_NAME` is not null) and (`performance_schema`.`events_waits_history_long`.`EVENT_NAME` like 'wait/io/file/%')) 
order by `performance_schema`.`events_waits_history_long`.`TIMER_START`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>


mysql> select * from latest_file_io_raw;
+------------------------+-----------------------------------------------------------------------------+-----------+-----------+-----------+
| thread                 | file                                                                        | latency   | operation | requested |
+------------------------+-----------------------------------------------------------------------------+-----------+-----------+-----------+
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\MyPC.log                         |  27600429 | write     |        57 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  85656006 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  18514571 | read      |        64 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  26785597 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |   7293388 | read      |      3476 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  13987682 | close     |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |   7219203 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 641052635 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           | 518815003 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  38607879 | write     |       176 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  22860609 | write     |       100 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  16757389 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   8394935 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   8044461 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   8140300 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7609376 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7687170 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7574088 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7526770 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7852382 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  44696262 | chsize    |      1024 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           | 102876550 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 115218929 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  38438256 | open      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   6677853 | read      |        24 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |    789168 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   3022738 | read      |       339 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |  73413877 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   1182950 | seek      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   5409891 | read      |        20 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   2586450 | read      |      1280 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   1042600 | seek      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   3952256 | read      |        20 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   2153771 | read      |      1543 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |    958791 | seek      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   3997970 | read      |        20 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   1996980 | read      |      1202 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |  35292812 | write     |        96 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |   1247511 | tell      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |   2868353 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |    255838 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |   4205688 | read      |        96 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  63665968 | write     |       124 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   5678962 | write     |         2 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 184342106 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           | 124138773 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 141266285 | delete    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |  93093353 | delete    |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\MyPC.log                         |  28632603 | write     |        40 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\MyPC.log                         |  19960577 | write     |        61 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  85139117 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  16528418 | read      |        64 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  25623900 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |   7239253 | read      |      3344 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  14083521 | close     |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |   8134285 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           | 626691221 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           | 493983880 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  31832182 | write     |       176 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  12938265 | write     |       100 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   8823203 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7795841 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7610579 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7805465 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7639050 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7535191 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7808673 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7686368 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7537597 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  43320832 | chsize    |      1024 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |  89308314 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           | 104258797 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  39969274 | open      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   6673041 | read      |        24 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |    786762 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   3104141 | read      |       339 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |  72283859 | open      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |  27100783 | write     |       100 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |   1311671 | tell      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |   2707953 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |    264259 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |   4439471 | read      |       100 |
+------------------------+-----------------------------------------------------------------------------+-----------+-----------+-----------+
82 rows in set (0.04 sec)

mysql>
[/SQl]


<strong>SHOW FULL PROCESSLISTのような感じでperformance_schemaから情報を取得している。</strong>
[SQL]
mysql> desc processlist;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| thd_id                 | bigint(20) unsigned | NO   |     | NULL    |       |
| conn_id                | bigint(20) unsigned | YES  |     | NULL    |       |
| user                   | varchar(128)        | YES  |     | NULL    |       |
| db                     | varchar(64)         | YES  |     | NULL    |       |
| command                | varchar(16)         | YES  |     | NULL    |       |
| state                  | varchar(64)         | YES  |     | NULL    |       |
| time                   | bigint(20)          | YES  |     | NULL    |       |
| current_statement      | varchar(65)         | YES  |     | NULL    |       |
| last_statement         | varchar(65)         | YES  |     | NULL    |       |
| last_statement_latency | varchar(16)         | YES  |     | NULL    |       |
| lock_latency           | varchar(16)         | YES  |     | NULL    |       |
| rows_examined          | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_sent              | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_affected          | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_tables             | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_disk_tables        | bigint(20) unsigned | YES  |     | NULL    |       |
| full_scan              | varchar(3)          | NO   |     |         |       |
| last_wait              | varchar(128)        | YES  |     | NULL    |       |
| last_wait_latency      | varchar(16)         | YES  |     | NULL    |       |
| source                 | varchar(64)         | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

mysql> show create view processlist\G
*************************** 1. row ***************************
                View: processlist
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
SQL SECURITY INVOKER VIEW `processlist` AS select `pps`.`THREAD_ID` AS `thd_id`,
`pps`.`PROCESSLIST_ID` AS `conn_id`,if((`pps`.`NAME` = 'thread/sql/one_connection'),
concat(`pps`.`PROCESSLIST_USER`,'@',`pps`.`PROCESSLIST_HOST`),replace(`pps`.`NAME`,'thread/','')) AS `user`,
`pps`.`PROCESSLIST_DB` AS `db`,`pps`.`PROCESSLIST_COMMAND` AS `command`,`pps`.`PROCESSLIST_STATE` AS `state`,
`pps`.`PROCESSLIST_TIME` AS `time`,`format_statement`(`pps`.`PROCESSLIST_INFO`) AS `current_statement`,
if((`esc`.`TIMER_WAIT` is not null),`format_statement`(`esc`.`SQL_TEXT`),NULL) AS `last_statement`,
if((`esc`.`TIMER_WAIT` is not null),`format_time`(`esc`.`TIMER_WAIT`),NULL) AS `last_statement_latency`,
`format_time`(`esc`.`LOCK_TIME`) AS `lock_latency`,`esc`.`ROWS_EXAMINED` AS `rows_examined`,
`esc`.`ROWS_SENT` AS `rows_sent`,`esc`.`ROWS_AFFECTED` AS `rows_affected`,`esc`.`CREATED_TMP_TABLES` AS `tmp_tables`,
`esc`.`CREATED_TMP_DISK_TABLES` AS `tmp_disk_tables`,if(((`esc`.`NO_GOOD_INDEX_USED` > 0) or 
(`esc`.`NO_INDEX_USED` > 0)),'YES','NO') AS `full_scan`,`ewc`.`EVENT_NAME` AS `last_wait`,
if((isnull(`ewc`.`TIMER_WAIT`) and (`ewc`.`EVENT_NAME` is not null)),'Still Waiting',
`format_time`(`ewc`.`TIMER_WAIT`)) AS `last_wait_latency`,`ewc`.`SOURCE` AS `source` 
from ((`performance_schema`.`threads` `pps` left join `performance_schema`.`events_waits_current` `ewc` 
on((`pps`.`THREAD_ID` = `ewc`.`THREAD_ID`))) left join `performance_schema`.`events_statements_current` `esc` 
on((`pps`.`THREAD_ID` = `esc`.`THREAD_ID`))) order by `pps`.`PROCESSLIST_TIME`
desc,if((isnull(`ewc`.`TIMER_WAIT`) and (`ewc`.`EVENT_NAME` is not null)),'Still Waiting',`format_time`(`ewc`.`TIMER_WAIT`)) desc
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> show create table processlist\G
*************************** 1. row ***************************

mysql> select * from processlist;
+--------+---------+---------------------------------+-----------+---------+--------------+-------+-----------------------------------------------------------+-----------------------------------------------------------+------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------------------+-------------------+-------------------+
| thd_id | conn_id | user                            | db        | command | state        | time  | current_statement                                         | last_statement                                            | last_statement_latency | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_wait                  | last_wait_latency | source            |
+--------+---------+---------------------------------+-----------+---------+--------------+-------+-----------------------------------------------------------+-----------------------------------------------------------+------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------------------+-------------------+-------------------+
|      1 |    NULL | sql/main                        | NULL      | NULL    | System lock  | 15348 | INTERNAL DDL LOG RECOVER IN PROGRESS                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     47 |      27 | root@localhost                  | test      | Query   | init         |     0 | insert into language(name) values("Ez00lbg0ueyrCQDOmo1K") | insert into language(name) values("Ez00lbg0ueyrCQDOmo1K") | 120.69 us              | 0 ps         |             0 |         0 |             1 |          0 |               0 | NO        | wait/io/table/sql/handler  | Still Waiting     | handler.cc:7267   |
|     21 |       1 | root@localhost                  | ps_helper | Query   | Sending data |     0 | select * from processlist                                 | NULL                                                      | NULL                   | 0 ps         |             0 |         0 |             0 |          1 |               0 | YES       | wait/io/file/sql/query_log | 10.28 us          | mf_iocache.c:1788 |
|     11 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     20 |    NULL | sql/con_sockets                 | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      3 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     13 |    NULL | innodb/srv_lock_timeout_thread  | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      4 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     14 |    NULL | innodb/srv_error_monitor_thread | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      5 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     15 |    NULL | innodb/srv_monitor_thread       | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      6 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     16 |    NULL | innodb/srv_master_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      7 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     17 |    NULL | innodb/srv_purge_thread         | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      8 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     18 |    NULL | innodb/page_cleaner_thread      | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      9 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     19 |    NULL | sql/shutdown                    | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      2 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     10 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
+--------+---------+---------------------------------+-----------+---------+--------------+-------+-----------------------------------------------------------+-----------------------------------------------------------+------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------------------+-------------------+-------------------+
21 rows in set (0.00 sec)

mysql>

ユーザー処理を確認出来る。

mysql> select * from user_summary;
+------+------------------+---------------+-------------+---------------------+-------------------+--------------+
| user | total_statements | total_latency | avg_latency | current_connections | total_connections | unique_hosts |
+------+------------------+---------------+-------------+---------------------+-------------------+--------------+
| root |           680477 | 00:01:05.15   | 95.75 us    |                   2 |                29 |            1 |
+------+------------------+---------------+-------------+---------------------+-------------------+--------------+
1 row in set (0.00 sec)

mysql> select * from user_summary_by_stages;
+------+-------------------------------------+---------+-----------+-----------+
| user | event_name                          | count   | wait_sum  | wait_avg  |
+------+-------------------------------------+---------+-----------+-----------+
| root | stage/sql/freeing items             |  680402 | 25.96 s   | 37.77 us  |
| root | stage/sql/init                      | 1360606 | 19.15 s   | 13.96 us  |
| root | stage/sql/update                    |  680000 | 10.80 s   | 15.60 us  |
| root | stage/sql/Opening tables            |  681688 | 2.47 s    | 3.28 us   |
| root | stage/sql/closing tables            |  680386 | 1.63 s    | 2.05 us   |
| root | stage/sql/System lock               |  680230 | 1.59 s    | 2.05 us   |
| root | stage/sql/query end                 |  680386 | 922.56 ms | 1.23 us   |
| root | stage/sql/checking permissions      |  680609 | 669.60 ms | 821.02 ns |
| root | stage/sql/Sending data              |     150 | 484.30 ms | 3.23 ms   |
| root | stage/sql/cleaning up               |  680458 | 468.11 ms | 410.51 ns |
| root | stage/sql/Creating sort index       |      53 | 327.28 ms | 6.17 ms   |
| root | stage/sql/end                       |  680144 | 250.31 ms | 0 ps      |
| root | stage/sql/removing tmp table        |     147 | 21.33 ms  | 144.91 us |
| root | stage/sql/executing                 |     156 | 14.14 ms  | 90.31 us  |
| root | stage/sql/explaining                |       8 | 8.55 ms   | 1.07 ms   |
| root | stage/sql/converting HEAP to MyISAM |       1 | 6.44 ms   | 6.44 ms   |
| root | stage/sql/Creating tmp table        |      36 | 4.87 ms   | 135.06 us |
| root | stage/sql/preparing                 |     158 | 3.86 ms   | 24.22 us  |
| root | stage/sql/statistics                |     158 | 3.39 ms   | 21.35 us  |
| root | stage/sql/optimizing                |     164 | 1.18 ms   | 6.98 us   |
| root | stage/sql/Sorting for group         |       5 | 297.21 us | 59.11 us  |
| root | stage/sql/Sorting result            |      45 | 110.84 us | 2.46 us   |
| root | stage/sql/updating                  |       1 | 17.65 us  | 17.65 us  |
+------+-------------------------------------+---------+-----------+-----------+
23 rows in set (0.00 sec)

mysql>

I/O処理を確認出来る。
IOスレッド,ファイルIO時間,ファイルIO量,IOイベント時間,処理ごとのIO

mysql> select * from io_by_thread_by_latency limit 0,1;
+----------------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
| user           | count_star | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+----------------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
| root@localhost |       7546 | 90.63 ms      | 210.12 ns   | 63.86 us    | 2.22 ms     |        21 |              1 |
+----------------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from io_global_by_file_by_latency limit 0,1;
+----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                 | count_star | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| @@datadir/mypc.log   |     680475 | 5.26 s        |          0 | 0 ps         |      680472 | 5.26 s        |          3 | 162.39 us    |
+----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>


mysql> select * from io_global_by_file_by_bytes limit 0,1;
+-----------------------------+------------+------------+----------+-------------+---------------+-----------+------------+-----------+
| file                        | count_read | total_read | avg_read | count_write | total_written | avg_write | total      | write_pct |
+-----------------------------+------------+------------+----------+-------------+---------------+-----------+------------+-----------+
| @@datadir/MyPC-bin.000026   |          2 | 120 bytes  | 60 bytes |       13556 | 105.71 MiB    | 7.99 KiB  | 105.71 MiB |    100.00 |
+-----------------------------+------------+------------+----------+-------------+---------------+-----------+------------+-----------+
1 row in set (0.00 sec)

mysql>


mysql> select * from io_global_by_wait_by_latency limit 0,1;
+---------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+----------+-------------+---------------+-------------+
| event_name    | count_star | total_latency | avg_latency | max_latency | read_latency | write_latency | misc_latency | count_read | total_read | avg_read | count_write | total_written | avg_written |
+---------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+----------+-------------+---------------+-------------+
| sql/query_log |     680479 | 5.26 s        | 7.74 us     | 61.26 ms    | 0 ps         | 5.26 s        | 162.39 us    |          0 | 0 bytes    | 0 bytes  |      680476 | 46.78 MiB     | 72 bytes    |
+---------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+----------+-------------+---------------+-------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from latest_file_io limit 0,10;
+------------------------+------------------------------------------------+-----------+-----------+-----------+
| thread                 | file                                           | latency   | operation | requested |
+------------------------+------------------------------------------------+-----------+-----------+-----------+
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 7.45 us   | write     | 41 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 20.00 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 29.31 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 26.59 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 28.61 us  | write     | 41 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 30.14 us  | write     | 64 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 26.89 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 29.99 us  | write     | 65 bytes  |
| root@localhost:50115:1 | @@datadir/ps_helper/user_summary_by_stages.frm | 105.91 us | open      | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/user_summary_by_stages.frm | 22.66 us  | read      | 64 bytes  |
+------------------------+------------------------------------------------+-----------+-----------+-----------+
10 rows in set (0.03 sec)

mysql>

スキーマ毎のオブジェクト数を確認出来る。

mysql> show create table schema_object_overview\G
*************************** 1. row ***************************
                View: schema_object_overview
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
SQL SECURITY INVOKER VIEW `schema_object_overview` AS select `information_schema`.`routines`.`ROUTINE_SCHEMA` AS `db`,
`information_schema`.`routines`.`ROUTINE_TYPE` AS `object_type`,count(0) AS `count` from `information_schema`.`routines` 
group by `information_schema`.`routines`.`ROUTINE_SCHEMA`,`information_schema`.`routines`.`ROUTINE_TYPE` union 
select `information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,
count(0) AS `COUNT(*)` from `information_schema`.`tables` group by `information_schema`.`tables`.`TABLE_SCHEMA`,
`information_schema`.`tables`.`TABLE_TYPE` union select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,
concat('INDEX (',`information_schema`.`statistics`.`INDEX_TYPE`,')') AS `CONCAT('INDEX (', INDEX_TYPE, ')')`,
count(0) AS `COUNT(*)` from `information_schema`.`statistics` group by `information_schema`.`statistics`.`TABLE_SCHEMA`,
`information_schema`.`statistics`.`INDEX_TYPE` union select `information_schema`.`triggers`.`TRIGGER_SCHEMA` AS `TRIGGER_SCHEMA`,
'TRIGGER' AS `TRIGGER`,count(0) AS `COUNT(*)` from `information_schema`.`triggers` 
group by `information_schema`.`triggers`.`TRIGGER_SCHEMA` union select `information_schema`.`events`.`EVENT_SCHEMA` AS `EVENT_SCHEMA`,
'EVENT' AS `EVENT`,count(0) AS `COUNT(*)` from `information_schema`.`events` 
group by `information_schema`.`events`.`EVENT_SCHEMA` order by `DB`,`OBJECT_TYPE`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

mysql>


mysql> select * from schema_object_overview;
+--------------------+------------------+-------+
| db                 | object_type      | count |
+--------------------+------------------+-------+
| copy_test          | BASE TABLE       |     5 |
| copy_test          | INDEX (BTREE)    |     7 |
| information_schema | SYSTEM VIEW      |    59 |
| mysql              | BASE TABLE       |    28 |
| mysql              | INDEX (BTREE)    |    63 |
| performance_schema | BASE TABLE       |    52 |
| ps_helper          | FUNCTION         |     8 |
| ps_helper          | PROCEDURE        |    12 |
| ps_helper          | VIEW             |    53 |
| sakila             | BASE TABLE       |    16 |
| sakila             | FUNCTION         |     3 |
| sakila             | INDEX (BTREE)    |    45 |
| sakila             | INDEX (FULLTEXT) |     2 |
| sakila             | PROCEDURE        |     3 |
| sakila             | TRIGGER          |     6 |
| sakila             | VIEW             |     7 |
| sys                | FUNCTION         |     8 |
| sys                | PROCEDURE        |    16 |
| sys                | VIEW             |    63 |
| test               | BASE TABLE       |     5 |
| test               | INDEX (BTREE)    |     8 |
| world              | BASE TABLE       |     3 |
| world              | INDEX (BTREE)    |     4 |
+--------------------+------------------+-------+
23 rows in set (0.55 sec)

mysql> 

実行時間がかかっているSQLを確認。
performance_schema.events_statements_summary_by_digestからselectするのと同じ。

mysql> select * from statement_analysis limit 0,1;
+-------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| 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           |
+-------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| INSERT INTO LANGUAGE ( NAME ) VALUES (?)  | test |           |     680000 |         0 |          0 | 00:01:03.32   | 61.76 ms    | 92.78 us    | 18.98 s      |         0 |             0 |             0 |                 0 |          0 |               0 |           0 |                 0 | 9c2953f8e62dc70ec329b2b787819a46 | 2014-08-08 09:14:27 | 2014-08-08 13:45:27 |
+-------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

FULL TABLEスキャンでインデックスが使えてないQueryを特定。

mysql> select * from statements_with_full_table_scans limit 0,3;
+-------------------------------------------------------------------+--------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db                 | exec_count | 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 `user_summary` S ... em` . `SUM_TIMER_WAIT` ) DESC  | ps_helper          |         12 |                  12 |                        0 |               100 |        12 |          4020 |             1 |               335 | 2014-08-08 13:17:16 | 2014-08-08 13:46:02 | aa63ec3352becb56cdb68b82fe669d5b |
| SELECT * FROM `events_statements_current`                         | performance_schema |          8 |                   8 |                        0 |               100 |        12 |            12 |             2 |                 2 | 2014-08-08 09:58:55 | 2014-08-08 10:00:19 | 6aa57f9427136fc389e84be571cad650 |
| SELECT `EVENT_NAME` , `COUNT_S ...  BY `COUNT_STAR` DESC LIMIT ?  | performance_schema |          7 |                   7 |                        0 |               100 |        70 |          1995 |            10 |               285 | 2014-08-08 09:11:48 | 2014-08-08 11:21:03 | a629020ce748ea0b8845ad529e26935e |
+-------------------------------------------------------------------+--------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql>

Temp Tableを最も使用しているQueryを特定

mysql> select * from statements_with_temp_tables limit 0,3;
+-------------------------------------------------------------------+-----------+------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db        | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+-----------+------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `schema_object_o ... MA` , `information_schema` ... | ps_helper |          1 |               189 |              33 |                      189 |                     17 | 2014-08-08 14:00:31 | 2014-08-08 14:00:31 | 54f9bd520f0bbf15db0c2ed93386bec9 |
| SHOW CREATE TABLE `schema_obje ... istics` . `TABLE_SCHEMA` , ... | ps_helper |          3 |                21 |               9 |                        7 |                     43 | 2014-08-08 14:01:41 | 2014-08-08 14:02:11 | 3ee60de4f4e84b761149e92903897574 |
| EXPLAIN SELECT * FROM `innodb_ ...  . `COMPRESSED_SIZE` ) ) DESC  | ps_helper |          3 |                12 |               3 |                        4 |                     25 | 2014-08-08 13:22:17 | 2014-08-08 13:22:52 | 6297d8cbe1e79362ec755bc21886e09d |
+-------------------------------------------------------------------+-----------+------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql>

パフォーマンス・スキーマ:MySQLサーバーの稼働統計を確認可能
A collection of scripts to help MySQL DBAs


MySQL 5.6 performance_schemaの状態とテーブル
Assuming that the Performance Schema is available,
it is enabled by default as of MySQL 5.6.6. Before 5.6.6,
it is disabled by default. To enable or disable it explicitly,
start the server with the performance_schema
variable set to an appropriate value.

5.6.6以前の場合、5.6.6以降はDefaultでEnableになっている。
For example, use these lines in your my.cnf file:

[mysqld]
performance_schema=on
mysql> use performance_schema
Database changed

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 100   |
| performance_schema_digests_size                        | 10000 |
| performance_schema_events_stages_history_long_size     | 10000 |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |
| performance_schema_events_waits_history_size           | 10    |
| performance_schema_hosts_size                          | 100   |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 3504  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | 6770  |
| performance_schema_max_mutex_classes                   | 200   |
| performance_schema_max_mutex_instances                 | 15906 |
| performance_schema_max_rwlock_classes                  | 40    |
| performance_schema_max_rwlock_instances                | 9102  |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | 322   |
| performance_schema_max_stage_classes                   | 150   |
| performance_schema_max_statement_classes               | 168   |
| performance_schema_max_table_handles                   | 4000  |
| performance_schema_max_table_instances                 | 12500 |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 402   |
| performance_schema_session_connect_attrs_size          | 512   |
| performance_schema_setup_actors_size                   | 100   |
| performance_schema_setup_objects_size                  | 100   |
| performance_schema_users_size                          | 100   |
+--------------------------------------------------------+-------+
31 rows in set (0.00 sec)

22.9.1 Performance Schema Table Index
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-table-index.html

mysql> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+----------------------------------------------------+
| accounts                                           | Connection statistics per client account
| cond_instances                                     | synchronization object instances
| events_stages_current                              |  Current stage events
| events_stages_history                              |  Most recent stage events for each thread
| events_stages_history_long                         |  Most recent stage events overall
| events_stages_summary_by_account_by_event_name     |  Stage events per account and event name
| events_stages_summary_by_host_by_event_name        |  Stage events per host name and event name
| events_stages_summary_by_thread_by_event_name      |  Stage waits per thread and event name
| events_stages_summary_by_user_by_event_name        |  Stage events per user name and event name
| events_stages_summary_global_by_event_name         |  Stage waits per event name
| events_statements_current                          |  Current statement events
| events_statements_history                          |  Most recent statement events for each thread
| events_statements_history_long                     |  Most recent statement events overall
| events_statements_summary_by_account_by_event_name |  Statement events per account and event name
| events_statements_summary_by_digest                |  Statement events per schema and digest value
| events_statements_summary_by_host_by_event_name    |  Statement events per host name and event name
| events_statements_summary_by_thread_by_event_name  |  Statement events per thread and event name
| events_statements_summary_by_user_by_event_name    |  Statement events per user name and event name
| events_statements_summary_global_by_event_name     |  Statement events per event name
| events_waits_current                               |  Current wait events
| events_waits_history                               |  Most recent wait events for each thread
| events_waits_history_long                          |  Most recent wait events overall
| events_waits_summary_by_account_by_event_name      |  Wait events per account and event name
| events_waits_summary_by_host_by_event_name         |  Wait events per host name and event name
| events_waits_summary_by_instance                   |  Wait events per instance
| events_waits_summary_by_thread_by_event_name       |  Wait events per thread and event name
| events_waits_summary_by_user_by_event_name         |  Wait events per user name and event name
| events_waits_summary_global_by_event_name          |  Wait events per event name
| file_instances                                     |  File instances
| file_summary_by_event_name                         |  File events per event name
| file_summary_by_instance                           |  File events per file instance
| host_cache                                         |  Information from the internal host cache
| hosts                                              |  Connection statistics per client host name
| mutex_instances                                    |  Mutex synchronization object instances
| objects_summary_global_by_type                     |  Object summaries
| performance_timers                                 |  Which event timers are available
| rwlock_instances                                   |  Lock synchronization object instances
| session_account_connect_attrs                      |  Connection attributes per for the current session
| session_connect_attrs                              |  Connection attributes for all sessions
| setup_actors                                       |  How to initialize monitoring for new foreground threads
| setup_consumers                                    |  Consumers for which event information can be stored
| setup_instruments                                  |  Classes of instrumented objects for which events can be collected
| setup_objects                                      |  Which objects should be monitored
| setup_timers                                       |  Current event timer
| socket_instances                                   |  Active connection instances
| socket_summary_by_event_name                       |  Socket waits and I/O per event name
| socket_summary_by_instance                         |  Socket waits and I/O per instance
| table_io_waits_summary_by_index_usage              |  Table I/O waits per index
| table_io_waits_summary_by_table                    |  Table I/O waits per table
| table_lock_waits_summary_by_table                  |  Table lock waits per table
| threads                                            |  Information about server threads
| users                                              |  Connection statistics per client user name
+----------------------------------------------------+
52 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE setup_timers\G
*************************** 1. row ***************************
       Table: setup_timers
Create Table: CREATE TABLE `setup_timers` (
  `NAME` varchar(64) NOT NULL,
  `TIMER_NAME` enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK') NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

Defaultでは全てのユーザーのデータを取得するようになっている。
MS SQLのProfiler同様にHeavyな環境で全てのデータを取得するとパフォーマンスに
影響が出るかもしれないので、ある程度対象が分かっていればここでUpdateして設定する
のも良いのかもしれません。

mysql> select * from setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+
1 row in set (0.00 sec)

mysql>

こちらもフィルター出来る値。
setup_actors, setup_consumers, setup_instruments, setup_objects,setup_timers

The Performance Schema is implemented as a storage engine.
If this engine is available (which you should already have checked earlier),
you should see it listed with a SUPPORT value of YES in the output from the
INFORMATION_SCHEMA.ENGINES table or the SHOW ENGINES statement:

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES
    -> WHERE ENGINE='PERFORMANCE_SCHEMA'\G
*************************** 1. row ***************************
      ENGINE: PERFORMANCE_SCHEMA
     SUPPORT: YES
     COMMENT: Performance Schema
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
1 row in set (0.00 sec)

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

22.9.2.3 The setup_instruments Table
http://dev.mysql.com/doc/refman/5.6/en/setup-instruments-table.html
setup_instruments lists the set of instruments for which events
can be collected and shows which of them are enabled:

Initially, not all instruments and consumers are enabled,
so the performance schema does not collect all events.
To turn all of these on and enable event timing, execute two statements
(the row counts may differ depending on MySQL version):

mysql> select enabled, count(*) as 'count' from setup_instruments group by enabled;
+---------+-------+
| enabled | count |
+---------+-------+
| YES     |   213 |
| NO      |   338 |
+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM events_waits_current\G
Empty set (0.00 sec)

mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 338 rows affected (0.04 sec)
Rows matched: 551  Changed: 338  Warnings: 0

mysql> UPDATE setup_consumers SET ENABLED = 'YES';
Query OK, 8 rows affected (0.00 sec)
Rows matched: 12  Changed: 8  Warnings: 0

mysql> select enabled, count(*) as 'count' from setup_instruments group by enabled;
+---------+-------+
| enabled | count |
+---------+-------+
| YES     |   551 |
+---------+-------+
1 row in set (0.00 sec)

mysql>

To see what the server is doing at the moment,
examine the events_waits_current table. It contains one row per thread
showing each thread’s most recent monitored event:

mysql> SELECT * FROM events_waits_current\G
*************************** 1. row ***************************
            THREAD_ID: 21
             EVENT_ID: 53
         END_EVENT_ID: 53
           EVENT_NAME: wait/io/file/sql/query_log
               SOURCE: mf_iocache.c:1788
          TIMER_START: 898529738286969
            TIMER_END: 898529803494782
           TIMER_WAIT: 65207813
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log
           INDEX_NAME: NULL
          OBJECT_TYPE: FILE
OBJECT_INSTANCE_BEGIN: 309205312
     NESTING_EVENT_ID: 52
   NESTING_EVENT_TYPE: STAGE
            OPERATION: write
      NUMBER_OF_BYTES: 63
                FLAGS: NULL
1 row in set (0.00 sec)


mysql> select * from events_waits_current;
+-----------+----------+--------------+-----------------------------------------+-------------------+------------------+------------------+------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                              | SOURCE            | TIMER_START      | TIMER_END        | TIMER_WAIT | SPINS | OBJECT_SCHEMA | OBJECT_NAME                                           | INDEX_NAME | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE | OPERATION | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+-----------------------------------------+-------------------+------------------+------------------+------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
|        21 |      816 |          816 | wait/io/file/sql/query_log              | mf_iocache.c:1788 | 4905373104295754 | 4905373111110348 |    6814594 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              815 | STAGE              | write     |              49 |  NULL |
|        28 |   719555 |       719555 | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.cc:4606 | 4905373186684011 | 4905373186736943 |      52932 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |           719551 | STAGE              | lock      |            NULL |  NULL |
+-----------+----------+--------------+-----------------------------------------+-------------------+------------------+------------------+------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
2 rows in set (0.00 sec)

mysql>

1st event of above is indicates that thread 21 was waiting for 65,207,813
picoseconds to write /io/file/sql/query_log.

■A picosecond is an SI unit of time equal to 10−12 of a second.
 That is one trillionth, or one millionth of one millionth of a second,
 or 0.000 000 000 001 seconds.

22.9.4.2 The events_waits_history Table
http://dev.mysql.com/doc/refman/5.6/en/events-waits-history-table.html
The events_waits_history table contains the most recent
10 wait events per thread. To change the table size,
modify the performance_schema_events_waits_history_size
system variable at server startup.

mysql> SELECT THREAD_ID,EVENT_ID, EVENT_NAME, TIMER_WAIT
    -> FROM events_waits_history;
+-----------+----------+----------------------------+-----------------+
| THREAD_ID | EVENT_ID | EVENT_NAME                 | TIMER_WAIT      |
+-----------+----------+----------------------------+-----------------+
|        21 |       28 | idle                       |  12831474863490 |
|        21 |       31 | wait/io/file/sql/query_log |        56420700 |
|        21 |       50 | idle                       |  15451250340070 |
|        21 |       53 | wait/io/file/sql/query_log |        65207813 |
|        21 |       68 | idle                       | 312420345274150 |
|        21 |       71 | wait/io/file/sql/query_log |        66936925 |
|        21 |       88 | idle                       |  12749062928440 |
|        21 |       91 | wait/io/file/sql/query_log |        68074562 |
|        21 |      106 | idle                       |  41669289225990 |
|        21 |      109 | wait/io/file/sql/query_log |        58701588 |
+-----------+----------+----------------------------+-----------------+
10 rows in set (0.00 sec)

mysql>

mysql> select * from events_waits_history;
+-----------+----------+--------------+----------------------------+-------------------+------------------+------------------+-----------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                 | SOURCE            | TIMER_START      | TIMER_END        | TIMER_WAIT      | SPINS | OBJECT_SCHEMA | OBJECT_NAME                                           | INDEX_NAME | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE | OPERATION | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+----------------------------+-------------------+------------------+------------------+-----------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
|        21 |      816 |          816 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4905373104295754 | 4905373111110348 |         6814594 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              815 | STAGE              | write     |              49 |  NULL |
|        21 |      831 |          831 | idle                       | mysqld.cc:910     | 4904012990931030 | 5057673130661700 | 153660139730670 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      836 |          836 | idle                       | mysqld.cc:910     | 5057673304717940 | 5083137360707880 |  25464055989940 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      839 |          839 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 5084549130565107 | 5084549198935607 |        68370500 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              838 | STAGE              | write     |              63 |  NULL |
|        21 |      762 |          762 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4897270600937719 | 4897270677689119 |        76751400 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              761 | STAGE              | write     |              63 |  NULL |
|        21 |      777 |          777 | idle                       | mysqld.cc:910     | 4895911828544680 | 4901262906444130 |   5351077899450 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      780 |          780 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4902624030649702 | 4902624040836305 |        10186603 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              779 | STAGE              | write     |              49 |  NULL |
|        21 |      795 |          795 | idle                       | mysqld.cc:910     | 4901263149466050 | 4902370987466420 |   1107838000370 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      798 |          798 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4903732414301910 | 4903732421811036 |         7509126 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              797 | STAGE              | write     |              49 |  NULL |
|        21 |      813 |          813 | idle                       | mysqld.cc:910     | 4902371237467010 | 4904011224095990 |   1639986628980 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
+-----------+----------+--------------+----------------------------+-------------------+------------------+------------------+-----------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
10 rows in set (0.00 sec)

mysql>

22.9.9.1 Event Wait Summary Tables
The Performance Schema maintains tables for collecting current and recent wait events,
and aggregates that information in summary tables.
http://dev.mysql.com/doc/refman/5.6/en/wait-summary-tables.html

sort the events_waits_summary_global_by_event_name table on the
COUNT_STAR or SUM_TIMER_WAIT column, which correspond to a
COUNT(*) or SUM(TIMER_WAIT) value, respectively, calculated over all events:

mysql> SELECT EVENT_NAME, COUNT_STAR
    -> FROM events_waits_summary_global_by_event_name
    -> ORDER BY COUNT_STAR DESC LIMIT 10;
+-----------------------------------------+------------+
| EVENT_NAME                              | COUNT_STAR |
+-----------------------------------------+------------+
| wait/synch/mutex/sql/THD::LOCK_thd_data |      50018 |
| wait/io/socket/sql/client_connection    |      40031 |
| wait/synch/mutex/mysys/THR_LOCK::mutex  |      20000 |
| wait/lock/table/sql/handler             |      20000 |
| idle                                    |      10067 |
| wait/io/file/sql/query_log              |      10039 |
| wait/synch/mutex/innodb/autoinc_mutex   |      10001 |
| wait/synch/mutex/innodb/trx_undo_mutex  |      10000 |
| wait/io/table/sql/handler               |      10000 |
| wait/io/file/sql/FRM                    |        925 |
+-----------------------------------------+------------+
10 rows in set (0.04 sec)

mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT
    -> FROM events_waits_summary_global_by_event_name
    -> ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
+-----------------------------------------+------------------+
| EVENT_NAME                              | SUM_TIMER_WAIT   |
+-----------------------------------------+------------------+
| idle                                    | 3303044059943910 |
| wait/io/socket/sql/client_connection    |     392231099217 |
| wait/io/table/sql/handler               |     108480957278 |
| wait/io/file/sql/query_log              |      73737054930 |
| wait/io/file/sql/binlog                 |      19253155256 |
| wait/io/file/sql/FRM                    |       7584131446 |
| wait/lock/table/sql/handler             |       6172912196 |
| wait/synch/mutex/sql/THD::LOCK_thd_data |       2878397649 |
| wait/io/file/myisam/dfile               |       2485265269 |
| wait/io/file/sql/dbopt                  |       2225433309 |
+-----------------------------------------+------------------+
10 rows in set (0.02 sec)

mysql>

22.9.2.5 The setup_timers Table
http://dev.mysql.com/doc/refman/5.6/en/setup-timers-table.html
Setup tables are used to configure and display monitoring characteristics.
For example, to see which event timers are selected, query the setup_timers tables:

mysql> SELECT * FROM setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | MICROSECOND |
| statement | MICROSECOND |
+-----------+-------------+
4 rows in set (0.00 sec)

mysql>

22.9.3.2 The file_instances Table
The file_instances table lists all the files seen by the Performance Schema
when executing file I/O instrumentation. If a file on disk has never been opened,
it will not be in file_instances. When a file is deleted from the disk,
it is also removed from the file_instances table.
http://dev.mysql.com/doc/refman/5.6/en/file-instances-table.html

mysql> SELECT * FROM file_instances limit 0,3\G
*************************** 1. row ***************************
 FILE_NAME: C:\Program Files\MySQL\MySQL Server 5.6\share\english\errmsg.sys
EVENT_NAME: wait/io/file/sql/ERRMSG
OPEN_COUNT: 0
*************************** 2. row ***************************
 FILE_NAME: C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\Index.xml
EVENT_NAME: wait/io/file/mysys/charset
OPEN_COUNT: 0
*************************** 3. row ***************************
 FILE_NAME: C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.frm
EVENT_NAME: wait/io/file/sql/FRM
OPEN_COUNT: 0
3 rows in set (0.00 sec)

mysql> select * from file_instances order by OPEN_COUNT desc limit 0,5;
+--------------------------------------------------------------+-------------------------------+------------+
| FILE_NAME                                                    | EVENT_NAME                    | OPEN_COUNT |
+--------------------------------------------------------------+-------------------------------+------------+
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-slow.log   | wait/io/file/sql/slow_log     |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log        | wait/io/file/sql/query_log    |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.000026 | wait/io/file/sql/binlog       |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.index  | wait/io/file/sql/binlog_index |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\user.MYI    | wait/io/file/myisam/kfile     |          1 |
+--------------------------------------------------------------+-------------------------------+------------+
5 rows in set (0.00 sec)

mysql>

File events per event name
File events per file instance

mysql> select * from file_summary_by_event_name order by AVG_TIMER_WAIT desc limit 0,3;
+---------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME                | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | SUM_NUMBER_OF_BYTES_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | SUM_NUMBER_OF_BYTES_WRITE | COUNT_MISC | SUM_TIMER_MISC | MIN_TIMER_MISC | AVG_TIMER_MISC | MAX_TIMER_MISC |
+---------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| wait/io/file/sql/pid      |          3 |     1157890708 |              0 |      385963302 |      855847483 |          0 |              0 |              0 |              0 |              0 |                        0 |           1 |        73653675 |               0 |        73653675 |        73653675 |                         5 |          2 |     1084237033 |              0 |      542118316 |      855847483 |
| wait/io/file/archive/data |          2 |      346097085 |              0 |      173048342 |      195457425 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |          2 |      346097085 |              0 |      173048342 |      195457425 |
| wait/io/file/sql/casetest |         10 |     1309209662 |              0 |      130920886 |      344558047 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |         10 |     1309209662 |              0 |      130920886 |      344558047 |
+---------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from file_summary_by_instance limit 0,10;
+------------------------------------------------------------------+-------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| FILE_NAME                                                        | EVENT_NAME                    | OBJECT_INSTANCE_BEGIN | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | SUM_NUMBER_OF_BYTES_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | SUM_NUMBER_OF_BYTES_WRITE | COUNT_MISC | SUM_TIMER_MISC | MIN_TIMER_MISC | AVG_TIMER_MISC | MAX_TIMER_MISC |
+------------------------------------------------------------------+-------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| C:\Program Files\MySQL\MySQL Server 5.6\share\english\errmsg.sys | wait/io/file/sql/ERRMSG       |             309198976 |          5 |      140991600 |        5298012 |       28198320 |       57330168 |          3 |       49863147 |        5298012 |       16621049 |       22329685 |                    59320 |           0 |               0 |               0 |               0 |               0 |                         0 |          2 |       91128453 |       33798285 |       45564026 |       57330168 |
| C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\Index.xml | wait/io/file/mysys/charset    |             309199680 |          3 |       67456220 |       11204341 |       22485273 |       30768329 |          1 |       25483550 |       25483550 |       25483550 |       25483550 |                    18305 |           0 |               0 |               0 |               0 |               0 |                         0 |          2 |       41972670 |       11204341 |       20986335 |       30768329 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.frm      | wait/io/file/sql/FRM          |             309202496 |         13 |      100849896 |         266264 |        7757345 |       49996279 |          7 |       31000909 |        1237085 |        4428644 |       18924794 |                     1104 |           0 |               0 |               0 |               0 |               0 |                         0 |          6 |       69848987 |         266264 |       11641431 |       49996279 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.MYI      | wait/io/file/myisam/kfile     |             309203200 |          5 |      136199650 |         655635 |       27239930 |       59826794 |          2 |       16195588 |        2857526 |        8097794 |       13338062 |                      364 |           0 |               0 |               0 |               0 |               0 |                         0 |          3 |      120004062 |         655635 |       40001354 |       59826794 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.MYD      | wait/io/file/myisam/dfile     |             309203904 |          4 |      241976232 |        1195782 |       60494058 |      190878406 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |          4 |      241976232 |        1195782 |       60494058 |      190878406 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-slow.log       | wait/io/file/sql/slow_log     |             309204608 |          4 |      352624162 |        1618837 |       88155840 |      217803150 |          0 |              0 |              0 |              0 |              0 |                        0 |           1 |       127974338 |       127974338 |       127974338 |       127974338 |                       197 |          3 |      224649824 |        1618837 |       74883141 |      217803150 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log            | wait/io/file/sql/query_log    |             309205312 |     110121 |   869759674344 |        1604000 |        7898096 |    61257359094 |          0 |              0 |              0 |              0 |              0 |                        0 |      110118 |    869597279369 |         5130394 |         7896893 |     61257359094 |                   7928189 |          3 |      162394975 |        1604000 |       54131391 |      157402525 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.000025     | wait/io/file/sql/binlog       |             309206016 |          8 |     9908398022 |        1994975 |     1238549452 |     9632917037 |          2 |       98211717 |       18486100 |       49105658 |       79725617 |                    16384 |           0 |               0 |               0 |               0 |               0 |                         0 |          6 |     9810186305 |        1994975 |     1635030984 |     9632917037 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.000026     | wait/io/file/sql/binlog       |             309206720 |       2205 |    29322108064 |        1129617 |       13297962 |     3103265216 |          2 |       14349384 |        4167192 |        7174692 |       10182192 |                      120 |        2196 |     24314145379 |         3582534 |        11072011 |       103890679 |                  17930890 |          7 |     4993613301 |        1129617 |      713372985 |     3103265216 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.index      | wait/io/file/sql/binlog_index |             309207424 |          5 |      510165834 |        1413525 |      102032846 |      380365342 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |          5 |      510165834 |        1413525 |      102032846 |      380365342 |
+------------------------------------------------------------------+-------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
10 rows in set (0.00 sec)

mysql>

22.9.2.2 The setup_consumers Table
http://dev.mysql.com/doc/refman/5.6/en/setup-consumers-table.html
The setup_consumers table lists the types of consumers for which event
information can be stored and which are enabled:

mysql> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | YES     |
| events_stages_history          | YES     |
| events_stages_history_long     | YES     |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
| events_waits_current           | YES     |
| events_waits_history           | YES     |
| events_waits_history_long      | YES     |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+
12 rows in set (0.00 sec)

mysql>

“Current statement events” and “Information about server threads” like “Show Full Processlist”

mysql> select * from events_statements_current;
+-----------+----------+--------------+----------------------+---------------+------------------+------------------+------------+-----------+-----------------------------------------------------------+----------------------------------+-------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME           | SOURCE        | TIMER_START      | TIMER_END        | TIMER_WAIT | LOCK_TIME | SQL_TEXT                                                  | DIGEST                           | DIGEST_TEXT                               | CURRENT_SCHEMA     | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | ERRORS | WARNINGS | ROWS_AFFECTED | ROWS_SENT | ROWS_EXAMINED | CREATED_TMP_DISK_TABLES | CREATED_TMP_TABLES | SELECT_FULL_JOIN | SELECT_FULL_RANGE_JOIN | SELECT_RANGE | SELECT_RANGE_CHECK | SELECT_SCAN | SORT_MERGE_PASSES | SORT_RANGE | SORT_ROWS | SORT_SCAN | NO_INDEX_USED | NO_GOOD_INDEX_USED | NESTING_EVENT_ID | NESTING_EVENT_TYPE |
+-----------+----------+--------------+----------------------+---------------+------------------+------------------+------------+-----------+-----------------------------------------------------------+----------------------------------+-------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+
|        21 |      616 |         NULL | statement/sql/select | mysqld.cc:940 | 4322525515586060 |             NULL |       NULL |         0 | select * from events_statements_current                   | NULL                             | NULL                                      | performance_schema | NULL        | NULL          | NULL        |                  NULL |           0 | NULL              | NULL         |      0 |        0 |             0 |         0 |             0 |                       0 |                  0 |                0 |                      0 |            0 |                  0 |           1 |                 0 |          0 |         0 |         0 |             1 |                  0 |             NULL | NULL               |
|        27 |   718200 |       718227 | statement/sql/insert | mysqld.cc:940 | 4322525313615140 | 4322525502860250 |  189245110 |         0 | insert into language(name) values("RWyDULUHlJYgnjw9Yx8o") | 9c2953f8e62dc70ec329b2b787819a46 | INSERT INTO LANGUAGE ( NAME ) VALUES (?)  | test               | NULL        | NULL          | NULL        |                  NULL |           0 | 00000             | NULL         |      0 |        0 |             1 |         0 |             0 |                       0 |                  0 |                0 |                      0 |            0 |                  0 |           0 |                 0 |          0 |         0 |         0 |             0 |                  0 |             NULL | NULL               |
+-----------+----------+--------------+----------------------+---------------+------------------+------------------+------------+-----------+-----------------------------------------------------------+----------------------------------+-------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from threads;
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+
| THREAD_ID | NAME                                   | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB     | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO                     | PARENT_THREAD_ID | ROLE | INSTRUMENTED |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+
|         1 | thread/sql/main                        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             6986 | System lock       | INTERNAL DDL LOG RECOVER IN PROGRESS |             NULL | NULL | YES          |
|         2 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         3 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         4 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         5 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         6 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         7 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         8 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         9 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        10 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        11 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        13 | thread/innodb/srv_lock_timeout_thread  | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        14 | thread/innodb/srv_error_monitor_thread | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        15 | thread/innodb/srv_monitor_thread       | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        16 | thread/innodb/srv_master_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        17 | thread/innodb/srv_purge_thread         | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        18 | thread/innodb/page_cleaner_thread      | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        19 | thread/sql/shutdown                    | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |                1 | NULL | YES          |
|        20 | thread/sql/con_sockets                 | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |                1 | NULL | YES          |
|        21 | thread/sql/one_connection              | FOREGROUND |              1 | root             | localhost        | performance_schema | Query               |                0 | Sending data      | select * from threads                |               20 | NULL | YES          |
|        35 | thread/sql/one_connection              | FOREGROUND |             15 | root             | localhost        | test               | Sleep               |                0 | NULL              | NULL                                 |             NULL | NULL | YES          |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+
21 rows in set (0.00 sec)

mysql>

Wait events per account and event name
Wait events per host name and event name

mysql> select * from events_waits_summary_by_account_by_event_name where USER IS NOT NULL limit 0,3;
+------+-----------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| USER | HOST      | EVENT_NAME                                    | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------+-----------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| root | localhost | wait/synch/mutex/sql/PAGE::lock               |          0 |              0 |              0 |              0 |              0 |
| root | localhost | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync   |          0 |              0 |              0 |              0 |              0 |
| root | localhost | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active |          0 |              0 |              0 |              0 |              0 |
+------+-----------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql> select * from events_waits_summary_by_host_by_event_name limit 0,3;
+------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| HOST | EVENT_NAME                                    | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| NULL | wait/synch/mutex/sql/PAGE::lock               |          0 |              0 |              0 |              0 |              0 |
| NULL | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync   |          0 |              0 |              0 |              0 |              0 |
| NULL | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active |          0 |              0 |              0 |              0 |              0 |
+------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql>

Connection statistics per client host name

mysql> select * from hosts;
+-----------+---------------------+-------------------+
| HOST      | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+-----------+---------------------+-------------------+
| NULL      |                  19 |                21 |
| localhost |                   2 |                10 |
+-----------+---------------------+-------------------+
2 rows in set (0.00 sec)

mysql>

Mutex synchronization object instances

mysql> select * from mutex_instances;
+-------------------------------------------------+-----------------------+---------------------+
| NAME                                            | OBJECT_INSTANCE_BEGIN | LOCKED_BY_THREAD_ID |
+-------------------------------------------------+-----------------------+---------------------+
| wait/synch/mutex/mysys/my_thread_var::mutex     |             711531888 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |             749544400 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK::mutex          |             713680056 |                NULL |
| wait/synch/mutex/innodb/autoinc_mutex           |             749887584 |                NULL |
| wait/synch/mutex/innodb/zip_pad_mutex           |             713913576 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |             749871280 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |             749874056 |                NULL |
| wait/synch/mutex/innodb/trx_mutex               |             748756816 |                NULL |
| wait/synch/mutex/innodb/trx_undo_mutex          |             748757472 |                NULL |
+-------------------------------------------------+-----------------------+---------------------+
9 rows in set (0.00 sec)

mysql>

Active connection instances

mysql> select * from socket_instances;
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
| EVENT_NAME                           | OBJECT_INSTANCE_BEGIN | THREAD_ID | SOCKET_ID | IP               | PORT  | STATE  |
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
| wait/io/socket/sql/client_connection |              10782080 |        33 |       380 | ::ffff:127.0.0.1 | 50522 | ACTIVE |
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
1 row in set (0.00 sec)

mysql>

events_statements_summary_by_digest
Statement events per schema and digest value
累積実行時間が最も長いSQLを特定する事が可能

mysql>  SELECT * FROM performance_schema.events_statements_summary_by_digest order by sum_timer_wait desc limit 1\G
*************************** 1. row ***************************
                SCHEMA_NAME: test
                     DIGEST: 9c2953f8e62dc70ec329b2b787819a46
                DIGEST_TEXT: INSERT INTO LANGUAGE ( NAME ) VALUES (?)
                 COUNT_STAR: 590000
             SUM_TIMER_WAIT: 53454275577080
             MIN_TIMER_WAIT: 60344970
             AVG_TIMER_WAIT: 90312200
             MAX_TIMER_WAIT: 61756713890
              SUM_LOCK_TIME: 15936952000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 590000
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2014-08-08 09:14:27
                  LAST_SEEN: 2014-08-08 11:23:22
1 row in set (0.00 sec)

mysql>

Reference:
22.1 Performance Schema Quick Start
22.9.1 Performance Schema Table Index
パフォーマンス・スキーマ:MySQLサーバーの稼働統計を確認可能


MYSQL5.5.25にはオプティマイザーのバグがあったようで、
5.5.25をインストールした人は5.5.25aにアップグレードしてくださいとの事です。

Bug #65745
MySQL 5.5.25a is a replacement of MySQL 5.5.25, in that version a
regression bug was detected in the optimizer (Bug #65745).
That bug is specific to MySQL 5.5.25, other versions are not affected.

MySQL Community Server 5.5.25a has been released

MySQL 5.5.25a がリリースされました

改めてMYSQLのシステムテーブル(performance_schema)をレビューしてみます。

データベースサイズ

select table_schema, 
sum(data_length+index_length) /1024 /1024 as MB
from information_schema.tables group by table_schema 
order by sum(data_length+index_length) desc;

テーブルサイズ

select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as allMB,
floor((data_length)/1024/1024) as dMB,
floor((index_length)/1024/1024) as iMB
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;

MYSQL TABLE SIZE

PERFORMANCE_SCHEMA

mysql> show tables;
+———————————————-+
| Tables_in_performance_schema |
+———————————————-+
| cond_instances |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| mutex_instances |
| performance_timers |
| rwlock_instances |
| setup_consumers |
| setup_instruments |
| setup_timers |
| threads |
+———————————————-+
17 rows in set (0.01 sec)

mysql>

PERFORMANCE_SCHEMAを有効にしてパフォーマンスを確認

mysql> SHOW VARIABLES LIKE ‘perf%’;
+—————————————————+———+
| Variable_name | Value |
+—————————————————+———+
| performance_schema | OFF |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+—————————————————+———+
16 rows in set (0.00 sec)

mysql> set performance_schema = ON;
ERROR 1238 (HY000): Variable ‘performance_schema’ is a read only variable
mysql>

動的に設定変更出来ないようなので、/etc/my.cnfに以下のラインを追加して再起動。

————————————————————–
[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=1500
————————————————————–

[root@CentOS64VM mysql]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL…….. SUCCESS!
[root@CentOS64VM mysql]#

mysql> SHOW VARIABLES LIKE ‘perf%’;
+—————————————————+———+
| Variable_name | Value |
+—————————————————+———+
| performance_schema | ON |
| performance_schema_events_waits_history_long_size | 1500 |
| performance_schema_events_waits_history_size | 20 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+—————————————————+———+
16 rows in set (0.01 sec)

mysql>

テーブルが幾つかあるので、I/O関連のテーブルを2つだけ確認してみました。

file_summary_by_instance
File events summarized per file instance

SELECT SUBSTRING_INDEX(FILE_NAME, '/', -2) AS DATA_FILE, 
       COUNT_READ, 
       IFNULL(ROUND(SUM_NUMBER_OF_BYTES_READ/1024/1024/1024, 2), 0.00) AS READ_GB,
       IFNULL(ROUND((SUM_NUMBER_OF_BYTES_READ/COUNT_READ)/1024, 2), 0.00) AS AVG_READ_KB,
       COUNT_WRITE, 
       IFNULL(ROUND(SUM_NUMBER_OF_BYTES_WRITE/1024/1024/1024, 2), 0.00) AS WRITE_GB,
       IFNULL(ROUND((SUM_NUMBER_OF_BYTES_WRITE/COUNT_WRITE)/1024, 2), 0.00) AS AVG_WRITE_KB,
       IFNULL(ROUND((SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE)/1024/1024/1024, 2), 0.00) AS TOTAL_GB, 
       IFNULL(100-((SUM_NUMBER_OF_BYTES_READ/(SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE))*100), 0.00) AS WRITE_PCT 
  FROM file_summary_by_instance 
 ORDER BY TOTAL_GB DESC LIMIT 10;

file_summary_by_instance
file_summary_by_instance

events_waits_summary_global_by_event_name
Wait events summarized per event name

SELECT EVENT_NAME,
       SUM_TIMER_WAIT/1000000000 WAIT_MS,
       COUNT_STAR
  FROM performance_schema.events_waits_summary_global_by_event_name 
 ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC LIMIT 30;

events_waits_summary_global_by_event_name

参考
MySQL 5.5新機能徹底解説
Tracking IO with PERFORMANCE_SCHEMA
Monitoring MySQL IO Latency with performance_schema
Tracking mutex locks in a process list, MySQL 5.5′s PERFORMANCE_SCHEMA
21.2.2. Performance Schema Startup Configuration
21.8. Performance Schema System Variables