Replication was single threaded in MariaDB 5.5 and MySQL 5.5. The previous transaction must commit on the slave before the next transaction can start.
A single I/O thread works to replicate events from the Master’s binlog to the relay log in the Slave.
On the Slave, the SQL Thread will apply those events, one after the other.
With that said, as you can imagine, replication can sometimes lag. Being single threaded, it is challenging to keep the Master and Slave in sync.
Enable Parallel Slave
To enable, indicate slave-parallel-threads=# in your my.cnf.
Configure the number (#) of worker threads to apply events in parallel for all your slaves.
If you indicate a value of zero, then that means no worker threads are created.
The value should be at least twice the number of multi-source master connections utilized.
You do not have to restart mysqld when you configure slave-parallel-threads=#, because it is a dynamic variable. However, all slaves connections must be stopped when modifying the value.
Here is a link that shows the performance improvement when using parallel replication.
https://kristiannielsen.livejournal.com/18435.html
SHOW PROCESSLIST to Check Worker Thread Status
| State | Meaning |
| Waiting for work from main SQL threads | Worker thread is idle |
| Waiting for prior transaction to start commit before starting next transaction | The previous batch of transactions that committed together on the master has to complete first |
| Waiting for prior transaction to commit | Transaction has been executed by the worker thread |
Parallel Slave Queue Size
SQL thread will read ahead in the relay logs when parallel replication is used. This will queue events in memory while looking for opportunities for executing events in parallel. The system variable that sets a limit for how much memory it will use for this is slave_parallel_max_queued
In order for the SQL thread to read far enough ahead in the binary log to exploit all possible paralellism, the slave_parallel_threads system variable should be set large enough.
To prevent limit throughput, the slave_parallel_max_queued system variable could be set relatively high. It should just be set low enough that total allocation of the parallel slave queue will not cause the server to run out of memory.
Slave Parallel Mode
There are 2 options for Slave Parallel Mode: In-Order and Out-of-Order
In-order runs transactions in parallel, but instructs the commit steps of the transactions in the precise same sequence as on the master.
Out-of-order will have the ability to execute and commit transactions in a different order on the slave than primarily on the master. The application must be tolerant to viewing updates occur in random order.
Cheers!
Leave a Reply