暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 基于行事件导致从库运行缓慢及解决方法

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

IT那活儿 2022-06-26
411

点击上方“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那活儿”公众号

线程数据库主键
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论