1、参数innodb_flush_log_at_trx_commit的设置
innodb_flush_log_at_trx_commit参数用来平衡事务的ACID特性和高性能,默认设置1最为安全,当然写性能也最低,可以设置成0或2获得更好的写入性能,但可能在mysqld异常崩溃或者OS异常崩溃时丢失一秒的事务操作
a、innodb_flush_log_at_trx_commit=0
写性能最高,安全性最低,InnoDB log buffer 每隔一秒写一次到log file并且flush到磁盘,所以,mysqld进程异常崩溃后,会丢失1s的事务操作,1s的事务操作对于一些系统来说是不可忍受的,比如支付系统;
实测写性能6000 records/s 未开启binlog
b、innodb_flush_log_at_trx_commit=1
写性能最低,安全性最高,每次事务提交都会写InnoDB log buffer到日志文件并且立即刷日志文件到磁盘;
实测写性能20 records/s 未开启binlog
c、innodb_flush_log_at_trx_commit=2
写性能低,安全性低,每次事务提交会立即写InnoDB log buffer到日志文件,但每隔1s刷日志文件到磁盘,这种设置在OS异常崩溃,异常掉电后,依然有1s的事务操作没有最终持久化到磁盘文件;
实测写性能100 records/s 未开启binlog
2、参数sync_binlog的设置
该参数控制收集到多少次事务提交后,同步bin log到磁盘;
a、sync_binlog=0
永远不写bin log到磁盘;
b、默认值sync_binlog=1
所有的事务在提交前都同步到bin log,设置为1保证了所有的事务操作不会在 bin log中丢失,当事务执行失败,系统会根据重做日志回滚这些事务操作,这是最安全的设置,也由于频繁的写bin log磁盘文件,导致写性能非常低;
c、sync_binlog>1(N)
系统在收集到N个事务操作时,把事务操作写到bin log磁盘文件,在系统异常崩溃时,N-1个事务已经提交,但没有写bin log,导致bin log和InnoDB存储文件的不一致性,bin log中丢失的事务永远不可能被恢复,会给恢复和主从复制带来问题;当然,降低了写bin log的频率,写性能可大幅提高;
世间没有绝对完美的事,性能和安全只能根据系统特性权衡了
innodb_flush_log_at_trx_commit=1 && sync_binlog=1 最安全
当然,系统如果允许丢失1s的事务操作,也可设置为它值获得更好的写性能