概要
第16回 MySQLのエラーコードについてでは、
昔のMySQLはエラーはエラーログに出力されるかアプリケーション側に出力されるため、
今回は、
error_log
このテーブルはMySQLで起きたエラーログの内容が書き込まれます。カラムは上から時間
サブシステムはイベントが発生したサブシステムを確認するカラムで、
mysql> SELECT * FROM performance_schema.error_log ORDER BY LOGGED DESC limit 1\G
*************************** 1. row ***************************
LOGGED: 2021-04-23 14:19:15.873988
THREAD_ID: 15
PRIO: Warning
ERROR_CODE: MY-011153
SUBSYSTEM: Repl
DATA: Timeout waiting for reply of binlog (file: mysql-bin.000075, pos: 416), semi-sync up to file , position 0.
1 row in set (0.00 sec)
このテーブルは固定サイズのメモリを確保し、
- Error_
log_ buffered_ bytes - 現在利用しているerror_
logテーブルのサイズ - Error_
log_ buffered_ events - 現在存在しているerror_
logテーブルのイベントの数(error_ logテーブルの件数) - Error_
log_ expired_ events - 領域を確保するために破棄されたイベントの数
- Error_
log_ latest_ write - 最後に書き込まれたエラーログの時間
mysql> show status like 'Error_log%'; +---------------------------+------------------+ | Variable_name | Value | +---------------------------+------------------+ | Error_log_buffered_bytes | 117920 | | Error_log_buffered_events | 681 | | Error_log_expired_events | 0 | | Error_log_latest_write | 1619155155873988 | +---------------------------+------------------+ 4 rows in set (0.00 sec)
このテーブルはSQLでエラーログを引くことができる点が魅力です。たとえば、
mysql> SELECT RIGHT(logged,15),prio,error_code,subsystem,LEFT(data,22) FROM performance_schema.error_log ORDER BY LOGGED desc limit 3; +------------------+---------+------------+-----------+------------------------+ | RIGHT(logged,15) | prio | error_code | subsystem | LEFT(data,22) | +------------------+---------+------------+-----------+------------------------+ | 14:19:15.873988 | Warning | MY-011153 | Repl | Timeout waiting for re | | 00:41:39.522233 | System | MY-010931 | Server | /usr/local/mysql8023/b | | 00:41:39.510540 | Warning | MY-010330 | Server | 'tables_priv' entry 't | +------------------+---------+------------+-----------+------------------------+ 3 rows in set (0.01 sec)
events_errors_summary_by_account_by_error
このテーブルは、
SUM_SUM_ などで条件を絞って確認してください。筆者の場合ですと、
また、
mysql> SELECT * FROM events_errors_summary_by_user_by_error where ERROR_NAME = 'WARN_DATA_TRUNCATED'; +------+--------------+---------------------+-----------+------------------+-------------------+---------------------+---------------------+ | USER | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +------+--------------+---------------------+-----------+------------------+-------------------+---------------------+---------------------+ | NULL | 1265 | WARN_DATA_TRUNCATED | 01000 | 0 | 0 | NULL | NULL | | root | 1265 | WARN_DATA_TRUNCATED | 01000 | 1 | 0 | 2020-04-10 13:12:30 | 2020-04-10 13:12:30 | | repl | 1265 | WARN_DATA_TRUNCATED | 01000 | 0 | 0 | NULL | NULL | +------+--------------+---------------------+-----------+------------------+-------------------+---------------------+---------------------+ 3 rows in set (0.02 sec)"
events_errors_summary_by_host_by_error
このテーブルは、
mysql> SELECT * FROM events_errors_summary_by_host_by_error where ERROR_NAME = 'WARN_DATA_TRUNCATED'; +-----------+--------------+---------------------+-----------+------------------+-------------------+------------+-----------+ | HOST | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +-----------+--------------+---------------------+-----------+------------------+-------------------+------------+-----------+ | NULL | 1265 | WARN_DATA_TRUNCATED | 01000 | 0 | 0 | NULL | NULL | | localhost | 1265 | WARN_DATA_TRUNCATED | 01000 | 0 | 0 | NULL | NULL | +-----------+--------------+---------------------+-----------+------------------+-------------------+------------+-----------+ 2 rows in set (0.00 sec)
events_errors_summary_by_thread_by_error
このテーブルはTHREAD_
ただし、
mysql> SELECT * FROM events_errors_summary_by_thread_by_error WHERE SUM_ERROR_RAISED <> 0; +-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+ | THREAD_ID | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+ | 68 | 3554 | ER_NO_SYSTEM_TABLE_ACCESS | HY000 | 12 | 0 | 2021-04-29 11:50:22 | 2021-04-29 12:08:14 | +-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+ 1 row in set (0.06 sec)
events_errors_summary_global_by_error
このテーブルは発生したエラーの内容と件数を発生時間等を確認することができます。上の3つテーブルと違って、
mysql> SELECT * FROM events_errors_summary_global_by_error WHERE SUM_ERROR_RAISED <> 0 limit 3; +--------------+---------------------------------+-----------+------------------+-------------------+---------------------+---------------------+ | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +--------------+---------------------------------+-----------+------------------+-------------------+---------------------+---------------------+ | 1064 | ER_PARSE_ERROR | 42000 | 4 | 0 | 2021-03-16 00:42:14 | 2021-04-29 12:21:42 | | 1774 | ER_MALFORMED_GTID_SPECIFICATION | HY000 | 2 | 0 | 2021-04-23 14:17:02 | 2021-04-23 14:18:33 | | 3554 | ER_NO_SYSTEM_TABLE_ACCESS | HY000 | 52 | 0 | 2021-04-27 05:47:48 | 2021-04-29 12:26:13 | +--------------+---------------------------------+-----------+------------------+-------------------+---------------------+---------------------+ 3 rows in set (0.01 sec)
まとめ
今回はMySQLのPerformance_
ヘルスチェックであったり、