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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 基准测试的设置概览如下

01

基准测试的设置


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

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


02

初步结果


第一次测试,对比没有启用二进制日志与启用二进制日志(设置 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 个事务各执行一次二进制日志的同步写入。


03

测试结果

下面用表格的方式列出吞吐量的中位数(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对性能的影响更大。


04

件系统


以上所有结果都在 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

我们可以观察到一个总体趋势,即 XFS 的吞吐量中位数比 EXT4 差一点,差异几乎相同。

吞吐量的差异很小,您可以使用 XFS 或 EXT4中的任意一种。


05

硬件规


Supermicro 服务器:

  • Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz
  • 2 sockets / 28 cores / 56 threads
  • Memory: 256GB of RAM
  • Storage: SAMSUNG  SM863 1.9TB Enterprise SSD
  • Filesystem: ext4/xfs
  • Percona-Server-5.7.21-20
  • OS: Ubuntu 16.04.4, kernel 4.13.0-36-generic
相关实践学习
通过日志服务实现云资源OSS的安全审计
本实验介绍如何通过日志服务实现云资源OSS的安全审计。
相关文章
|
19天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
56 3
|
5月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
19天前
|
SQL 运维 关系型数据库
深入探讨MySQL的二进制日志(binlog)选项
总结而言,对MySQL binlogs深度理解并妥善配置对数据库运维管理至关重要;它不仅关系到系统性能优化也是实现高可靠性架构设计必须考虑因素之一。通过精心规划与周密部署可以使得该机能充分发挥作用而避免潜在风险带来影响。
54 6
|
19天前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(上)
最终建议:当前系统是完美的读密集型负载模型,优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存,预期可降低30%的CPU负载,同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
82 6
|
19天前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(中)
使用MYSQL Report分析数据库性能
75 1
|
2月前
|
缓存 关系型数据库 MySQL
MySQL数据库性能调优:实用技术与策略
通过秉持以上的策略实施具体的优化措施,可以确保MySQL数据库的高效稳定运行。务必结合具体情况,动态调整优化策略,才能充分发挥数据库的性能潜力。
118 0
|
4月前
|
关系型数据库 MySQL 分布式数据库
Super MySQL|揭秘PolarDB全异步执行架构,高并发场景性能利器
阿里云瑶池旗下的云原生数据库PolarDB MySQL版设计了基于协程的全异步执行架构,实现鉴权、事务提交、锁等待等核心逻辑的异步化执行,这是业界首个真正意义上实现全异步执行架构的MySQL数据库产品,显著提升了PolarDB MySQL的高并发处理能力,其中通用写入性能提升超过70%,长尾延迟降低60%以上。
|
5月前
|
SQL 监控 关系型数据库
MySQL日志分析:binlog、redolog、undolog三大日志的深度探讨。
数据库管理其实和写小说一样,需要规划,需要修订,也需要有能力回滚。理解这些日志的作用与优化,就像把握写作工具的使用与运用,为我们的数据库保驾护航。
213 23
|
5月前
|
存储 SQL 关系型数据库
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
|
6月前
|
SQL 运维 关系型数据库
MySQL Binlog 日志查看方法及查看内容解析
本文介绍了 MySQL 的 Binlog(二进制日志)功能及其使用方法。Binlog 记录了数据库的所有数据变更操作,如 INSERT、UPDATE 和 DELETE,对数据恢复、主从复制和审计至关重要。文章详细说明了如何开启 Binlog 功能、查看当前日志文件及内容,并解析了常见的事件类型,包括 Format_desc、Query、Table_map、Write_rows、Update_rows 和 Delete_rows 等,帮助用户掌握数据库变化历史,提升维护和排障能力。

热门文章

最新文章

推荐镜像

更多