To Nha Notes | April 8, 2024, 9:18 a.m.
| Command-Line Format | --replica-parallel-workers=# |
|---|---|
| Introduced | 8.0.26 |
| System Variable | replica_parallel_workers |
| Scope | Global |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | Integer |
| Default Value (≥ 8.0.27) | 4 |
| Default Value (8.0.26) | 0 |
| Minimum Value | 0 |
| Maximum Value | 1024 |
Beginning with MySQL 8.0.26, slave_parallel_workers is deprecated, and you should use replica_parallel_workers instead. (Prior to MySQL 8.0.26, you must use slave_parallel_workers to set the number of applier threads.)
replica_parallel_workers enables multithreading on the replica and sets the number of applier threads for executing replication transactions in parallel. When the value is greater than or equal to 1, the replica uses the specified number of worker threads to execute transactions, plus a coordinator thread that reads transactions from the relay log and schedules them to workers. When the value is 0, there is only one thread that reads and applies transactions sequentially. If you are using multiple replication channels, the value of this variable applies to the threads used by each channel.
Prior to MySQL 8.0.27, the default value of this system variable is 0, so replicas use a single worker thread by default. Beginning with MySQL 8.0.27, the default value is 4, which means that replicas are multithreaded by default.
As of MySQL 8.0.30, setting this variable to 0 is deprecated, raises a warning, and is subject to removal in a future MySQL release. For a single worker, set replica_parallel_workers to 1 instead.
When replica_preserve_commit_order (or slave_preserve_commit_order) is set to ON (the default in MySQL 8.0.27 and later), transactions on a replica are externalized on the replica in the same order as they appear in the replica's relay log. The way in which transactions are distributed among applier threads is determined by replica_parallel_type (MySQL 8.0.26 and later) or slave_parallel_type (prior to MySQL 8.0.26). Starting with MySQL 8.0.27, these system variables also have appropriate defaults for multithreading.
To disable parallel execution, set replica_parallel_workers to 1, in which case the replica uses one coordinator thread which reads transactions, and one worker thread which applies them, which means that transactions are applied sequentially. When replica_parallel_workers is equal to 1, the replica_parallel_type (slave_parallel_type) and replica_preserve_commit_order (slave_preserve_commit_order) system variables have no effect and are ignored. If replica_parallel_workers is equal to 0 while the CHANGE REPLICATION SOURCE TO option GTID_ONLY is enabled, the replica has one coordinator thread and one worker thread, exactly as if replica_parallel_workers had been set to 1. (GTID_ONLY is available in MySQL 8.0.27 and later.) With one parallel worker, the replica_preserve_commit_order (slave_preserve_commit_order) system variable also has no effect.
Setting replica_parallel_workers has no immediate effect but rather applies to all subsequent START REPLICA statements.
Multithreaded replicas are supported by NDB Cluster beginning with NDB 8.0.33. (Previously, NDB silently ignored any setting for replica_parallel_workers.) See Section 25.7.11, “NDB Cluster Replication Using the Multithreaded Applier”, for more information.
Increasing the number of workers improves the potential for parallelism. Typically, this improves performance up to a certain point, beyond which increasing the number of workers reduces performance due to concurrency effects such as lock contention. The ideal number depends on both hardware and workload; it can be difficult to predict and typically has to be found by testing. Tables without primary keys, which always harm performance, may have even greater negative performance impact on replicas having replica_parallel_workers > 1; so make sure that all tables have primary keys before enabling this option.
This parameter sets the number of worker threads on the replica instance running transactions in parallel. The default value is 0, and that value disables parallel execution of worker threads. Allowed values are 0–1,024.
Using parallel transaction execution on replicas provides better scalability in replication. However, this approach works only if master and replicas both are on at least MySQL 5.6 or higher. When slave_parallel_workers is set to greater than zero, you can’t retry transactions and slave_transaction_retries is treated as equal to 0. Setting this value for this parameter wasn’t always honored correctly in MySQL 5.6.3 (bug 13334470), which has been fixed in 5.6.4. Also, bug 84415 can affect things when this parameter is enabled.
In MySQL 5.6, parallel replication uses only one thread per schema so can be effectively used where there are multiple databases. In 5.7, this approach can also be used for workloads within schemas.
Increasing the slave_parallel_workers value doesn’t improve replication performance linearly. How to estimate the best value of this parameter depends on the workload and needs testing by simulating an actual workload and monitoring replication lag.
You can enable some of the performance schema instrumentation related to transaction to record executed transactions. Then, to know how many transactions are executed by each replication thread, check the performance schema tables performance_schema.events_transactions_summary_by_thread_by_event_name and performance_schema.replication_applier_status_by_worker. Doing so helps you see if all threads are used properly and then adjust the slave_parallel_workers value as needed. For more information, see this useful blog post from Percona.
How to tune this value depends on your workload. If your read replica is not being used for any read operations but only as a standby replica, you can set this value to the number of vCPUs for the instance. If your read replica is being used to serve read workloads and not as a standby, test your workload after you have set the value.
https://dev.mysql.com/doc/refman/8.0/en/replication-threads.html