资讯专栏INFORMATION COLUMN

基于行事件导致从库运行缓慢及解决方法

IT那活儿 / 2507人阅读
基于行事件导致从库运行缓慢及解决方法

点击上方“IT那活儿”公众号,关注后了解更多内容,不管IT什么活儿,干就完了!!!




问题现象
最近,在处理了一个从库延迟问题,在分析期间,发现从库 SQL 线程在处理来自主库二进制日志的基于行的事件时无法跟上。
例如:
1)从库复制状态
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
...
Master_Log_File: binlog.0000185
Read_Master_Log_Pos: 86698585
...
Relay_Master_Log_File: binlog.0000185
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Exec_Master_Log_Pos: 380
Relay_Log_Space: 85699128
...
Master_UUID: 98974e7f-2fbc-18e9-72cd-07003817585c
...
Retrieved_Gtid_Set: 98974e7f-2fbc-18e9-72cd-07003817585c:1055-1057
Executed_Gtid_Set: 7f42e2c5-3fbc-16e7-7fb8-05003715789a:1-2,
98974e7f-2fbc-18e9-72cd-07003817585c:1-1056
...
SQL 线程的 processlist 状态可以是以下其中之一: 
  • 从中继日志中读取事件;

  • System lock 或是其他的一些状态。

2)目前

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+----------------------------------+------------------+
...
|
  4 | system user |                 | NULL | Connect | 268 | Reading event from the relay log | NULL |
...
+----+-----------------+-----------------+------+---------+------+----------------------------------+------------------+



导致此类行为的原因以及注意事项
当 SQL 线程应用基于行的事件更改时,它必须找到被更新的确切行。使用主键,因为只有一行才可能具有相同的主键。
但是,如果从库表上没有主键,SQL 线程必须搜索整个表才能找到要更新或删除的行。它重复搜索每个更新的行。这种搜索会占用大量资源(CPU 占用率最高可达 100%),又会导致从库延迟。
对于 InnoDB 表,不能使用没有主键的表的聚集索引来避免在整个表中搜索更新或删除的行。
“隐藏”主键仅对每个 MySQL 实例是唯一的,因此主库和从库通常不会对同一行的“隐藏”主键具有相同的值。



如何解决这个问题
最好的解决方案是确保所有表都有一个主键这不仅确保 SQL 线程可以轻松找到需要更新或删除的行,因为它确保所有行都是唯一的。
如果无法在逻辑上为表添加自然主键,一个潜在的解决方案是添加一个自增无符号整数列作为主键。
通过下面查询找到没有主键的表:
SELECT tables.table_schema, tables.table_name, tables.table_rows
FROM information_schema.tables
LEFT JOIN (
SELECT table_schema, table_name
FROM information_schema.statistics
GROUP BY table_schema, table_name, index_name
HAVING
SUM(
CASE WHEN non_unique = 0 AND nullable != YES THEN 1 ELSE 0 END
) = COUNT(*)
) puks
ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name
WHERE puks.table_name IS NULL
AND tables.table_schema NOT IN (mysql, information_schema, performance_schema, sys)
AND tables.table_type = BASE TABLE AND engine=InnoDB;
如果应用程序端或者业务系统上有很多关联,更改后需要测试的未知应用程序行为等,并不可以立即将主键添加到表中。
在这种情况下,一个短期的解决方案是更改从库使用的搜索算法,以定位由基于行的事件更改的行。
搜索算法是使用 MySQL 5.6 及更高版本中可用的 slave_rows_search_algorithms 选项设置的。默认值是尽可能使用索引扫描,否则使用表扫描。
但是,对于没有主键的表使用散列扫描,这会导致 SQL 线程临时缓存散列以减少搜索整个表的开销。slave_rows_search_algorithms的值可以使用以下方法动态更改:
mysql> SET GLOBAL slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN;
使用哈希扫描时要注意的一件事是,哈希仅在一个基于行的事件中重复使用。(每个基于行的事件可能对主库同一SQL 语句的同一表中的几行进行更改)。
复制主服务器上的 binlog_row_event_max_size 选项控制基于行的事件的最大大小。默认的最大事件大小为 8kB。

意味着切换到哈希扫描只会在以下情况下提高 SQL 线程的性能:

  • 如果对大行(例如,使用 blob 或文本数据)执行更新或删除,增加主库上binlog_row_event_max_size的值可能会有所帮助 。

    只能在 MySQL 配置文件中设置 binlog_row_event_max_size  ,重置该值需要重启数据库。

总结:即使启用哈希扫描可以提高从库的性能,但永久的解决方案是为每个表添加显式主键,这种模式可以避免许许多多的问题。


END




本文作者:高智飞(上海新炬王翦团队)

本文来源:“IT那活儿”公众号

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/129433.html

相关文章

  • 跨云迁移过程中的数据同步一致性校验实践(一)

    摘要:通过对一些客户的跨云迁移过程进行总结,发现普遍存在的挑战有三点数据完整性和一致性挑战。简而言之,跨云迁移过程中的数据一致性主要就集中在存量数据的迁移如何保证一致。前言随着互联网业务发展对容灾以及对访问加速、多供应商成本控制等需求的产生,互联网公司的多云部署和跨云迁移逐渐成为刚需,而在此过程中,最困扰运维和研发人员的就是数据的迁移和同步。俗语说 上屋搬下屋,搬洒一箩谷 ,在业务的迁移过程中一旦...

    Tecode 评论0 收藏0

发表评论

0条评论

IT那活儿

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<