MySQL45讲之IO性能提升 - flowers

前言

本文介绍 MySQL 的 binlog 和 redo log 写入机制和刷盘策略，以及如何提升 MySQL 的 IO 性能。

binlog 的写入机制

binlog 的写入流程是：

先将日志写入到 binlog cache 中，然后再 write page cache 到磁盘（文件系统向内核申请的一块内存空间，当 MySQL 进程重启时，不影响这块空间），最后 fsync 到磁盘。

每个事务对应的 binlog 是不能拆开的，所以必须一次性保存。每个线程有自己的 binlog cache，通过 binlog_cache_size 参数设置，但是多个线程共享同一个 binlog 文件。如果 binlog cache 存不下，就会存到磁盘中。

binlog 的刷盘策略，即 write 和 fsync 的时机，是由参数 sync_binlog 控制的：

1. sync_binlog=0的时候，表示每次提交事务都只write，不fsync；

2. sync_binlog=1的时候，表示每次提交事务都会执行fsync；

3. sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

一般不会将 sync_binlog 设置为 0，那样可能会丢失大量的日志。为了提高 IO 性能，可能会将 sync_binlog 设置为 100 ~ 1000 的值，这样只会丢失最近的 N 条日志。

redo log 的写入机制

redo log 的写入流程：

先将日志记录在 redo log buffer 中，然后再 write page cache，最后 fsync 到磁盘。并且，有一个后台线程，每隔一段时间就会将 redo log buffer 同步到磁盘。（可能事务未提交，但也同步到磁盘的情况）

redo log 的刷盘策略，由 InnoDB 提供了 innodb_flush_log_at_trx_commit 参数控制，它有三种可能取值：

1. 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;

2. 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；

3. 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。

注意，如果把 innodb_flush_log_at_trx_commit 设置为 1，那么 redo log 在两阶段提交的第一个 prepare 阶段就会刷盘，第二个 commit 阶段只会 write page cache。

双“1”配置

MySQL 的双“1”配置指的就是 innodb_flush_log_at_trx_commit=1 和 sync_binlog=1 。即一次事务提交，需要等待两次 fsync。

组提交（group commit）

这时候，你可能有一个疑问，这意味着我从MySQL看到的TPS是每秒两万的话，每秒就会写四万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的TPS？

解释这个问题，就要用到组提交 group commit 机制了。

这里，我需要先和你介绍日志逻辑序列号 LSN（log sequence number）的概念。LSN是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log， LSN的值就会加上length。

组提交举例：

对于多个并发事务，他们都写完了 redo log buffer，准备持久化到磁盘，那么会从这些事务中选择一个 leader，然后取他们中最大的 LSN，让这个 leader 带着最大的 LSN 取写盘，这样小于 LSN 的日志就都写到了磁盘，也就完成了一个组提交，其他事务直接返回即可。

所以，一次组提交中事务越多，可以节省的 IOPS 也就越多。

MySQL 在进行两阶段提交时，redo log 和 binlog 都是可以使用组提交的。此外，为了提高 binlog 使用组提交的效果，可以设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现：

1. binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;

2. binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。

注意，如果 sync_binlog 设置为 0，那么 binlog_group_commit_sync_delay 会进行延迟，但不会 fsync。

MySQL的IO性能优化

综合所述，可以通过以下方法进行优化：

1. 将 sync_binlog 设置为大于 1 的（一般为 100 ~ 1000），这样可能会丢失最近的 N 条日志

2. 将 innodb_flush_log_at_trx_commit 设置为 2，主机断电会导致丢失数据

3. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count参数，提升组优化效果，减少写盘次数，但是会增加事务响应时间，也可能有丢失日志的风险

参考

• [1] MySQL是怎么保证数据不丢的