MySQL Replication Lag Check

MySQLreplication

we are about to replicate our database servers into several slaves in different physical locations.

The company which is doing replication for us, has asked to give them a sql select statement on one of our tables to check lag and latency like

SELECT id FROM table1 ORDER BY id DESC LIMIT 1

Is it a common practice to check the lag between several instances?

Thank you in advance.

Best Answer

It's unclear what they want to measure. To see the network lag, all they need is ping in the OS. That should be much less than a second, even to the other side of the world. (Unless they have a sloppy network.)

Seconds_behind_master is problematical. It is a handy tool for the DBA, but it can be "wrong" for many reasons.

When running a long query, it shows how long ago the query started on the Master. So, Seconds_behind_master will rise as long as that query is running on the Slave.
If you flood the Master with lots of writes from lots of connections, the Slave can get "behind". This is congestion because of single-threaded Slave, not because of network lag.

One thing it can tell (I think) -- If the timezones are inconsistent, or they don't have a good clock sync (NTP).

A SELECT will not propagate through replication, only writes. So, I see no use for your suggested SELECT.

If you are running at least 5.6.4: CREATE a DATABASE and a login limited to that database (GRANT ALL ON db.* ...). Give them the user name an password. Let them INSERT into the Master and see what pops up in the Slave. I think this is what is needed (but I have not tried it):

-- Create on Master and propagate to Slave:
CREATE TABLE Timer (
    id INT NOT NULL,
    raw_time TIMESTAMP(6) NOT NULL,
    sys_time TIMESTAMP(6) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;
-- Run on Master:
REPLACE INTO Timer (id, raw_time, sys_time) VALUES (1, NOW(6), SYSDATE(6));
-- Run on Slave:
SELECT * FROM Timer;

The difference of the two columns will say how much delay where was -- Including

A tiny delay between NOW and SYSDATE on the Master
Network delay
Slave backlog (if something was running on the Slave when the REPLACE was being replicated; this can be minimized by using multi-threaded replication)

Note: The (6) everywhere gives you microsecond precision. This is more than sufficient to measure the milliseconds for replicating cross-country.

Note: That technique won't work if you run with --sysdate-is-now.

Point the Company at this Answer; challenge them to top it.

Related Solutions

MySQL Replication Lag Behaving Erraticly

MySQL Replication performs weird calculations for Seconds_Behind_Master based on a few things.

What NOW() returns for SQL run on the master as recorded in relay logs
What NOW() returns on the slave
The last position from the master that was executed

Here is a SHOW SLAVE STATUS\G and how to identify the Last Executed SQL from the Master

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.17.20.102
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002814
          Read_Master_Log_Pos: 823078734
               Relay_Log_File: relay-bin.007364
                Relay_Log_Pos: 823078879
        Relay_Master_Log_File: mysql-bin.002814
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 823078734
              Relay_Log_Space: 823079071
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

Notice the following fields in SHOW SLAVE STATUS\G

Master_Log_File (Line 6) : Log File on the Master whose position was last read
Read_Master_Log_Pos (Line 7) : Last position read on the Slave from the Master
Relay_Master_Log_File (Line 10) : Log File on the Master whose position was last executed
Exec_Master_Log_Pos (Line 22) : Last position executed on the Slave from the Master
Relay_Log_Space (Line 23) : Sum of bytes from all relay logs

To calculate Seconds_Behind_Master, it would essentially figured out

NOW() as recorded in Master LogFile 'Master_Log_File' LogPos 'Read_Master_Log_Pos'
minus
NOW() on the Slave

This number can intermittently grow because the I/O thread can collect more entries from the Master and loading it into the last relay log while processing the SQL statement located at Master LogFile 'Relay_Master_Log_File' and Master LogPos 'Exec_Master_Log_Pos'. This increasing number is also manifested by three(3) variables changing: Relay_Log_Space, Master_Log_File, Read_Master_Log_Pos. If Seconds_Behind_Master is 0, this is good indication that Read_Master_Log_Pos and Exec_Master_Log_Pos virtually the same. Also worth noting is the fact that long running SQL can fool you into thinking that replication is behind. Once that SQL statement is complete and no other statements have sufficiently backlogged, Seconds_Behind_Master can drop dramatically, even to 0.

So, why would Seconds_Behind_Master bounce back and forth between 0 and an increasing number. It is possible for network latency in transmitting SQL through the I/O thread to contribute to miscalculation because the needed TIMESTAMP variable was not reached yet in the back of the last relay log (remember this is asynchronous replication). To verify this, run mysqlbinlog against any binary log or relay log and see where the TIMESTAMP variables are written between statements.

This behavior was first addressed June 22, 2007 and the bug was supposedly fixed then.

Here is one sure way to take MySQL Replication and screw its head on correctly (For this example, use the same SHOW SLAVE STATUS\G)

Using Relay_Master_Log_File mysql-bin.002814 and Exec_Master_Log_Pos 823078734 as the Log FilePosition to Restart From, run these commands

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002814',MASTER_LOG_POS=823078734;
START SLAVE;

These steps should 1) kill both I/O and SQL Threads, 2) wipe out All Collected Relay Logs, 3) start with a New Relay Log, and 4) establish New I/O and SQL Threads.

Replication should be good to go from here. As to whether or not this bug can truly be addressed, this now in Oracle's hands (That last statement does not inspire any confidence, does it ???)

Mysql – Two master databases for two separate apps

MULTIMASTER SETUP

I would recommend a multimaster setup only with the idea that you restrict Asia's data to one master, and Europe's data to the other master. As for the shared databases, make sure auto_increment_increment and auto_increment_offset are in full use. In addition, make sure you only write Asia's data to the shared databases at the Asian DC and likewise with Europe.

STORAGE ENGINE CONSIDERATIONS

If there is no specfic reason to keep MyISAM (such as FULLTEXT indexing or heavy volume reads), I would recommend converting everything to InnoDB. That way, if a server crashes, then crash recovery can bring databases to a consistent state. With MyISAM, tables can be left in a crashed state and repairing those tables may result in the disappearence of one or more rows of data.

HIGH AVAILABILITY

You should strengthened your HA Setup by implementing DRBD. DRBD should be setup as at each DC. What are the benefits?

You maintain a disk-level replica of the mysql data within both data centers
You can have automatic failover within each data center
You only worry about MySQL Replication between DCs

REPLICATION

To minimize data loss with MySQL Replication over geographic distance, I would recommend using smaller binary logs. By default, a binary log/relaylog is 1G (max_binlog_size and max_relay_log_size). You can make these values much smaller. I got this idea from PostgreSQL because WAL files are 16M by default. This creates more log files, but those log files will be closed and complete faster on the acting slave. If you are not using it, you should upgrade to MySQL 5.5 because it has Semisynchronous Replication. It can be tuned to detect heartbeat timeouts with some granularity.

I wrote about some of these concepts in an earlier post back on March 29, 2011.