22.5.5 Connection Compression with X Plugin (original) (raw)
22.5.5 Connection Compression with X Plugin
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.
Configuring Connection Compression for X Plugin
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 thedeflate_stream compression algorithm setting for X Protocol connections is equivalent to thezlib setting for classic MySQL protocol connections.
On the server side, you can disallow any of the compression algorithms by setting themysqlx_compression_algorithms system variable to include only those permitted. The algorithm names zstd_stream,lz4_message, anddeflate_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 22.1 X Protocol Compression Algorithm Characteristics
| Algorithm | Alias | Compression Ratio | Throughput | CPU Cost | Default Priority |
|---|---|---|---|---|---|
| zsth_stream | zstd | High | High | Medium | First |
| lz4_message | lz4 | Low | High | Lowest | Second |
| deflate_stream | deflate | High | Low | Highest | Third |
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 theprotocol_compression_algorithms server system variable. If you do not specify themysqlx_compression_algorithms 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 inTable 22.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 inSection 22.5.3, “Using Encrypted Connections with X Plugin”. For information about compression for classic MySQL protocol connections, see Section 6.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).
Note
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 forpreferredmode, 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 themysqlx_zstd_default_compression_level,mysqlx_lz4_default_compression_level, andmysqlx_deflate_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 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 themysqlx_zstd_max_client_compression_level,mysqlx_lz4_max_client_compression_level, andmysqlx_deflate_max_client_compression_level system variables.
If the server and client permit more than one algorithm in common, the default priority order for choosing an algorithm during negotiation is shown inTable 22.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 inMonitoring 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.
Compressed Connection Characteristics for X Plugin
X Protocol connection compression operates with the following behaviors and boundaries:
- The
_streamand_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
Mysqlx.Ok,Mysqlx.Error, andMysqlx.Sql.StmtExecuteOkmessages. - 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 themysqlx_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 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.
Monitoring Connection Compression for X Plugin
You can monitor the effects of message compression using the X Plugin status variables. When message compression is in use, the sessionMysqlx_compression_algorithm status variable shows which compression algorithm is in use for the current X Protocol connection, andMysqlx_compression_level shows 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 inConfiguring Connection Compression for X Plugin.
When message compression is in use, theMysqlx_bytes_sent status 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 messages. TheMysqlx_bytes_sent_compressed_payload status variable shows the total number of bytes sent as compressed message payloads, measured after compression, and theMysqlx_bytes_sent_uncompressed_frame 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 expression:
mysqlx_bytes_sent_uncompressed_frame / mysqlx_bytes_sent_compressed_payloadThe 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_sentFor messages received by the server from clients, theMysqlx_bytes_received_compressed_payload status variable shows the total number of bytes received as compressed message payloads, measured before decompression, and theMysqlx_bytes_received_uncompressed_frame status variable shows the total number of bytes for those same message payloads but measured after decompression. TheMysqlx_bytes_received status variable includes compressed message payloads measured before decompression, any uncompressed items in compressed messages, and any uncompressed messages.