Connections to the server can use compression on the traffic between client and server to reduce 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.
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.
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. Compression control does not apply to
FEDERATED tables. 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.
X Protocol connections to a MySQL Server instance support compression from MySQL 8.0.19, but connection compression for X Protocol operates independently from the connection compression described in this section, and is controlled separately. See Section 20.5.5, “Connection Compression with X Plugin” for information on X Protocol connection compression.
As of MySQL 8.0.18, these configuration parameters are available for controlling connection compression:
protocol_compression_algorithmssystem variable configures which compression algorithms the server permits for incoming connections.
--zstd-compression-levelcommand-line options configure permitted compression algorithms and
zstdcompression level for these client programs: mysql, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlpump, mysqlshow, mysqlslap, and mysqltest, and mysql_upgrade. MySQL Shell also offers these command-line options from its 8.0.20 release.
MYSQL_OPT_ZSTD_COMPRESSION_LEVELoptions for the
mysql_options()function configure permitted compression algorithms and
zstdcompression level for client programs that use the MySQL C API.
MASTER_ZSTD_COMPRESSION_LEVELoptions for the
CHANGE MASTER TOstatement configure permitted compression algorithms and
zstdcompression level for slave servers participating in master/slave replication.
group_replication_recovery_zstd_compression_levelsystem variables configure permitted compression algorithms and
zstdcompression 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
zstd: Permit connections that use the
zstdcompression algorithm (zstd 1.3).
uncompressed: Permit uncompressed connections.
uncompressed is an algorithm name
that may or may not be configured, it is possible to configure
MySQL not to support uncompressed
To configure which compression algorithms the server permits for incoming connections, set the
protocol_compression_algorithmssystem variable. By default, the server permits all available algorithms. To configure that setting explicitly at startup, use these lines in the server
To set and persist the
protocol_compression_algorithmssystem variable to that value at runtime, use this statement:
SET PERSIST protocol_compression_algorithms='zlib,zstd,uncompressed';
SET PERSISTsets the value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the
GLOBALkeyword rather than
PERSIST. See Section 220.127.116.11, “SET Syntax for Variable Assignment”.
To permit only incoming connections that use
zstdcompression, configure the server at startup like this:
Or, to make the change at runtime:
SET PERSIST protocol_compression_algorithms='zstd';
To permit the mysql client to initiate
uncompressedconnections, invoke it like this:
To configure replication slaves to connect to the master using
zstdconnections, with a compression level of 7 for
zstdconnections, use a
CHANGE MASTER TOstatement:
CHANGE MASTER TO MASTER_COMPRESSION_ALGORITHMS = 'zlib,zstd', MASTER_ZSTD_COMPRESSION_LEVEL = 7;
For successful connection setup, both sides of the connection
must agree on which compression algorithm to use. The
algorithm-negotiation process attempts to use
uncompressed. If the two sides can find no
common algorithm, the connection attempt fails.
Because both sides must agree on the compression algorithm, and
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
level is 3. The compression level setting has no effect on
connections that do not use
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.
Prior to MySQL 8.0.18, these configuration parameters are available for controlling connection compression:
Client programs support a
--compresscommand-line option to specify use of compression for the connection to the server.
For programs that use the MySQL C API, enabling the
MYSQL_OPT_COMPRESSoption for the
mysql_options()function specifies use of compression for the connection to the server.
For master/server replication, enabling the
slave_compressed_protocolsystem variable specifies use of compression for slave connections to the master.
In each case, when use of compression is specified, the
connection uses the
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. An
exception is MySQL Shell, where the
--compress command-line option
remains current, and can be used to request compression without
selecting compression algorithms. For information on
MySQL Shell's connection compression control, see
Using Compressed Connections.
The legacy compression parameters interact with the newer parameters and their semantics change as follows:
--compressis equivalent to specifying an algorithm set of
zliband the full client-side algorithm set is the union of
zlibplus the algorithms specified by
--compression-algorithms. For example, with both
--compression-algorithms=zlib,zstd, the permitted-algorithm set is
zlib,zstd; that is,
zlib,zstd. With both
--compression-algorithms=zstd,uncompressed, the permitted-algorithm set is
zstd,uncompressed; that is,
The same type of interaction occurs between the legacy
MYSQL_OPT_COMPRESSoption and the
MYSQL_OPT_COMPRESSION_ALGORITHMSoption for the
mysql_options()C API function.
slave_compressed_protocolsystem variable is enabled, it takes precedence over
MASTER_COMPRESSION_ALGORITHMSand connections to the master use
zlibcompression if both master and slave support that algorithm. If
slave_compressed_protocolis disabled, the value of
The legacy compression-control parameters are deprecated as of MySQL 8.0.18 and will be removed in a future MySQL version.
indicate whether the current connection uses compression.
The mysql client
command displays a line that says
Compressed if compression is enabled for the current
connection. If that line is not present, the connection is
As of 8.0.14, the MySQL Shell
command displays a
Compression: line that
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_levelstatus 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
For master/slave replication connections, the configured compression algorithms and compression level are available from multiple sources:
The Performance Schema
mysql.slave_master_infosystem table has
Master_zstd_compression_levelcolumns. If the
master.infofile exists, it contains lines for those values as well.