Mysql IO异常记录

故障记录

监控显示磁盘IO异常，客户方断断续续接收到磁盘告警，如下：

我方：增加监控显示如下：

并且当出现IO问题时，执行lsof | grep deleted，可见：

已确定，由于数据库问题，而导致磁盘IO异常。

临时处理，将mysql的临时目录路径修改到大的空间去：

参考地址：https://cloud.tencent.com/developer/article/1506494

修改下mysql的tmpdir目录.

[mysqld]

tmpdir = /utry/data

然后启动mysql即可

当出现IO异常时，显示如下，并可见目录路径已修改到/utry/data

解决办法：

开启mysql慢日志.

[mysqld]

slow_query_log = ON

slow_query_log_file = /utry/mysql/data/slow.log

long_query_time = 1

查看日志，对慢日志进行分析。查找到信息如下：

# Time: 2021-03-11T03:41:00.398273Z

# Time: 2021-03-11T03:41:00.398273Z

# User@Host: root[root] @ [10.0.0.1] Id: 2163

# Query_time: 385.629880 Lock_time: 0.000674 Rows_sent: 10 Rows_examined: 5705690

use ccdb;

SET timestamp=1615434060;

SELECT

CDRS.ANSWER_STAMP AS answerStamp,

CDRS.END_STAMP AS endStamp,

CDRS.START_STAMP AS startStamp,

CDRS.BILLSEC AS billsec,

CDRS.CALL_ID AS callId,

CDRS.CALLER_ID_NUMBER AS callerIdNumber,

CDRS.CALL_ID AS callId,

CDRS.CALLER_ID_NUMBER AS callerIdNumber,

CDRS.CONTEXT AS context,

CDRS.DESTINATION_NUMBER AS destinationNumber,

CDRS.DIRECTION AS direction,

CDRS.DURATION AS duration,

CDRS.HANGUP_CAUSE AS hangupCause,

CDRS.LOCAL_IP_V4 AS localIpV4,

READ_CODEC AS readCodec,

CDRS.ROUTE AS route,

CDRS.ROUTE_IP AS routeIp,

CDRS.UUID AS uuid,

CDRS.WRITE_CODEC AS writeCodec,

CDRS.VLAN_ID AS vlanId,

RECORD_CDRS.FULL_RECORD_PATH AS recordPath

FROM

CDRS LEFT JOIN record_cdrs ON CDRS.uuid=record_cdrs.uuid

 

 

ORDER BY

startStamp desc

 

 

LIMIT 0,10;

解决办法：

确定升级时间，添加索引。