MySQL主从延迟分析

中国网络水泥

　　Step1 : iostat 查看IO情况
　　iostat -x 1 查看IO情况,哪个磁盘的IO负载较高，接下来我们就来定位具体的负载来源
　　Step2： iotop定位负载来源进程
　　iotop的本质是一个python脚本，从proc中获取thread的IO信息，进行汇总。
　　从下图可以看出大部分的IO来源都来自于mysqld进程。因此可以确定dfa的负载来源是数据库
　　Step3 pt-ioprofile定位负载来源文件
　　pt-ioprofile的原理是对某个pid附加一个strace进程进行IO分析。
　　以下是摘自官网的一段警示：
　　However, it works by attaching strace to the process using ptrace(), which will make it run very slowly until strace detaches. In addition to freezing the server, there is also some risk of the process crashing or performing badly after strace detaches from it, or indeed of strace not detaching cleanly and leaving the process in a sleeping state. As a result, this should be considered an intrusive tool, and should not be used on production servers unless you are comfortable with that.
　　通过ps aux|grep mysqld 找到 mysqld进程对应的进程号，通过pt-ioprofile查看哪个文件的IO占用时间最多。
　　默认参数下该工具展示的是IO占用的时间。
　　pt-ioprofile --profile-pid 3082
　　对于定位问题更有用的是通过IO的吞吐量来进行定位。使用参数 --cell=sizes，该参数将结果已 B/s 的方式展示出来
　　pt-ioprofile --profile-pid 3082 --cell
　　第一种：表未建主键和二级索引（排查最容易忽略的情况）
　　如下图，当sql_thread在重放relay log时会根据表是否有主键（注：这就是为什么建表必须要有主键原因之一）和二级索引来判断是否全表扫描

　　在MySQL5.6中提供了一个新的参数：slave_rows_search_algorithms, 可以部分解决无主键表导致的复制延迟问题，其基本思路是对于在一个ROWS EVENT中的所有前镜像收集起来，然后在一次扫描全表时，判断HASH中的每一条记录进行更新；
　　slave_rows_search_algorithms由三个值的组合组成：TABLE_SCAN，INDEX_SCAN， HASH_SCANTABLE_SCAN,INDEX_SCAN  (默认配置，表示如果有索引就用索引，否则使用全表扫描)
　　使用组合包括：
　　INDEX_SCAN,HASH_SCAN
　　TABLE_SCAN,HASH_SCAN
　　TABLE_SCAN,INDEX_SCAN,HASH_SCAN(等价于INDEX_SCAN, HASH_SCAN)
　　未完待续。。。。。