二进制日志和文件系统是如何影响MySQL的性能的（译自Percona）

oracleace 2023-04-03

464

关于号主，姚远：

Oracle ACE（Oracle和MySQL数据库方向）。
Oracle MAA 大师。
华为云MVP。
《MySQL 8.0运维与优化》的作者。
拥有 Oracle 10g和12c OCM等20+数据库认证。
曾任IBM公司数据库部门经理。
20+年DBA经验，服务2万+客户。

原文网址：

https://www.percona.com/blog/how-binary-logs-and-filesystems-affect-mysql-performance/

作者：Vadim Tkachenko

这里我将仔细测试在不同文件系统上启用二进制日志对MySQL性能的影响，特别是在 MySQL 8.0 启用默认二进制日志的情况下。

—

基准测试的设置

基准测试的设置概览如下：

Percona Server for MySQL 5.7.21
InnoDB 存储引擎
启用了外键，使用可重复读取隔离级别，并使用了 UTF8 字符集。
数据集：sysbench-tpcc 有 10 个表和 100个仓库，总共有 1000个仓库，数据集大小约为90GB。
使用 80GB、70GB 和 60GB innodb_buffer_pool_size来模拟不同的 IO 负载，并评估这对二进制日志写入的影响。

—

初步结果

第一次测试，对比没有启用二进制日志与启用二进制日志（设置 sync_binlog=0）：

我们可以看到没有启用二进制日志的性能通常略好。另一个值得注意的现象是，启用二进制日志且 sync_binglog=0 的情况下，每40秒会出现吞吐量下降到0，并持续 1-2 秒，这时基本上任何连接的应用都会停滞。原因是二进制日志文件（max_binlog_size）的大小有限制，即 1GB。当达到1GB的限制时，MySQL会执行二进制日志轮换。因为使用 sync_binlog=0 ，以前对二进制日志的所有写入都缓存在操作系统缓存中，在二进制轮换时，MySQL 强制同步刷新所有的更新到磁盘，这导致每 40 秒应用完全停止一次，40秒是在上述测试中填满 1GB 二进制日志所需的时间。

我们该如何解决这个问题？显而易见的解决方案是更频繁地进行二进制日志的同步写入，这可以通过将sync_binlog设置为>0来实现。流行的选择是sync_binlog=1，这时每次提交都会写二进制日志，它可以最大限度的保护数据，但伴随着明显的性能下降。我将测试 sync_binlog=1000 和 sync_binlog=10000，这意味着分别每 1000 和 10000 个事务各执行一次二进制日志的同步写入。

—

测试结果

下面用表格的方式列出吞吐量的中位数（tps越多性能越好）

InnoDB缓存/ sync_binlog	0	1	1000	10000	无二进制日志
60GB	4174.945	3598.12	5263.375	4205.165	4277.955
70GB	5053.11	4541.985	4997.87	4997.87	5328.96
80GB	5701.985	5263.375	5303.145	5664.155	6087.925

我们可以得出一些结论：

sync_binlog=1 的性能损失最大，但差异最小，这与在没有二进制日志的情况下运行相当。
sync_binlog=0 在已启用的二进制日志时提供最佳的性能，但差异很大。
sync_binlog=1000 是一个很好的折衷方案，它以最小的差异提供比 sync_binlog=1 更好的性能。
sync_binlog=10000 可能不好，显示的差异比sync_binlog=0 少，但仍然很大。

那么我们应该把sync_binlog设置成什么值呢？这可能是在 sync_binlog=1 或 1000 之间做选择。这取决于您的应用和存储，我这里使用的是enterprise SATA SSD SAMSUNG SM86，但在存储性能比较差的情况下，设置sync_binlog=1对性能的影响更大。

—

文件系统

以上所有结果都在 EXT4 文件系统上，让我们与 XFS 进行比较。它会显示不同的吞吐量和差异吗？

下面用表格的方式列出吞吐量的中位数（tps越多性能越好）


sync_binlog	Buffer pool(GB)	EXT4	XFS
0	60	4174.945	3902.055
0	70	5053.11	4884.075
0	80	5701.985	5596.025
1	60	3598.12	3526.545
1	70	4541.985	4538.455
1	80	5263.375	5255.38
1000	60	3950.19	3620.05
1000	70	4714	4526.49
1000	80	5303.145	5150.11
10000	60	4205.165	3874.03
10000	70	4997.875	4845.85
10000	80	5664.155	5557.61
No binlog	60	4277.955	4169.215
No binlog	70	5328.96	5139.625
No binlog	80	6087.925	5957.015