Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 48.2Mb
PDF (A4) - 48.3Mb
PDF (RPM) - 43.6Mb
HTML Download (TGZ) - 11.0Mb
HTML Download (Zip) - 11.1Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 238.5Kb
Man Pages (Zip) - 343.0Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
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. By default, connections are uncompressed, but they can be compressed if the server and the client agree on a compression algorithm to use. Enabling compression reduces the number of bytes sent over the network, but has 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.

By default, X Protocol announces support for the Deflate, LZ4, and zstd compression algorithms. Compression with the Deflate algorithm is carried out using the zlib software library, so X Protocol's deflate_stream compression algorithm setting is equivalent to the zlib setting for MySQL Server. 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
deflate_stream High Low Highest
lz4_message Low High Lowest
zstd_stream High High Medium

Note that X Protocol's list of permitted compression algorithms (whether user-specified or default) operates independently of the list of compression algorithms announced by MySQL Server, which is specified by the protocol_compression_algorithms server system variable. X Protocol does not fall back to using MySQL Server's compression settings if you do not specify the mysqlx_compression_algorithms system variable, instead using 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 Secure Connections with X Plugin”. For information on how connection compression works for MySQL Server, see Section 4.2.6, “Connection Compression Control”.

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 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 Protocol 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.

To prevent excessive resource consumption on the server, X Protocol 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. 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.

The default compression levels used by X Protocol 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.

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:

  • X Protocol message headers are not compressed, only message payloads.

  • 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 have their payloads 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 Protocol returns a decompression error and closes the connection.

  • If a client requests compression but there are no permitted compression algorithms in common between the server and the client, the connection closes with an error.

  • 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.