Master/Slave Replication In Limited Bandwidth Scenarios

In a healthy replication channel, the status would be "Has sent all binlog to slave; waiting for binlog to be updated". We suspected this might be an issue related to network bandwidth, but when we ran a series of STOP SLAVE / START SLAVE processes, we noticed that the number of threads in status “Writing to net” was increasing, with up to 20 concurrent open threads. Connections simply weren’t being closed. Further investigation revealed that those connections were staying in CLOSE_WAIT status. Based on our observations we concluded that the limited network bandwidth was at fault, and we needed a solution to compress the transmission between the master and slave databases. Luckily MySQL provides a convenient parameter, which is dynamic and is set on the slave: slave_compressed_protocol=1

Here is how to implement it: mysql> show global variables like 'slave_compressed_protocol'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | slave_compressed_protocol | OFF | +---------------------------+-------+ 1 row in set (0.00 sec) mysql> stop slave;

Query OK, 0 rows affected (0.25 sec) mysql> set global slave_compressed_protocol=1; Query OK, 0 rows affected (0.00 sec) mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'slave_compressed_protocol'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | slave_compressed_protocol | ON | +-----------------------------------+ Resolution: We set slave_compressed_protocol=1 and restarted the slave. Replication started catching up at a very surprising speed. Even though it was catching up, we noticed the same behavior we’d noticed in the past: Seconds_Behind_Master was jumping from 0 to a random high value, and then back to 0, the io_thread was behind, and we didn’t see a high load on the server. As you can see in these graphs, network traffic did not increase much, but CPU usage and commands/sec had surprisingly high jumps.       In short, DB_Slave was processing more queries because the IO thread was receiving more data from DB_Master, thanks to compression.