My Oracle Support Banner

Fix Replication Lag with Parallel Replica Threads (Doc ID 2491599.1)

Last updated on MAY 12, 2022

Applies to:

MySQL Server - Version 5.7 and later
MySQL Database Service - Version N/A to N/A
Information in this document applies to any platform.
Disclaimer: Starting from v8.0, some terms have been deprecated. Older releases will only use the deprecated terminology, and new releases will only use new terminology. Please see https://dev.mysql.com/doc/mysqld-version-reference/en/ for a complete list of those changes, and in which minor versions it happened.




Symptoms

When having a replication topology, replication lag could be present depending on many factors.

 

Changes

MySQL 5.5 has single threaded replica binlog applier and this often resulted in a bottleneck and caused replication lag to increase.
MySQL 5.6 introduced the DATABASE parallel replication scheme, which considers that any transactions that work on different databases can be executed in parallel on the replica applier.
MySQL 5.7 introduced the LOGICAL_CLOCK parallel replication scheme, extending parallelism to transactions within the same database. Initially it considered that only transactions that committed together on the same binary log group commit could be executed together on the replica, but then that was improved upon to work on intervals of dependencies, reducing the impact of serialization points that were seen at group commit boundaries.
MySQL 5.7.14 introduces transaction_write_set_extraction with XXHASH64 along with;
MySQL 5.7.22 introduced binlog_transaction_dependency_tracking=WRITESET dependency tracking for further parallelization.
MySQL 8.0 (and MySQL Database Service) defaults to transaction_write_set_extraction=XXHASH64 and binlog_transaction_dependency_tracking=WRITESET

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
 Check Source and Relay Configuration
 Adjusting configuration at runtime
 For MySQL 5.7 or 8.0 source servers:
 For MySQL 5.7 replica or relay servers:
 For MySQL 8.0 replica or relay servers:
 Check Resource Consumption of the replica
 CPU
 DiskIO
 Check replica hardware shape/size
 Check replication worker distribution history
 Check for primary keys
 Verification
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.