Compression for MySQL Replication

Posted on July 25th, 2008 in General,Linux System Administration,MySQL by Brandon

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 Responses to 'Compression for MySQL Replication'

Subscribe to comments with RSS or TrackBack to 'Compression for MySQL Replication'.

  1. Don Y. said,

    on December 18th, 2008 at 10:50 am

    Hi Brandon,

    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?
    Many thanks,

    Don

  2. Brandon said,

    on December 20th, 2008 at 11:49 am

    Don,

    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:

    Host remote.domain.com
        Compression yes
        CompressionLevel 9
    
  3. cpg said,

    on October 21st, 2009 at 4:21 am

    Hmmm, what option is that, Brandon?

    I cannot seem to find it in the manual. Well, no reference to compression that I could find!

  4. Brandon said,

    on November 16th, 2009 at 11:16 am

    The ‘-P’ argument breaks traffic down by port. here are all of the options.

    [root@dev ~]# iftop -h
    iftop: display bandwidth usage on an interface by host
    
    Synopsis: iftop -h | [-npbBP] [-i interface] [-f filter code] [-N net/mask]
    
       -h                  display this message
       -n                  don't do hostname lookups
       -N                  don't convert port numbers to services
       -p                  run in promiscuous mode (show traffic between other
                           hosts on the same network segment)
       -b                  don't display a bar graph of traffic
       -B                  Display bandwidth in bytes
       -i interface        listen on named interface
       -f filter code      use filter code to select packets to count
                           (default: none, but only IP packets are counted)
       -F net/mask         show traffic flows in/out of network
       -P                  show ports as well as hosts
       -m limit            sets the upper limit for the bandwidth scale
       -c config file      specifies an alternative configuration file
    
    iftop, version 0.17
    copyright (c) 2002 Paul Warren
     and contributors
    

Post a comment

Please copy the string iKTYZc to the field below: