Multi-threaded Replication - Only One Thread Works At A Time? (Doc ID 1580190.1)

Last updated on MARCH 08, 2017

Applies to:

MySQL Server - Version 5.6 to 5.7 [Release 5.6 to 5.7]
Information in this document applies to any platform.

Symptoms

Even though Multi Threaded Slave (MTS) is enabled, all the slave worker threads do not work in parallel : 

 

mysql> select * from mysql.slave_worker_info;
+----+---------------------------------------+---------------+------------------+----------------+-----------------------------------+--------------------------+----------------------------+---------------------------+------------------+
| Id  | Relay_log_name                          | Relay_log_pos | Master_log_name  | Master_log_pos | Checkpoint_relay_log_name     | Checkpoint_relay_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos | Checkpoint_seqno | 
+----+---------------------------------------+---------------+------------------+----------------+-----------------------------------+--------------------------+----------------------------+---------------------------+------------------+
|  1  |                                                       |             0         |                             |                     0 |                                                        |                            0       |                                          |                                 0 |                0 |
|  2  |                                                       |             0         |                             |                     0 |                                                        |                            0       |                                          |                                 0 |                0 |
|  3  |                                                       |             0         |                             |                     0 |                                                        |                            0       |                                          |                                 0 |                0 |
|  4  |                                                       |             0         |                             |                     0 |                                                        |                            0       |                                          |                                 0 |                0 |
|  5  | /data/relay_logs/relay-bin.000791 |     132586803 | mysql-bin.029988 |     132586640 | /data/relay_logs/relay-bin.000791 |                132477675  | mysql-bin.029988           |                     132477512 |               34 |
|  6  | /data/relay_logs/relay-bin.000791 |     346324121 | mysql-bin.029988 |    346323958  | /data/relay_logs/relay-bin.000791 |                 343332091 | mysql-bin.029988           |                     343331928 |               25 |
|  7  | /data/relay_logs/relay-bin.000791 |     343589778 | mysql-bin.029988 |     343589615 | /data/relay_logs/relay-bin.000791 |                343332091  | mysql-bin.029988           |                     343331928 |               18 |
|  8  | /data/relay_logs/relay-bin.000791 |     348763028 | mysql-bin.029988 |     348762865 | /data/relay_logs/relay-bin.000791 |                346341703  | mysql-bin.029988           |                     346341540 |               23 |
+----+-----------------------------------+---------------+------------------+----------------+------------------------------------+--------------------------+----------------------------+---------------------------+------------------+
8 rows in set (0.00 sec)



Changes

Multi Thread Slave has been enabled with below settings in my.cnf file:

 

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms