Troubleshooting MySQL Replication Lag Using New Replication Metrics

This post is Part 1 of the MySQL 9.7 LTS Technical Series, focusing on observability features, performance enhancements, and administrative strategies in MySQL 9.7 LTS.

In replication environments, database administrators frequently encounter replication lag. For years, primary health checks relied heavily on SHOW REPLICA STATUS and the Seconds_Behind_Source metric.

While Seconds_Behind_Source confirms that a replica is falling behind, it provides minimal context regarding why the delay occurs. Finding the root cause traditionally required querying multiple Performance Schema tables, reading process lists, and examining system-level I/O metrics.

To solve this observability gap, MySQL introduced the Replication Applier Metrics Component in MySQL 9.1.0. This capability is fully supported in MySQL 9.7 LTS to help identify worker saturation, transaction backlogs, scheduling delays, and lock contention faster.

Causes of Replication Lag in Multi-Threaded Replicas (MTR/MTS)

When using Multi-Threaded Replication (MTR/MTS), lag typically stems from one of the following root causes:

• Worker thread saturation
• Unresolved transaction dependencies
• Commit-order wait constraints
• Row-level or metadata lock contention
• Missing primary keys or secondary indexes on the target table
• Monolithic/large transactions blocking parallel progress
• System-level CPU or storage I/O bottlenecks

Installing the Replication Applier Metrics Component

The Replication Applier Metrics Component introduces two dedicated Performance Schema tables:

1. replication_applier_metrics
2. replication_applier_progress_by_worker

You can install this component dynamically at runtime without restarting the MySQL daemon:

mysql> INSTALL COMPONENT 'file://component_replication_applier_metrics';
Query OK, 0 rows affected (0.006 sec)


mysql> SELECT * FROM mysql.component WHERE component_urn LIKE '%replication_applier_metrics%';
+--------------+--------------------+----------------------------------------------+
| component_id | component_group_id | component_urn                                |
+--------------+--------------------+----------------------------------------------+
|            1 |                  1 | file://component_replication_applier_metrics |
+--------------+--------------------+----------------------------------------------+

Important Requirement: The component collects metrics only when parallel execution is enabled. Ensure replica_parallel_workers is configured to a value greater than 0. If set to 0, these tables will yield empty or unavailable datasets.

Once installed, inspect the newly added Performance Schema tables:

SHOW TABLES FROM performance_schema LIKE 'replication_applier_%';
+------------------------------------------------------+
| Tables_in_performance_schema (replication_applier_%) |
+------------------------------------------------------+
| replication_applier_configuration                    |
| replication_applier_filters                          |
| replication_applier_global_filters                   |
| replication_applier_metrics                          |
| replication_applier_progress_by_worker               |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_applier_status_by_worker                 |
+------------------------------------------------------+

New Performance Schema Metrics

1. performance_schema.replication_applier_metrics

The performance_schema.replication_applier_metrics table provides coordinator-level statistics for the replication applier means the co-ordinator is the thread that reads events from the relay log and distributes them to the worker threads for parallel execution 

Key metrics captured include:

• Transaction processing throughput
• Applier thread utilization rates
• Worker scheduling delays
• Commit-order wait times

Sample Query: Coordinator Metrics Overview

mysql> SELECT 
    TRANSACTIONS_ONGOING_COUNT AS ongoing_transaction, 
    TRANSACTIONS_PENDING_COUNT AS waiting_tx, 
    WAITS_FOR_AVAILABLE_WORKER_SUM_TIME AS worker_wait_time, 
    WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME AS dep_wait_time,
    WAITS_DUE_TO_COMMIT_ORDER_SUM_TIME AS commit_order_wait_time 
FROM performance_schema.replication_applier_metrics\G

Sample Output:

*************************** 1. row ***************************
   ongoing_transaction: 1
            waiting_tx: 1
      worker_wait_time: 1531919180500
         dep_wait_time: 684080071783
commit_order_wait_time: 469379232

Metric Breakdown:

