MySQL主从复制性能异常缓慢问题排查求助

先给你理清楚整个场景和问题点，再一步步分析根源，最后给你可落地的解决方案：

环境与问题概述 #

你有一台MariaDB 10.1.27主库，承载1900个数据库、100GB数据，采用InnoDB引擎且开启了innodb_file_per_table。主库午夜停机后用tar备份/var/lib/mysql，恢复到两台同硬件配置的从库。结果一台从库1小时就追上了1天的延迟（CPU跑满、磁盘wa约20%），另一台却卡了2天只完成20%同步——binlog已经全部传输到从库，但SQL线程应用日志的速度极慢，CPU和磁盘负载都处于低位。

下面先把你提供的关键状态信息整理清楚：

异常从库的核心状态 #

Slave Status详情 #

show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXXXXXXXXX
Master_User: XXXXXXXX
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000572
Read_Master_Log_Pos: 524619108
Relay_Log_File: relay-bin.000004
Relay_Log_Pos: 916671538
Relay_Master_Log_File: mysqld-bin.000571
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: apsc.%,billing.%,information_schema.%,performance_schema.%,horde.%,mysql.%,phpmyadmin_gmRJC4rn_tom.%,psa.%,roundcubemail.%,sitebuilder5.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 916671245
Relay_Log_Space: 1598365917
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: 24730
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: 111
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)

系统负载（top） #

====== top - 03:36:36 up 114 days, 12:26, 9 users, load average: 1.82, 1.86, 1.89
Tasks: 364 total, 1 running, 362 sleeping, 0 stopped, 1 zombie
Cpu(s): 1.5%us, 0.7%sy, 0.0%ni, 87.7%id, 4.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32993560k total, 32271192k used, 722368k free, 625464k buffers
Swap: 4194296k total, 229512k used, 3964784k free, 23100280k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13809 mysql 1 -19 6819m 5.8g 7332 S 8.9 18.5 132:12.33 mysqld
======

问题根源拆解 #

1. 复制过滤规则的巨大开销 #

你设置了11个Replicate_Wild_Ignore_Table通配符规则，每个事务在应用前都要逐一匹配这些规则——MariaDB 10.1的复制过滤是单线程处理的，哪怕开了并行复制，这一步也无法并行。如果主库有大量小事务或者跨库操作，规则匹配的累加耗时会直接拖慢SQL线程的速度。

2. 并行复制模式选错了 #

你用的是conservative并行模式，这个模式要求事务之间完全无冲突才能并行执行，在你这种有1900个数据库的场景下，能满足并行条件的事务极少，反而会因为并行调度的额外开销导致比单线程更慢，这也是你开多线程反而延迟增加的原因。

3. InnoDB缓冲池冷启动的影响 #

两台从库虽然硬件相同，但恢复后的缓冲池状态不一样。正常追上的从库因为CPU跑满，InnoDB一直在高效预热缓冲池；而异常从库CPU使用率低，说明很多请求在等待，大概率是缓冲池没缓存到需要的数据，导致频繁的随机IO（不过top里wa只有4%，说明IO本身不忙，更多是过滤规则或锁等待卡住了）。

4. 潜在的锁等待问题 #

虽然慢查询日志没记录，但复制线程应用事务时可能被从库上的用户查询阻塞。比如有人在从库上跑长时间查询，占用了行锁，导致SQL线程一直等锁，无法推进。

可落地的解决方案 #

1. 优化复制过滤规则（最关键） #

• 把过滤移到主库：尽量在主库用binlog_ignore_db或binlog_do_db过滤不需要的库，这样从库就不用做规则匹配了——主库直接不生成这些库的binlog，从库只需要应用所有收到的日志，能节省大量匹配开销。

  注意：主库改binlog过滤要谨慎，先在测试环境验证，确保不会遗漏需要复制的数据库。

• 合并过滤规则：如果必须在从库过滤，把类似的规则合并，比如多个前缀相同的库可以合并成一个通配符规则，减少匹配次数。

2. 调整并行复制配置 #

• 把并行模式改成optimistic：这个模式允许事务先并行应用，冲突时再回滚重试，在多库场景下并行度高很多，适合你的环境。
• 设置合理的并行线程数：根据CPU核心数来，比如4核的话设slave_parallel_threads=8。

  配置方法：在my.cnf里添加：

  slave_parallel_threads = 8
  slave_parallel_mode = optimistic
  

  然后重启从库，或者执行动态设置（10.1版本支持的话）：

  SET GLOBAL slave_parallel_threads=8;
  SET GLOBAL slave_parallel_mode='optimistic';
  

3. 预热InnoDB缓冲池 #

从正常的从库导出缓冲池状态，导入到异常从库，减少冷启动的影响：

1. 在正常从库执行：

   SET GLOBAL innodb_buffer_pool_dump_now=1;
   

2. 停止异常从库的MySQL，把正常从库的ib_buffer_pool文件复制到异常从库的/var/lib/mysql目录。
3. 启动异常从库，执行：

   SET GLOBAL innodb_buffer_pool_load_now=1;
   

4. 排查并消除锁等待 #

• 执行show processlist，看看有没有用户查询在从库上运行，尤其是长时间的查询。如果有，建议把从库设为read_only=1（确保复制用户有超级权限，不影响复制），禁止用户在从库上跑查询。
• 执行show engine innodb status，查看TRANSACTIONS部分，有没有等待锁的事务，找到阻塞线程并杀掉。

5. 其他辅助优化 #

• 调整innodb_log_file_size：建议设为1G-2G（32G内存可以设2G），减少日志切换频率，提升InnoDB写入效率。
• 关闭查询缓存：MariaDB 10.1的查询缓存会给复制带来额外开销，直接关闭：

  query_cache_type=0
  query_cache_size=0
  

内容的提问来源于stack exchange，提问作者Mike Patutin