参数说明
innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，不同参数设置对磁盘IO影响不同。
参数innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit=0：每秒一次将Log Buffer中数据写入到Log File中，并且Flush到磁盘。事务提交不会主动触发写磁盘操作。 innodb_flush_log_at_trx_commit=1：每次事务提交时将Log Buffer数据写入到Log File中，并且Flush到磁盘。
innodb_flush_log_at_trx_commit=2：每次事务提交时将Log Buffer数据写入到Log File中，但不立即Flush到磁盘，MySQL会每秒一次刷新到磁盘。
由于进程调度问题，每条一次操作不能保证每一秒都执行一次。 当innodb_flush_log_at_trx_commit=0时，最近一秒的事务日志存在MySQL的Log Buffer中，无论时MySQL实例停止还是MySQL服务器宕机，都会导致最近一秒的事务日志丢失。 当innodb_flush_log_at_trx_commit=1时，最近一秒的事务日志存在操作系统的文件缓存中，MySQL实例停止不会导致事务日志丢失，但MySQL服务器宕机会导致最近一秒事务日志丢失。
上述的一秒一次刷新，取决于参数innodb_flush_log_at_timeout默认值为1，DDL或其他InnoDB内部操作并不受参数innodb_flush_log_at_trx_commit的限。
图片来源于：https://www.h3399.cn/201809/614170.html
参数sync_binlog：
sync_binlog=0：每次事务提交后，将Binlog Cache中的数据写入到Binlog文件，但不立即刷新到磁盘。由文件系统(file system)决定何时刷新到磁盘中。 sync_binlog=N：每N次事务提交后，将Binlog Cache中的数据写入到Binlog文件,调用fdatasync()函数将数据刷新到磁盘中。 当sync_binlog=0(默认设置)时，不会执行强制刷盘指令，性能最好同时风险最高。 当sync_binlog=N时，当MySQL服务器宕机后，会导致最近N个事务的BINLOG丢失。
Mysql配置参数sync_binlog说明
MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。
默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。
对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。 所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。
BINLOG和REDO/UNDO LOG的区别
1、处理层次不同，REDO/UNDO LOG由Innodb存储引擎处理，而BINLOG由MySQL 服务层处理。 2、记录内容不同，REDO/UNDO LOG记录的数据页的修改情况，REDO LOG采用物理日志+逻辑日志的方式存储，UNDO LOG采用逻辑日志方式存储，用于保证数据一致性；而BINLOG日志记录的事务操作的内容，用于主从复制。 3、记录时机不同，REDO/UNDO LOG在事务的执行过程中不断生成和写入，而BINLOG在事务最终COMMIT前写入。 4、涉及到数据更新的SELECT操作会被记录到BINLOG中(基于语句格式复制)。 5、BINLOG刷新到磁盘的行为由参数sync_binlog决定，而REDO LOG写入磁盘的行为受参数innodb_flush_log_at_trx_commit的影响。 6、可以通过会话级参数SQL_LOG_BIN来设置某事务不生成BINLOG,但不能通过参数控制是否生成事务日志。
某台从库存在严重的复制延迟，调整参数后性能变化：
1、主库(MySQL 5.5版本)凌晨2点开始清理历史数据。 2、从库(MySQL 5.7版本)开启多线程复制，但由于从库做多源复制且单线程复制(主库MySQL 5.5)，磁盘使用率飙升至80%，出现复制延迟。 3、从库在08:35调整参数为双0，CPU使用率从3%提升至7%，磁盘使用率从75%降至8%，磁盘写次数从8500下降至2200，复制延迟开始下降。 4、从库在08:51复制无延迟，积压时间被消费完，CPU使用率恢复至1%，磁盘使用率从8%降至1%，磁盘写次数从2200下降至300。 5、从库在09:01调整参数为双1，CPU使用率无变化，磁盘使用率从1%升至8%，磁盘写次数从300升职2200。
IOPS测试
创建测试表：
## 创建测试表 CREATE TABLE `tb003` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `C1` int(11) DEFAULT NULL, `DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
创建测试脚本：
#!/bin/bash for i in $(seq 1 10000) do /export/servers/mysql/bin/mysql --host='127.0.0.1' --port=3358 --user='root' --password="root_psw" -e 'insert into db001.tb003(c1)values(0);' 1>/dev/nul 2>&1 done
运行测试脚本发现，每秒执行230个左右事务，使用iostat -dxk 1查看发现服务器每秒写1380，事务提交次数和每秒IO写次数的比约为1:6。
Comments | NOTHING