当前位置： 首页 > news >正文

MySQL双一参数性能测试

news 2025/8/4 10:09:27

MySQL双一参数

MySQL 数据库中有两个非常重要的参数，一个是innodb_flush_log_at_trx_commit，另外一个是sync_binlog，这两个参数称之为双一参数，前者控制了redo log 的刷盘机制，后者控制了binlog 的刷盘机制

这两个参数取值不同时，会影响数据库性能以及安全。

参数名称允许值
innodb_flush_log_at_trx_commit0, 1, 2
sync_binlog0,1, N

sync_binlog

sync_binlog是MySQL Binlog日志的重要参数，用于控制Binlog的更新策略，通过对该参数的调优，可以提升数据库的性能和数据安全性：

  • 0：binlog 不刷盘，依赖于操作系统的刷盘机制，在断电或者是操做系统崩溃的情况下，这些事务将全部丢失
  • 1：这是最安全的方式，binlog 在 binlog 组提交的 sync 阶段都进行刷盘操作，在断电或操作系统崩溃的情况下，二进制日志中丢失的事务仅处于准备状态，在恢复的时候直接回滚掉
  • N：binlog 将在 N 次 sync 队列形成后进行 sync 刷盘

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit是MySQL InnoDB存储引擎独有的参数，用于控制InnoDB的Redo log日志记录方式。通过调优该参数，可以提升数据库的性能和数据安全性。该参数的取值范围为0、1、2，不同的值代表MySQL数据库的Redo log不同的刷盘的方式

  • 1： 当innodb_flush_log_at_trx_commit=1时，InnoDB将在每次事务提交时将log buffer的数据更新到文件系统os buffer中，并调用文件系统的flush操作将数据缓存更新至磁盘中。此种方式下，数据库完全遵守ACID特性，安全性较高
  • 2：当innodb_flush_log_at_trx_commit=2时，InnoDB将在每次事务提交时将log buffer中的数据更新到文件系统缓存中，每秒钟将文件系统缓存中的数据更新到磁盘一次，该操作由操作系统调度。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置，不能完全保证每秒更新磁盘一次，没有被更新到磁盘中的事务可能会因宕机而丢失
  • 0：当innodb_flush_log_at_trx_commit=0时，InnoDB会每秒钟将log buffer中的数据更新到磁盘中。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置，并不能完全保证每秒将数据更新到磁盘一次。因此，在实例崩溃恢复场景中，可能会出现丢失1秒钟的事务
    在这里插入图片描述

需要注意的是：

  • 当设置为0时，速度最快，安全性最差，mysqld进程的崩溃最多会导致上一秒钟所有事务数据的丢失。
  • 当设置为1，该模式是最安全的，对数据库的性能影响也最大,即使宕机也不会丢失事务。
  • 当设置为2，该模式速度较快，较取值为0情况下更安全，mysqld crash不会丢失事务，但宕机则可能丢失事务

最佳实践

您可以按照您的业务场景，根据下表来选择最适合您的配置。

innodb_flush_log_at_trx_commitsync_binlog描述
11适用于对数据安全性要求非常高，同时磁盘有足够的写入能力的场景。这种模式下性能最差
10适用于对数据安全性要求高，但是磁盘写入能力有限的场景。
2N对数据安全性要求低，此时性能最佳
00对数据安全性要求不高，应用需要较快的写入能力

性能测试配置

  • 只写测试
# 准备数据,执行以下sysbench命令可以创建，sysbench的内置表,并在每个表中生成对应数据
# --table_size 	没个表多少行数据
# --tables			多少个表
sysbench --db-driver=mysql \
--mysql-host=xxx \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_write \
--table_size=100000 \
--tables=10 \
--events=0 \
--time=600 \
/usr/local/share/sysbench/oltp_write_only.lua prepare# 测试数据(run)
# --report-interval 		多少秒报告一次
sysbench --db-driver=mysql \
--mysql-host=xxx \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_write \
--table_size=100000 \
--tables=10 \
--time=200 \
--threads=30 \
--report-interval=5 \
/usr/local/share/sysbench/oltp_write_only.lua run# 清理测试数据(cleanup),将生成的表删除
sysbench --db-driver=mysql \
--mysql-host=xxx  \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_write \
--table_size=100000 \
--tables=10 \
/usr/local/share/sysbench/oltp_write_only.lua cleanup
  • 只更新测试(没有index)
# 准备数据,执行以下sysbench命令可以创建，sysbench的内置表,并在每个表中生成对应数据
# --table_size 	没个表多少行数据
# --tables			多少个表
sysbench --db-driver=mysql \
--mysql-host=xxx  \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_update \
--table_size=100000 \
--tables=10 \
--events=0 \
--time=600 \
/usr/local/share/sysbench/oltp_update_non_index.lua prepare# 测试数据(run)
# --report-interval 		多少秒报告一次
# --time 测试120s
sysbench --db-driver=mysql \
--mysql-host=xxx   \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_update \
--table_size=100000 \
--tables=10 \
--report-interval=5 \
--threads=30 \
--time=200 \
/usr/local/share/sysbench/oltp_update_non_index.lua run# 清理测试数据(cleanup),将生成的表删除
sysbench --db-driver=mysql \
--mysql-host=xxx   \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_update \
--table_size=100000 \
--tables=10 \
/usr/local/share/sysbench/oltp_update_non_index.lua cleanup
  • 只更新数据(有index)
