4.2.6 Connection Compression Control

Contents

Connections to the server can use a compressed protocol that reduces the number of bytes sent over the connection. By default, connections are uncompressed, but can be compressed if the server and the client agree on the compression algorithm to use.

This section describes the available compression-control configuration parameters and the information sources available for monitoring use of compression.

Compression control applies to connections to the server by client programs and by servers participating in master/slave replication or Group Replication. (In the following discussion, “client connection” is shorthand for a connection to the server originating from any source for which compression is supported, unless context indicates a specific connection type.) Compression control does not apply to X Protocol connections or connections for FEDERATED tables.

Compressed connections originate on the client side but affect CPU load on both the client and server sides because both sides perform compression and decompression operations. Because enabling compression decreases performance, its benefits occur primarily when there is low network bandwidth, network transfer time dominates the cost of compression and decompression operations, and result sets are large.

Connection Compression Configuration

As of MySQL 8.0.18, these configuration parameters are available for controlling connection compression:

The protocol_compression_algorithms system variable configures which compression algorithms the server permits for incoming connections.
The --compression-algorithms and --zstd-compression-level command-line options configure permitted compression algorithms and zstd compression level for these client programs: mysql, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlpump, mysqlshow, mysqlslap, and mysqltest, and mysql_upgrade.
The MYSQL_OPT_COMPRESSION_ALGORITHMS and MYSQL_OPT_ZSTD_COMPRESSION_LEVEL options for the mysql_options() function configure permitted compression algorithms and zstd compression level for client programs that use the MySQL C API.
The MASTER_COMPRESSION_ALGORITHMS and MASTER_ZSTD_COMPRESSION_LEVEL options for the CHANGE MASTER TO statement configure permitted compression algorithms and zstd compression level for slave servers participating in master/slave replication.
The group_replication_recovery_compression_algorithm and group_replication_recovery_zstd_compression_level system variables configure permitted compression algorithms and zstd compression level for Group Replication recovery connections when a new member joins a group and connects to a donor.

Configuration parameters that enable specifying compression algorithms are string-valued and take a list of one or more comma-separated compression algorithm names, in any order, chosen from the following items (not case-sensitive):

zlib: Permit connections that use the zlib compression algorithm.
zstd: Permit connections that use the zstd compression algorithm (zstd 1.3).
uncompressed: Permit uncompressed connections.

Note

Because uncompressed is an algorithm name that may or may not be configured, it is possible to configure MySQL not to support uncompressed connections.

Examples:

To configure which compression algorithms the server permits for incoming connections, set the protocol_compression_algorithms system variable. By default, the server permits all available algorithms. To configure that setting explicitly at startup, use these lines in the server my.cnf file:
```
[mysqld]
protocol_compression_algorithms=zlib,zstd,uncompressed
```
To set and persist the protocol_compression_algorithms system variable to that value at runtime, use this statement:
1. SET PERSIST protocol_compression_algorithms='zlib,zstd,uncompressed';
SET PERSIST sets the value for the running MySQL instance. It also saves the value, causing it to be used for subsequent server restarts. To change the value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 13.7.5.1, “SET Syntax for Variable Assignment”.
To permit only incoming connections that use zstd compression, configure the server at startup like this:
```
[mysqld]
protocol_compression_algorithms=zstd
```
Or, to make the change at runtime:
1. SET PERSIST protocol_compression_algorithms='zstd';
To permit the mysql client to initiate zlib or uncompressed connections, invoke it like this:
```
mysql --compression-algorithms=zlib,uncompressed
```
To configure replication slaves to connect to the master using zlib or zstd connections, with a compression level of 7 for zstd connections, use a CHANGE MASTER TO statement:
1. CHANGE MASTER TO
2. MASTER_COMPRESSION_ALGORITHMS = 'zlib,zstd',
3. MASTER_ZSTD_COMPRESSION_LEVEL = 7;
This assumes that the slave_compressed_protocol system variable is disabled, for reasons described in Legacy Connection Compression Configuration.

For successful connection setup, both sides of the connection must agree on which compression algorithm to use. The algorithm-negotiation process attempts to use zlib, then zstd, then uncompressed. If the two sides can find no common algorithm, the connection attempt fails.

