Speeding up the receiving of mysql binlog when replication is configured

Have you ever set up a mysql replication between two host with more than 100ms latency between. Let’s say one of your server is in US and the other is in Europe. The latency between the servers could reach more than 100ms and your MySQL replication (or galera cluster, or master-master configuration) which could easily turn into problems when you have really busy servers. In our case we have really busy servers with more than 7G binlog of every minute, which means 7G/minute transfer between the servers with a latency of 100ms and it could get worse your connectivity just hiccup for a minute or two and you would have a replication behind with more than 20 binlog files, which could take up to an hour to advance. There is a good chance to get the binlog files a lot faster – between 5-8 times faster

just compress them!

You can enable compression between your servers so they will transfer the binlog in a compressed format, the compression algo is really fast and almost no CPU consuming you’ll not observe any penalties (even if you enable it between servers on “the same switch”) and your traffic will decrease with almost 5-8 times it depends on your queries but in general it is around such values! So you now 1G binlog could be transferred 5-8 times faster using only 150Mbytes of your connection!
Here is how to enable this option

  1. Enter MySQL console
  2. set the compression to true
  3. verify the compression is set
  4. STOP and then START the slave – the compression won’t happen between the servers if you DO NOT DO this!!!
srv@local ~ # mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 55856
Server version: 10.1.30-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set global slave_compressed_protocol=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like 'slave_compressed_protocol';
| Variable_name             | Value |
| slave_compressed_protocol | ON    |
1 row in set (0.00 sec)

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.15 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

Then check your network:

main menu
Network Statistics

main menu
Network Packets

Leave a Reply

Your email address will not be published. Required fields are marked *