Mysql性能优化之配置I/O行为

Mysql一些配置项会影响数据库将数据同步到磁盘和执行恢复的方式。这会涉及IO操作，因此会极大的影响性能。这些选项还代表了性能和数据之前的权衡。一般来说确保数据立即且一致的写入磁盘的代价是很高的。

本文主要介绍三个参数

• innodb_log_buffer_size
• innodb_flush_log_at_trx_commit
• sync_binlog

innodb事务日志redo log,binlog逻辑过程如下:

1. 事务写入redo log buffer中；
2. 将redo log buffer刷新到redo log中，不过会先写一个TX PREPARE标记；
3. 写binlog
4. 在redo log中写入TX COMMIT标记；
5. 将写binlog成功的标记写入redo log。

日志缓存区
innodb_log_buffer_size

InnoDB 修改数据时会将修改记录写入日志缓冲区，并将其保存在内存中。当满足一下条件时InnoDB会将缓冲区刷新到磁盘日志文件中。 如有大型事务，增加缓冲区有助于减少IO操作。（三个条件先满足为准）

• 缓冲区满了
• 事务提交时
• 每秒一次

通常来说缓存区不需要设置过大。建议范围1~8MB（默认1MB）

刷新日志缓存区
innodb_flush_log_at_trx_commit

日志缓冲区必须被刷新到持久存储中，以确保提交的事务完全持久。可以更改innodb_flush_log_at_trx_commit来控制日志缓冲区的刷新位置和刷新频率

innodb_flush_log_at_trx_commit 参数解析

值	描述
0	每秒定时将日志缓冲区写入日志文件，并刷新日志文件，但在事务提交时不做任何操作
1	每次事务提交时，将日志缓冲区写入日志文件，并将其刷新到持久存储中。这是默认的，也是最安全的设置；它能保证不会丢失任何已提交的事务
2	每次事务提交时都将日志缓冲区写入日志文件，单不执行刷新。InnoDB按计划每秒刷新一次

设置2与0设置最重要的区别是，如果mysql进程奔溃，设置为2不会丢失任何事务。但是，如果整个服务器崩溃或断电，仍然可能丢失事务。

将日志缓冲区写入日志文件和将日志刷新到持久存储之间的区别很重要。在大多数操作系统中，将缓冲区写入日志只是将数据从InnoDB的内存缓冲区移动到操作系统的缓存中，依然还是在内存中。它实际上不会将数据写入持久存储。因此，如果发生奔溃或断电，设置为0和2通常会导致最多1秒的数据丢失。因为数据只存在于操作系统缓存中。

二进制日志写盘
sync_binlog

改参数控制Mysql如何将二进制日志刷新到磁盘，默认值是1，意味着Mysql将执行刷新并保持二进制日志的持久性和安全性。强烈推荐将其设置为1，不建议设置为其他值，如果不将sync_binlog设置为1，发生奔溃可能会导致二进制日志与事务数据不同步。

# 设置
SET global innodb_flush_log_at_trx_commit = 2;
# 查看
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';