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?

  • 1
    The built-in "seconds behind master" is terribly inaccurate and a guesstimate at best. I hope the answer includes a finer resolution and a more accurate number. – David Hall Jan 4 '11 at 19:19
  • Can it even be that precise? I mean, we're talking about stuff that takes time to transfer, apply, etc. Getting to 8 significant figures or whatever isn't really that much of a help when it's going to fluctuate slightly with load, network traffic, etc. – Joe Jan 4 '11 at 22:52

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.



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.

use replagdb
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.


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

  • Good to know (+1). BTW Congrats on becoming an Oracle ACE !!! – RolandoMySQLDBA Aug 11 '11 at 17:13

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.