在一个繁忙的MySQL服务器上，二进制日志最终可能会成为使用磁盘空间的最大占用者之一。这意味着更高的I/O，更大的备份集（您正在备份二进制日志，对吗？），当向从库传输二进制日志时，网络流量会增加，等等。所以长期以来一直希望有一个二进制日志压缩功能，允许对MySQL正在使用的二进制日志进行压缩。从MySQL 8.0.20开始，现在可以了。这篇文章将探讨这个新功能。
01—
配置
二进制日志压缩功能由两个变量控制，一个用于启用该功能，另一个用于指定压缩级别。
| 变量名称 | 默认值 | 允许值 |
| binlog_transaction_compression | OFF | OFF/ON |
| binlog_transaction_compression_level_zstd | 3 | 1-22 |
这两个变量的作用可以从它们的名称推断出来：
- binlog_transaction_compression指定是否启用压缩，
- binlog_transaction_compression_level_zstd指定压缩级别。原则上，高级别的压缩消耗更多的CPU。
这两个选项都可以在全局范围内和会话范围内动态设置。但是，不允许在事务中间更改。如果您这样做，您会收到这样的错误：
mysql> SET SESSION binlog_transaction_compression = ON;ERROR: 1766 (HY000): The system variable binlog_transaction_compression cannot be set when there is an ongoing transaction.
虽然配置二进制日志压缩很容易，但也需要注意一些限制。
02—
限制
关于二进制日志压缩的限制简短地说就是只压缩事务行事件。基于语句的事件、GTID信息、旋转事件、非事务表的行事件等没有压缩。此外，如果您对事务添加非事务性更改，那么两者都不会被压缩。这也意味着每笔交易都是单独压缩的。有关这意味着什么的更多信息，请参阅本文中接下来的例子。
如果您对二进制日志使用所有默认值，并使用InnoDB存储引擎（默认值），则压缩可以起作用。有关限制的完整列表，请参阅官方文档中的二进制日志事务压缩（https://dev.mysql.com/doc/refman/8.0/en/binary-log-transaction-compression.html）。正如我通常所宣扬的，监控是了解您的系统的关键。下面说明如何监控二进制日志压缩功能。
03—
监视
有两种方法可以监控二进制日志压缩功能的性能，一种是性能视图中的压缩统计，另一种是性能视图中的新增的两个阶段事件。性能视图中的binary_log_transaction_compression_stats表包括自上次重新启动MySQL（或上次截断表）以来用于压缩的统计信息。该表有与两个二进制日志相关的行，一行用于压缩事件，一行用于未压缩的事件。从库中同样将有两行用于中继日志。一个查询这个视图和二进制日志的例子是：
mysql> SELECT * FROM binary_log_transaction_compression_stats\G*************************** 1. row *************************** LOG_TYPE: BINARY COMPRESSION_TYPE: ZSTD TRANSACTION_COUNTER: 15321 COMPRESSED_BYTES_COUNTER: 102796461 UNCOMPRESSED_BYTES_COUNTER: 252705572 COMPRESSION_PERCENTAGE: 59 FIRST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:30730 FIRST_TRANSACTION_COMPRESSED_BYTES: 313FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 363 FIRST_TRANSACTION_TIMESTAMP: 2020-05-07 19:26:37.744437 LAST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:46058 LAST_TRANSACTION_COMPRESSED_BYTES: 712 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 1627 LAST_TRANSACTION_TIMESTAMP: 2020-05-07 19:38:14.149782*************************** 2. row *************************** LOG_TYPE: BINARY COMPRESSION_TYPE: NONE TRANSACTION_COUNTER: 20 COMPRESSED_BYTES_COUNTER: 5351 UNCOMPRESSED_BYTES_COUNTER: 5351 COMPRESSION_PERCENTAGE: 0 FIRST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:30718 FIRST_TRANSACTION_COMPRESSED_BYTES: 116FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 116 FIRST_TRANSACTION_TIMESTAMP: 2020-05-07 19:26:37.508155 LAST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:31058 LAST_TRANSACTION_COMPRESSED_BYTES: 116 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 116 LAST_TRANSACTION_TIMESTAMP: 2020-05-07 19:30:30.8407672 rows in set (0.0026 sec) mysql> SHOW BINARY LOGS;+---------------+-----------+-----------+| Log_name | File_size | Encrypted |+---------------+-----------+-----------+| binlog.000142 | 240 | No || binlog.000143 | 4933 | No || binlog.000144 | 28238118 | No || binlog.000145 | 24667167 | No || binlog.000146 | 39221771 | No || binlog.000147 | 11944631 | No || binlog.000148 | 196 | No |+---------------+-----------+-----------+7 rows in set (0.0005 sec)
该示例是在启用GTID的情况下生成的，除了binlog.000142，所有二进制日志都是在上次重新启动后创建的。这表明有15341个事务（74470a0c-8ea4-11ea-966e-080027effed8:30718-46058），其中15321个事务被压缩，平均压缩率是59%（将252,705,572字节压缩为102,796,461字节）。还有第一个和最近一次被压缩的事务的统计数据，也有20个事务无法压缩。另外一件事是要知道是在进行压缩和解压方面付出的代价。这可以使用性能模式中的两个阶段事件进行监控：
- stage/sql/Compressing transaction changes.
- stage/sql/Decompressing transaction changes. （是的，英文句号是名称的一部分。）
默认情况下，两者都不启用，要收集这些性能计量，您还需要启用events_stages_current消费者。例如，要在配置文件中启用这些配置参数：
[mysqld]performance-schema-instrument = "stage/sql/%Compressing transaction changes.=ON"performance-schema-consumer-events-stages-current = ON
需要注意，启用这些阶段计量会产生一些开销。如果您考虑在生产系统上启用这些影响，请确保首先测试影响。您可以将这两个阶段计量信息与wait/io/file/sql/binlog事件（默认启用）进行比较，后者是花在做I/O上的时间。例如：
mysql> SELECT EVENT_NAME, COUNT_STAR, FORMAT_PICO_TIME(SUM_TIMER_WAIT) AS total_latency, FORMAT_PICO_TIME(MIN_TIMER_WAIT) AS min_latency, FORMAT_PICO_TIME(AVG_TIMER_WAIT) AS avg_latency, FORMAT_PICO_TIME(MAX_TIMER_WAIT) AS max_latency FROM performance_schema.events_stages_summary_global_by_event_name WHERE EVENT_NAME LIKE 'stage/sql/%transaction changes.'\G*************************** 1. row *************************** EVENT_NAME: stage/sql/Compressing transaction changes. COUNT_STAR: 15321total_latency: 6.10 s min_latency: 22.00 ns avg_latency: 397.96 us max_latency: 982.12 ms*************************** 2. row *************************** EVENT_NAME: stage/sql/Decompressing transaction changes. COUNT_STAR: 0total_latency: 0 ps min_latency: 0 ps avg_latency: 0 ps max_latency: 0 ps2 rows in set (0.0008 sec) mysql> SELECT * FROM sys.io_global_by_wait_by_latency WHERE event_name = 'sql/binlog'\G*************************** 1. row *************************** event_name: sql/binlog total: 27537total_latency: 4.83 min avg_latency: 10.51 ms max_latency: 723.92 ms read_latency: 138.25 uswrite_latency: 290.69 ms misc_latency: 4.82 min count_read: 37 total_read: 1002 bytes avg_read: 27 bytes count_write: 16489total_written: 99.26 MiB avg_written: 6.16 KiB1 row in set (0.0015 sec)
这里sys视图输出的数据已经自动转换为人类可读的格式，而性能视图中的时间使用FORMAT_PICO_TIME()函数进行转换。在本例中，MySQL总计花了6.21秒进行二进制日志的压缩，每笔事务平均略低于400微秒。相比之下，MySQL总计花了4.8分钟在二进制日志文件上做I/O，这说明压缩在写日志的时间中占比很低。在启用压缩之前，您应该检查写入和读取二进制日志文件所花费的时间，以便确定性能的变化。您还应该检查CPU使用情况的变化。在上面的输出中，它显示压缩率为59%，但对于不同类型的工作负载来说，压缩率如何？
04—
示例 – 工作负载为了测量压缩的效果，我运行了一系列任务，并比较了压缩或不压缩的二进制日志的大小。为了比较，我还尝试手动压缩这些测试中未压缩的二进制日志，以查看最佳压缩效果（而不是MySQL使用的每笔事务压缩）。除了必要的设置外，测试采用默认的配置。已经测试了以下工作负载：
- 批量加载：加载employees示例数据库。
- 批量更新：更新employees.salaries中所有行salary列：UPDATE employees.salaries SET salary = salary + 1
- 批量加载：使用sysbench填充四个10万行的表，为oltp_read_write基准测试做准备。
- OLTP 工作负载：使用前面创建的4个表，用 sysbench运行oltp_read_write基准测试，选项 --events=15000。
- 单行删除：从sysbench测试中删除其中一个表中的所有10万行。这些行逐一删除，这是压缩的最坏情况，因为事务非常小，并且每个已删除行的二进制日志中只有前镜像。
使用MySQL Shell可以轻松执行单行删除，例如在Python语言模式下：
from datetime import datetime for i in range(100000): if i % 5000 == 0: print('{0}: i = {1}'.format(datetime.now(), i)) session.run_sql('DELETE FROM sbtest.sbtest1 WHERE id = {0}'.format(i+1))print('{0}: i = 1000000'.format(datetime.now()))
每个任务的大小生成25 MiB至82 MiB范围内的未压缩二进制日志。测试使用以下设置进行：
- 没有压缩
- 启用压缩
- 加密但未压缩
- 启用加密和压缩
- MySQL中没有压缩，手动使用zstd进行压缩
选择zstd的压缩，因为MySQL使用Zstandard压缩算法。如果您想自己尝试，您可以从Facebook的GitHub存储库下载Zstandard源代码，其中还包括编译说明。下表列出了每个组合生成的二进制日志字节大小。
| 测试 | 标准 | 压缩 | 加密 | 加密+压缩 | zstd |
| 加载employees表 | 66378639 | 28237933 | 66379151 | 28238634 | 26892387 |
| 批量更新 | 85698320 | 24667051 | 85698832 | 24667677 | 24779953 |
| sysbench准备 | 76367740 | 39221052 | 39221052 | 39221806 | 39775067 |
| sysbench运行 | 26190200 | 11933713 | 26190712 | 11936561 | 8468625 |
| 单行删除 | 47300156 | 39492400 | 47300668 | 39637684 | 16525219 |
分析加密和压缩的数据，可以推断出加密是压缩后完成，因为加密后数据压缩效果不佳。由于启用加密对压缩的效果影响不大，因此将只讨论正常（未压缩）、压缩和zstd结果。二进制日志大小也可以在下图中看到：
测试的结果并不让人吃惊，批量加载和批量更新的二进制压缩效果分别是51%和29%，sysbench OLTP压缩是46%，单行删除压缩是83%，比使用zstd压缩效果差很多。当将MySQL压缩的二进制日志与手动使用zstd压缩的二进制日志进行比较时，发现批量负载的文件大小大致相同，这说明对于大型事务，对每笔事务压缩和对整个文件压缩效果一样。随着事务大小变小，每笔事务压缩的相对效率降低，这对单行删除尤为明显。需要考虑的另一个因素是压缩级别。05—
示例 – 压缩级别
关于压缩级别有一些奇怪之处，为读者省事的说法是没有必要修改默认设置。第一个奇怪的是，允许的值是1-22，但zstd只支持1-19级。MySQL文档中没有任何内容可以解释差异。第二个奇怪的是，通过更改binlog_transaction_compression_level_zstd的值，压缩二进制日志的大小实际上不会发生变化，这可以从表中看到：
| 测试压缩级别 | MySQL加载 | MySQL OLTP | zstd 加载 | zstd OLTP |
| 1 | 28238142 | 11935450 | 34483207 | 8531545 |
| 3 | 28237933 | 11933713 | 26892387 | 8468625 |
| 11 | 28238128 | 11902669 | 24737194 | 6639524 |
| 19 | 28238246 | 11937664 | 18867187 | 5724300 |
| 22 | 28238125 | 11910022 |
- 加载是加载employees数据库。
- OLTP是使用sysbench进行oltp_read_write基准测试。
为了比较，1、3、11和19级的压缩包括使用zstd手动压缩二进制日志。数据也可以在下图中看到：
从图中可以看到，无论MySQL中使用的哪种压缩级别，文件大小基本上都没有差异；而对于zstd，文件大小会随着压缩级别的增加而正常减少。对于加载测试的1级，MySQL的压缩甚至明显优于zstd，看起来就像在MySQL中从未设置压缩级别一样。一个可能的解释是，Zstandard对给定类型的数据进行算法训练（创建字典），这尤其有助于改善小数据的压缩（应为改善大数据的压缩—译者注）。我不知道MySQL是否使用字典，如果是的话，是否使所有压缩级别的效果大致相等。
06—
结论
新的二进制日志事务压缩效果很好，可以成为减少I/O、磁盘使用量和网络使用量的好方法，建议您考虑启用它。除非您的CPU资源非常紧张，否则您很可能会从启用二进制日志压缩中受益。从这些测试中，二进制日志占用的磁盘空间可能大致减少一半。但实际情况还要看您的工作负载，您应该使用工作负载进行测试。建议：您还可以启用二进制日志传输压缩，但没有理由同时启用二进制日志事务压缩和传输压缩。另外，目前没有理由改变压缩级别。
