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

Comments are closed.

Post Navigation