From MySQL 8.0.19, X Plugin supports compression of messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a mutually supported compression algorithm. Enabling compression reduces the number of bytes sent over the network, but adds to the server and client an additional CPU cost for 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 support for connection compression differently; consult your client documentation for details. For example, for classic MySQL protocol connections, see Section 4.2.8, “Connection Compression Control”.
By default, X Plugin supports the zstd, LZ4, and Deflate
compression algorithms. Compression with the Deflate algorithm
is carried out using the zlib software library, so the
deflate_stream compression algorithm setting
for X Protocol connections is equivalent to the
zlib setting for classic MySQL protocol
On the server side, you can disallow any of the compression
algorithms by setting the
system variable to include only those permittted. The algorithm
deflate_stream can be specified in any
combination, and the order and lettercase are not important. If
the system variable value is the empty string, no compression
algorithms are permitted and connections are uncompressed.
The following table compares the characteristics of the different compression algorithms and shows their assigned priorities. By default, the server chooses the highest-priority algorithm permitted in common by the server and the client; clients may change the priorities as described later. The short form alias for the algorithms can be used by clients when specifying them.
Table 20.1 X Protocol Compression Algorithm Characteristics
|Algorithm||Alias||Compression Ratio||Throughput||CPU Cost||Default Priority|
The X Protocol set of permitted compression algorithms (whether
user-specified or default) is independent of the set of
compression algorithms permitted by MySQL Server for
classic MySQL protocol connections, which is specified by the
server system variable. If you do not specify the
system variable, X Plugin does not fall back to using
compression settings for classic MySQL protocol connections. Instead,
its default is to permit all algorithms shown in
Table 20.1, “X Protocol Compression Algorithm Characteristics”.
This is unlike the situation for the TLS context, where MySQL
Server 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 about compression for classic MySQL protocol connections,
see Section 4.2.8, “Connection Compression Control”.
On the client side, an X Protocol connection request can specify several parameters for compression control:
The compression mode.
The compression level (from MySQL 8.0.20).
The list of permitted compression algorithms in priority order (from MySQL 8.0.22).
Some clients or Connectors might not support a given compression-control feature. For example, specifying compression level for X Protocol connections is supported only by MySQL Shell, not by other MySQL clients or Connectors. See the documentation for specific products for details about supported features and how to use them.
The connection mode has these permitted values:
disabled: The connection is uncompressed.
preferred: The server and client negotiate to find a compression algorithm they both permit. If no common algorithm is available, the connection is uncompressed. This is the default mode if not specified explicitly.
required: Compression algorithm negotiation occurs as for
preferredmode, but if no common algorithm is available, the connection request terminates with an error.
In addition to agreeing on a compression algorithm for each connection, 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 (3 for zstd, 0 for LZ4, and 6 for Deflate), 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.
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 apply if the client does not
request a compression level for the algorithm. The default
compression levels are initially set to 3 for zstd, 2 for LZ4,
and 3 for Deflate. 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 requests by a
client are supported only by MySQL Shell). The maximum
compression levels are initially set to 11 for zstd, 8 for LZ4,
and 5 for Deflate. You can adjust these settings using the
If the server and client permit more than one algorithm in common, the default priority order for choosing an algorithm during negotiation is shown in Table 20.1, “X Protocol Compression Algorithm Characteristics”. From MySQL 8.0.22, for clients that support specifying compression algorithms, the connection request can include a list of algorithms permitted by the client, specified using the algorithm name or its alias. The order of these algorithms in the list is taken as a priority order by the server. The algorithm used in this case is the first of those in the client list that is also permitted on the server side. However, the option for compression algorithms is subject to the compression mode:
If the compression mode is
disabled, the compression algorithms option is ignored.
If the compression mode is
preferredbut no algorithm permitted on the client side is permitted on the server side, the connection is uncompressed.
If the compression mode is
requiredbut no algorithm permitted on the client side is permitted on the server side, an error occurs.
To monitor the effects of message compression, use the X Plugin status variables described in Monitoring Connection Compression for 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 connection compression operates with the following behaviors and boundaries:
_messagesuffixes in algorithm names refer to two different operational modes: In stream mode, all X Protocol messages in a single connection are compressed into a continuous stream and must be decompressed in the same manner—following the order they were compressed and without skipping any messages. In message mode, each message is compressed individually and independently, and need not be decompressed in the order in which they were compressed. Also, message mode does not require all compressed messages to be decompressed.
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 a mutually permitted compression 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 pertain to compression level requests by clients, which is supported only 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 an algorithm compression level that exceeds the server maximum permitted level, the server uses the maximum permitted level.
If a client requests an algorithm compression level that is less than the server minimum permitted level, the server uses the minimum permitted level.
You can monitor the effects of message compression using the
X Plugin status variables. When message compression is in use,
status variable shows which compression algorithm is in use for
the current X Protocol connection, and
the compression level that was selected. These session status
variables are available from MySQL 8.0.20.
From MySQL 8.0.19, X Plugin status variables can be used to calculate the efficiency of the compression algorithms that are selected (the data compression ratio), and the overall effect of using message compression. Use the session value of the status variables in the following calculations to see what the benefit of message compression was for a specific session with a known compression algorithm. Or use the global value of the status variables to check the overall benefit of message compression for your server across all sessions using X Protocol connections, including all the compression algorithms that have been used for those sessions, and all sessions that did not use message compression. You can then tune message compression by adjusting the permitted compression algorithms, maximum compression level, and default compression level, as described in Configuring Connection Compression for X Plugin.
When message compression is in use, the
variable shows the total number of bytes sent out from the
server, including compressed message payloads measured after
compression, any items in compressed messages that were not
compressed such as X Protocol headers, and any uncompressed
status variable shows the total number of bytes sent as
compressed message payloads, measured after compression, and the
status variable shows the total number of bytes for those same
message payloads but measured before compression. The
compression ratio, which shows the efficiency of the compression
algorithm, can therefore be calculated using the following
mysqlx_bytes_sent_uncompressed_frame / mysqlx_bytes_sent_compressed_payload
The effectiveness of compression for X Protocol messages sent by the server can be calculated using the following expression:
(mysqlx_bytes_sent - mysqlx_bytes_sent_compressed_payload + mysqlx_bytes_sent_uncompressed_frame) / mysqlx_bytes_sent
For messages received by the server from clients, the
status variable shows the total number of bytes received as
compressed message payloads, measured before decompression, and
status variable shows the total number of bytes for those same
message payloads but measured after decompression. The
variable includes compressed message payloads measured before
decompression, any uncompressed items in compressed messages,
and any uncompressed messages.