Is there a way to measure replication lag in MySQL with a resolution less than 1 second?

Question

Is there a way by which you can measure replication lag in MySQL with a resolution of less than 1 second?

That is, can replication lag be measured on the microsecond or millisecond level?

Answer 1

Have you tried maatkit heartbeat ? This is the one I'm currently using. You will need another perl process running, but from my perspective, this is much more helpful than the current MySQL mechanism.

I'm also using mk-slave-delay to generate lag. I keep a 1 hour lag in one of the slave to have an extra layer of protection in case of SQL injection or user errors.

These two scripts do the trick.

Saludos

Answer 2

As a quick and dirty answer, you may want to use the source code and create a UDF to make replication monitor the lag in microseconds.

However, I thought of an interesting method for figuring out a more granular approach to replication.

Create a database (replagdb) whose sole purpose is to record this granular timestamp. Within the replagdb database, create a table that only holds a floating point number representing the timestamp.

CREATE DATABASE IF NOT EXISTS replagdb;
use replagdb
DROP TABLE IF EXISTS replagtb;
CREATE TABLE replagtb (tmstmp DOUBLE) ENGINE=MEMORY;
INSERT INTO replagtb VALUES (0);

Create a perl script the opens a DB Connection on the master, and then in an infinite loop performs this on the master:

1. Get the output of date +"%s.%N" from the OS on the master
2. UPDATE replagdb.replagtb SET tmstmp = numberFromStep1;

Letting this replicate from the master to the slave, you could then perform this on the slave:

1. Get the output of date +"%s.%N" from the OS on the slave, store it in a variable t1
2. SELECT tmstmp FROM replagdb.replagtb and store this in variable t2.
3. Set variable df = t1 - t2

df will represent the replication lag as a double precision floating point number.

To prevent massive relay log growth on the slave, you must set max_relay_log_size to 8G on the slave. You must also carefully monitor the rate of growth for your master's binary logs.

Answer 3

There are several issues inventing a precise replication delay metric.

• If your slave is on the other side of the world, you could have a 200ms delay just because of the connection distance.

• Similarly, if you are monitoring from here, but your slave is there, you information will be another 200ms out of date.

• If you inject a precise heartbeat, all it tells you is the delay in that heartbeat; it tells you nothing about the long statement that started right after it. Nor the statements that are queued up behind that statement.

• If you inject a sub-second heartbeat too often, you will be slowing down replication.

How do you expect to "use" a precise heartbeat?

Answer 4

Maatkit's mk-heartbeat tool has support for microsecond-precision timestamps in recent versions. This will do what you are looking for.