MySQL5.6では、Performance SchemaはDefaultでONになっている。
このスキーマを利用して、色々とMySQLの状態を確認する事が出来る。
例)ストレージエンジン,クライアント種類,効率の悪いIndex,時間のかかっているQuery
root@localhost [mysql]>SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+ 1 row in set (0.00 sec) root@localhost [mysql]>SELECT * FROM information_schema.ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) root@localhost [mysql]> root@localhost [performance_schema]>select * from session_connect_attrs; +----------------+-----------------+----------------+------------------+ | PROCESSLIST_ID | ATTR_NAME | ATTR_VALUE | ORDINAL_POSITION | +----------------+-----------------+----------------+------------------+ | 1 | _os | linux-glibc2.5 | 0 | | 1 | _client_name | libmysql | 1 | | 1 | _pid | 2638 | 2 | | 1 | _client_version | 5.6.21 | 3 | | 1 | _platform | x86_64 | 4 | | 3 | _os | linux-glibc2.5 | 0 | | 3 | _client_name | libmysql | 1 | | 3 | _pid | 2882 | 2 | | 3 | _client_version | 5.6.21 | 3 | | 3 | _platform | x86_64 | 4 | | 3 | program_name | mysql | 5 | +----------------+-----------------+----------------+------------------+ 11 rows in set (0.00 sec) root@localhost [performance_schema]> root@localhost [performance_schema]>SELECT object_schema, object_name, index_name -> FROM performance_schema.table_io_waits_summary_by_index_usage -> WHERE index_name IS NOT NULL -> AND count_star = 0 -> AND OBJECT_SCHEMA = 'test' -> ORDER BY object_schema, object_name; +---------------+-----------------+------------+ | object_schema | object_name | index_name | +---------------+-----------------+------------+ | test | federated_table | PRIMARY | | test | federated_table | name | | test | MyISAM_InnoDB | PRIMARY | | test | Personal_Info | PRIMARY | | test | Personal_Info0 | PRIMARY | | test | Personal_Info1 | PRIMARY | +---------------+-----------------+------------+ 6 rows in set (0.01 sec) root@localhost [performance_schema]> root@localhost [performance_schema]>SELECT PROCESSLIST_ID AS id, -> PROCESSLIST_USER AS user, -> PROCESSLIST_HOST AS host, -> PROCESSLIST_DB AS db, -> PROCESSLIST_COMMAND AS command, -> PROCESSLIST_TIME AS time, -> PROCESSLIST_STATE AS state, -> LEFT(PROCESSLIST_INFO, 80) AS info -> FROM performance_schema.threads -> WHERE PROCESSLIST_ID IS NOT NULL -> AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump') -> ORDER BY PROCESSLIST_TIME ASC; +------+---------------+-----------+--------------------+------------------+------+------------------------------------------------------------------+----------------------------------------------------------------------------------+ | id | user | host | db | command | time | state | info | +------+---------------+-----------+--------------------+------------------+------+------------------------------------------------------------------+----------------------------------------------------------------------------------+ | 3 | root | localhost | performance_schema | Query | 0 | Creating sort index | SELECT PROCESSLIST_ID AS id,PROCESSLIST_USER AS user,PROCESS | | 1 | GTID_SSL_USER | CentOS02 | NULL | Binlog Dump GTID | 4743 | Master has sent all binlog to slave; waiting for binlog to be up | NULL | +------+---------------+-----------+--------------------+------------------+------+------------------------------------------------------------------+----------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) root@localhost [performance_schema]>
MySQL待機イベントの確認方法
performance_schemaはそれなりにメモリーも使うので、すべてのデータ取得を常にしている訳ではありません。
特定の値を調べたい場合は、setup_consumersの値をONにして調査して終了後に再度NOにすると良いでしょう。
root@localhost [performance_schema]>select * from performance_schema.setup_consumers WHERE name like 'events_waits%'; +---------------------------+---------+ | NAME | ENABLED | +---------------------------+---------+ | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | +---------------------------+---------+ 3 rows in set (0.00 sec) root@localhost [performance_schema]>UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 root@localhost [performance_schema]>select * from performance_schema.setup_consumers WHERE name like 'events_waits%'; +---------------------------+---------+ | NAME | ENABLED | +---------------------------+---------+ | events_waits_current | YES | | events_waits_history | YES | | events_waits_history_long | YES | +---------------------------+---------+ 3 rows in set (0.00 sec) root@localhost [performance_schema]> root@localhost [performance_schema]>select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name -> where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 10; Empty set (0.01 sec) root@localhost [performance_schema]>select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name -> where COUNT_STAR > 0 and event_name not in ('idle') order by sum_timer_wait desc limit 10; +--------------------------------------+------------+----------------+ | event_name | count_star | sum_timer_wait | +--------------------------------------+------------+----------------+ | wait/io/file/innodb/innodb_data_file | 636 | 2851389865555 | | wait/io/file/sql/FRM | 1197 | 582446988970 | | wait/io/file/sql/binlog | 756 | 389156664435 | | wait/io/file/innodb/innodb_log_file | 24 | 280608815795 | | wait/io/file/csv/data | 866 | 133876420370 | | wait/io/file/myisam/kfile | 157 | 118736606065 | | wait/io/file/csv/metadata | 28 | 79348938130 | | wait/io/file/sql/slow_log | 4 | 63380202270 | | wait/io/file/sql/binlog_index | 30 | 45983365505 | | wait/io/file/sql/query_log | 4 | 38931406360 | +--------------------------------------+------------+----------------+ 10 rows in set (0.01 sec) root@localhost [performance_schema]> root@localhost [performance_schema]>UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE name like 'events_waits%'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 root@localhost [performance_schema]>select * from performance_schema.setup_consumers WHERE name like 'events_waits%'; +---------------------------+---------+ | NAME | ENABLED | +---------------------------+---------+ | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | +---------------------------+---------+ 3 rows in set (0.00 sec) root@localhost [performance_schema]>