How can I verify that MySQL's slave_compressed_protocol is working

Question

I've set slave_compressed_protocol=1 on both my master and slave.

Using SHOW VARIABLES, I can see that it's set.

How can I check that it's actually working?

Answer 1

Connection Phase of the Client/Server Protocol

At the Client Response, there is crosscheck of capabilities between the client and the server. The CLIENT_COMPRESS flag (value 0x00000020) needs to be mutually exchanged before continuing authentication.

To be honest with you, I have never heard of a MySQL question this deep. Good the thing all of this appears in the MySQL Internals Documentation.

How on earth would you actually know where to intercept the CLIENT_COMPRESS ?

I would suggest using tcpdump or snort to inspect packets.

I will leave it to you to read up on the needed exchange packets

• Protocol::Handshake
• Protocol::HandshakeResponse

Answer 2

slave_compressed_protocol = 1 is only required on the slave to notify the master that it wants to use the compression.

I use iftop -P to verify it's actually working. For example,

Disable compression, restart MySQL and monitor it on the slave:

root@slave # vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
slave_compressed_protocol = 0

root@slave # systemctl restart mysql.service

root@slave # mysql -e "SHOW variables WHERE variable_name REGEXP 'slave_compressed_protocol';"

| slave_compressed_protocol | OFF    |

root@slave # iftop -P

TOTAL:                  8.90MB

Run the insert query 1000 times on the master:

exp.student schema:

CREATE TABLE `student` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Age` int(10) unsigned NOT NULL,
  `Intro` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

root@master # vim bench.bash

#!/bin/bash

longStr=$(printf 'asdf %d ' {1..1000})

for i in {1..1000}; do
        mysql -e "INSERT INTO exp.student (Name, Age, Intro) VALUES ('Jun ${i}', 15, '${longStr}');"
done

root@master # bash bench.bash

Enable compression, restart MySQL, do the above steps again and compare the total transfer result:

root@slave # vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
slave_compressed_protocol = 1

root@slave # systemctl restart mysql.service

root@slave # mysql -e "SHOW variables WHERE variable_name REGEXP 'slave_compressed_protocol';"

| slave_compressed_protocol | ON    |