1.1 SQL 线程异常 -- slave_pending_jobs_size_max 参数调整

1.1.1 异常描述

1、MySQL 版本

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.16, for Linux (x86_64) using  EditLine wrapper

2、MySQL 异常描述

生产环境中 SQL 线程出现中断的异常，报错信息如下。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.10.129.162
                  Master_User: replicator
                  Master_Port: 3129
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002852
          Read_Master_Log_Pos: 18972296
               Relay_Log_File: slave-relay.000265
                Relay_Log_Pos: 6983387
        Relay_Master_Log_File: mysql-bin.002832
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1864
                   Last_Error: Cannot schedule event Write_rows_v1, relay-log name /home/mysql/data3129/mysql/slave-relay.000265, position 6983573 to Worker thread because its size 190156933 exceeds 167772160 of slave_pending_jobs_size_max.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 25087747
              Relay_Log_Space: 1221747673
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1864
               Last_SQL_Error: Cannot schedule event Write_rows_v1, relay-log name /home/mysql/data3129/mysql/slave-relay.000265, position 6983573 to Worker thread because its size 190156933 exceeds 167772160 of slave_pending_jobs_size_max.

• 从报错信息看，是因为slave_pending_jobs_size_max的值小于当前需要执行事件所需的内存大小 190156933 。经查看，slave_pending_jobs_size_max的大小设置的是默认值167772160（即16M），所以 slave_pending_jobs_size_max 的值要大于190156933。

• 在多线程复制时，队列中Pending的事件所占用的最大内存默认为16M，如果内存富余，或者延迟较大时，可以适当调大。

1.1.2 异常处理

1、slave_pending_jobs_size_max 的值调整为 240000000

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slave_pending_jobs_size_max=240000000;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

2、检查主从状态

• 参数调整后数据库主从同步恢复正常

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.10.129.162
                  Master_User: replicator
                  Master_Port: 3129
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002852
          Read_Master_Log_Pos: 18986477
               Relay_Log_File: slave-relay.000265
                Relay_Log_Pos: 198457862
        Relay_Master_Log_File: mysql-bin.002832
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

1.1.3 注

如果数据库主从状态并没有恢复，请按照如下步骤处理。

1、按照 1.1.2 中的步骤多次执行

多等待一些时间，检查主从状态。

2、查看主库 max_allowed_packet 的值再次调整

• 查看主库 max_allowed_packet 的值

mysql> show variables like 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)

• 从库 slave_pending_jobs_size_max 的值大于，上步中获得的主库 max_allowed_packet 的值（1073741824）

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slave_pending_jobs_size_max=1200000000;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

• 再次检查主从状态

mysql> show slave status\G