• TRANSACTIONS_ONGOING_COUNT: Number of transactions actively executing across worker threads.
• TRANSACTIONS_PENDING_COUNT: Number of transactions present in the relay log that have not finished executing. A steadily increasing value points directly to replication lag[1].
• WAITS_FOR_AVAILABLE_WORKER_SUM_TIME: Total nanoseconds spent by the coordinator waiting for a free worker thread. High values signal worker saturation, insufficient thread counts, or large transactions monopolizing workers.
• WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME: Time lost waiting on transactional dependencies. Dependent transactions must process sequentially even when parallel replication is active.
• WAITS_DUE_TO_COMMIT_ORDER_SUM_TIME: Time spent holding commits to preserve the exact commit sequence executed on the source server.

Building Monitoring Dashboards

These metrics are excellent candidates for monitoring solutions such as:

• Grafana
• Prometheus
• PMM (Percona Monitoring and Management)

Useful dashboard panels include:

• Pending transaction count
• Active transaction count
• Worker saturation
• Commit dependency waits
• Commit order waits
• Replication throughput

These metrics provide far more visibility than relying solely on Seconds_Behind_Source.

2. performance_schema.replication_applier_progress_by_worker

When coordinator metrics confirm a queue accumulation or worker saturation, this table isolates the specific worker thread and query responsible.

Sample Query: Worker-Level Execution Status

SELECT
  WORKER_ID,
  THREAD_ID,
  ONGOING_TRANSACTION_TYPE AS tx_type,
  ROUND(ONGOING_TRANSACTION_FULL_SIZE_BYTES / 1024 / 1024, 2) AS total_transaction_mb,
  ROUND(ONGOING_TRANSACTION_APPLIED_SIZE_BYTES / 1024 / 1024, 2) AS written_so_far_mb,
  ROUND(
    (ONGOING_TRANSACTION_APPLIED_SIZE_BYTES / ONGOING_TRANSACTION_FULL_SIZE_BYTES) * 100,
    2
  ) AS progress_percentage
FROM performance_schema.replication_applier_progress_by_worker
WHERE ONGOING_TRANSACTION_TYPE != 'UNASSIGNED';

Sample Output:

mysql> SELECT * FROM performance_schema.replication_applier_progress_by_worker  WHERE ONGOING_TRANSACTION_TYPE != 'UNASSIGNED';
+--------------+-----------+-----------+--------------------------+-------------------------------------+----------------------------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | ONGOING_TRANSACTION_TYPE | ONGOING_TRANSACTION_FULL_SIZE_BYTES | ONGOING_TRANSACTION_APPLIED_SIZE_BYTES |
+--------------+-----------+-----------+--------------------------+-------------------------------------+----------------------------------------+
|              |         0 |        71 | DML                      |                                 346 |                                    249 |
|              |         1 |        72 | DML                      |                                 342 |                                    249 |
|              |         2 |        73 | DML                      |                                 350 |                                    249 |
+--------------+-----------+-----------+--------------------------+-------------------------------------+----------------------------------------+

This detail provides insight into the worker ID, thread ID, transaction category (DML/DDL), overall payload size, bytes applied, and real-time completion percentages.

Common Replication Lag Scenarios

Scenario 1: Lock Contention

• Symptoms: progress_percentage remains stationary, written_so_far_mb halts, and overall replication lag continues rising.
• Causes: Worker thread blocked by metadata locks, row-level locks, or long-running read queries executing locally on the replica.

Scenario 2: Missing Index

• Symptoms: Transaction payload size is minimal, completion progress moves slowly, and a single CPU core runs at 100% utilization.
• Causes: Replicated UPDATE or DELETE statements executing without a primary key or suitable index force expensive full table scans on the replica.

Scenario 3: Large Transactions

• Symptoms: One worker runs for an extended period while other worker threads sit idle.
• Causes: Individual transactions cannot be distributed across multiple workers. Large operations like batch updates, bulk deletions, or heavy data loads force single-threaded processing.

Practical Troubleshooting Guide: Metadata Lock Contention

Consider a scenario where an ALTER TABLE statement reaches the replica while a local long-running read operation holds a metadata lock on that same table.

Problem Setup

On the Replica:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)

mysql> SELECT *, SLEEP(60) FROM multi_thread_test.billing_status LIMIT 1;
+----+-------------+------------+-----------+
| id | status_name | updated_at | SLEEP(60) |
+----+-------------+------------+-----------+
|  1 | TESTING     | 2026-06-11 |         0 |
+----+-------------+------------+-----------+
1 row in set (1 min 0.001 sec)

