MySQL 从库执行show table status导致复制延迟的原因

r囧r小猫 2023-06-01 12:46 41阅读 0赞

一个从库出现延迟，且延迟数值越来越大，查看复制情况：

15:06:15>show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: xxx.xxx.xxx.xxx
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.256926
              Read_Master_Log_Pos: 505156921
                   Relay_Log_File: relay-bin.170011
                    Relay_Log_Pos: 420906823
            Relay_Master_Log_File: mysql-bin.256926
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 420906677
                  Relay_Log_Space: 2693021421
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 997
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 301183306
    1 row in set (0.00 sec)

首先通过`Slave_IO_State: Waiting for master to send event`信息，以及对比主库的binlog写入位点，确认从库IO thread没问题。  
重复执行几次`show slave status`，发现Seconds\_Behind\_Master数值基本上和时间同步递增；同时通过观察Exec\_Master\_Log\_Pos，可以发现该数值保持不变，那么可以确认是SQL thread没有正常运行。  
那么到底是什么问题呢？是什么阻塞了SQL thread执行relay log？  
该从库已经设置了read\_only=on，所以不存在DML冲突，是否MDL争用？show processlist看一下：

可以看到，没有DDL在执行，也没有显示有metadata lock的等待。而从库的复制线程却是在等待global read lock。  
全局读锁，可以确定并不是某一个表的MDL争用，而是全局性的锁等待。全局性的锁一般是flush tables之类的，这里只有`SHOW TABLE STATUS`一个查看表信息的局域，难道也会占有锁？而且看到`SHOW TABLE STATUS`已经执行了1504秒，太不正常了。  
其实，这跟统计信息收集有关，没错，`show table status`触发了更新所有表的统计信息。  
我们通常都不知道`show table status`还会触发统计信息收集，是因为新版本的MySQL在统计信息方面做了一些默认的改动。  
在较低版本，比如5.5，统计信息默认是非持久化，也就是说重启以后需要重新收集；在新版本中，比如5.7，统计信息默认是持久化到系统表，重启以后还可以读取系统表里存储的统计信息，不需要重新收集。  
那么对于非持久化统计信息，还有一个特性，就是在执行show table status、查询information\_schema.tables、information\_schema.staticstic时，会更新非持久化统计信息，效果类似于执行analyze table。  
当然，是否自动更新可以通过`innodb_stats_on_metadata`控制，比如本文的案例中，就是将innodb\_stats\_on\_metadata开启，即表示在执行前文提到的命令时，会自动更新统计信息。

所以，从库延迟的真相就是，`SHOW TABLE STATUS`语句触发了统计信息收集，导致复制线程拿不到全局读锁，所以SQL thread一直停滞不前。  
据观察，等统计信息收集完以后，从库就意料中的追平数据了。  
既然提到统计信息的持久化和非持久化，那么怎么区分哪些版本是默认持久化呢？其实不需要去了解版本的信息，而是应该直接看参数`innodb_stats_persistent`，这个参数是控制统计信息是否持久化。如果没有这个参数，那应该就是版本过低，比如MySQL 5.5，就是默认非持久化。