MySQL异常处理思路

一、现象

业务反馈：查询页面缓慢，写入数据缓慢甚至无法写入；

监控告警：CPU长时间100%使用率告警

二、排查思路

【1】操作系统检查：CPU、内存、硬盘的使用率

1、检查CPU
top
top -Hu mysql
    用户态CPU ----us
    系统态cpu ----sy
    wa这个值比较大的时候，一般是io出了问题，需要使用iotop看每个线程负载
2、检查内存和swap使用率
free -g3、检查磁盘使用率
df -hl
复制

【2】根据日志定位问题根因

操作系统层面
/var/log/messages
数据库层面
/data/mysql/logs/error.log (主要看这个故障时间点的错误信息)
其他参考日志
    MGR服务日志：/data/tools/HAIPMGR/HAIPMGR-master/HAIPMGR3307.log
    MHA服务日志：/data/mha/ha_monitor/monitor.log
复制

【3】数据库层面检查：检查大事务、并发数、是否有元数据锁

查看执行时间较长的事务（5个）：
select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time 
from INFORMATION_SCHEMA.INNODB_TRX t 
order by idle_time desc limit 5\G;
查看执行时间大于10min的事务
select t.* from INFORMATION_SCHEMA.INNODB_TRX t where to_seconds(now())-to_seconds(t.trx_started)>600\G;
查看数据库实时的并发数
show status like '%thread%';
Threads_running 表示正在执行的SQL，也就是并发数
查不到具体的sql语句时，可以根据下面的sql再次查询确认：
select trx_id,trx_operation_state,trx_mysql_thread_id prs_id,now(),
trx_started,to_seconds(now())-to_seconds(trx_started) trx_es_time,
user,db,host,command,state,Time,info current_sql,PROCESSLIST_INFO 
last_sql,t4.ROWS_AFFECTED 'ROWS_AFFECTED(last)',t4.ROWS_SENT as 'ROWS_SENT(last)' 
,t4.ROWS_EXAMINED as 'ROWS_EXAMINED(last)',t1.trx_rows_locked,t1.trx_rows_modified 
from information_schema.innodb_trx t1,information_schema.processlist t2,performance_schema.threads  
t3,performance_schema.events_statements_current t4 where t1.trx_mysql_thread_id=t2.id  and   
t1.trx_mysql_thread_id=t3.PROCESSLIST_ID and t1.trx_mysql_thread_id!=connection_id() and   
t3.THREAD_ID = t4.THREAD_ID and to_seconds(now())-to_seconds(trx_started) >= 5;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
information_schema.INNODB_TRX 表中包含了当前innodb内部正在运行的事务信息，这个表中给出了事务的开始时间，我们可以稍加运算即可得到事务的运行时间
查询正在执行的事务：SELECT * FROM information_schema.INNODB_TRX
根据这个事务的线程ID（trx_mysql_thread_id）:可以使用mysql命令：kill  线程id
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
复制

检查锁状态

MySQL5.7版本

包含锁信息的三张表：INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS
查看正在锁的事务
select * from information_schema.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    根据等待锁的requesting_trx_id去查找具体的sql（INNODB_LOCK_WAITS.requesting_trx_id=INNODB_TRX.trx_id）
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 16188222          | 16188222:97:3:4   | 16188219        | 16188219:97:3:4  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
查看锁表状态
show OPEN TABLES where In_use > 0;
查看具体的sql（INNODB_LOCK_WAITS.requesting_trx_id=INNODB_TRX.trx_id）
select * from INNODB_TRX where trx_id=16188224\G;
复制

MySQL8.0版本

从 MySQL 8.0 开始，performance_schema.data_locks显示 InnoDB 数据锁，Information Schema 表INNODB_LOCKS和INNODB_LOCK_WAITS从 MySQL 5.7 开始弃用

select * from performance_schema.data_lock_waits\G
    data_lock_waits中包含了线程ID，可以根据threads表去查看procesid和sql信息
select * from threads where THREAD_ID='65'\G;
    数据库process ID：PROCESSLIST_ID
    sql信息：PROCESSLIST_INFO
复制

三、其他总结项

检查存储，是否数据目录满，这个问题一般测试库比较多
检查是否有慢sql，常见就是慢SQL阻塞后续业务。
检查是否有备份任务影响业务SQL，不常见。例如：xtrabackup备份陷阱引起的一次严重业务故障如果并发SQL比较高时，1-2s的SQL也需要关注和优化
复制