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