On the Source:

mysql> ALTER TABLE multi_thread_test.billing_status MODIFY COLUMN updated_at VARCHAR(100);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

Step 1: Evaluate Replication Backlog

Check coordinator statistics to confirm whether the applier queue depth is accumulating:

mysql> SELECT      TRANSACTIONS_ONGOING_COUNT AS active_worker_threads,      TRANSACTIONS_PENDING_COUNT AS applier_queue_depth,      ROUND(WAITS_FOR_AVAILABLE_WORKER_SUM_TIME / 1000000000000, 2)          AS worker_saturation_seconds,      ROUND(WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME / 1000000000000, 2)    AS data_dependency_wait_seconds,      ROUND(WAITS_DUE_TO_COMMIT_ORDER_SUM_TIME / 1000000000000, 2)           AS commit_sequence_wait_seconds  FROM performance_schema.replication_applier_metrics\G

*************************** 1. row ***************************
        active_worker_threads: 1
          applier_queue_depth: 206
   worker_saturation_seconds: 1.56
data_dependency_wait_seconds: 24.99
commit_sequence_wait_seconds: 0.01

The output shows 206 pending transactions building up in the queue, indicating an active applier block.

Step 2: Identify the Blocked Transaction and Query

Query worker progress combined with statement event details:

mysql> SELECT 
    t.PROCESSLIST_ID AS process_id, 
    w.ONGOING_TRANSACTION_TYPE AS tx_type, 
    ROUND(w.ONGOING_TRANSACTION_FULL_SIZE_BYTES / 1024 / 1024, 2) AS total_transaction_mb, 
    ROUND(w.ONGOING_TRANSACTION_APPLIED_SIZE_BYTES / 1024 / 1024, 2) AS written_so_far_mb, 
    ROUND( 
      (w.ONGOING_TRANSACTION_APPLIED_SIZE_BYTES / NULLIF(w.ONGOING_TRANSACTION_FULL_SIZE_BYTES, 0)) * 100, 
      2 
    ) AS progress_percentage, 
    COALESCE(esc.SQL_TEXT, t.PROCESSLIST_STATE) AS query 
FROM performance_schema.replication_applier_progress_by_worker w
JOIN performance_schema.threads t ON w.THREAD_ID = t.THREAD_ID 
LEFT JOIN performance_schema.events_statements_current esc ON t.THREAD_ID = esc.THREAD_ID 
WHERE w.ONGOING_TRANSACTION_TYPE != 'UNASSIGNED'\G

*************************** 1. row ***************************
          process_id: 222
             tx_type: DDL
total_transaction_mb: 0.00
   written_so_far_mb: 0.00
 progress_percentage: 30.62
               query: ALTER TABLE multi_thread_test.billing_status MODIFY COLUMN updated_at VARCHAR(100)
1 row in set (0.001 sec)

To view a unified snapshot, join both metrics tables together:

mysql> SELECT 
    ram.TRANSACTIONS_ONGOING_COUNT AS active_worker_threads, 
    ram.TRANSACTIONS_PENDING_COUNT AS applier_queue_depth,  
    ROUND(ram.WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME / 1000000000000, 2) AS data_dependency_wait_seconds, 
    t.PROCESSLIST_ID AS process_id, 
    t.PROCESSLIST_TIME AS query_duration_seconds, 
    w.ONGOING_TRANSACTION_TYPE AS transaction_type, 
    ROUND((w.ONGOING_TRANSACTION_APPLIED_SIZE_BYTES / NULLIF(w.ONGOING_TRANSACTION_FULL_SIZE_BYTES, 0)) * 100, 2) AS progress_percentage, 
    t.PROCESSLIST_STATE AS current_thread_state, 
    COALESCE(esc.SQL_TEXT, t.PROCESSLIST_STATE) AS query 
FROM performance_schema.replication_applier_progress_by_worker w 
JOIN performance_schema.threads t 
  ON w.THREAD_ID = t.THREAD_ID AND w.ONGOING_TRANSACTION_TYPE <> 'UNASSIGNED' 
LEFT JOIN performance_schema.events_statements_current esc 
  ON t.THREAD_ID = esc.THREAD_ID, 
