mysql异常案例分析

问题背景

mysql主从同步

1、结构:主库业务数据增删改查,从库是负责报表的查询

2、问题:主从异常导致报表数据不准确,上线期间,出现多次,发现时同步已经中断很长时间。

3、解决:脚本自动监控+短信提醒,及时发现异常,人工干预恢复。

mysql查询效率

问题:系统上线运行后,随着数据的不断增长,可能一些语句执行会越来越慢。

解决方向:借助mysql EXPLAIN 排查sql语句。进行针对性修改。

mysql连接数增长异常

1、问题:4月底多次出现知识库mysql连接被占满,导致系统故障。

不是瞬间,隔几天会出现一次。

2、排查:死锁、连接未关闭(接口平台groovy脚本)、show processlist

3、解决:根据排查,优化代码及jdbc连接数配置,解决故障。

代码执行效率

排查:借助Fiddler(http协议调试代理工具),查找执行慢的代码。

解决:优化代码结构,解决问题。

mysql主从同步

监控脚本

监测内容:

1、检查主机IP及端口是否一致

2、检查主从mysql-bin文件是否一致

3、检查主从日志文件位置Position是否一致

4、检查slave_IO_running和slave_sql_running两个线程是否启动【重要】

异常短信发送:使用curl -d 参数 请求连接 (发送短信内容及号码)

人工干预

1、通过sql查询导致同步异常的数据 select * from performance_schema.replication_applier_status_by_worker\G

2、步骤1会查出具体哪张表的问题数据,修改查询出来的异常数据

3、跳过异常步骤 set global sql_slave_skip_counter =1;

4、重新启动同步操作 start slave;

5、查询同步状态是否正常 show slave status\G

mysql连接数异常

监控脚本

监测内容:

1、查询mysql进程,select * from infomation_schema.processlist

Id: 就是这个线程的唯一标识\User: 启动线程的用户\Host: 记录了发送请求的客户端的 IP 和 端口号\DB: 数据库名称\Command: 是指此刻该线程正在执行的命令\Time: 表示该线程处于当前状态的时间(秒)\State: 线程的状态\Info: 记录线程执行的语句。默认只显示前100个字符.

2、查询锁表信息:show open tables where in_use> 0;

3、当连接数超出阈值时,短信提醒,并且抓取1和2的实时结果,定位问题连接。

排查优化

排查:

1、select * from infomation_schema.processlist及锁表信息查询出耗时进程,包含3个:A、sleep进程;

B、km_search_log; C、km_task_sheet 及sys_task_type 。

2、排查A:检查jdbc配置,db.druid.timeBetweenEvictionRunsMillis=60000,db.druid.initialSize=5,60秒关闭空闲连接,B语句耗时长、C语句执行间隔短于执行时长,造成一直在创建新的连接,初始化连接比较小,sleep连接不能回收。

优化–现在正常情况是在30-40之间

1、调整初始化连接至30,减少连接的新建次数,空出回收空闲连接的时间。

2、梳理登录业务,屏蔽km_search_log查询,效果明显

3、反馈信息提醒业务(km_task_sheet 及sys_task_type),原20秒执行一次,调整频率为两分钟,减少语句进程一直叠加。

mysql查询效率-EXPLAIN

EXPLAIN查询结果字段解释

id :编号,id值越大执行优先级越高,id值相同则从上往下执行,id为null最后执行。

select_type :查询类型

table :表名

partitions :分区

type :类型,type常见类型从最优到最差:system > const > eq_ref > ref > range > index > ALL,

possible_keys :预测用到的索引

key :实际使用的索引,如果没有为null。根据这个字段判断是否需要增加索引。

key_len :实际使用索引的长度,

ref :表之间的引用

rows :通过索引查询到的数据量

filtered :指返回结果的行占需要读到的行(rows列的值)的百分比。

Extra :额外的信息

1、Using index

2、Using where

3、Using where Using index

4、NULL

5、Using index condition

6、Using temporary表示MySql需要创建一张临时表来处理查询。一般需要优化

mysql查询效率-案例分析

1、关联表字段类型不一致,导致索引失效

描述:原因是his_phs_send的phs_id和csp_contact的call_id字段类型不一致,导致索引失效

排查过程:

EXPLAIN 查看语句分析结果,d表Extra提示未使用索引,key值使用索引项为空,type为ALL,效率最慢,数据超600W。缩减语句至his_phs_send和csp_contact关联,索引还未执行,定位至d.call_id未走索引,排查原因,phs_id为bigint类型,call_id为varchar类型,修改phs_id为varchar,语句执行时间由9秒多减少至0.3秒。

2、减少关联出来的数据量

3、对应字段增加索引

4、减少语句中的子查询

多个子查询拼接的sql语句,索引不会生效

代码执行效率-Fiddler

mysql恢复delete数据

1、日志查找delete删除语句

mysqlbinlog –no-defaults –database=cspwcsdb –start-datetime=”2019-01-11 08:20:09″ –stop-datetime=”2019-01-11 10:00:50″ –base64-output=decode-rows -v -v mysql-bin.000022 | sed -n ‘/### DELETE FROM /,/COMMIT/p’ > cspwcsdb20190111-3.txt

2、转换delete语句为insert语句

cat cspwcsdb20190111-3.txt | sed -n ‘/###/p’ | sed ‘s/### //g;s//*. /,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;’ | sed -r ‘s/(@4. ),/\1;/g’ | sed ‘s/@[1-9]=//g’ > t1.sql

3、替换;和其余@10之后的@序号,生成需要执行的insert语句

备注:此方式只恢复过少量数据,大批量数据没有使用过。当时生成的文件未保存,大家可以测试下,看下生成的文件内容。

总结

本次分享主要涵盖了mysql同步异常监控、恢复、sql性能优化、代码优化工具及delete数据恢复,在现场实施使用率较高。

执行效率的提升,直接提升用户感知。

  • mysql 主从同步的监控
  • show processlist讲解
  • mysql EXPLAIN分析
  • 抓包工具fiddler分享
  • mysql恢复delete数据
SegmentFault博客
我还没有学会写个人说明!
上一篇

不改一行代码!快速迁移 Express 应用上云

下一篇

基于源码剖析nodejs模块系统

你也可能喜欢

评论已经被关闭。

插入图片