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サーバーの稼働統計を確認可能

Comments are closed.

Post Navigation