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?
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 only takes a minute to sign up.Sign up to join this community
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.
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:
Letting this replicate from the master to the slave, you could then perform this on the slave:
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.
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?