Description:
MySQL 5.6 introduces slave_parallel_workers which provides some replication parallelisation.
This is good. The configuration variable is also dynamic which is good.
However, its affect is not taken into account until the SQL threads are stopped and restarted.
How to repeat:
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
...
root@myserver [(none)]> show processlist;
+---------+-----------------+-----------------+-------+---------+---------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-----------------+-----------------+-------+---------+---------+-----------------------------------------------------------------------------+------------------+
| 3 | event_scheduler | localhost | NULL | Daemon | 6467264 | Waiting on empty queue | NULL |
| 7583528 | system user | | NULL | Connect | 1443165 | Waiting for master to send event | NULL |
| 7583529 | system user | | NULL | Connect | 3 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
... other threads ...
| 9757200 | root | localhost | NULL | Query | 0 | init | show processlist |
+---------+-----------------+-----------------+-------+---------+---------+-----------------------------------------------------------------------------+------------------+
7 rows in set (0.00 sec)
root@myserver [(none)]> show global variables like '%parallel%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 10 |
+------------------------+-------+
1 row in set (0.00 sec)
root@myserver [(none)]> stop slave;
Query OK, 0 rows affected (0.02 sec)
root@myserver [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
root@myserver [(none)]> show processlist;
+---------+-----------------+-----------------+-------+---------+---------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-----------------+-----------------+-------+---------+---------+-----------------------------------------------------------------------------+------------------+
| 3 | event_scheduler | localhost | NULL | Daemon | 6467305 | Waiting on empty queue | NULL |
... other threads ...
| 9757200 | root | localhost | NULL | Query | 0 | init | show processlist |
| 9757247 | system user | | NULL | Connect | 6 | Waiting for master to send event | NULL |
| 9757248 | system user | | NULL | Connect | 2 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 9757249 | system user | | NULL | Connect | 2 | Waiting for an event from Coordinator | NULL |
| 9757250 | system user | | NULL | Connect | 6 | Waiting for an event from Coordinator | NULL |
| 9757251 | system user | | NULL | Connect | 6 | Waiting for an event from Coordinator | NULL |
| 9757252 | system user | | NULL | Connect | 6 | Waiting for an event from Coordinator | NULL |
| 9757253 | system user | | NULL | Connect | 6 | Waiting for an event from Coordinator | NULL |
| 9757254 | system user | | NULL | Connect | 6 | Waiting for an event from Coordinator | NULL |
| 9757255 | system user | | NULL | Connect | 6 | Waiting for an event from Coordinator | NULL |
| 9757256 | system user | | NULL | Connect | 6 | Waiting for an event from Coordinator | NULL |
| 9757257 | system user | | NULL | Connect | 9 | Waiting for an event from Coordinator | NULL |
| 9757258 | system user | | NULL | Connect | 2 | Waiting for an event from Coordinator | NULL |
+---------+-----------------+-----------------+-------+---------+---------+-----------------------------------------------------------------------------+------------------+
17 rows in set (0.00 sec)
root@mc101myserver [(none)]>
Suggested fix:
1. Improvement: Documentation in http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html states that the current setting is dynamic. It does not state that it will not be acted upon until the SQL threads are restarted. Please correct the documentation.
2. FR: Please consider making this setting dynamic, in that the "SQL threads" will periodically (every few seconds?) check the setting and if it changes adjust the number of parallel slave threads as indicated. This avoids the overhead of having to manually stop the SQL threads, change the setting and then start the SQL threads again.
If you do step 2, please ensure the change is LOGGED so that it is obvious that a change has taken place.
Remark to paragraph 2 of this option on the url above: "When parallel execution is enabled, ..." This seems to imply that you need to "enable" parallel execution (with some other setting) whereas setting a value > 0 seems to be the way to "enable" this. Please consider adjusting the wording to avoid that confusion.
