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

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

解决问题

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

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

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

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

方案2是在slave上执行，具体流程如下：

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逻辑是什么？

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，其逻辑如下：

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));
    /*
...
}

如果没有开启MTS，则exec_relay_log_event执行获取到event
如果开启了MTS，则exec_relay_log_event只是将event分配到worker中

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

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

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

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

请参考MySQL复制原理