mysql 案例~mysql主从复制延迟处理(2)

一 简介:今天来聊聊周期性从库延迟的问题，是上一篇的基础分析的一个场景

二 背景:近期每天的指定时间段,收到从库延迟的报警,然后过一段时间恢复.由于从库是提供读服务的,所以需要解决

三 分析思路:

            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的压力