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]>

Comments are closed.

Post Navigation