Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 48.2Mb
PDF (A4) - 48.3Mb
PDF (RPM) - 43.9Mb
HTML Download (TGZ) - 11.0Mb
HTML Download (Zip) - 11.1Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 239.7Kb
Man Pages (Zip) - 343.4Kb
Info (Gzip) - 4.4Mb
Info (Zip) - 4.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Connection Compression with X Plugin

20.5.5 Connection Compression with X Plugin

From MySQL 8.0.19, compression is supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client can agree on a compression algorithm to use. Enabling compression reduces the number of bytes sent over the network, but adds an additional CPU cost to the server and client due to performing compression and decompression operations. The benefits of compression therefore occur primarily when there is low network bandwidth, network transfer time dominates the cost of compression and decompression operations, and result sets are large.

Note

Different MySQL clients implement their support for connection compression differently; consult your client's documentation for details.

By default, X Plugin supports the Deflate, LZ4, and zstd compression algorithms. Compression with the Deflate algorithm is carried out using the zlib software library, so X Protocol connections' deflate_stream compression algorithm setting is equivalent to the zlib setting for classic MySQL protocol connections. You can disallow any of the compression algorithms by setting the mysqlx_compression_algorithms system variable to include only the ones you permit. The algorithm names deflate_stream, lz4_message, and zstd_stream can be specified in any combination, and the order and case are not important. If you set the system variable to the empty string, no compression algorithms are permitted and only uncompressed connections are used.

The compression algorithms that you can permit or disallow for X Protocol compare as follows:

Table 20.1 Comparison of X Protocol Compression Algorithms

Algorithm Compression Ratio Throughput CPU Cost Priority
zstd_stream High High Medium First
lz4_message Low High Lowest Second
deflate_stream High Low Highest Third

_stream and _message refer to two different operation modes: In the stream mode, all X Protocol messages in a single connection are compressed into a continuous stream and their decompression must be performed in the same manner—following the order they were compressed and without skipping any messages. In the message mode, each message is compressed individually and independently, so that the order by which the messages are decompressed needs not be the same as the order they were compressed. Also, the message mode does not require all compressed messages to be decompressed.

Notice that X Protocol connections' list of permitted compression algorithms (whether user-specified or default) operates independently of the list of compression algorithms supported by MySQL Server for classic MySQL protocol connections, which is specified by the protocol_compression_algorithms server system variable. X Plugin does not fall back to using MySQL Server's compression settings for classic MySQL protocol connections if you do not specify the mysqlx_compression_algorithms system variable, using instead its own default of allowing all the supported algorithms. This is not like the situation for the SSL system variables, where MySQL Server's settings are used if the X Plugin system variables are not set, as described in Section 20.5.3, “Using Encrypted Connections with X Plugin”. For information on how connection compression works for MySQL Server, see Section 4.2.8, “Connection Compression Control”.

A client can specify connection compression to be disabled, preferred, or required:

  • If compression is disabled, the connection is uncompressed.

  • If compression is preferred, the client negotiates with the server to find a compression algorithm supported by both of them. If no common algorithm is available (or the server version does not support connection compression), the connection is uncompressed.

  • If compression is required, compression algorithm negotiation occurs as with compression preferred. If no common algorithm is available, the connection terminates with an error.

If more than one algorithm is supported by both the server and client, there is a set priority for picking an algorithm during their negotiation, which is shown in Table 20.1, “Comparison of X Protocol Compression Algorithms”. As well as agreeing on a compression algorithm for each session, the server and client can agree on a compression level from the numeric range that applies to the agreed algorithm. As the compression level for an algorithm increases, the data compression ratio increases, which reduces the network bandwidth and transfer time needed to send the message to the client. However, the effort required for data compression also increases, taking up time and CPU and memory resources on the server. Increases in the compression effort do not have a linear relationship to increases in the compression ratio.

In MySQL 8.0.19, X Plugin always uses the library default compression level for each algorithm (6 for Deflate, 0 for LZ4, and 3 for zstd), and the client cannot negotiate this. From MySQL 8.0.20, the client can request a specific compression level during capability negotiations with the server for an X Protocol connection.

Note

Users can request a specific compression level for X-Protocol connections only with MySQL Shell; the feature is not supported by other MySQL clients or Connectors.

The default compression levels used by X Plugin from MySQL 8.0.20 have been selected through performance testing as being a good trade-off between compression time and network transit time. These defaults are not necessarily the same as the library default for each algorithm. They are applied if the client does not request a compression level for the algorithm. The default compression levels are initially set to 3 for Deflate, 2 for LZ4, and 3 for zstd. You can adjust these settings using the mysqlx_deflate_default_compression_level, mysqlx_lz4_default_compression_level, and mysqlx_zstd_default_compression_level system variables.

To prevent excessive resource consumption on the server, X Plugin sets a maximum compression level that the server permits for each algorithm. If a client requests a compression level that exceeds this setting, the server uses its maximum permitted compression level (compression level request by a client is only supported by MySQL Shell). The maximum compression levels are initially set to 5 for Deflate, 8 for LZ4, and 11 for zstd. You can adjust these settings using the mysqlx_deflate_max_client_compression_level, mysqlx_lz4_max_client_compression_level, and mysqlx_zstd_max_client_compression_level system variables.

You can monitor the effects of message compression using the X Plugin status variables described in Section 20.5.5.1, “Monitoring Connection Compression with X Plugin”. You can use these status variables to calculate the benefit of message compression with your current settings, and use that information to tune your settings.

X Protocol's connection compression operates with the following behaviors and boundaries:

  • Compression is not applied to any messages that are sent before authentication succeeds.

  • Compression is not applied to control flow messages such as Mysqlx.Ok, Mysqlx.Error, and Mysqlx.Sql.StmtExecuteOk messages.

  • All other X Protocol messages can be compressed if the server and client agree on the use of compression with a mutually supported algorithm during capability negotiation. If the client does not request compression at that stage, neither the client nor the server applies compression to messages.

  • When messages sent over X Protocol connections are compressed, the limit specified by the mysqlx_max_allowed_packet system variable still applies. The network packet must be smaller than this limit after the message payload has been decompressed. If the limit is exceeded, X Plugin returns a decompression error and closes the connection.

  • The following points are about compression level request by a client, which is only supported by MySQL Shell:

    • Compression levels must be specified by the client as an integer. If any other type of value is supplied, the connection closes with an error.

    • If a client specifies an algorithm but not a compression level, the server uses its default compression level for the algorithm.

    • If a client requests a compression level that exceeds the server's setting for the maximum permitted compression level for that algorithm, the server uses its maximum permitted compression level for the algorithm.

    • If a client requests a compression level that is less than the minimum compression level provided by that algorithm, the server uses the minimum compression level for the algorithm.

    • If a client requests a compression level that is not supported by the agreed algorithm at all, the server uses the nearest value that is supported and also permitted for the algorithm.