Mesure lag replicaton with timestamps

MySQL & MariaDB Other MySQL® Questions
1

Hi

one of my inmediate goals is get some way to monitor lag on replication. I read some howtos about lag replication, and of most common solutions is to compare timestamps.

Excuse me but I don’t understand exactly how this can be work.

For example, one solution is create a table on master and load it with timestamps of server (by sh,php script, whatever). Then in slave we will connect against MASTER, we’ll get records and then we’ll compare with SLAVE timestamp.

But…the timestamp of both servers always will be the same, or not? I mean, if we have both servers with system date syncronized by some service, dates will be the same.

Excuses for a poor knowledge of this (

If someone can bring me a little light will be wellcome.

Thanks!

UPDATE:

After talking with a partner, we have noticed maybe is don’t necessary to have a table and php script on MASTER. My partner have told me that we can get the current timestamp of MASTER doing query directly against MASTER with “Select Now()”. With this procedure, we don’t need the table and cron on MASTER. Are you agree?

2

You must continuously insert timestamps into a table on your master.

Then on your client you SELECT NOW()-MAX(timestamp) FROM tbl to find out the lag. Just make sure the clocks are synced.

3

Hi gmouse

the method understood, more or less. So, if I understand well, this method is based on knowledge of time respond between two servers.

  • The system date on every server has to be the same.
  • We record timestamp on MASTER every second
  • When we do the query on SLAVE, the system time is the same, but in order to do the query, SQL Thread or I/O Thread maybe waste some seconds to do the query against MASTER and is in this moment when we can notice the lag.

Are you agree?

Thanks