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?
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.
Maatkit's mk-heartbeat tool has support for microsecond-precision timestamps in recent versions. This will do what you are looking for.
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 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.
There are several issues inventing a precise replication delay metric.
How do you expect to "use" a precise heartbeat?