Documentation Home
MySQL 8.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 262.0Kb
Man Pages (Zip) - 367.6Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 8.4 Reference Manual  / ...  / MySQL Programs  / Using MySQL Programs  /  Connection Compression Control

6.2.8 Connection Compression Control

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 a mutually permitted compression algorithm.

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. It applies to classic MySQL protocol connections.

Compression control applies to connections to the server by client programs and by servers participating in source/replica replication or Group Replication. Compression control does not apply to connections forFEDERATED 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.

Note

X Protocol connections to a MySQL Server instance support compression, but compression for X Protocol connections operates independently from the compression for classic MySQL protocol connections described here, and is controlled separately. SeeSection 22.5.5, “Connection Compression with X Plugin” for information on X Protocol connection compression.

Configuring Connection Compression

These configuration parameters are available for controlling connection compression:

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 thezlib compression algorithm.

  • zstd: Permit connections that use thezstd compression algorithm.

  • uncompressed: Permit uncompressed connections.

Note

Becauseuncompressed is an algorithm name that may or may not be configured, it is possible to configure MySQLnot to permit uncompressed connections.

Examples:

  • To configure which compression algorithms the server permits for incoming connections, set theprotocol_compression_algorithms system variable. By default, the server permits all available algorithms. To configure that setting explicitly at startup, use these lines in the servermy.cnf file:

    [mysqld]protocol_compression_algorithms=zlib,zstd,uncompressed

    To set and persist theprotocol_compression_algorithms system variable to that value at runtime, use this statement:

    SET PERSIST protocol_compression_algorithms='zlib,zstd,uncompressed';

    SET PERSIST sets a 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 theGLOBAL keyword rather thanPERSIST. SeeSection 15.7.6.1, “SET Syntax for Variable Assignment”.

  • To permit only incoming connections that usezstd compression, configure the server at startup like this:

    [mysqld]protocol_compression_algorithms=zstd

    Or, to make the change at runtime:

    SET PERSIST protocol_compression_algorithms='zstd';

  • To permit themysql client to initiatezlib oruncompressed connections, invoke it like this:

    mysql --compression-algorithms=zlib,uncompressed

  • To configure replicas to connect to the source usingzlib orzstd connections, with a compression level of 7 forzstd connections, use aCHANGE REPLICATION SOURCE TO statement:

    CHANGE REPLICATION SOURCE TO  SOURCE_COMPRESSION_ALGORITHMS = 'zlib,zstd',  SOURCE_ZSTD_COMPRESSION_LEVEL = 7;

    This assumes that thereplica_compressed_protocol system variable is disabled, for reasons described inConfiguring Legacy Connection Compression.

For successful connection setup, both sides of the connection must agree on a mutually permitted compression algorithm. The algorithm-negotiation process attempts to usezlib, thenzstd, thenuncompressed. If the two sides can find no common algorithm, the connection attempt fails.

Because both sides must agree on the compression algorithm, and becauseuncompressed is an algorithm value that is not necessarily permitted, fallback to an uncompressed connection does not necessarily occur. For example, if the server is configured to permitzstd and a client is configured to permitzlib,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 thezstd compression level take an integer value from 1 to 22, with larger values indicating increasing levels of compression. The defaultzstd compression level is 3. The compression level setting has no effect on connections that do not usezstd compression.

A configurablezstd 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.

Configuring Legacy Connection Compression

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.

  • For programs that use the MySQL C API, enabling theMYSQL_OPT_COMPRESS option for themysql_options() function specifies use of compression for the connection to the server.

  • For source/replica replication, enabling the system variablereplica_compressed_protocol specifies use of compression for replica connections to the source.

In each case, when use of compression is specified, the connection uses thezlib compression algorithm if both sides permit 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 inConfiguring Connection Compression. 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, seeUsing Compressed Connections.

The legacy compression parameters interact with the newer parameters and their semantics change as follows:

Monitoring Connection Compression

TheCompression status variable isON orOFF to indicate whether the current connection uses compression.

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

The MySQL Shell\status command displays aCompression: line that saysDisabled orEnabled to indicate whether the connection is compressed.

These additional sources of information are available for monitoring connection compression:

  • To monitor compression in use for client connections, use theCompression_algorithm andCompression_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 theprotocol_compression_algorithms system variable.

  • For source/replica replication connections, the configured compression algorithms and compression level are available from multiple sources:

    • The Performance Schemareplication_connection_configuration table hasCOMPRESSION_ALGORITHMS andZSTD_COMPRESSION_LEVEL columns.

    • Themysql.slave_master_info system table hasMaster_compression_algorithms andMaster_zstd_compression_level columns. If themaster.info file exists, it contains lines for those values as well.