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:
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
set global slave_compressed_protocol=1;
show global variables like 'slave_compressed_protocol'; // ON
Also, check PROCESSLIST on master and replication user status,