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