MySQL 案例解析:无主键表导致的主从延迟

2024-02-2009:14:03数据库教程Comments454 views字数 2337阅读模式

MySQL 案例解析:无主键表导致的主从延迟文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html

记一次MySQL 主从延迟分析处理过程,聊聊我的思路。
以一个例子为切入点

一、问题背景
某业务数据库每次跑批都会出现从库延迟告警的情况,本文总结了这次从库延迟排查的全过程,并分析了导致从库延迟的原因及解决方案。
希望给大家提供一个从库延迟的排查及解决思路。
基础环境:
  • 操作系统:DB:RetHet 7.8
  • 存储:SSD
  • 内存:32 G
  • CPU核数:16CORE
  • 数据库环境:5.7
  • 事务隔离级别:RR
问题现象:
MySQL 从库延迟告警
二、分析说明
MySQL 的主从架构在很多场景下都在使用,同时 MySQL 的同步延迟也是很多 DBA、运维、开发的同学经常面对的问题之一。本文围绕同步延迟的场景之一:无主键表,来看看延迟产生的原因,以及应对的策略。
当然,最简单的办法是给表建个主键,不过在关于这个问题,其实还有一些其他的方式可以尝试。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html

MySQL 的主从同步原理图:

MySQL 案例解析:无主键表导致的主从延迟
简而言之,在主库上的数据变化记录在 binlog 中之后通过网络传到从库并记录在 relaylog 中,之后再由 sql 线程在从库上“再执行一遍”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html
当数据库使用 row 模式的时候,binlog 会记录所有的数据变更,这也意味着一个 update 或者 delete 语句如果修改了非常多的数据,那么每一行数据的变化都会记录到 binlog 中,最终会产生非常多的 binlog 日志。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html

从库在处理这些日志时,每一行数据的变化都会去尝试定位具体的数据,然后再判断是不是需要执行操作来完成数据变更。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html

那么可以想象得到,如果在某张大表上 update 或者 delete 一些数据,而这张表没有索引,那么定位数据的时候就会变成全表扫描,且 update 或者 delete 的每一行数据都会触发一次全表扫描,从库会产生非常大的延迟。
显然,在从库上临时先加点索引是一个很好的办法,那么除了索引以外,还有什么其他的办法吗?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html
MySQL 在这类场景下,有一个专门的参数来调整从库定位数据的方法:
slave_rows_search_algorithms
参考官方文档的参数设置表:
MySQL 案例解析:无主键表导致的主从延迟
可以看到设置了 HASH SCAN 之后,非主键和唯一索引的情况下,会使用 Hash scan 算法来定位数据,而且这个参数变更之后是即时生效的,那么看起来遇到类似问题的时候,可以通过调整这个参数来尝试解决延迟的问题?
实践是检验真理的唯一标准!
本次测试环境使用云数据库 MySQL,配置为 4 核 8GB 内存。测试数据使用 sysbench 生成,单表 2000 万行数据,且没有主键和唯一索引。
测试时修改了 binlog_row_image 的值为 FULL。这个参数是可以动态修改的,所以调整起来没有什么额外的成本。
测试项目为 delete 语句,影响的行数为约 300 万行,随机删除数据。
模拟的场景和结果如下:
  • where 条件无索引
    • 场景1:表没有其他索引。
    • 场景2:表有其他优质索引(数据区分度高)。
  • where 条件有索引
    • 场景3:表没有其他的索引。
    • 场景4:表有其他的优质索引(数据区分度高)。
图例中 1-Table 代表场景1下,Table_Scan,Index_Scan 设置下的延迟时间,1-Hash 代表场景 1 下,Hash_Scan,Index_Scan 设置下的延迟时间。
MySQL 案例解析:无主键表导致的主从延迟
从对比数据上可以知道:
  • 场景 1 下,完全没有索引的时候延迟时间超过 24 小时,完全没有对比意义,因此取值为 -1。
  • 在模拟的随机 delete 大量数据的场景下,Hash Scan 并没有能减少延迟时间,反而增加了 50%~800%
  • 当从库可以利用索引时,如果索引的区分度较高,那么延迟的时间会明显减少,减少幅度为 75%~95%
  • 对比场景 2 和场景 3 的情况当从库有多个索引,且 where 条件本身有索引的时候,会直接使用 where 条件的索引,如果 where 条件没有索引则会用到其他的索引。
看起来 Hash Scan 似乎是成了负优化的样子,随机修改数据的场景使用 Table Scan 会稍微好一些。
那么 Hash Scan 在哪些场景能发挥作用呢?考虑到 Hash 算法的特点,调整一下测试项目,以较低区分度的列为准,挑选出重复值比较多的 top10 的值进行删除,总共约 1400 行,例如:
delete from test where a in (9951634,10010874,10031037,10041605,9999038,10045918,10047948,10000971,10045729,9956622)
那么再来测试一下场景1,这次也加上 update 的测试项目,where 条件与 delete 保持一致。
MySQL 案例解析:无主键表导致的主从延迟
可以很明显的看出来,当完全无索引,且修改重复行数较多的数据时,Hash Scan 的效果要好很多,且随着受影响行数变多,两种参数设置的情况下,延迟时间都会增长,Hash Scan 的效果会相对越明显。

特殊情况

关于 binlog_row_image 这个参数,FULL 和 MINIMAL 的差别在于 MINIMAL 记录的是主键信息和 where 条件列的内容,但是 FULL 会记录表中所有列的内容。
而 slave_rows_search_algorithms 会按照主键->唯一索引->辅助索引的顺序来依次尝试,因此在场景2(where 条件无索引,表有其他优质索引)的时候,FULL 的情况下会自动利用其他的索引,而 MINIMAL 没有记录其他列的信息,只能用全表扫描。

五、总结

确保每个表都有主键是最好的解决办法,如果确实有客观原因,那至少保证 where 条件全部能利用到索引。
当问题已经发生了,可以根据实际出问题的 SQL 语句,把参数改为 INDEX_SCAN,HASH_SCAN 来减少延迟时间。可以在information_schema.innodb_trx中看到同步的速度,确认修改参数之后是否有提升。
如果使用了 FULL 的话,用 Table Scan 在大多数时候没什么问题。
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html
  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/60394.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定