MySQL 5.7从库报错exceeds of slave_pending_jobs_size_max. Error_code: 1864

偏执的太偏执、 2024-04-18 00:43 8阅读 0赞

生产环境MySQL 5.7从库复制报错  
查看错误日志

1.  2018-04-02T00:16:27.388790Z 22595518 \[Note\] Slave SQL thread for channel 'master\_182' initialized, starting replication in log 'mysql-bin.000183' at position 200105916, relay log '/opt/mysql01/db\_log/mysqld-relay-bin-master\_182.000480' position: 200106105  
    
2.  2018-04-02T00:16:27.794506Z 22595518 \[ERROR\] Slave SQL for channel 'master\_182': Cannot schedule event Update\_rows\_v1, relay-log name /opt/mysql01/db\_log/mysqld-relay-bin\-master\_182.000480, position 200106867 to Worker thread because its size 28375738 exceeds 28375040 of slave\_pending\_jobs\_size\_max. Error\_code: 1864  
    
3.  2018-04-02T00:16:27.794526Z 22595518 \[Warning\] Slave: Cannot schedule event Update\_rows\_v1, relay-log name /opt/mysql01/db\_log/mysqld-relay-bin\-master\_182.000480, position 200106867 to Worker thread because its size 28375738 exceeds 28375040 of slave\_pending\_jobs\_size\_max. Error\_code: 1864  
    
4.  2018-04-02T00:16:27.794532Z 22595518 \[ERROR\] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000183' position 200105916

报错原因：  
MySQL 5.7开启了多线程复制，与多线程复制相关的有一个参数slave\_pending\_jobs\_size\_max，错误日志中提示这个参数设置的小。这个参数在MySQL 5.6以后引入。  
对于多线程复制从库，这个参数设置从库工作队列中没有应用的事件缓存。默认单位是字节。如果没有开启多线程复制，则这个参数没有作用。  
这个参数的最小值是1024字节，默认值是16MB。最大值是18446744073709551615。  
注意，这个参数的值不能超过主库的max\_allowed\_packet参数的值，否则从库的工作队列可能会变满。  
  
解决方法：  
将这个参数设置的大一些，并重启复制；也可以关闭多线程复制。

1.  mysql> set global slave\_pending\_jobs\_size\_max=29999104;  
    
2.    
    
3.  mysql> show global variables like 'slave\_pending\_jobs\_size\_max';  
    
4.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\+  
    
5.  | Variable\_name | Value |  
    
6.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\+  
    
7.  | slave\_pending\_jobs\_size\_max | 29999104 |  
    
8.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\+  
    
9.  1 row in set (0.00 sec)  
    
10.   
    
11. mysql> show global variables like 'slave\_parallel\_workers';  
    
12. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
13. | Variable\_name | Value |  
    
14. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
15. | slave\_parallel\_workers | 4 |  
    
16. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
17. 1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2152483/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2152483/