performance_schema.replication_applier_metrics ram\G

*************************** 1. row ***************************
        active_worker_threads: 1
          applier_queue_depth: -205
data_dependency_wait_seconds: 25.06
                 process_id: 222
     query_duration_seconds: 24375
           transaction_type: DDL
        progress_percentage: 30.62
       current_thread_state: Waiting for table metadata lock
                      query: ALTER TABLE multi_thread_test.billing_status MODIFY COLUMN updated_at VARCHAR(100)
1 row in set (0.001 sec)

Step 3: Pinpoint the Blocking Session

To find the exact session holding the metadata lock, run the following query against metadata_locks:

mysql> SELECT 
    ml.OBJECT_SCHEMA AS db, 
    ml.OBJECT_NAME AS table_name, 
    ml.LOCK_TYPE, 
    w_t.PROCESSLIST_ID AS blocked_worker_pid, 
    b_t.PROCESSLIST_ID AS holding_mysql_pid, 
    b_t.PROCESSLIST_USER AS lock_holding_user, 
    b_t.PROCESSLIST_HOST AS lock_holding_host, 
    IFNULL(esc_blocking.SQL_TEXT, b_t.PROCESSLIST_STATE) AS lock_holding_last_sql 
FROM performance_schema.metadata_locks ml 
JOIN performance_schema.threads w_t ON ml.OWNER_THREAD_ID = w_t.THREAD_ID 
JOIN performance_schema.metadata_locks ml_holding 
  ON ml.OBJECT_NAME = ml_holding.OBJECT_NAME 
  AND ml.OBJECT_SCHEMA = ml_holding.OBJECT_SCHEMA 
  AND ml.LOCK_STATUS = 'PENDING' 
  AND ml_holding.LOCK_STATUS = 'GRANTED' 
JOIN performance_schema.threads b_t ON ml_holding.OWNER_THREAD_ID = b_t.THREAD_ID 
LEFT JOIN performance_schema.events_statements_current esc_blocking 
  ON b_t.THREAD_ID = esc_blocking.THREAD_ID\G

*************************** 1. row ***************************
                   db: multi_thread_test
           table_name: billing_status
            LOCK_TYPE: EXCLUSIVE
   blocked_worker_pid: 222
    holding_mysql_pid: 226
    lock_holding_user: root
    lock_holding_host: localhost
lock_holding_last_sql: SELECT *, SLEEP(60) FROM multi_thread_test.billing_status LIMIT 1
*************************** 2. row ***************************
                   db: multi_thread_test
           table_name: billing_status
            LOCK_TYPE: EXCLUSIVE
   blocked_worker_pid: 222
    holding_mysql_pid: 222
    lock_holding_user: root
    lock_holding_host: localhost
lock_holding_last_sql: ALTER TABLE multi_thread_test.billing_status MODIFY COLUMN updated_at VARCHAR(100)
2 rows in set (0.001 sec)

The output confirms process ID 226 running SELECT *, SLEEP(60)... holds the lock, blocking process 222 (ALTER TABLE). Terminating process 226 (KILL 226;) resolves the lock block and allows replication to resume.

Comparing Historical vs. Modern Replication Observability

Before the introduction of the Replication Applier Metrics Component, DBAs relied on standard tables:

• replication_applier_configuration
• replication_applier_filters
• replication_applier_global_filters
• replication_applier_status
• replication_applier_status_by_coordinator
• replication_applier_status_by_worker

While these tables supplied general status information, they lacked targeted insights into worker execution percentages, real-time applier queue depths, dependency wait metrics, and worker stall durations.

By utilizing replication_applier_metrics and replication_applier_progress_by_worker, database operations teams can move past basic delay timers and immediately identify root causes.

The Replication Applier Metrics Component available in MySQL 9.7 LTS significantly improves database observability. By converting replication troubleshooting into a structured diagnostic process, database teams can quickly resolve performance bottlenecks and keep replica nodes synchronized.

For additional strategies on optimizing MySQL high availability architectures, explore our insights on MySQL Performance Schema in Action.

Need assistance optimizing your MySQL replication architecture or resolving complex performance bottlenecks? Partner with our expert database engineers for round-the-clock support.