两个非常重要的参数一个是innodb_flush_log_at_trx_commit另外一个是sync_binlog这两个参数称之为双一参数前者控制了redo

log

参数名称允许值innodb_flush_log_at_trx_commit0,

2sync_binlog0,1,

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

0binlog

不刷盘依赖于操作系统的刷盘机制在断电或者是操做系统崩溃的情况下这些事务将全部丢失1这是最安全的方式binlog

binlog

阶段都进行刷盘操作在断电或操作系统崩溃的情况下二进制日志中丢失的事务仅处于准备状态在恢复的时候直接回滚掉Nbinlog

sync

innodb_flush_log_at_trx_commit是MySQL

log日志记录方式。

通过调优该参数可以提升数据库的性能和数据安全性。

该参数的取值范围为0、1、2不同的值代表MySQL数据库的Redo

当innodb_flush_log_at_trx_commit1时InnoDB将在每次事务提交时将log

buffer中并调用文件系统的flush操作将数据缓存更新至磁盘中。

此种方式下数据库完全遵守ACID特性安全性较高2当innodb_flush_log_at_trx_commit2时InnoDB将在每次事务提交时将log

buffer中的数据更新到文件系统缓存中每秒钟将文件系统缓存中的数据更新到磁盘一次该操作由操作系统调度。

因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置不能完全保证每秒更新磁盘一次没有被更新到磁盘中的事务可能会因宕机而丢失0当innodb_flush_log_at_trx_commit0时InnoDB会每秒钟将log

buffer中的数据更新到磁盘中。

因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置并不能完全保证每秒将数据更新到磁盘一次。

因此在实例崩溃恢复场景中可能会出现丢失1秒钟的事务

当设置为0时速度最快安全性最差mysqld进程的崩溃最多会导致上一秒钟所有事务数据的丢失。

当设置为1该模式是最安全的对数据库的性能影响也最大,即使宕机也不会丢失事务。

当设置为2该模式速度较快较取值为0情况下更安全mysqld

最佳实践

innodb_flush_log_at_trx_commitsync_binlog描述11适用于对数据安全性要求非常高同时磁盘有足够的写入能力的场景。

这种模式下性能最差10适用于对数据安全性要求高但是磁盘写入能力有限的场景。

2N对数据安全性要求低此时性能最佳00对数据安全性要求不高应用需要较快的写入能力

性能测试配置

准备数据,执行以下sysbench命令可以创建sysbench的内置表,并在每个表中生成对应数据

--table_size

/usr/local/share/sysbench/oltp_write_only.lua

prepare#

/usr/local/share/sysbench/oltp_write_only.lua

run#

/usr/local/share/sysbench/oltp_write_only.lua

准备数据,执行以下sysbench命令可以创建sysbench的内置表,并在每个表中生成对应数据

--table_size

/usr/local/share/sysbench/oltp_update_non_index.lua

prepare#

/usr/local/share/sysbench/oltp_update_non_index.lua

run#

/usr/local/share/sysbench/oltp_update_non_index.lua

cleanup只更新数据(有index)

准备数据,执行以下sysbench命令可以创建sysbench的内置表,并在每个表中生成对应数据

--table_size

--mysql-dbbenchmark_update_index

--table_size100000

/usr/local/share/sysbench/oltp_update_index.lua

prepare#

--mysql-dbbenchmark_update_index

--table_size100000

/usr/local/share/sysbench/oltp_update_index.lua

run#

--mysql-dbbenchmark_update_index

--table_size100000

/usr/local/share/sysbench/oltp_update_index.lua

cleanup只读数据

准备数据,执行以下sysbench命令可以创建sysbench的内置表,并在每个表中生成对应数据

--table_size

/usr/local/share/sysbench/oltp_read_only.lua

prepare#

/usr/local/share/sysbench/oltp_read_only.lua

run#

/usr/local/share/sysbench/oltp_read_only.lua

cleanup性能测试结果

容器设置参数,bitnami默认脚本写死了是基于命令行启动的且默认启动为双一启动的-

name:

--innodb_flush_log_at_trx_commit0#

镜像坐标docker.io/bitnami/mariadb:10.6.10innodb_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%