![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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