Mysql – Huge MySQL slave replication lag (days) at almost idle servers. Slave is mostly ‘Waiting for dependent transaction to commit’

MySQL

I have a strange situation with MySQL replication lag happening on relatively idle servers.

Slave shows no symptoms of IO saturation (according to top, iotop or iostat), no symptoms of CPU saturation. If I do SHOW SLAVE STATUS at regular intervals, it seems, that slave spends 99% of it's time at two events:

Waiting for slave workers to process their queues
Waiting for dependent transaction to commit (happening most often).

The problem is that the database is about 1Tb in size and it's not easy experimenting with it. Both master and slave are Xeon E5 with SSD RAID10 storage. Investigation showed no lagging of slave IO thread. Everything looks like slave SQL thread is constantly waiting for some event to complete before replaying next transaction from log.

Replication type used is row-based. We tried to switch to slave_parallel_type = LOGICAL_CLOCK and set slave_parallel_workers = 24 but with no success. We see no server resource over-utilization. All metrics show that the server is mostly idle. log_slow_slave_statements shows nothing. innodb_flush_log_at_trx_commit = 2 to prevent fsyncs() on small transactions (not that it was necessary because we are nowhere near iops limit but we wanted to try something).

Putting additional load to server does not affect replication lag.

Database being replicated contains about 95% of InnoDB tables and some TokuDB tables.

This situation started about several months ago at a time point none can remember. Despite of the fact, that it is a visible point in Grafana for us, we can't remember or find something in logs that could bring us closer to the problem cause.

MySQL server version is 5.7 by Percona.

Best Answer

Check these two server parameters (my.cnf):

slave_pending_jobs_size_max should be same or more than the value of max_allowed_packet https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#sysvar_slave_pending_jobs_size_max
slave_transaction_retries value should be twice of slave_parallel_workers.

Related Solutions

MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

The "Time" in the SQL thread is (I think) identical to Seconds_behind_master. It is "How long ago did this query start on the Master ".

All other Times are indicate when the query started on the Slave.

Some fluctuation is caused by what it is measuring (the Master's start time).

Sometimes (rarely), I see the value (both places) bouncing between 0 and some large value. I have yet to track this down. I have seen it on 4.0, 4.1, and 5.1. It eventually goes away, and becomes civilized.

There may be cases where no traffic leads to strange values. But I don't have any Master-Slave setups with little enough traffic for me to comment.

Suppose you do ALTER on the Master, and it took 1 hour (3600 seconds). Also, suppose not much else is going on. The ALTER replicates and starts running. Immediately, the Seconds_behind_master will be about 3600. After the ALTER finishes on the Slave (say, 3600 more seconds later), subsequent replication items will execute with (probably) smaller Times. Eventually replication catches up.

Mysql – Is MySQL Replication Affected by a High-Latency Interconnect

The direct answer to your question is Yes, but it depends on the version of MySQL you are running. Before MySQL 5.5, replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

As of MySQL 5.5, using Semisynchronous Replication, now replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Acknowledges Master of the Receipt of the SQL Event
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

This new paradigm will permit a Slave to be closer sync'd to its Master.

Notwithstanding, latency within the network could hamper MySQL Semisync Replication to the point where it reverts back to the old-style asynchronous replication. Why ? If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semisynchronous slave catches up, the master returns to semisynchronous replication.

UPDATE 2011-08-08 14:22 EDT

The configuration of MySQL 5.5 Semisynchronous Replication is straightforward

Step 1) Add these four(4) lines to /etc/my.cnf

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled

Step 2) Restart MySQL

service mysql restart

Step 3) Run these commands in the MySQL client

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave  SONAME 'semisync_slave.so';

Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled

Step 5) Restart MySQL

service mysql restart

All Done !!! Now just setup MySQL Replication as usual.

Best Answer

Related Solutions

MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

Mysql – Is MySQL Replication Affected by a High-Latency Interconnect

Related Question