您现在的位置是：首页 > 数据库

当前栏目

压缩MySQL二进制日志（译文）

mysql 日志二进制压缩译文

2023-06-13 09:18:17 时间

摘要：二进制日志通常会占用大量的磁盘空间，从MySQL 8.0.20开始，可以对MySQL正在使用的二进制日志进行压缩。这篇文章将探讨这个新功能。

原文网址：https://mysql.wisborg.dk/2020/05/07/mysql-compressed-binary-logs/

作者：Jesper Wisborg Krogh ，著有4本MySQL专著，参与sys schema的开发和4个版本的OCP考试命题。

关于译者，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云MVP
《MySQL 8.0运维与优化》的作者
中国唯一一位Oracle高可用大师
拥有包括 Oracle 10g和12c OCM在内的20+数据库相关认证。
曾任IBM公司数据库部门经理
现在一家第三方公司任首席数据库专家，服务2万+客户。

在一个繁忙的MySQL服务器上，二进制日志最终可能会成为使用磁盘空间的最大占用者之一。这意味着更高的I/O，更大的备份集（您正在备份二进制日志，对吗？），当向从库传输二进制日志时，网络流量会增加，等等。所以长期以来一直希望有一个二进制日志压缩功能，允许对MySQL正在使用的二进制日志进行压缩。从MySQL 8.0.20开始，现在可以了。这篇文章将探讨这个新功能。

—

配置

二进制日志压缩功能由两个变量控制，一个用于启用该功能，另一个用于指定压缩级别。

变量名称	默认值	允许值
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.

虽然配置二进制日志压缩很容易，但也需要注意一些限制。

—

限制

关于二进制日志压缩的限制简短地说就是只压缩事务行事件。基于语句的事件、GTID信息、旋转事件、非事务表的行事件等没有压缩。此外，如果您对事务添加非事务性更改，那么两者都不会被压缩。这也意味着每笔交易都是单独压缩的。有关这意味着什么的更多信息，请参阅本文中接下来的例子。

如果您对二进制日志使用所有默认值，并使用InnoDB存储引擎（默认值），则压缩可以起作用。有关限制的完整列表，请参阅官方文档中的二进制日志事务压缩

（https://dev.mysql.com/doc/refman/8.0/en/binary-log-transaction-compression.html）。

正如我通常所宣扬的，监控是了解您的系统的关键。下面说明如何监控二进制日志压缩功能。

—

监视

有两种方法可以监控二进制日志压缩功能的性能，一种是性能视图中的压缩统计，另一种是性能视图中的新增的两个阶段事件。

性能视图中的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: 313
FIRST_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: 116
FIRST_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.840767
2 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: 15321
total_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: 0
total_latency:   0 ps
  min_latency:   0 ps
  avg_latency:   0 ps
  max_latency:   0 ps
2 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: 27537
total_latency: 4.83 min
  avg_latency: 10.51 ms
  max_latency: 723.92 ms
 read_latency: 138.25 us
write_latency: 290.69 ms
 misc_latency: 4.82 min
   count_read: 37
   total_read: 1002 bytes
     avg_read:   27 bytes
  count_write: 16489
total_written: 99.26 MiB
  avg_written: 6.16 KiB
1 row in set (0.0015 sec)

这里sys视图输出的数据已经自动转换为人类可读的格式，而性能视图中的时间使用FORMAT_PICO_TIME()函数进行转换。在本例中，MySQL总计花了6.21秒进行二进制日志的压缩，每笔事务平均略低于400微秒。相比之下，MySQL总计花了4.8分钟在二进制日志文件上做I/O，这说明压缩在写日志的时间中占比很低。

在启用压缩之前，您应该检查写入和读取二进制日志文件所花费的时间，以便确定性能的变化。您还应该检查CPU使用情况的变化。

在上面的输出中，它显示压缩率为59%，但对于不同类型的工作负载来说，压缩率如何？

—

示例 – 工作负载

为了测量压缩的效果，我运行了一系列任务，并比较了压缩或不压缩的二进制日志的大小。为了比较，我还尝试手动压缩这些测试中未压缩的二进制日志，以查看最佳压缩效果（而不是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压缩的二进制日志进行比较时，发现批量负载的文件大小大致相同，这说明对于大型事务，对每笔事务压缩和对整个文件压缩效果一样。随着事务大小变小，每笔事务压缩的相对效率降低，这对单行删除尤为明显。

需要考虑的另一个因素是压缩级别。

—

示例 – 压缩级别

关于压缩级别有一些奇怪之处，为读者省事的说法是没有必要修改默认设置。

第一个奇怪的是，允许的值是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是否使用字典，如果是的话，是否使所有压缩级别的效果大致相等。

—

结论

新的二进制日志事务压缩效果很好，可以成为减少I/O、磁盘使用量和网络使用量的好方法，建议您考虑启用它。除非您的CPU资源非常紧张，否则您很可能会从启用二进制日志压缩中受益。从这些测试中，二进制日志占用的磁盘空间可能大致减少一半。但实际情况还要看您的工作负载，您应该使用工作负载进行测试。

建议：您还可以启用二进制日志传输压缩，但没有理由同时启用二进制日志事务压缩和传输压缩。另外，目前没有理由改变压缩级别。

猜你喜欢

C# 手动缩放图片帮助方法
Java消息处理实践突破性技术Redis（redis消息 java）
用Oracle简单分组求和的技巧（oracle 写分组求和）
MySQL: 如何实现主键不连续？（mysql主键不连续）
利用Oracle中HR模式服务组织发展（oracle中的hr模式）
Go 编译器优化
利用yac与redis缓存系统实现极速存储（yac和redis）
mse均方误差例题_误差函数计算器
hel-micro 模块联邦新革命
Java ReEntrantLock （Java代码实战-001）详解编程语言
[图]巴西卫生部官网存严重漏洞 2.43亿巴西人个人信息被泄露
故障分析 | MySQL 使用 load data 导入数据错误的一个场景
形结构利用PHP和MySQL构建树形结构（phpmysql树）
配置Linux重新调整环境，激活配置生效。（linux 重新加载）
立Oracle一对一关系建立技巧研究（oracle一对一怎么建）
Linux系统下设置 PATH 环境变量（linux设置环境变量path）
MySQL Error number: MY-010165; Symbol: ER_RPL_CANT_MAKE_PATHS; SQLSTATE: HY000 报错故障修复远程处理

zl程序教程

当前栏目

压缩MySQL二进制日志（译文）

配置

限制

监视

示例 – 压缩级别

结论

相关文章