Anyone using parallel replication with only one logical db for a large mission critical use case?

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi Folks,

I know of companies using multiple logical dbs in same mysql instance ( like create database db1, create database db2 etc) for parallel replication since 5.6.

In 5.7, new feature was added to be able to parallelize replication within one logical db.

We tried it once more than a year ago with the following settings

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 20

We saw replication broke on slave with this weird error:

 Last_Errno: 1755
 Last_Error: Cannot execute the current event group in the parallel mode. Encountered event Gtid, relay-log name ./db4030a-relay-bin.009066, position 73699 which prevents execution of this event group in parallel mode. Reason: The master event is logically timestamped incorrectly..

Since that was a new use case anyway, at that time we decided to use multiple logical dbs for parallelizing replication rather than debug the issue.

Now for our main legacy use case still on 5.6, as we migrate to 8.0, we are wondering whether to use parallel replication with just one logical db or try to split the tables into logical dbs.

If you have a large mission critical use case with hundreds of diverse tables, I would like to know if parallel replication with one logical db is causing any issues for you.

Thanks!
Vamsi

1 Like
2

Hi Vamsi,

Replication with one logical db and slave_parallel_type = LOGICAL_CLOCK is considered stable and production ready for large mission critical use cases.

Regarding the issue you got, config and relay logs should be checked to better understand the cause of the error. For example it can be related to this bug report MySQL Bugs: #89375: Parallel replication always fails with specific workload from sysbench related to replicate-same-server-id .
I suggest using latest version to benefit from latest improvement and bug fixes, and in case you get the same issue again, then you disable parallel replication for a while with the following steps Last_SQL_Error: Cannot execute the current event group in the parallel mode – Databases and Data Technologies to restore replication.

As a last comment, when using MTS replication with logical_clock , parallel replication might be limited depending on writeload and dependencies on master. You can check parallel replication efficiency and calculate optimal number of worker threads following the steps on this blogpost Estimating potential for MySQL 5.7 parallel replication - Percona Database Performance Blog

1 Like
3

ok, thanks for prompt response!
Vamsi

1 Like