2016年9月13日火曜日

MySQL 8.0.0現在で追加されているperformance_schema

MySQL 8.0.0時代のmy.cnfの探り方 で出てきたvariables_info の他にも events_errors_summary_* というテーブルが増えてる。


$ diff <(mysql80 -sse "SHOW TABLES FROM p_s") <(mysql57 -sse "SHOW TABLES FROM p_s")
3,7d2
< events_errors_summary_by_account_by_error
< events_errors_summary_by_host_by_error
< events_errors_summary_by_thread_by_error
< events_errors_summary_by_user_by_error
< events_errors_summary_global_by_error
93d87
< variables_info

中身はこんな感じ。


mysql80> SELECT * FROM events_errors_summary_global_by_error LIMIT 10;
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
| ERROR_NUMBER | ERROR_NAME              | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
|         NULL | NULL                    | NULL      |                0 |                 0 | NULL       | NULL      |
|         1002 | ER_NO                   | HY000     |                0 |                 0 | NULL       | NULL      |
|         1003 | ER_YES                  | HY000     |                0 |                 0 | NULL       | NULL      |
|         1004 | ER_CANT_CREATE_FILE     | HY000     |                0 |                 0 | NULL       | NULL      |
|         1005 | ER_CANT_CREATE_TABLE    | HY000     |                0 |                 0 | NULL       | NULL      |
|         1006 | ER_CANT_CREATE_DB       | HY000     |                0 |                 0 | NULL       | NULL      |
|         1007 | ER_DB_CREATE_EXISTS     | HY000     |                0 |                 0 | NULL       | NULL      |
|         1008 | ER_DB_DROP_EXISTS       | HY000     |                0 |                 0 | NULL       | NULL      |
|         1010 | ER_DB_DROP_RMDIR        | HY000     |                0 |                 0 | NULL       | NULL      |
|         1012 | ER_CANT_FIND_SYSTEM_REC | HY000     |                0 |                 0 | NULL       | NULL      |
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
10 rows in set (0.00 sec)

エラーコード単位で、何回起こったかとfirst_seen, last_seenを記録してくれるっぽい。
取り敢えず何かエラーにしてみようか。


mysql80> DROP DATABASE non_exist_database_name;
ERROR 1008 (HY000): Can't drop database 'non_exist_database_name'; database doesn't exist

mysql80> SELECT * FROM events_errors_summary_global_by_error LIMIT 10;
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME              | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         NULL | NULL                    | NULL      |                0 |                 0 | NULL                | NULL                |
|         1002 | ER_NO                   | HY000     |                0 |                 0 | NULL                | NULL                |
|         1003 | ER_YES                  | HY000     |                0 |                 0 | NULL                | NULL                |
|         1004 | ER_CANT_CREATE_FILE     | HY000     |                0 |                 0 | NULL                | NULL                |
|         1005 | ER_CANT_CREATE_TABLE    | HY000     |                0 |                 0 | NULL                | NULL                |
|         1006 | ER_CANT_CREATE_DB       | HY000     |                0 |                 0 | NULL                | NULL                |
|         1007 | ER_DB_CREATE_EXISTS     | HY000     |                0 |                 0 | NULL                | NULL                |
|         1008 | ER_DB_DROP_EXISTS       | HY000     |                1 |                 0 | 2016-09-09 12:28:01 | 2016-09-09 12:28:01 |
|         1010 | ER_DB_DROP_RMDIR        | HY000     |                0 |                 0 | NULL                | NULL                |
|         1012 | ER_CANT_FIND_SYSTEM_REC | HY000     |                0 |                 0 | NULL                | NULL                |
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

記録された。


mysql80> SELECT * FROM events_errors_summary_by_account_by_error WHERE SUM_ERROR_RAISED <> 0;
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| USER | HOST      | ERROR_NUMBER | ERROR_NAME                | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| root | localhost |         1008 | ER_DB_DROP_EXISTS         | HY000     |                1 |                 0 | 2016-09-09 12:28:01 | 2016-09-09 12:28:01 |
| root | localhost |         1049 | ER_BAD_DB_ERROR           | 42000     |                1 |                 0 | 2016-09-09 12:21:11 | 2016-09-09 12:21:11 |
| root | localhost |         1329 | ER_SP_FETCH_NO_DATA       | 02000     |                2 |                 2 | 2016-09-09 12:21:27 | 2016-09-09 12:21:34 |
| root | localhost |         3554 | ER_NO_SYSTEM_TABLE_ACCESS | HY000     |              142 |                 0 | 2016-09-09 12:18:41 | 2016-09-09 12:29:11 |
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
4 rows in set (0.01 sec)

ここまでやるなら、 events_errors_summary_by_digest_by_error テーブル(エラーになったステートメントと紐付く)もあったら嬉しかったりするんだけどな。。
レプリケーション関連のテーブル (replication_*)は今のところ動きがなさげ(つまりまだ Seconds_behind_masterとかは取れない)

0 件のコメント :

コメントを投稿