数据库优化经典案例

1、数据库优化经典案例技术创新 变革未来如果事务过程中发生了网络异常TCP重传会对数据库产生什么影响大量删除导致的SQL慢查一次奇怪的SQL慢查的分析一次SQL RT增加的问题排查利用strace利器解决性能问题如何去定位MySQL性能问题的根源案例列表以下案例均在事务开始之后假如强制关闭应用假如client机器突然崩溃宕机/断电假如网络发生抖动/网卡发生故障如果事务中发生了网络异常模拟client突然宕机如果事务中发生了网络异常Client 192.168.56.102Server 192.168.56.10110:00:00begin;select * from zandb.t1 for up

2、date;10:00:10power off断电MySQL Server事务信息如果事务中发生了网络异常MySQL Server 主机TCP连接信息如果事务中发生了网络异常启动一个新的client，对t1表进行加锁需要等待如果事务中发生了网络异常服务端为什么没有退出这个事务呢？TCP 新建连接需要三次握手TCP 关闭连接需要四次挥手如果事务中发生了网络异常如果事务中发生了网络异常如果事务中发生了网络异常网络异常的时候，TCP连接的状态还是 ESTABLISHED，说明了任何一方都没有发送FIN 包，服务端还在等待CLIENT发送数据，此时的MySQL事务无法直接退出。如果事务中发生了网络异常事

3、务什么时候会退出？1.MySQL参数 wait_timeout（默认8小时） 表示的是MySQL的一个连接空闲超过8小时，MySQL自动断开该连接如果事务中发生了网络异常事务什么时候会退出？2.等待TCP超时TCP 超时参数/proc/sys/net/ipv4/tcp_keepalive_time = 7200（等待空闲时间秒）/proc/sys/net/ipv4/tcp_keepalive_intvl = 75（探测间隔秒）/proc/sys/net/ipv4/tcp_keepalive_probes = 9（探测次数）如果事务中发生了网络异常事务什么时候会退出？2.等待TCP超时 修改TC

4、P超时# echo 60 /proc/sys/net/ipv4/tcp_keepalive_time# echo 7 /proc/sys/net/ipv4/tcp_keepalive_intvl# echo 5 /proc/sys/net/ipv4/tcp_keepalive_probes如果事务中发生了网络异常事务什么时候会退出？开始抓包# tcpdumphost 192.168.56.102 -s 0 -w /tmp/tcp_timeout.pcap如果事务中发生了网络异常事务什么时候会退出？3.主动kill 异常会话mysql kill thread_id;TCP重传会对数据库产生什么影

5、响超时重传TCP每发送一个报文段，就对这个报文段设置一次计时器。 当计时器超时而没有收到确认时，就重传该报文。快重传在发送端一连收到4个ack报文，其中3个重复报文时，就 立即重传相应的报文而不等到定时器超时。TCP重传会对数据库产生什么影响数据库大量慢查模拟40%的重传$sudo tc qdisc add dev eth0 root netem loss 40%TCP重传会对数据库产生什么影响数据库大量慢查TCP重传会对数据库产生什么影响数据库大量慢查在网络发生重传的时候，SQL的执行结果发送给客户端的时 候需要通过TCP重传来完成，SQL的执行state表现为 Sending to cli

6、ent。如果一个block在net_write_timeout时间内都没有完成发送，SQL执行将会中断，抛出1161错误(Got timeout writing communication packets)TCP重传会对数据库产生什么影响大量SQL被kill每个实例部署了sql killer当实例中存在执行时间超过1秒的SQL，立即执行kill query thread_id。收到大量SQL被kill的告警TCP重传会对数据库产生什么影响大量SQL被kill日志里面的thread id都是同一个执行时间超过killer的阈值，以1秒递增TCP重传会对数据库产生什么影响大量SQL被kill在执行

7、 kill query thread_id 时，MySQL 做了两件事：把对应的session的运行状态值(killed )改成THD:KILL_QUERY给对应的session的执行线程发一个信号。被kill的线程需要在埋点的地方/可唤醒的等待判断线程状态TCP重传会对数据库产生什么影响大量SQL被kill为啥执行kill query thread_id无效线程没有执行到判断线程状态的逻辑。读写 IO 的函数一直无法返 回，线程一直在等待进入innodb等，会导致不能及时判断线程的状 态。终止逻辑耗时较长。例如大事务回滚，临时文件删除等。需要等这 些收尾工作完成后，SQL语句才会退出。大量删

