mysql_replication_lag

Alerts
1

mysql_replication_lag

Database | MySQL, MariaDB

This alert presents the number of seconds that the replica is behind the master.
Receiving this means that the replication SQL thread is far behind processing the source binary log.
A constantly high value (or an increasing one) indicates that the replica is unable to handle events
from the source in a timely fashion.

This alert is raised into warning when the metric exceeds 10 seconds.
If the number of seconds that the replica is behind the master exceeds 30 seconds then the alert is
raised into critical.

In MySQL, replication involves the source database writing down every change made to the data
held within one or more databases in a special file known as the binary log. Once the replica
instance has been initialized, it creates two threaded processes. The first, called the IO
thread, connects to the source MySQL instance and reads the binary log events line by line,
and then copies them over to a local file on the replica’s server called the relay log. The
second thread, called the SQL thread, reads events from the relay log and then applies them
to the replica instance as fast as possible.

Recent versions of MySQL support two methods for replicating data. The difference between these
replication methods has to do with how replicas track which database events from the source
they’ve already processed.1

For further information, please have a look at the References and Sources section.

References and Sources
  1. Replication in MySQL
  2. MySQL Replication Slave Lag
  3. log_slow_slave_statements
  4. long_query_time
  5. log_slow_verbosity

Troubleshooting Section

Query optimization and "log_slow_slave_statements"

To minimize slave SQL_THREAD lag, focus on query optimization. The following logs will help you identify the problem:

  1. Enable log_slow_slave_statements
    to see queries executed by slave that take more than long_query_time.
  2. To get more information about query performance, set the configuration option log_slow_verbosity to full.

You can also read the Percona blog for a nice write-up aboutMySQL replication slave lag.