I have a MySQL database that does a fair number of updates and inserts. The server is replicated to an off-site server located across the country. With MySQL replication, any Insert, Update, or Delete statements are written to the binary log, then sent from the master server in San Jose to the slave in New York.
I noticed today that the slave server was falling behind the master and had trouble keeping up. I noticed that there was a sizable amount of bandwidth between the two servers and after investigating for a little while, determined that the bandwidth between the servers wasn’t sufficient to keep up with the replication.
We have applications running on the server in New York that were significantly behind or slow. After a bit of research, I found the slave_compressed_protocol setting in MySQL which allows the master and slave to compress the replication data between the two servers. After enabling that, the slave was able to catch up within a matter of minutes and has stayed caught up just fine. The bandwidth usage has dropped from a consistent 600 kb/s to around 20 kb/s.
Upon looking into MySQL replication, I also experimented with SSH compression since the replication goes through an SSH Tunnel. I had similar success with SSH compression as well.
4 thoughts on “Compression for MySQL Replication”
Would you mind that I ask you a simple question? I have enabled replication compression in my application both in master and slave as slave_compressed_protocol | ON
But I ask my network people and it seems we haven’t got the noticable bandwidth gain? So Could you give us a clue? By the way, how could we compressed by SSH?
I’m not sure what monitoring tools you have available, or what your network people are reporting on. I’ve found iftop very useful because you can run it directly on the server. I just noticed that there is an option available to monitory per-port as well. With that, you could confirm the actual bandwidth that the mysql traffic is consuming.
As for SSH compression, I just a enabled in in ~/.ssh/config like this:
Hmmm, what option is that, Brandon?
I cannot seem to find it in the manual. Well, no reference to compression that I could find!
The ‘-P’ argument breaks traffic down by port. here are all of the options.