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