Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
    
see How to accept an answer –  jcolebrand May 31 '11 at 2:37
add comment

4 Answers

up vote 8 down vote accepted

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

share|improve this answer
add comment

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

share|improve this answer
    
Good to know (+1). BTW Congrats on becoming an Oracle ACE !!! –  RolandoMySQLDBA Aug 11 '11 at 17:13
add comment

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.

share|improve this answer
add comment

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?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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