Because both sides must agree on the compression algorithm, and because uncompressed is an algorithm value that need not be supported, fallback to an uncompressed connection does not necessarily occur. For example, if the server is configured to permit zstd and a client is configured to permit zlib,uncompressed, the client cannot connect at all. In this case, no algorithm is common to both sides, so connection attempts fail.

Configuration parameters that enable specifying the zstd compression level take an integer value from 1 to 22, with larger values indicating increasing levels of compression. The default zstd compression level is 3. The compression level setting has no effect on connections that do not use zstd compression.

A configurable zstd compression level enables choosing between less network traffic and higher CPU load versus more network traffic and lower CPU load. Higher compression levels reduce network congestion but the additional CPU load may reduce server performance.

Contents Haut

Legacy Connection Compression Configuration

Prior to MySQL 8.0.18, these configuration parameters are available for controlling connection compression:

Client programs support a --compress command-line option to specify use of compression for the connection to the server.
MySQL Shell supports compression for connections that use classic MySQL protocol. It provides a --compress command-line option, a compression parameter for URI-like string and key-value pair connection specifications, and a defaultCompress configuration option. (These are available as of MySQL Shell 8.0.14.)
For programs that use the MySQL C API, enabling the MYSQL_OPT_COMPRESS option for the mysql_options() function specifies use of compression for the connection to the server.
For master/server replication, enabling the slave_compressed_protocol system variable specifies use of compression for slave connections to the master.

In each case, when use of compression is specified, the connection uses the zlib compression algorithm if both sides support it, with fallback to an uncompressed connection otherwise.

As of MySQL 8.0.18, the compression parameters just described become legacy parameters, due to the additional compression parameters introduced for more control over connection compression that are described in Connection Compression Configuration. The legacy compression parameters interact with the newer parameters and their semantics change as follows:

The meaning of the legacy --compress option depends on whether --compression-algorithms is specified:
- When --compression-algorithms is not specified, --compress is equivalent to specifying a client-side algorithm set of zlib,uncompressed.
- When --compression-algorithms is specified, --compress is equivalent to specifying an algorithm set of zlib and the full client-side algorithm set is the union of zlib plus the algorithms specified by --compression-algorithms. For example, with both --compress and --compression-algorithms=zlib,zstd, the permitted-algorithm set is zlib plus zlib,zstd; that is, zlib,zstd. With both --compress and --compression-algorithms=zstd,uncompressed, the permitted-algorithm set is zlib plus zstd,uncompressed; that is, zlib,zstd,uncompressed.
The same type of interaction occurs between the legacy MYSQL_OPT_COMPRESS option and the MYSQL_OPT_COMPRESSION_ALGORITHMS option for the mysql_options() C API function.
If the slave_compressed_protocol system variable is enabled, it takes precedence over MASTER_COMPRESSION_ALGORITHMS and connections to the master use zlib compression if both master and slave support that algorithm. If slave_compressed_protocol is disabled, the value of MASTER_COMPRESSION_ALGORITHMS applies.

Note

The legacy compression-control parameters are deprecated as of MySQL 8.0.18 and will be removed in a future MySQL version.

Contents Haut

Connection Compression Monitoring

The Compression status variable is ON or OFF to indicate whether the current connection uses compression.

The mysql client \status command displays a line that says Protocol: Compressed if compression is enabled for the current connection. If that line is not present, the connection is uncompressed.

As of 8.0.14, the MySQL Shell \status command displays a Compression: line that says Disabled or Enabled to indicate whether the connection is compressed.

As of MySQL 8.0.18, these additional sources of information are available for monitoring connection compression:

To monitor compression in use for client connections, use the Compression_algorithm and Compression_level status variables. For the current connection, their values indicate the compression algorithm and compression level, respectively.
To determine which compression algorithms the server is configured to permit for incoming connections, check the protocol_compression_algorithms system variable.
For master/slave replication connections, the configured compression algorithms and compression level are available from multiple sources:
- The Performance Schema replication_connection_configuration table has COMPRESSION_ALGORITHMS and ZSTD_COMPRESSION_LEVEL columns.
- The mysql.slave_master_info system table has Master_compression_algorithms and Master_zstd_compression_level columns. If the master.info file exists, it contains lines for those values as well.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-connection-compression-control.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut