有赞数据库经典优化案例.pptx

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

2、 update;断电10:00:10 power 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超

4、时修改TCP超时# 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如果事务中发生了网络异常事务什么时候会退出？开始抓包# tcpdump host 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表现为Sendin

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

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

8、尾工作完成后，SQL语句才会退出。大量删除导致的SQL慢查现象描述1. 执行sbtest1表的部分查询非常慢，状态为 Sending data2. 执行除sbtest1表外的其他表查询都非常快大量删除导致的SQL慢查分析问题1. 数据库的隔离级别为RR2. 查询监控慢查的量是大量删除操作之后开始的3. 询问业务方，得知该表会经常执行删除旧数据的操作4. History list length 非常大，达到几万，并且一直在增长5. 查询innodb_trx 表发现有一个事务，很久没有提交大量删除导致的SQL慢查搭建模拟测试环境，利用sysbench创建一张48730893行的sbtest1表Se

9、ss1Sess2Sess3t1t2t3begin;select * from sbtest1 limit 1;delete from sbtest1 order by id limit1000;()select * from sbtest1 limit 1()select * from sysbench.sbtest1 order byid desc limit 1;t4select * from sbtest2 limit 1()select * from sysbench.sbtest1 whereid=21134001 limit 1;(select * from sysbench.sb

10、test1 wherek=25990706 limit 1;大量删除导致的SQL慢查 执行sbtest1主键扫描很慢 执行sbtest2 主键扫描很快大量删除导致的SQL慢查通过profile，发现大部分时间耗费在sending data大量删除导致的SQL慢查Sending dataThe thread is reading and processing rows for a SELECT statement, andsending data to the client. Because operations occurring during this statetend to perfor

11、m large amounts of disk access (reads), it is often thelongest-running state over the lifetime of a given query.表示在读取以及处理行数据以及发送数据到客户端，由于数据只有一行，且当时网络正常，那么时间就是耗费在读取和处理select的数据大量删除导致的SQL慢查主键记录deleted:1deleted:1deleted:1deleted:1deleted:1deleted:1deleted:1deleted:1deleted:0deleted:0deleted:01DATA_TRX_

12、ID(1000)DATA_TRX_ID(1001)DATA_TRX_ID(1002)DATA_TRX_ID(1003)DATA_TRX_ID(1004)DATA_ROLL_PTRDATA_ROLL_PTRDATA_ROLL_PTRDATA_ROLL_PTRDATA_ROLL_PTRaaabbbcccdddeee2345.200000002000000120000002DATA_TRX_ID(20)DATA_TRX_ID(20)DATA_TRX_ID(20)DATA_ROLL_PTRDATA_ROLL_PTRDATA_ROLL_PTRabcedfghi大量删除导致的SQL慢查select * f

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

14、记录可见5. 由于ID=2已经被标记为DELETED，删除记录可见6. 由于表数据还没有全部扫描完成，未满足limit 1，继续扫描下一条记录7. 重复4-6步骤，直到满足找到一条记录，或者全表扫描完成大量删除导致的SQL慢查总结 大量删除是由ID从小到大进行，由于存在活跃事务，因此ID头部存在大量标记为删除还没被purge的行，在主键中依然存在 当通过主键正序全表扫描取limit 1的时候，根据当前的事务ID去判断该行是否可见，发现不可见之后，继续往下扫描，直到找到第一行 当通过主键逆序全表扫描取limit 1的时候，由于逆序这边ID没有进行删除操作，只需要取一条判断可见即可返回 当通过二级

15、索引扫描记录时，如果选择性好，符合条件的记录少，SQL速度快。相反的，如果符合条件的记录多且大部分被标记为删除的时候，SQL速度慢。 只针对RR有效，RC不存在该问题一次奇怪的SQL慢查的分析慢查解剖mysql select * from t1 where b=1234567890A limit 1;Empty set (8.23 sec)Rows_examined太多了，全表扫描了吧一次奇怪的SQL慢查的分析表结构b 列上面有索引，没走索引么，隐式转化了么？一次奇怪的SQL慢查的分析查看执行计划走索引的呀，为啥rows那么大，实际匹配为0呢？一次奇怪的SQL慢查的分析不会碰到BUG了吧？ 从

16、业务方确认SQL的作用 业务上一直重复插入，如果记录存在就不插入 确认了下业务方最近插入的记录一次奇怪的SQL慢查的分析好像突然发现了什么插入的数据被截断了一次奇怪的SQL慢查的分析一共有20万的脏数据一次奇怪的SQL慢查的分析为什么会慢呢1. MySQL Server 在传给Innodb执行的时候，由于Innodb 定义t1表的b字段为varchar(10)，截取了前 10 个字符1234567890，给Innodb。2. Innodb 拿到第/下一条数据后，返回Server层。3. Server层判断发现b 的值不是1234567890X，继续请求下一条4. 循环执行步骤2和步骤3，直到扫

17、描完全部1234567890的记录5. 返回空一次奇怪的SQL慢查的分析如果表里面的b列都是123456789 会慢么？一次奇怪的SQL慢查的分析如果表里面的b列都是123456789 会慢么？1. MySQL Server 在传给Innodb执行的时候，由于Innodb 定义t1表的b字段为varchar(10)，截取了前 10 个字符1234567890，给Innodb。2. Innodb 查找索引发现没有1234567890的记录，直接返回空。一次SQL RT增加的问题排查问题现状 分布在100ms到1s的SQL大量增加 慢查日志的SQL都是扫描100行以下 数据库的thread run

18、ning 没有特别升高 多个机器上的实例都出现了慢查一次SQL RT增加的问题排查问题排查 是不是某台交换机出问题了？ 网络发生了堵塞？ 有没有批量修改过数据库的参数？ 是不是在执行批量任务？ 一次SQL RT增加的问题排查排查结果 网络流量正常，没有大文件传输，没有网络重传 数据库发生过机房切换，已经过去5天，数据库的量同比持平 不同机房的数据库主要参数均保持一致 备份任务已经完成，且备份任务均在从库发生 机器上未发现异常进程一次SQL RT增加的问题排查继续排查 总不可能这些机器同时出现问题吧？ 难道磁盘或者其他硬件有问题？一次SQL RT增加的问题排查无磁盘坏块一次SQL RT增加的问题

19、排查磁盘的状态一次SQL RT增加的问题排查磁盘的状态qusize 抖动一次SQL RT增加的问题排查正常机器qusize 稳定一次SQL RT增加的问题排查Raid 的日志一次SQL RT增加的问题排查Raid 数据一致性校验一致性校验是磁盘阵列控制器的一种高级维护功能。它可以预先检查阵列上的数据，以保证它们的一致性，即数据是正确的、没有被破坏。对于有奇偶校验值的阵列（RAID-5），一致性校验通过数据的奇偶校验，并且和存校验值的盘上的校验值进行比较，确定并纠正数据的一致性。一次SQL RT增加的问题排查改进 降低校验的速率MegaCli64 -AdpSetProp CCRate 10 aA

20、LL 修改校验的时间MegaCli64 -AdpCcSched -SetStartTime xx -Aall 扩大检验的间隔MegaCli64 -AdpCcSched -SetDelay 720 -aALL 关闭raid 校验利用strace利器解决性能问题业务逻辑业务写入A表，通过canal监听binlog产生消息，应用接收消息经过一定逻辑(a,b,c)对消息进行三次校验(查询DB)并且聚合，然后写入B表。抽象的逻辑图示如下:aA - binlog - b - Bc利用strace利器解决性能问题分析过程 a,b,c 的dao层耗时增加，部分简单查询达到了100多ms 实际测试SQL执行非常

21、快，1ms 都不需要 将Proxy慢查日志记录设置到100ms，未发现慢查 逻辑死锁出现利用strace利器解决性能问题DBA也要去看代码分析业务操作DB的逻辑，如下func ado_somethingt1=now()call get_xxx_from_db()do_somethingrt=now()-t1 # 计算耗时利用strace利器解决性能问题又陷入了僵局get_xxx_from_db() 存在部分业务逻辑业务上线一个月，稳定运行，无慢查利用strace利器解决性能问题linux性能问题诊断神器- strace 分析工具write写log动作调用耗时0.1s(rsyslog)证据在此p

22、oll/recvfrom(处理db请求)则小于1ms如何去定位MySQL性能问题的根源 当出现大量慢查的时候，有些是慢查的根源，有些是是被拖累的，当然也有可能是系统等外部因素导致的。 如果是某个慢查导致的话，无非是这个SQL扫描了大量了数据，并且又有并发。通过扫描行数可以基本确定到罪魁祸首 通过innodb_trx 表去查询在innodb中的事务(快照)，查看TOP SQL 是谁 当某个表的查询非常慢的时候，确认该表是否存在长时间未提交的事务 如果慢查中所有的SQL都是非常棒的，考虑是否有备份或者DDL，查看秒级网络监控是否有丢包，系统IO是否正常(磁盘损坏，BBU策略等)。 如果大量实例出现慢查，考虑是否有网络的原因，不过也要分析机器自身的原因如何去定位MySQL性能问题的根源这里，我们可以发现慢查出现的时间一致，除了第一条SQL，其他SQL扫描行数都很少，基本可以断定为第一条SQL导致的，慢查中该SQL平均扫描了26537行如何去定位MySQL性能问题的根源优化手段1. Kill 空闲事务2. 修改隔离级别为 READ COMMITTED3. 优化count，可以用1秒的缓存4. 尽可能的优化慢查5. 在从库或者专门的备份实例上进行备份6. 提升网络专线质量7. 用SSD/PCIE等 提升数据库的性能8. 用利器找到证据