Shulou(Shulou.com)05/31 Report--

This article mainly introduces the error report of MySQL 5.7from the library exceeds of slave_pending_jobs_size_max. Error_code: 1864 how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

Production environment MySQL 5.7Replying errors from the library

View error log

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

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

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

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

Reason for reporting an error:

MySQL 5.7enables multithreaded replication. There is a parameter slave_pending_jobs_size_max related to multithreaded replication, which is indicated in the error log as small. This parameter was introduced after MySQL 5. 6.

For multithreaded copy slave libraries, this parameter sets the event cache that is not applied from the library work queue. The default unit is bytes. If multithreaded replication is not enabled, this parameter has no effect.

The minimum value for this parameter is 1024 bytes, and the default value is 16MB. The maximum value is 18446744073709551615.

Note that the value of this parameter cannot exceed the value of the max_allowed_packet parameter of the master library, otherwise the work queue of the slave library may become full.

Solution:

Set this parameter larger and restart replication; you can also turn off multithreaded replication.

Mysql > set global slave_pending_jobs_size_max=29999104

Mysql > show global variables like 'slave_pending_jobs_size_max'

+-+ +

| | Variable_name | Value |

+-+ +

| | slave_pending_jobs_size_max | 29999104 | |

+-+ +

1 row in set (0.00 sec)

Mysql > show global variables like 'slave_parallel_workers'

+-+ +

| | Variable_name | Value |

+-+ +

| | slave_parallel_workers | 4 |

+-+ +

1 row in set (0.00 sec)

Thank you for reading this article carefully. I hope the article shared by the editor, "MySQL 5.7 reports mistakes from the library exceeds of slave_pending_jobs_size_max. Error_code: what to do" will be helpful to everyone. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.