以上两个参数是控制MySQL磁盘写入策略以及数据安全性的关键参数。
innodb_flush_log_at_trx_commit
sync_binlog
需要对数据安全性和写入性能根据业务场景进行权衡设置。
innodb_flush_log_at_trx_commit参数:
0【写性能最好,但当mysqld异常崩溃时会丢失数据】: 由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file,并调用文件系统的sync操作,将日志刷新到磁盘。
1【最安全不丢失数据,但写性能最低】:每次事务提交时,将存储引擎log buffer中的redo日志写入到log file,并调用文件系统的sync操作,将日志刷新到磁盘。
2【写性能一般,但当os异常崩溃或者断电时会丢失数据】:每次事务提交时,将存储引擎log buffer中的redo日志写入到log file,并由存储引擎的main_thread 每秒将日志刷新到磁盘。
sync_binlog参数:
0 :存储引擎不进行binlog的刷新到磁盘,而由操作系统的文件系统控制缓存刷新。
1:每提交一次事务,存储引擎调用文件系统的sync操作进行一次缓存的刷新,这种方式最安全,但性能较低。
n:当提交的日志组=n时,存储引擎调用文件系统的sync操作进行一次缓存的刷新。
总结:
(1)生产环境中,大部分时间情况下,这2个参数都设置为1。因为不允许有数据丢失出现。
(2)在大促或者业务高峰期时,在确保数据库和服务器不会出现异常宕机的情况,可以临时性的进行如下修改:set global sync_binlog=0; set global innodb_flush_log_at_trx_commit=2; 等待大促过后,再修改回双1模式。