2

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?

3

Connection Phase of the Client/Server Protocol

The 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

| improve this 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    |
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.