MySQL双一参数性能测试

MySQL双一参数

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

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

参数名称	允许值
innodb_flush_log_at_trx_commit	0, 1, 2
sync_binlog	0,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_commit	sync_binlog	描述
1	1	适用于对数据安全性要求非常高，同时磁盘有足够的写入能力的场景。这种模式下性能最差
1	0	适用于对数据安全性要求高，但是磁盘写入能力有限的场景。
2	N	对数据安全性要求低，此时性能最佳
0	0	对数据安全性要求不高，应用需要较快的写入能力

性能测试配置

• 只写测试

# 准备数据,执行以下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_commit	sync_binlog	类型	tps/s	qps/s	95分位延迟(ms)	压测线程数	MariaDB配置	CPU/内存使用率
1	1	只写	1296.23	7777.36	42.61	30	1C2G /10.6	100%/45%
0	0	只写	1426.84	8561.04	54.83	30	1C2G /10.6	100%/32%
1	1	更新(no_index)	3396.20	3396.20	13.95	30	1C2G /10.6	80%/15%
0	0	更新(no_index)	5787.01	5787.01	41.85	30	1C2G /10.6	100%/32%
1	1	更新(with_index)	3271.28	3271.28	15.27	30	1C2G /10.6	85%/16%
0	0	更新(with_index)	4930.37	4930.37	52.89	30	1C2G /10.6	100%/32%
1	1	只读	447.49	7159.90	94.10	30	1C2G /10.6	100%/16%
0	0	只读	449.51	7192.15	94.10	30	1C2G /10.6	100%/32%