一 简介:今天来聊聊周期性从库延迟的问题，是上一篇的基础分析的一个场景
二 背景:近期每天的指定时间段,收到从库延迟的报警,然后过一段时间恢复.由于从库是提供读服务的,所以需要解决
三 分析思路:
1 周期性延时,而且全部从库都出现延迟，应该是由于主库的DML操作引起的
2 查看主库的慢日志记录(我们的数据库会每小时进行切割)，也并没有发生DML慢语句,排除因为慢sql(DML操作)导致的问题，主库的DML操作如果出现慢语句,同步到从库会更慢,比如update,delete语句
3 查看从库的慢日志记录，是否出现DML慢语句，并没有出现
4 查看天兔平台记录的DML语句曲线图,发现这段时间内出现了大量的并发insert操作,定位到了问题
四 解决问题:
1 采用mysqlbing进行指定时间段内的分析
mysqlbinlog --no-defaults --start-datetime='2017-11-17 07:50:00' --stop-datetime='2017-11-17 08:20:00' --base64-output=decode-rows -vv binlogname > result.txt
2 运用AWK工具进行这段时间内的增删查改统计
awk '/###/ {if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count) print i," ",count[i]}' 文件名| column -t | sort -k3n
会统计 库+表 增删查改次数 并进行排序
3 根据结果，发现了 insert最高的一张表，然后和运维确认业务IP，和研发进行沟通，得知业务一段时间进行集中处理，导致了上述情况。
4 可以先根据pt-iopfile进行定位,可以清晰的定位到表,具体为pt-iopfile -p pid,针对mysql文件具体IO进行分析,对于集中表的业务,能快速具体进行定位
五 针对大事务的处理方式
1 生成大量event的真大事务
2 时间跨度大才提交的假大事务
分析脚本
#!/bin/bash
BINLOG_FILE="mysql-bin.000006"
mysqlbinlog --base64-output=decode-rows -vv $BINLOG_FILE | awk
'BEGIN {xid="null";s_type="";stm="";endtm="";intsta=0;inttal=0;s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;bf=0;period=0;}
{
if(match($0,/^(BEGIN)/)) {bg=1;}
if(match($0,/#.*server id/))
{if(bg==1) {statm=substr($1,2,6)" "$2;cmd=sprintf("date -d "%s" +%%s",statm);cmd|getline intsta;close(cmd);bg=0;bf=1;}
else if(bf==1) {endtm=substr($1,2,6)" "$2;cmd=sprintf("date -d "%s" +%%s",endtm);cmd|getline inttal;close(cmd);}}
if(match($0,/#.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4);flag=1}
else if(match($0,/#.*Xid =.*/)) {xid=$(NF)}
else if(match($0,/(### INSERT INTO .*..*)/)) {count=count+1;insert_count=insert_count+1;s_type="INSERT";s_count=s_count+1;}
else if(match($0,/(### UPDATE .*..*)/)) {count=count+1;update_count=update_count+1;s_type="UPDATE";s_count=s_count+1;}
else if(match($0,/(### DELETE FROM .*..*)/)) {count=count+1;delete_count=delete_count+1;s_type="DELETE";s_count=s_count+1;}
else if(match($0,/^(# at)/) && flag==1 && s_count>0) {print " Query Type : "s_type " " s_count " row(s) affected" ;s_type="";s_count=0;}
else if(match($0,/^(COMMIT)/)) {period=inttal-intsta;if(inttal==0) {period=0};
print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " delete_count " Xid : " xid " period : " period
" ]
+----------------------+----------------------+----------------------+----------------------+";
count=0;insert_count=0;update_count=0;delete_count=0;s_type="";s_count=0;flag=0;bf=0;bg=0;}}' >> fenxi.txt
cat fenxi.txt|grep -v 'Xid'|awk -F':' '{print $5,$6}'|awk '{print $1,$4}'|sort -n |uniq -c|sort -nr 按照库-表-操作 进行分类统计
六 解决方式:
1 研发进行业务优化,减少DML最高的表的处理
2 不同业务库进行迁移,减少单台DB的压力