控制 MySQL 磁盘写入策略 以及 数据安全性 的两个关键参数: innodb_flush_log_at_trx_commit 和 sync_binlog
参数:innodb_flush_log_at_trx_commit
①如果设置为0,log buffer将每秒一次地写入log file中,并且同时进行log file的flush(刷新到磁盘中)操作。此模式下,在事务提交时,不主动触发写入磁盘的操作;
②如果设置为1,此模式下,在每次事务提交时,mysql都会把log buffer的数据写入log file中,并且同时进行log file的flush(刷新到磁盘中)操作;
③如果设置为2,此模式下,在每次事务提交时,mysql都会把log buffer的数据写入log file中,但是不会同时进行log file的flush(刷新到磁盘中)操作,会每秒执行一次log file的flush(刷新到磁盘中)操作。
注意:由于进程调度策略问题,不能保证"每秒执行一次flush(刷新到磁盘中)操作"100%的"每一秒执行一次"。
参数:sync_binlog
①如果设置N=0,像操作系统刷新其他文件的机制一样,mysql不会同步到磁盘中去,而是依赖操作系统来刷新binary log。
②如果设置N>0,mysql在每写N次二进制日志binary log时,会调用fdatasync()函数将二进制日志binary log同步到磁盘中去。
注意:如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。
如图:
![](https://www.codeprj.com/image/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTEwODA5MC8yMDE5MTIvMTEwODA5MC0yMDE5MTIxMTE2MDk0MzI0Ni0xMDg2MDEwNDI0LmpwZw==.png)
性能:
①测试场景1:
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
②测试场景2:
innodb_flush_log_at_trx_commit=1
sync_binlog=1000
③测试场景3:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
④测试场景4:
innodb_flush_log_at_trx_commit=1
sync_binlog=1000
⑤测试场景5:
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
innodb_flush_log_at_trx_commit |
sync_binlog |
TPS |
1000 |
2 |
41000 |
1000 |
1 |
33000 |
1 |
1 |
26000 |
1000 |
1 |
33000 |
由此可见:
Ⅰ、当innodb_flush_log_at_trx_commit=1和sync_binlog=1时,写入操作性能最差;
Ⅱ、当innodb_flush_log_at_trx_commit=2和sync_binlog=2时,写入操作达到最高性能;
安全:
①当innodb_flush_log_at_trx_commit设置为0时,mysqld进程崩溃会导致上一秒所有事务数据丢失。
②当innodb_flush_log_at_trx_commit和sync_binlog都为1时最为安全,mysqld进程崩溃或者服务器crash的情况下,binary log只有可能最多丢失一个事务。
③当innodb_flush_log_at_trx_commit设置为2时,只有在服务器崩溃或断电情况下,上一秒所有事务数据才可能丢失。 |