The MySQL Protocol is the network protocol that is used between a MySQL server and a client. This is called the “classic” protocol as there is now a newer protobuf based protocol called X Protocol. However the “classic” protocol is used by many database drivers, applications, etc. and also by MySQL Replication.
The MySQL Protocol has the option to compress network traffic. Most client libraries don’t enable this by default as this increases CPU usage.
Some history
Initially this was added in MySQL 3.22 (based on historical release notes) and was based on zlib.
Then in MySQL 8.0.18 a second compression algorithm, Zstandard, was added based on a contribution by Facebook.
So zlib has been there basically forever and Zstandard support is new-ish (8.0.18 was released in October 2019).
Why you might want to use compression.
There are multiple usecases that are often mentioned when talking about the compressed protocol. The first one is slow connections. This could, for example, be a DBA doing remote work.
The second usecase is metered connections. This could be a DBA using a mobile connection or to reduce network transfer cost in a Cloud environment.
Basic usage
The usage is as simple as
mysql --compression-algorithms=zstd .... Instead of
zstd you can also use zlib or even provide
both (zstd,zlib) and see what’s available on the server.
Most other clients like mysqldump, MySQL Shell, etc also
support this setting. Older versions use
mysql --compress ... which enables zlib based
compression. What is used in the end depends on what the server
supports.
For zstd there is a --zstd-compression-level
option that allows you to set a level between 1 and 22, where the
default is 3. This allows you to better balance between faster and
better compression.
Once connected you could check the Compression,
Compression_algorithm and Compression_level
status variables to verify what is being used.
sql> SHOW STATUS LIKE 'Compression%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Compression | ON |
| Compression_algorithm | zstd |
| Compression_level | 3 |
+-----------------------+-------+
3 rows in set (0.0043 sec)In MySQL Shell the \status command also provides you
with this information:
sql> \status
MySQL Shell version 8.2.0
Connection Id: 30
Current schema: test
Current user: root@localhost
SSL: Cipher in use: TLS_AES_128_GCM_SHA256 TLSv1.3
Using delimiter: ;
Server version: 8.2.0 MySQL Community Server - GPL
Protocol version: Compressed 10 <------------------
Client library: 8.2.0
Connection: 127.0.0.1 via TCP/IP
TCP port: 3306
Server characterset: utf8mb4
Schema characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Result characterset: utf8mb4
Compression: Enabled (zstd) <------------------
Uptime: 1 day 3 hours 20 min 4.0000 sec
Threads: 2 Questions: 200 Slow queries: 0 Opens: 348 Flush tables: 3 Open tables: 266 Queries per second avg: 0.002The connectors for your favorite programming language probably have
settings for this as well. Some might only allow you to enable
compression with zlib and not yet offer you the option for
zstd.
| Language | Driver | zlib | zstd | Setting |
|---|---|---|---|---|
| C | MySQL C API (libmysqlclient) | ✅ | ✅ | MYSQL_OPT_COMPRESSION_ALGORITHMS and
MYSQL_OPT_COMPRESS |
| Python | Connector/Python | ✅ | ❌ | compress=True |
| Java | Connector/J | ✅ | ❌, Bug #112732 | useCompression=true |
| Go | go-sql-driver/mysql | ❌ #24 | ❌ | - |
| PHP | mysqli | ✅ | ❌ | MYSQLI_CLIENT_COMPRESS |
| Perl | DBD::mysql | ✅ | ✅ | mysql_compression=zlib |
Note that many drivers are based on libmysqlclient and
only require minimal changes to support this.
A look at the internals
Handshake
When establishing a connection both the client and server send a set
of capability
flags. There are two flags that are used for compression: First
there is CLIENT_COMPRESS that is used to advertise support
for zlib and then there is
CLIENT_ZSTD_COMPRESSION_ALGORITHM that is used to advertise
support for Zstandard. If both the client and the server have a flag set
then that feature is used.
A bit of a special case is possible when both the client and server
set both of the compression flags, in that case zlib is used. You can
try this out by running --compression-algorithms=zstd,zlib
and looking at the status variables.
If the client has the CLIENT_ZSTD_COMPRESSION_ALGORITHM
flag set then the Protocol::HandshakeResponse41
also contains a compression level for Zstandard.
Packet compression
The MySQL Protocol consists of “MySQL Packets” that are send between the client and server. When compression is used this is added as a separate layer in the network stack.
So the stack basically looks something like this:
| MySQL Protocol |
| MySQL Compressed Protocol |
| TLS (Optional) |
| TCP/IP | Unix Socket | ..... |And yes, if you want you can use the compressed protocol over a Unix Socket. But I can’t think of a good reason to do so.
The ..... above is because of other
connection options on Windows, like Shared memory and Named
Pipes.
As this is a separate layer there isn’t a 1-to-1 mapping between compressed packets and regular packets.
So what do these MySQL Compressed Protocol packets look like?
Let’s start by looking at the header.
Description:
| <compressed_length[3]> | <compressed_sequence[1]> | <uncompressed_length[3] |
Example values (hex):
| 15 00 00 | 00 | 000000 |So here we have: - Compressed Length: 21. This is
15 00 00 in hex. This is a 24 bit (3 byte) integer. -
Compressed Sequence: 0 - Uncompressed Length: 0
If you know the regular MySQL Protocol this probably looks very similar, including the use of the somewhat excentric 24 bit integer.
That the Uncompressed Length is set to zero means that for this
packet the payload isn’t actually compressed. This happens when the side
that does compression doesn’t think it makes sense to compress the
payload. MySQL doesn’t compress the payload if it is less than
MIN_COMPRESS_LENGTH, which is 50 bytes.
After the header we get the payload which depending on the capability flags might be compressed with zlib or Zstandard or might be uncompressed if the Uncompressed Length is set to 0.
This is how this looks in Wireshark:
A compressed protocol packet can also contain multiple (regular) MySQL packets:
Here there are eight regular MySQL Packets inside the compressed packet.
Wireshark can uncompress the compressed payload (both zlib and zstd). This is why it is able to show you what’s in the compressed packet. On the bottom you can see “Fram (202 bytes)” and “compressed data (147 bytes)”.
For this packet we have: - Compressed Length: 129 bytes - Uncompressed Length: 147 bytes - TCP Payload Length: 136 bytes (this matches our expectation: 129 bytes payload + 7 byte header)
So instead of sending 147 bytes we sent 136 bytes. We saved 11 bytes (~7.5%).
The actual compression ratio depends on the size (bigger is better), randomness of the content, the compression algorithm and compression level. For zlib the compression level is hardcoded, so you can only use different compression levels for Zstandard.
We talked about two situations: The first situation is one compressed packet with one MySQL packet and the second situation is one compressed packet with multiple MySQL packets. But there is a third situation: Multiple compressed packets for one MySQL packet.
A single MySQL Protocol Packet can be split over multi compressed packets.
Efficiency
To get good results with compression in general it really helps if you’re compressing larger sets of data and if the compressed data isn’t very random.
Let’s see what results we can get in a best case senario by using
REPEAT() to send a string of 100 million times
x.
To do this we need to make sure we set a large
max_allowed_packet for both the server and client. We also
disable SSL/TLS as that makes capturing things easier. In case you
really need this, Wireshark is able to decrypt
MySQL sessions with TLS.
$ mysql --compression-algorithms=zlib -h 127.0.0.1 -u root -pRoot123@ --ssl-mode=DISABLED --max-allowed-packet=1073741824
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 237
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> pager wc
PAGER set to 'wc'
mysql> select repeat('x',100*1024*1024);
5 9 419431449
1 row in set (1.06 sec)
mysql> ^DByeThe traffic that we captured in Wireshark looks like this: