MySQL双一参数性能测试

2024-03-24 10:28
文章标签 参数 mysql 性能 测试 database 双一

本文主要是介绍MySQL双一参数性能测试，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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%

这篇关于MySQL双一参数性能测试的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/841309。

相关文章

MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
Python如何使用__slots__实现节省内存和性能优化

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景：内存吃得满满的类__slots__：你的内存管理小助手举个大概的例子：看看效果如何？1.
阅读更多...
mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言：第一步：第二步：第三步：总结：前言：当你想通过命令窗口想打开mysql时候发现提http://www.cpp
阅读更多...
MySQL大表数据的分区与分库分表的实现

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区？1.2 分区的类型1.3 分区的优点1.4 分
阅读更多...
MySQL错误代码2058和2059的解决办法

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》：本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法（敲重点！！！）1. php前置理解2058和2059的错误
阅读更多...
Mysql删除几亿条数据表中的部分数据的方法实现

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T
阅读更多...
MySQL INSERT语句实现当记录不存在时插入的几种方法

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面：本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE
阅读更多...
MySQL Workbench 安装教程(保姆级)

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言：详细步骤：一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本，要是
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2