MySQL 5.7 parallel replication

MySQL & MariaDB Percona Server for MySQL 5.7
,
1

Hello, could you please help with setting up parallel replication?

Currently I tried a lot of things. Here is my master configuratuion on slave:

slave_parallel_workers=8
slave_parallel_type=LOGICAL_CLOCK
slave-preserve-commit-order=ON

Checked status of workers as per Estimating potential for MySQL 5.7 parallel replication - Percona Database Performance Blog and found that 4 of 8 workers really works, but SBM still increasing

Tried to increse binlog-group-commit-sync-delay on master but no luck

What should I try? All other parameters (InnoDB, IO and so on) are tuned well

Got an idea to use the following on slave:

set global transaction_write_set_extraction=XXHASH64;
set global binlog_transaction_dependency_tracking=WRITESET;

Can this help? I think it might broke replica and I cant loose it since it is a PROD

2

Hi caseyrubick,

Does the replica has same hardware as primary ?
Did you monitor system resources to find if the bottleneck is CPU/disk/concurrency? or if the replica is swapping?

If SBM increases it can be either to system resources not being enough for the workload, it can be the workload is not parallelizable (i.e if there is a hot table that gets most of the writes) , or if there are long running/frequent DDLs that can be executing on parallel on the primary, but the replica requires the DDL to finish executing before being able to continue applying relay logs.

Above said, it seems you already have configured parallel replication correctly, but it’s likely either the system resources, other configuration or the workload makes the replica lag behind and requires further analysis.

Regards