数据库系列之InnoDB性能有关参数解析

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平均响应时间
0261.65s382.182.61ms
1428.65s233.294.28ms
5263.77s379.102.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平均响应时间
043.29s2309.740.43ms
1213.82s467.682.13ms
244.71s2236.770.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_commitsync_binlog适用场景
11适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如充值消费系统
10适合数据安全性要求高,磁盘IO写能力支持业务不富余,允许备库落后或无复制
20或N(>0)适合数据安全性有要求,允许丢失一点事务日志,复制架构的延迟也能接受
00磁盘IO写能力有限,无复制或允许复制延迟稍微长点能接受,例如:日志性登记业务

当两个参数设置为(1,1)的时候,写入性能最差;sync_binlog=N (N>1) innodb_flush_log_at_trx_commit=2时,InnoDB的写操作才能达到最高性能。

2)测试结果如下:

innodb_flush_log_at_trx_commitsync_binlog执行时间平均TPS平均响应时间
0043.29s2309.740.43ms
10213.82s467.682.13ms
2044.71s2236.770.44ms
01217.92s317.423.14ms
11380.87s262.563.80ms
21206.53s484.182.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平均响应时间
fsync360.59s277.313.60ms
O_DSYNC538.53s185.695.38ms
O_DIRECT428.54s233.354.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平均响应时间
0379.29s263.643.79ms
1375.13s266.573.75ms
2352.78s283.463.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

参考资料:

  1. https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html
  2. https://blog.csdn.net/juan083/article/details/49489655
  3. https://blog.csdn.net/thundermeng/article/details/50448614
  4. https://www.cnblogs.com/geaozhang/p/7265261.html

转载请注明原文地址:https://blog.csdn.net/solihawk/article/details/122140035
文章会同步在公众号“牧羊人的方向”更新,感兴趣的可以关注公众号,谢谢!
在这里插入图片描述

  • 3
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值