注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL安全之sync_binlog&innodb_flush_log_at_trx_commit参数

MySQL 彭东稳 8719次浏览 已收录 0个评论
innodb_flush_log_at_trx_commit和sync_binlog两个参数是控制MySQL磁盘写入策略以及数据安全性的关键参数。本文从参数含义,性能,安全角度阐述两个参数为不同的值时对db性能,数据的影响。

一、参数意义

1)innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。

如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去。

如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意:由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

2)sync_binlog

sync_binlog参数是用来控制MySQL二进制日志刷盘时机,在MySQL 5.7.7之前,sync_binlog的默认值是0,在这种情况下,根据操作系统刷其他文件的机制来刷新二进制日志到磁盘,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。从MySQL 5.7.7版本开始开始,sync_binlog的默认值为1,它是最安全的选择,但会影响性能。

当sync_binlog=0时,二进制日志从不进行二进制日志FSYNC(同步)到磁盘上,而是依赖操作系统刷盘机制来刷新二进制日志到磁盘。如果存在主从复制,那么主库dump线程会在flush阶段后进行binlog传输到从库。

当sync_binlog=1时,在没有组提交特性之前,每个事务在commit时都必须要FSYNC二进制日志到磁盘;有了组提交特性后,就成了每次组提交时进行FSYNC刷盘。如果存在主从复制,主库dump线程会在sync阶段后进行binlog传输。

当sync_binlog>1时,binlog将在指定次数组提交后进行FSYNC刷盘。如果存在主从复制,主库dump线程会在flush阶段后进行binlog传输。

设置大于0时,此数目的二进制日志组提交定期同步到磁盘。如当sync_binlog=1时,每次组提交进行时都必须先FSYNC刷盘。因此,系统即使在发生意外的重新启动,二进制日志丢失的任何事务仅处于prepare状态,这会导致服务器的自动恢复例行程序回滚这些事务,可以保证二进制日志没有事务丢失,从库也没有这些事务,是最安全的选项,不过这对性能有负面影响。

当sync_binlog=0或sync_binlog大于1,事务被提交,而二进制尚未同步到磁盘。因此,在电源故障或操作系统崩溃时有可能服务器已承诺尚未同步一些事务到二进制日志。因此它是不可能执行例行程序恢复这些事务,他们将会丢失二进制日志。

根据上述描述,网上摘了一张图,可以方便查看。

MySQL安全之sync_binlog&innodb_flush_log_at_trx_commit参数

二、性能

两个参数在不同值时对db的纯写入的影响表现如下:

MySQL安全之sync_binlog&innodb_flush_log_at_trx_commit参数

测试场景1(TPS:41000)

测试场景2(TPS:33000)

测试场景3(TPS:26000)

测试场景4(TPS:33000)

由此可见,当两个参数设置为双1的时候,写入性能最差,sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时,(在当前模式下)MySQL的写操作才能达到最高性能。

三、安全

当innodb_flush_log_at_trx_commit和sync_binlog 都为 1 时是最安全的,在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得,双11 会导致频繁的io操作,因此该模式也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。

双1适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。双1模式下,当磁盘IO无法满足业务需求时 比如11.11 活动的压力。推荐的做法是 innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache,防止系统断电异常。

四、小结

系统性能和数据安全是业务系统高可用稳定的必要因素。我们对系统的优化需要寻找一个平衡点,合适的才是最好的,根据不同的业务场景需求,可以将两个参数做组合调整,以便是db系统的性能达到最优化。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)or分享 (0)
关于作者:

您必须 登录 才能发表评论!