Troubleshooting Replication Lag on a Percona XtraDB Cluster (PXC) Async Replica

Nana Zedginidze

— 3 min read
Share

Replication lag is one of the most common problems MySQL DBAs encounter.The replica was almost nine hours behind the source. Both replication threads were running, there were no replication errors, and from a quick glance everything appeared healthy. Yet the replica was barely making any progress.

Environment

The affected server was running in the following environment:

  • MySQL 8.0
  • Percona XtraDB Cluster (PXC)
  • Configured as an asynchronous MySQL replica
  • GTID-based replication with auto-position enabled
  • Parallel replication enabled
  • replica_parallel_workers = 16
  • replica_parallel_type = LOGICAL_CLOCK
  • replica_preserve_commit_order = ON
  • binlog_transaction_dependency_tracking = WRITESET
  • transaction_write_set_extraction = XXHASH64

One important detail is that this wasn't a traditional standalone MySQL replica.

The affected server was a Percona XtraDB Cluster (PXC) node that was also configured as an asynchronous replica. That was making the replication path more complex than on a standard standalone replica.

The Symptoms

The initial symptom was simple.

Replication lag continued increasing even though replication itself appeared healthy. The first thing I checked was the replication status.

SHOW REPLICA STATUS\G

The important fields looked perfectly normal.

Replica_IO_Running  : Yes
Replica_SQL_Running : Yes
Last_IO_Error:
Last_SQL_Error:
Seconds_Behind_Source: ~32000

SHOW REPLICA STATUS is almost always the first command I run when investigating replication lag. It's almost never the last. The status output explains what is happening. It rarely explains why. Since the replication threads were running normally, the next logical step was understanding what the SQL applier was actually doing.

SHOW PROCESSLIST;

One worker immediately stood out.

ALTER TABLE ...EXCHANGE PARTITION ...

What was much more interesting was the state of almost every other replication worker.

Waiting for preceding transaction to commit

The coordinator thread was showing:

Waiting for replica workers to process their queues

Because the active statement was a partition exchange operation, metadata locking became the next obvious suspect. If another session was holding an exclusive metadata lock, it could easily explain why the workers weren't moving.

I checked the metadata locks.

SELECT *FROM performance_schema.metadata_locks;

There were active metadata locks, but nothing unusual. The important locks had already been granted.

The next step was checking InnoDB.

SHOW ENGINE INNODB STATUS\G

This was the first point where the investigation actually started making sense.

Inside the transaction section was a transaction that had been sitting in a PREPARED state for several hours. Immediately behind it was another transaction waiting for the previous one to commit. Suddenly, everything I had seen in the processlist made sense. Those replication workers weren't stuck because replication had failed. They weren't blocked by metadata locks. At this stage, the exact root cause still wasn't completely clear. What was clear was that:

  • replication itself wasn't broken;
  • the replica remained connected to the source;
  • replication workers were alive;
  • the SQL apply path was no longer making meaningful progress.

Decided to try recovery approach based on previous experience with similar behaviour on Percona XtraDB Cluster (PXC) configured as asynchronous replica. The first step was stopping parallel replication.

The configuration was changed from:

replica_parallel_workers = 16

to:

replica_parallel_workers = 0
skip-replica-start

MySQL service was then restarted. Once the server had fully recovered, replication was started manually.

START REPLICA;

Immediately both replication threads came online.

Replica_IO_Running  : Yes
Replica_SQL_Running : Yes

The interesting part was that the replica immediately began behaving differently.

Instead of repeatedly sitting in:

Waiting for preceding transaction to commit

the SQL applier was actively processing relay log events again.

The lag was still significant, but for the first time since the incident started, it was consistently decreasing. Since replication was making slow progress, decided to restore parallel replication without another MySQL restart so in runtime.

STOP REPLICA;

SET GLOBAL replica_parallel_workers = 16;

START REPLICA;

This change only affected the running instance. The replica continued reducing lag, but significantly faster than before. Eventually, it completely caught up with the source.

Follow-up

After the replica had fully caught up, we reviewed the role of this server.

At the time, it was operating as a single-node PXC component (wsrep_cluster_size = 1) and was only being used as an asynchronous replica. Since there were no additional PXC nodes participating in the cluster, the Galera/wsrep layer was no longer required for this server's intended role.

We disabled the wsrep layer by setting:

wsrep_on=OFF

The remaining wsrep-related configuration was disabled, MySQL was restarted, and replication was started manually. Replication started successfully, both replication threads remained healthy, and the replica stayed fully caught up. Most importantly, the DDL operations that had previously caused replication to stall no longer reproduced the same behaviour. While this doesn't prove that the wsrep layer was the root cause, it strongly suggests that keeping wsrep enabled on a server acting solely as an asynchronous replica contributed to the behaviour observed in this environment.

Read more