Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 47.5Mb
PDF (A4) - 47.6Mb
PDF (RPM) - 43.0Mb
HTML Download (TGZ) - 10.9Mb
HTML Download (Zip) - 11.0Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 231.9Kb
Man Pages (Zip) - 337.3Kb
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. You can disallow any of these 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

X Protocol uses the library default compression level for each algorithm (6 for Deflate, 0 for LZ4 and 3 for zstd). 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.

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”. Also note that X Protocol cannot be configured to disallow uncompressed connections (as MySQL Server can), and uncompressed connections are always allowed. For information on how connection compression works for MySQL Server, see Section 4.2.6, “Connection Compression Control”.

X Protocol message headers are not compressed, only message payloads. Compression is not applied to any messages that are sent before authentication occurs, or 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. If the client does request compression but there are no permitted compression algorithms in common between the server and the client, the connection closes with an error.

When compression is applied to messages sent over X Protocol connections, 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 has been decompressed. If the limit is exceeded, X Protocol returns a decompression error and closes the connection.

You can monitor the effects of message compression using the X Plugin status variables. When message compression is in use, the status variable Mysqlx_bytes_sent 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. The status variable Mysqlx_bytes_sent_compressed_payload shows the total number of bytes sent as compressed message payloads, measured after compression, and the status variable Mysqlx_bytes_sent_uncompressed_frame 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 formula:

mysqlx_bytes_sent_uncompressed_frame / mysqlx_bytes_sent_compressed_payload

The efficiency of compression for X Protocol messages sent by the server can be calculated using the following formula:

(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 Mysqlx_bytes_received_compressed_payload shows the total number of bytes received as compressed message payloads, measured before decompression, and the status variable Mysqlx_bytes_received_uncompressed_frame shows the total number of bytes for those same message payloads but measured after decompression. The status variable Mysqlx_bytes_received includes compressed message payloads measured before decompression, any uncompressed items in compressed messages, and any uncompressed messages.