8、除导致的SQL慢查现象描述执行sbtest1表的部分查询非常慢，状态为 Sending data执行除sbtest1表外的其他表查询都非常快大量删除导致的SQL慢查分析问题数据库的隔离级别为RR查询监控慢查的量是大量删除操作之后开始的询问业务方，得知该表会经常执行删除旧数据的操作History list length 非常大，达到几万，并且一直在增长查询innodb_trx 表发现有一个事务，很久没有提交搭建模拟测试环境，利用sysbench创建一张48730893行的sbtest1表Sess1Sess2Sess3t1begin;select * from sbtest1 limit 1;t2

9、delete from sbtest1 order by id limit 1000;()t3select * from sbtest1 limit 1()select * from sysbench.sbtest1 order by id desc limit 1;t4select * from sbtest2 limit 1()select * from sysbench.sbtest1 where id=21134001 limit 1;(select * from sysbench.sbtest1 where k=25990706 limit 1;大量删除导致的SQL慢查执行sbtes

10、t1主键扫描很慢执行sbtest2 主键扫描很快大量删除导致的SQL慢查通过profile，发现大部分时间耗费在sending data大量删除导致的SQL慢查Sending dataThe thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is oft

11、en the longest-running state over the lifetime of a given query.表示在读取以及处理行数据以及发送数据到客户端，由于数据只有一 行，且当时网络正常，那么时间就是耗费在读取和处理select的数据大量删除导致的SQL慢查deleted:11DATA_TRX_ID(1000)DATA_ROLL_PTRaaadeleted:12DATA_TRX_ID(1001)DATA_ROLL_PTRbbbdeleted:13DATA_TRX_ID(1002)DATA_ROLL_PTRcccdeleted:14DATA_TRX_ID(1003)DATA

12、_ROLL_PTRddddeleted:15DATA_TRX_ID(1004)DATA_ROLL_PTReeedeleted:1.deleted:1deleted:1.deleted:020000000DATA_TRX_ID(20)DATA_ROLL_PTRabcdeleted:020000001DATA_TRX_ID(20)DATA_ROLL_PTRedfdeleted:020000002DATA_TRX_ID(20)DATA_ROLL_PTRghi主键记录大量删除导致的SQL慢查deleted:11DATA_TRX_ID(1000)DATA_ROLL_PTRaaadeleted:12DAT

13、A_TRX_ID(1001)DATA_ROLL_PTRbbbdeleted:13DATA_TRX_ID(1002)DATA_ROLL_PTRcccselect * from sbtest1 limit 1扫描记录的过程通过主键，扫描到ID=1的记录，根据MVCC比对DATA_TRX_ID，记录可见由于ID=1已经被标记为DELETED，删除记录可见由于表数据还没有全部扫描完成，未满足limit 1，继续扫描下一条记录扫描到ID=2的记录，根据MVCC比对DATA_TRX_ID，记录可见由于ID=2已经被标记为DELETED，删除记录可见由于表数据还没有全部扫描完成，未满足limit 1，继续扫

14、描下一条记录重复4-6步骤，直到满足找到一条记录，或者全表扫描完成大量删除导致的SQL慢查总结大量删除是由ID从小到大进行，由于存在活跃事务，因此ID头部存在大量标 记为删除还没被purge的行，在主键中依然存在当通过主键正序全表扫描取limit 1的时候，根据当前的事务ID去判断该行是否 可见，发现不可见之后，继续往下扫描，直到找到第一行当通过主键逆序全表扫描取limit 1的时候，由于逆序这边ID没有进行删除操作，只需要取一条判断可见即可返回当通过二级索引扫描记录时，如果选择性好，符合条件的记录少，SQL速度快。 相反的，如果符合条件的记录多且大部分被标记为删除的时候，SQL速度慢。只针对

15、RR有效，RC不存在该问题大量删除导致的SQL慢查一次奇怪的SQL慢查的分析慢查解剖mysql select * from t1 where b=1234567890A limit 1; Empty set (8.23 sec)Rows_examined太多了，全表扫描了吧一次奇怪的SQL慢查的分析表结构b 列上面有索引，没走索引么，隐式转化了么？一次奇怪的SQL慢查的分析查看执行计划走索引的呀，为啥rows那么大，实际匹配为0呢？一次奇怪的SQL慢查的分析不会碰到BUG了吧？从业务方确认SQL的作用业务上一直重复插入，如果记录存在就不插入确认了下业务方最近插入的记录一次奇怪的SQL慢查的分析

16、好像突然发现了什么插入的数据被截断了一次奇怪的SQL慢查的分析一共有20万的脏数据一次奇怪的SQL慢查的分析为什么会慢呢MySQL Server 在传给Innodb执行的时候，由于Innodb 定义t1表的b字段为varchar(10)，截取了前 10 个字符1234567890，给Innodb。Innodb 拿到第/下一条数据后，返回Server层。Server层判断发现b 的值不是1234567890X，继续请求下一条循环执行步骤2和步骤3，直到扫描完全部1234567890的记录返回空一次奇怪的SQL慢查的分析如果表里面的b列都是123456789 会慢么？一次奇怪的SQL慢查的分析如果

17、表里面的b列都是123456789 会慢么？MySQL Server 在传给Innodb执行的时候，由于Innodb 定义t1表 的b字段为varchar(10)，截取了前 10 个字符1234567890，给Innodb。Innodb 查找索引发现没有1234567890的记录，直接返回空。一次SQL RT增加的问题排查问题现状分布在100ms到1s的SQL大量增加慢查日志的SQL都是扫描100行以下数据库的thread running 没有特别升高多个机器上的实例都出现了慢查一次SQL RT增加的问题排查问题排查是不是某台交换机出问题了？网络发生了堵塞？有没有批量修改过数据库的参数？是不是

18、在执行批量任务？一次SQL RT增加的问题排查排查结果网络流量正常，没有大文件传输，没有网络重传数据库发生过机房切换，已经过去5天，数据库的量同比持平不同机房的数据库主要参数均保持一致备份任务已经完成，且备份任务均在从库发生机器上未发现异常进程一次SQL RT增加的问题排查继续排查总不可能这些机器同时出现问题吧？难道磁盘或者其他硬件有问题？一次SQL RT增加的问题排查无磁盘坏块一次SQL RT增加的问题排查磁盘的状态一次SQL RT增加的问题排查磁盘的状态squize 抖动一次SQL RT增加的问题排查正常机器qusize 稳定一次SQL RT增加的问题排查Raid 的日志一次SQL RT增

19、加的问题排查Raid 数据一致性校验一致性校验是磁盘阵列控制器的一种高级维护功能。它可以预先 检查阵列上的数据，以保证它们的一致性，即数据是正确的、没 有被破坏。对于有奇偶校验值的阵列（RAID-5），一致性校验通 过数据的奇偶校验，并且和存校验值的盘上的校验值进行比较， 确定并纠正数据的一致性。一次SQL RT增加的问题排查改进降低校验的速率MegaCli64 -AdpSetProp CCRate 10 aALL修改校验的时间MegaCli64 -AdpCcSched -SetStartTime xx -Aall扩大检验的间隔MegaCli64 -AdpCcSched -SetDelay 7

20、20 -aALL关闭raid 校验利用strace利器解决性能问题业务逻辑业务写入A表，通过canal监听binlog产生消息，应用接 收消息经过一定逻辑(a,b,c)对消息进行三次校验(查询DB)并且聚合，然后写入B表。抽象的逻辑图示如下:aA - binlog -b - Bc分析过程a,b,c 的dao层耗时增加，部分简单查询达到了100多ms实际测试SQL执行非常快，1ms 都不需要将Proxy慢查日志记录设置到100ms，未发现慢查逻辑死锁出现利用strace利器解决性能问题DBA也要去看代码分析业务操作DB的逻辑，如下func a do_something t1=now()call get_xxx_from_db() do_somethingrt=now()-t1 # 计算耗时利用strace利器解决性能问题又陷入了僵局get_x