MySQL无索引行级复制延迟巨大

现场问题

rowslow

Zabbix监控图可以看出从15:58开始,slave的主从延迟成直线飚升,可见是堵在了这个具体的event上了。

排查问题

rowslow
通过show slave status命令得到slave卡在了nnmhdb02-relay-bin.000078:4596880的位置。

对此relaylog文件进行解析:

1
mysqlbinlog -vv --base64-output=DECODE-ROWS nnmhdb02-relay-bin.000078 > abc

rowslow
rowslow
rowslow

  1. 发现此事务里是对一张表进行了delete操作,涉及的行数大概900多行。正常情况下900个row delete event应该分分钟执行完成,现在却延迟了1个多小时,还没执行完成!!!
  2. 查看表结构,发现表竟然没有任何索引!!!
  3. 没有索引会导致每个row event都要去做全表扫描。这张表有近1000W行数据,故会进行900*10000W行扫描。

解决问题

如何解决这个问题呢?首先肯定是想到对表创建主键,但是客户没有明确主键,如果贸然加一个自增列的话,可能会导致现有应用程序出现问题!

那么只能考虑添加普通索引。那么问题来了,如何添加索引呢?

  1. 在master上执行alter table,slave等待同步过来
  2. 直接在slave上添加索引

因为目前slave已经远远落后master,如果选择1方案,那么需要等到slave完全同步之后alter table才会生效!slave同步的时间估算大概需要24小时!故放弃方案1,选择方案2

方案2是在slave上执行,具体流程如下:

1
2
3
4
mysql> stop slave sql_thread;
mysql> set sql_log_bin=0;
mysql> alter table xxx add index();
mysql> start slave sql_thread;

执行的过程中又遇到了新问题,由于当前的事务还在执行中,stop slave sql_thread hang住了!!

此时没有好的方法,只能等待,等待了大概两个小时,终于执行成功!!

修改成功后,几分钟就把binlog追平了!!

stop slave sql_thread逻辑是什么?

1
2
3
4
5
6
7
8
9
10
if (thread_mask & (SLAVE_SQL|SLAVE_FORCE_ALL))
{
DBUG_PRINT("info",("Terminating SQL thread"));
mi->rli->abort_slave= 1;
if ((error=terminate_slave_thread(mi->rli->info_thd, sql_lock,
&mi->rli->stop_cond,
&mi->rli->slave_running,
&total_stop_wait_timeout,
need_lock_term)) &&
!force_all)
  • stop slave sql_thread设置对应的sql线程abort_slave=1,然后调用terminate_slave_thread函数。

处理event的函数是handle_slave_sql,其逻辑如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
extern "C" void *handle_slave_sql(void *arg)
{
while (!sql_slave_killed(thd,rli))
{
exec_relay_log_event
}
}
bool sql_slave_killed(THD* thd, Relay_log_info* rli)
{
bool is_parallel_warn= FALSE;
DBUG_ENTER("sql_slave_killed");
DBUG_ASSERT(rli->info_thd == thd);
DBUG_ASSERT(rli->slave_running == 1);
if (rli->sql_thread_kill_accepted)
DBUG_RETURN(true);
if (abort_loop || thd->killed || rli->abort_slave)
{
rli->sql_thread_kill_accepted= true;
is_parallel_warn= (rli->is_parallel_exec() &&
(rli->is_mts_in_group() || thd->killed));
/*
...
}
  1. 如果没有开启MTS,则exec_relay_log_event执行获取到event
  2. 如果开启了MTS,则exec_relay_log_event只是将event分配到worker中

故对于stop slave分为两种情况:

  1. 没有开启MTS
    会直接放弃当前事务的执行,回滚事务
  2. 开启MTS
    • 当前事务的所有event已经分配出去,则需要等待当前事务执行完成。
    • 当前事务的event并未完全非给worker,则等待worker把分配的event执行完成后,此事务执行失败。

一般情况下,如果复制延迟出现本次故障的情况,sql_thread肯定是把所有event已经分配出去了,因为分配是很快的动作,复制延迟是因为worker线程执行慢才会堵住的。

所以出现这种情况,执行完stop slave sql_thread之后,等待即可。

请参考MySQL复制原理

本文标题:MySQL无索引行级复制延迟巨大

文章作者:Louis

发布时间:2017年09月26日 - 21:09

最后更新:2017年09月28日 - 22:09

原始链接:/2017/09/26/mysql-replication-row-slow/

许可协议: Louis-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。