mysql geographical replication lag

Recently while working on geographical master slave replication we encountered an issue, where in peak hours replication slave lagging.

when we checked SHOW SLAVE STATUS value of Seconds_Behind_Master was jumping from 0 to a random high value, and then back to 0.

We also found this in the processlist of Master,

1366318 rpluser [IP Address]:15120  NULL  Binlog  Dump   9033    Writing to net  NULL

The status “Writing to net” means that MASTER is sending (or trying to send) data to SLAVE.

In a healthy replication, the status would be “Has sent all binlog to slave; waiting for binlog to be updated“.

It usually happens when MASTER-SLAVE geographically distributed in a different data-center, network , and slow network.

Here limited network bandwidth was causing a problem, and we needed a solution to compress the transmission between the master and slave databases. MySQL provides a variable, which is dynamic and is set on the slave: slave_compressed_protocol=1

If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

changes on slave:

show global variables like 'slave_compressed_protocol';   //default OFF

stop slave;

set global slave_compressed_protocol=1;

start slave;

show global variables like 'slave_compressed_protocol';     // ON

Also,  check PROCESSLIST on master and replication user status,