# 准备数据,执行以下sysbench命令可以创建，sysbench的内置表,并在每个表中生成对应数据
# --table_size 	没个表多少行数据
# --tables			多少个表
sysbench --db-driver=mysql \
--mysql-host=xxx  \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_update_index \
--table_size=100000 \
--tables=10 \
--events=0 \
--time=600 \
/usr/local/share/sysbench/oltp_update_index.lua prepare# 测试数据(run)
# --report-interval 		多少秒报告一次
# --time 测试120s
sysbench --db-driver=mysql \
--mysql-host=xxx   \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_update_index \
--table_size=100000 \
--tables=10 \
--report-interval=5 \
--threads=30 \
--time=200 \
/usr/local/share/sysbench/oltp_update_index.lua run# 清理测试数据(cleanup),将生成的表删除
sysbench --db-driver=mysql \
--mysql-host=xxx   \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_update_index \
--table_size=100000 \
--tables=10 \
/usr/local/share/sysbench/oltp_update_index.lua cleanup
  • 只读数据
# 准备数据,执行以下sysbench命令可以创建，sysbench的内置表,并在每个表中生成对应数据
# --table_size 	没个表多少行数据
# --tables			多少个表
sysbench --db-driver=mysql \
--mysql-host=xxx  \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_read \
--table_size=100000 \
--tables=10 \
--events=0 \
--time=600 \
/usr/local/share/sysbench/oltp_read_only.lua prepare# 测试数据(run)
# --report-interval 		多少秒报告一次
# --time 测试120s
sysbench --db-driver=mysql \
--mysql-host=xxx   \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_read \
--table_size=100000 \
--tables=10 \
--report-interval=5 \
--threads=30 \
--time=200 \
/usr/local/share/sysbench/oltp_read_only.lua run# 清理测试数据(cleanup),将生成的表删除
sysbench --db-driver=mysql \
--mysql-host=xxx   \
--mysql-port=xxx \
--mysql-user=root \
--mysql-password=xxx \
--mysql_storage_engine=innodb \
--mysql-db=benchmark_read \
--table_size=100000 \
--tables=10 \
/usr/local/share/sysbench/oltp_read_only.lua cleanup

性能测试结果

# bitnami 容器设置参数,bitnami默认脚本写死了，是基于命令行启动的，且默认启动为双一启动的- name: MARIADB_EXTRA_FLAGS                    value: --sync-binlog=0 --innodb_flush_log_at_trx_commit=0# 镜像坐标：docker.io/bitnami/mariadb:10.6.10
innodb_flush_log_at_trx_commitsync_binlog类型tps/sqps/s95分位延迟(ms)压测线程数MariaDB配置CPU/内存使用率
11只写1296.237777.3642.61301C2G /10.6100%/45%
00只写1426.848561.0454.83301C2G /10.6100%/32%
11更新(no_index)3396.203396.2013.95301C2G /10.680%/15%
00更新(no_index)5787.015787.0141.85301C2G /10.6100%/32%
11更新(with_index)3271.283271.2815.27301C2G /10.685%/16%
00更新(with_index)4930.374930.3752.89301C2G /10.6100%/32%
11只读447.497159.9094.10301C2G /10.6100%/16%
00只读449.517192.1594.10301C2G /10.6100%/32%
http://www.lryc.cn/news/324185.html

相关文章：

  • CodeSys创建自定义的html5控件
  • Xcode15报错: SDK does not contain ‘libarclite‘
  • 矩阵快速幂
  • 数据之谜：解读Facebook的用户行为
  • 学习 考证 帆软 FCP-FineBI V6.0 考试经验
  • 《过滤器模式（极简c++）》
  • 【C++】如何用一个哈希表同时封装出unordered_set与unordered_map
  • Day45：WEB攻防-PHP应用SQL二次注入堆叠执行DNS带外功能点黑白盒条件
  • web安全之：三种常见的Web安全威胁
  • C#，图论与图算法，用于检查给定图是否为欧拉图（Eulerian Graph）的算法与源程序
  • Dubbo框架的介绍
  • 手机实时监控电脑屏幕（手机可以看到电脑在干什么吗）
  • 合成孔径雷达干涉测量InSAR数据处理、地形三维重建、形变信息提取、监测
  • 云原生（五）、Docker-Swarm集群
  • arm核的DMPIS是如何计算的
  • Axure RP 9 for Mac中文激活版：原型设计工具
  • Hive 数据迁移与备份
  • FFMpeg 获取音频音量、提高音量
  • 【java数据结构】基于java提供的ArrayList实现的扑克牌游戏-（附源码~）
  • R语言：microeco:一个用于微生物群落生态学数据挖掘的R包，第八：trans_func class
  • 王道c语言-二叉树前序、中序、后序、层次遍历
  • ＜REAL-TIME TRAFFIC OBJECT DETCTION FOR AUTONOMOUS DRIVING＞论文阅读
  • 优化 - 排序算法
  • Python实战：深拷贝与浅拷贝
  • rollup打包起手式
  • 【笔记】语言实例比较 3. 无重复字符的最长子串 C++ Rust Java Python
  • int的大小你知道时4个字节，那么类的大小你知道怎么计算吗？
  • OpenCV学习笔记（十一）——利用Sobel算子计算梯度
  • 扩展一下BenchmarkSQL，新增支持ASE/HANA/DB2/SQLServer，可以随便用了
  • Android 静默安装成功后自启动