MySQL双一参数&性能测试

最新推荐文章于 2024-06-18 21:52:50 发布
最新推荐文章于 2024-06-18 21:52:50 发布
阅读量841 收藏 26
点赞数 21
分类专栏： MySQL 文章标签： mysql 数据库 k8s 云原生
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41586875/article/details/136976062
版权

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%
旺仔_牛奶
关注
  • 21
    点赞
  • 踩
  • 26
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL是如何保证数据的完整性
12-14
今天我们就探讨下关于数据的完整性和强一致性，MySQL做了哪些改进。 一. MySQL的二阶段提交  在Oracle和MySQL这种关系型数据库中，讲究日志先行策略（Write-Ahead Logging）,只要日志持久化到磁盘，就能保证MySQL...
mysql核心参数_MySQL技术体系之核心参数
weixin_33765959的博客
02-02 492
本文主要基于MySQL 5.7版本的数据库环境，总结my.cnf文件中核心参数的配置使用，让更多的人对MySQL技术体系有更全面、更专业的深度了解。一、客户端核心参数1、port端口号，默认33062、socketSocket文件地址，默认以.sock为文件名称后缀，用于UNIX套接字通信。二、服务端全局区(SGA)缓冲区参数1、innodb_buffer_poolInnoDB缓冲池，位于主内存缓...
mysql双一参数_mysql的双1设置
weixin_31597865的博客
01-18 2066
摘要：网易游戏《炉石传说》游戏数据库发生宕机并引发数据丢失事故，最终决定回档并后续补偿玩家损失。详情可见官网公告：http://hs.blizzard.cn/articles/16/8565.有大神分析造成数据库损坏，如果也用MySQL数据库的话，看起来应该是没开启双1设置，我们了解一下什么是双1设置。innodb_flush_log_at_trx_commit和sync_binlog 两个参数是...
【Mysql】双1设置
一个萌新的博客
06-26 1302
双1设置
MySQL的“双1”配置指的是什么
jkzyx123的博客
01-23 840
MySQL的“双1”配置，sync_binlog=1和innodb_flush_log_at_trx_commit=1
[MySQL] 日志刷盘机制
从前有座无庙山
10-13 1385
概念 MySQL的InnoDB日志管理机制中，有一个概念叫MTR（mini-transaction）。 MySQL中把对底层页面的一次原子访问的过程称之为一个Mini-Transaction，这里的原子操作，指的是要么全部成功，要么全部失败，不存在中间状态。 向底层页面插入一个新的record，至少会产生两个MTR。首先对page的修改要持久化，需要redo log，这里是一个MTR。InnoDB为了支持MVCC和事务回滚，会产生一个undo log，undo log写入undo page，这需要一个MTR
mysql 双1设置_mysql 双1设置
weixin_42501119的博客
02-17 2142
双1设置：(适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务(磁盘为 PCIE SSD))sync_binlog、innodb_flush_log_at_trx_commit 这两个参数都要设置为1。// mysqld 服务崩溃或者服务器主机 crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务。sync_binlog该参数表示事务写入 binary log 并...
12.MySQL中双“1“参数
weixin_43346403的博客
02-07 432
mysql中双"1"参数
双层圆柱的双一阶彩虹频谱结构
02-23
结合理论及实验对双层圆柱双一阶彩虹的光强分布及频谱特性进行了研究。基于Debye 理论，通过数值计算几种干涉结构的频谱，获知双一阶彩虹频谱中4 个特征峰的成因。将与均匀圆柱叠加波结构频率成因相似的特征频率F
Linux 该性能服务器编程 游双
01-18
Linux 高性能服务器编程 游双 清晰电子版哦，喜欢的小伙伴可以下载
双一安全套终端宣传物料手册.pdf
10-21
双一安全套终端宣传物料手册.pdf
基于双一阶函数组合模型的不同粒径颗粒煤瓦斯吸附动力学特征研究-论文
07-07
基于不同粒径颗粒煤的吸附实验，建立了双一阶函数组合模型，对煤对瓦斯的吸附过程进行计算分析。研究结果表明：煤对瓦斯的吸附过程是瓦斯在孔隙中的宏观迁移和微观连续吸附的组合，采用双一阶函数组合模型可以实现...
MySQL双1参数innodb_flush_log_at_trx_commit、sync_binlog和两阶段提交
Lissen_Gan的博客
05-25 558
一、双1参数 1、innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit这个参数控制MySQL在事务提交时将内存中的Redo log file从Redo log buffer刷新到磁盘(Redo log file)的方式。 value: 0 1 2 1）0 每秒钟将内存中Redo log File从Redo Log Buffer刷新到OS Buffer并调用fsync()将OS Buffer刷新到磁盘(数据库宕机时，可能会有数据丢失)
mysql 双1设置_2020-10-15：mysql的双1设置是什么？
weixin_39955418的博客
01-19 103
福哥答案2020-10-15：#福大大架构师每日一题#其实就是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，都设置为1就是双1设置。MySQL 默认配置就是双1配置。innodb_flush_log_at_trx_commit 是 innodb 引擎的配置，sync_binlog 是 MySQL 引擎上层的配置，都是控制磁盘写入策略。MySQL ...
MySQL 双1配置
java_w的专栏
12-12 507
innodb_flush_log_at_trx_commit N=0 --每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上 N=1 –每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上 N=2 –每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度...
4.mysql 双1
as1123671649的博客
08-03 82
N:在提交事务时，log thread 将 binlog_cache 中的数据写入操作系统缓冲,每N次写磁盘,调用操作系统 fsync 刷写IO缓存。2:在提交事务时，log thread 将 log buffer 中的数据写入操作系统缓冲,每秒调用操作系统 fsync 刷写IO缓存。1:在提交事务时，log thread 将 log buffer 中的数据写入操作系统缓冲,并调用操作系统 fsync 刷写IO缓存。1 每个事务提交，把binlog刷到硬盘（最安全、最慢）
mysql的"双1设置"-数据安全的关键参数（案例分享）
weixin_30716725的博客
02-27 524
mysql的"双1验证"指的是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，这两个是是控制MySQL 磁盘写入策略以及数据安全性的关键参数。下面从参数含义，性能，安全角度阐述两个参数为不同的值时对db 性能,数据的影响。 一、参数意义 innodb_flush_log_at_trx_commit如果innodb_flush_l...
如何保证主从复制数据一致性
少昊 鹰 的 IT博客
10-29 6276
在MySQL中，一次事务提交后，需要写undo、写redo、写binlog，写数据文件等等。在这个过程中，可能在某个步骤发生crash，就有可能导致主从数据的不一致。为了避免这种情况，我们需要调整主从上面相关选项配置，确保即便发生crash了，也不能发生主从复制的数据丢失。 1. 在master上修改配置 innodb_flush_log_at_trx_commit = 1 sync_
MySQL-DML-约束
最新发布
weixin_51788042的博客
06-18 657
创建表时，可以给表的字段添加约束，可以保证数据的完整性、有效性。以上的删除属于DML的方式删除，这种删除的数据是可以通过事务回滚的方式重新恢复的，但是删除的效率较低。另外还有一种删除表中数据的方式，但是这种方式不支持事务，不可以回滚，删了之后数据是永远也找不回来了。email字段设置为唯一性，唯一性的字段值是可以为NULL的。表名后面的小括号当中的字段名如果省略掉，表示自动将所有字段都列出来了，并且字段的顺序和建表时的顺序一致。注意：这个语句删除效率非常高，巨大的表，瞬间干掉所有数据。
ieee的latex的双一作格式
08-08
IEEE（Institute of Electrical and Electronics Engineers）是一个国际性的专业学术机构，他们为各个领域的研究人员和工程师提供技术发展和交流的平台，并发布各种学术论文。在撰写学术论文时，使用IEEE的LATEX模板可以帮助作者按照IEEE的双一作格式来编写论文。 双一作格式是IEEE要求作者在学术论文中同时列出作者和机构的名称，在同一行中以平行形式排列，作者与机构名称之间用逗号分隔。对于多个作者和多个机构的情况，可以使用上标来表示各作者和机构的对应关系。 在LATEX中，可以使用IEEEtran模板来实现双一作格式。首先，在文档类声明中使用"ieeeaccess"或"ieeeconf"来指定使用IEEEtran模板。然后，在正文中使用"\author"命令来列出作者的名称，用逗号分隔不同作者。如果需要对应作者和非对应作者的区分，则可以使用"\thanks"命令。对应作者应该在机构名称后面用"*"标识。在每个作者后面使用"\IEEEauthorrefmark"命令来生成上标，指定作者和机构的对应关系。 例如，假设有两个作者Alice和Bob，他们分别隶属于两个机构A和B，那么在LATEX代码中可以这样编写： \author{% \IEEEauthorblockN{Alice, Bob} \IEEEauthorblockA{Institution A, Institution B\IEEEauthorrefmark{1}} } 最后，在文档末尾使用"\IEEEauthorrefmark{1}"命令来生成作者和机构的对应关系。该命令会自动将上标“1”对应到作者Alice和Bob所隶属的机构A和B。 通过使用IEEE的LATEX模板，按照上述步骤编写论文，就能够符合IEEE的双一作格式要求。这样的输出结果将符合IEEE论文的排版标准，并且便于读者查找作者和机构的信息。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

旺仔_牛奶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值