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.
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
algorithm setting is equivalent to the
setting for classic MySQL protocol connections. You can disallow any of
the compression algorithms by setting the
system variable to include only the ones you permit. The algorithm
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|
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
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
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
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.
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
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
You can monitor the effects of message compression using the X Plugin status variables described in Section 188.8.131.52, “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
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_packetsystem 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.