Support ZSTD compression

[dveeden]

The MySQL protocol support zlib (CLIENT_COMPRESS) and zstd (CLIENT_ZSTD_COMPRESSION_ALGORITHM) compression. Zstandard allows for better compression ratio and performance.

However it looks like the MariaDB libs don't support this yet: https://jira.mariadb.org/browse/MDEV-22310

Compression is useful to reduce cross-AZ data tranfer cost in Cloud deployments.

[renecannao]

Hi Daniel.

I confirm that ProxySQL doesn't support ZSTD for either frontend or backend connections.
I don't know yet how important is to support this feature, neither if it is commonly used.
Do you have any stats you can share about performance and benefits?

Thanks

[dveeden]

Compression could be useful to reduce data transfer costs in the Cloud. Many client libraries support compression. Many client libraries don't support zstd yet. The Go sql driver unfortunately doesn't support compression yet. Having ProxySQL with uncompressed frontend and compressed backand could help to reduce cost in the cloud. Other features like query caching could also help with this.

Some data on compression:

With mysql --compression-algorithms=zstd --zstd-compression-level 9 ...:

mysql> SHOW STATUS LIKE 'Compression%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Compression           | ON    |
| Compression_algorithm | zstd  |
| Compression_level     | 9     |
+-----------------------+-------+
3 rows in set (0.01 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.01 sec)

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'
mysql> TABLE city;
4079 rows in set (0.01 sec)

mysql> pager
Default pager wasn't set, using stdout.
mysql> SHOW STATUS LIKE 'Bytes%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Bytes_received | 63    |
| Bytes_sent     | 82066 |
+----------------+-------+
2 rows in set (0.01 sec)

With mysql ...:

mysql> SHOW STATUS LIKE 'Compression%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Compression           | OFF   |
| Compression_algorithm |       |
| Compression_level     | 0     |
+-----------------------+-------+
3 rows in set (0.00 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.01 sec)

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'
mysql> TABLE city;
4079 rows in set (0.00 sec)

mysql> pager
Default pager wasn't set, using stdout.
mysql> SHOW STATUS LIKE 'Bytes%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| Bytes_received | 49     |
| Bytes_sent     | 161185 |
+----------------+--------+
2 rows in set (0.00 sec)

With mysql --compression-algorithms=zlib ...:

mysql> SHOW STATUS LIKE 'Compression%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Compression           | ON    |
| Compression_algorithm | zlib  |
| Compression_level     | 6     |
+-----------------------+-------+
3 rows in set (0.00 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.01 sec)

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'
mysql> TABLE city;
4079 rows in set (0.01 sec)

mysql> pager
Default pager wasn't set, using stdout.
mysql> SHOW STATUS LIKE 'Bytes%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Bytes_received | 63    |
| Bytes_sent     | 80410 |
+----------------+-------+
2 rows in set (0.01 sec)

zstd:

mysql> SELECT 100*(82066/161185);
+--------------------+
| 100*(82066/161185) |
+--------------------+
|            50.9142 |
+--------------------+
1 row in set (0.00 sec)

zlib:

mysql> SELECT 100*(80410/161185);
+--------------------+
| 100*(80410/161185) |
+--------------------+
|            49.8868 |
+--------------------+
1 row in set (0.00 sec)

[dveeden]

This could be useful for performance testing: akopytov/sysbench#487

[renecannao]

If I am reading the above correctly, zlib compresses better than zstd .
But in this case it is just one simple query: application workloads have a lot of different query patterns, with some resultset compressing better while other no.
And probably not even a sysbench workload can tell us a lot: I think the benefits of zlib or zstd depend from the specific application workload.

Having ProxySQL with uncompressed frontend and compressed backand could help to reduce cost in the cloud.

This is already possible!
You can use Go sql driver to connect to ProxySQL without compression , and ProxySQL can use compression (currently only zlib) for backend traffic.
I think this is a low hanging fruit to take advantage of, even if zstd is not supported.

A minor note: mysql (both the client and server) only compresses data larger than 50 bytes .
This applies to any data, whatever is an OK or ERR packet, a small query : they are not compressed.

[renecannao]

Oh, I see the PR on akopytov/sysbench#487 , that was fast! 😄