InnoDB中像sync_binlog这样与事务日志有关的参数设置,对业务性能有着直接的影响,不同的设置是性能和安全的权衡考虑。本文列举了部分参数,并进行测试验证,通过测试结果对比理解不同参数值设置的影响。
1、InnoDB性能相关参数
1.1 sync_binlog
1.1.1 参数含义
sync_binlog参数用来控制数据库的binlog刷到磁盘的不同机制:
- sync_binlog=0,表示MySQL不控制binlog的刷新,不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。
- 如果sync_binlog=n,表示每sync_binlog次事务提交,MySQL调用文件系统的刷新操作进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。其中sync_binlog=1为默认值,表示每次事务提交,MySQL都会把binlog刷下去,是最安全但是性能损耗最大的设置。这样的话,在数据库所在的主机操作系统损坏或者突然掉电的情况下,系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO,但是设置sync_binlog=1,多个事务同时提交,同样很大的影响MySQL和IO性能。
1.1.2 测试结果
1)设置sync_binlog参数
mysql> set global sync_binlog=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 |
+---------------+-------+
1 row in set (0.00 sec)
2)使用sysbench插入表数据10w条
sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.112.121 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --oltp-test-mode=complex --oltp-tables-count=1 --oltp-table-size=0 --report-interval=1 prepare
向其中插入10w数据
sysbench ./tests/include/oltp_legacy/insert.lua --mysql-host=192.168.112.121 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --oltp-test-mode=complex --oltp-tables-count=1 --oltp-table-size=100000 --events=100000 --time=100 --report-interval=1 run
参数性能对比如下:
Sync_binlog | 执行时间 | 平均TPS | 平均响应时间 |
---|---|---|---|
0 | 261.65s | 382.18 | 2.61ms |
1 | 428.65s | 233.29 | 4.28ms |
5 | 263.77s | 379.10 | 2.63ms |
可以看到使用sync_binlog=0和sync_binlog=5后性能是有明显提升。从磁盘性能来看,w/s的速度没有明显差距:
1.2 innodb_flush_log_at_trx_commit
1.2.1 参数解析
InnoDB的redo日志记录了对数据做的修改操作,在提交事务的时候会将redo日志写入磁盘,此时会根据一定的策略将redo日志从redo log buffer里刷新到磁盘文件中。这个策略是通过 innodb_flush_log_at_trx_commit 来配置的,mysql操作日志大致步骤为log_buffer->mysql写(write)->log_file->OS刷新(flush)->disk:
- 值为0 : 提交事务的时候,不会立即把redo log buffer里的数据刷入磁盘文件的,而是依靠InnoDB的主线程每秒执行一次刷新到磁盘。但是每次事务的结束(commit或者是rollback)并不会触发Log Thread将log buffer中的数据写入文件。所以,当设置为0的时候,当MySQL Crash和OS Crash或者主机断电之后,此时内存里的数据全部丢失,最极端的情况是丢失1秒时间的数据变更。
- 值为1 : 默认值,提交事务的时候,都会触发Log Thread将log buffer中的redo log从内存刷入到磁盘文件里去并通知文件系统同步文件。只要事务提交成功,redo log就必然在磁盘里了。这个设置是最安全的设置,能够保证不论是MySQL Crash还是OS Crash或者是主机断电都不会丢失任何已经提交的数据。
- 值为2 : 提交事务的时候,把redo日志写入磁盘文件对应的os cache缓存里去,而不是直接进入磁盘文件。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件中,Log Thread就完全不知道了,可能1秒后才会把os cache里的数据写入到磁盘文件里去。当设置为2 的时候,MySQL Crash 并不会造成数据的丢失,但是OS Crash或者是主机断电后可能丢失的数据量就完全控制在文件系统上了
可以看到,只有1才能真正地保证事务的持久性,但是由于刷新操作fsync()是阻塞的,直到完成后才返回,因此MySQL的性能会明显地下降。
1.2.2 测试结果
1)设置innodb_flush_log_at_trx_commit参数
mysql> set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0 |
+--------------------------------+-------+
1 row in set (0.00 sec)
2)参数性能对比(sync_binlog=0)如下:
innodb_flush_log_at_trx_commit | 执行时间 | 平均TPS | 平均响应时间 |
---|---|---|---|
0 | 43.29s | 2309.74 | 0.43ms |
1 | 213.82s | 467.68 | 2.13ms |
2 | 44.71s | 2236.77 | 0.44ms |
可以看到,当innodb_flush_log_at_trx_commit设置为0或2时,性能要比设置为1时提升5倍,主要是因为设置为1时I/O操作是实时进行的,这部分消耗时间较多。从磁盘性能可以看到设置为1时的w/s明显下降,avgrq-sz值明显降低,IO性能变差:
1.2.3 推荐配置组合
1)innodb_flush_log_at_trx_commit和sync_binlog参数配合设置
innodb_flush_log_at_trx_commit | sync_binlog | 适用场景 |
---|---|---|
1 | 1 | 适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如充值消费系统 |
1 | 0 | 适合数据安全性要求高,磁盘IO写能力支持业务不富余,允许备库落后或无复制 |
2 | 0或N(>0) | 适合数据安全性有要求,允许丢失一点事务日志,复制架构的延迟也能接受 |
0 | 0 | 磁盘IO写能力有限,无复制或允许复制延迟稍微长点能接受,例如:日志性登记业务 |
当两个参数设置为(1,1)的时候,写入性能最差;sync_binlog=N (N>1) innodb_flush_log_at_trx_commit=2时,InnoDB的写操作才能达到最高性能。
2)测试结果如下:
innodb_flush_log_at_trx_commit | sync_binlog | 执行时间 | 平均TPS | 平均响应时间 |
---|---|---|---|---|
0 | 0 | 43.29s | 2309.74 | 0.43ms |
1 | 0 | 213.82s | 467.68 | 2.13ms |
2 | 0 | 44.71s | 2236.77 | 0.44ms |
0 | 1 | 217.92s | 317.42 | 3.14ms |
1 | 1 | 380.87s | 262.56 | 3.80ms |
2 | 1 | 206.53s | 484.18 | 2.06ms |
3)数据安全性
- 当innodb_flush_log_at_trx_commit和sync_binlog都为1时是最安全的,在mysqld服务崩溃或者服务器主机crash的情况下,binary log只有可能丢失最多一个语句或者一个事务。但是都为1会导致频繁的IO操作,因此该模式也是最慢的一种方式
- 当innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
- 当innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。
(1,1)适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。(1,1)模式下,当磁盘IO无法满足业务需求时,推荐的做法是innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache,防止系统断电异常。
1.3 sync_relay_log和sync_relay_log_info
1)sync_relay_log:默认值为10000,这个参数和sync_binlog是一样的
- 当设置为1时,slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区,然后刷入relay log中继日志里,这样是最安全的,因为在崩溃的时候,你最多会丢失一个事务,但会造成磁盘的大量I/O。
- 当设置为0时,并不是马上就刷入中继日志里,而是由操作系统决定何时来写入,虽然安全性降低了,但减少了大量的磁盘I/O操作。这个值默认是0,可动态修改,建议采用默认值。
2)sync_relay_log_info:默认值为10000,这个参数和sync_relay_log参数一样
- 当设置为1时,slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区,然后刷入relay-log.info里,这样是最安全的,因为在崩溃的时候,你最多会丢失一个事务,但会造成磁盘的大量I/O。
- 当设置为0时,并不是马上就刷入relay-log.info里,而是由操作系统决定何时来写入,虽然安全性降低了,但减少了大量的磁盘I/O操作。这个值默认是0,可动态修改,建议采用默认值。
1.4 innodb_flush_method
1.4.1 参数解析
innodb_flush_method参数控制着innodb数据文件及redo log的打开、刷写模式,有三个取值: fsync (默认),O_DSYNC,O_DIRECT
- 默认是fsync,调用fsync()去刷数据文件与redo log的buffer。fsync(int fd)函数作用是flush时将与fd文件描述符所指文件有关的buffer刷写到磁盘,并且flush完元数据信息(比如修改日期、创建日期等)才算flush成功
- O_DSYNC时,innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件。O_SYNC方式打开redo文件表示当write日志时,数据都是从innodb log buffer直接write到磁盘,不用通过操作系统的缓冲,并且元数据也需要更新,才返回成功
- O_DIRECT时,innodb使用O_DIRECT打开数据文件,使用fsync()刷写数据文件跟redo log。O_DIRECT表示我们的write操作是从innodb buffer里直接向磁盘上写,并不用通过操作系统的缓冲,而真正的完成也是在flush这步,日志还是要经过OS缓冲
1.4.2 测试结果
1)设置 innodb_flush_method参数
#修改配置文件/etc/my.cnf,修改参数innodb_flush_method
#重启mysql生效
# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL........... SUCCESS!
mysql> show variables like 'innodb_flush_method';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| innodb_flush_method | O_DSYNC |
+---------------------+---------+
1 row in set (0.01 sec)
2)参数性能对比如下:
innodb_flush_method | 执行时间 | 平均TPS | 平均响应时间 |
---|---|---|---|
fsync | 360.59s | 277.31 | 3.60ms |
O_DSYNC | 538.53s | 185.69 | 5.38ms |
O_DIRECT | 428.54s | 233.35 | 4.28ms |
可以看到设置为O_DSYNC性能最差,看下磁盘性能情况,O_DSYNC的w/s相对多些,avgrq-sz区别不大。
1.5 innodb_flush_neighbors
1.5.1 参数解析
InnoDB存储引擎从1.2.x版本开始提供了参数 innodb_flush_neighbors,用来控制是否启用刷新邻接页特性。对于传统机械硬盘建议启用该特性,而对于固态硬盘有着超高IOPS性能的磁盘,则建议将该参数设置为 0,即关闭此特性。
mysql> show variables like '%neigh%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_flush_neighbors | 0 | #默认关闭开功能
+------------------------+-------+
1 row in set (0.01 sec)
- 1:表示打开了刷新邻接页的功能,顺带着刷新在buffer pool中位于磁盘上相同的extend区的相邻的脏页
- 0:表示关闭刷新邻接页
- 2:表示刷新在buffer pool中位于磁盘上相同的extend区的脏页
当刷新一个脏页时,innodb存储引擎会检测该页所在区(extent)的所有页,如果是脏页,那么一起进行刷新。这样可以通过异步IO(AIO)将多个IO写入操作合并为一个IO操作,增大写入量,减少了物理写IO,所以innodb_flush_neighbors工作机制在传统机械磁盘下有着显著的优势。
- 在写入次数基本不增加的情况下,增加了写入的量;
- 加速了脏页的回收;
- 充分利用double write每次1M写入的特征;
- 这个功能打开以后会发现iostat里面的wrqm(合并写)这个值会比较高
1.5.2 测试结果
1)设置 innodb_flush_method参数
mysql> set global innodb_flush_neighbors=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_flush_neighbors';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_flush_neighbors | 1 |
+------------------------+-------+
1 row in set (0.00 sec)
2)参数性能对比如下:
innodb_flush_neighbors | 执行时间 | 平均TPS | 平均响应时间 |
---|---|---|---|
0 | 379.29s | 263.64 | 3.79ms |
1 | 375.13s | 266.57 | 3.75ms |
2 | 352.78s | 283.46 | 3.52ms |
可以看到innodb_flush_neighbors的设置对性能影响不大。
1.6 总结
以上是InnoDB中性能有关的一些参数, 在实际生产系统中,设置如下:
- sync_binlog=1
- innodb_flush_log_at_trx_commit=1
- sync_relay_log和sync_relay_log_info默认值10000
- innodb_flush_method= O_DIRECT
- innodb_flush_neighbors=0
参考资料:
- https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html
- https://blog.csdn.net/juan083/article/details/49489655
- https://blog.csdn.net/thundermeng/article/details/50448614
- https://www.cnblogs.com/geaozhang/p/7265261.html
转载请注明原文地址:https://blog.csdn.net/solihawk/article/details/122140035
文章会同步在公众号“牧羊人的方向”更新,感兴趣的可以关注公众号,谢谢!