😄日志刷盘策略调优

转载自： MySQL 的 “双 1 设置”- 数据安全的关键参数（案例分享）
作者： 散尽浮华

mysql 的 “双 1 验证” 指的是 innodb_flush_log_at_trx_commit 和 sync_binlog 两个参数设置，这两个是是控制 MySQL 磁盘写入策略以及数据安全性的关键参数。下面从参数含义，性能，安全角度阐述两个参数为不同的值时对 db 性能，数据的影响。

一、参数意义

innodb_flush_log_at_trx_commit
如果 innodb_flush_log_at_trx_commit 设置为 0：log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush (刷到磁盘) 操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作；
如果 innodb_flush_log_at_trx_commit 设置为 1：每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且 flush (刷到磁盘) 中去；
如果 innodb_flush_log_at_trx_commit 设置为 2：每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，但是 flush (刷到磁盘) 操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush (刷到磁盘) 操作。
注意：由于进程调度策略问题，这个 “每秒执行一次 flush (刷到磁盘) 操作” 并不是保证 100% 的 “每秒”。

sync_binlog
sync_binlog 的默认值是 0，像操作系统刷其他文件的机制一样，MySQL 不会同步到磁盘中去而是依赖操作系统来刷新 binary log。
当 sync_binlog =N (N>0) ，MySQL 在每写 N 次 二进制日志 binary log 时，会使用 fdatasync () 函数将它的写二进制日志 binary log 同步到磁盘中去。
注意：如果启用了 autocommit，那么每一个语句 statement 就会有一次写操作；否则每个事务对应一个写操作。

二、性能

两个参数在不同值时对 db 的纯写入的影响表现如下：
测试场景 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
在以上 5 个场景下的 TPS 分别为:
场景 1 41000
场景 2 33000
场景 3 26000
场景 4 33000
由此可见，当两个参数设置为双 1 的时候，写入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下) MySQL 的写操作才能达到最高性能。

三、安全

当 innodb_flush_log_at_trx_commit 和 sync_binlog 都为 1 时是最安全的，在 mysqld 服务崩溃或者服务器主机 crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双 11 会导致频繁的 io 操作，因此该模式也是最慢的一种方式。
当 innodb_flush_log_at_trx_commit 设置为 0，mysqld 进程的崩溃会导致上一秒钟所有事务数据的丢失。
当 innodb_flush_log_at_trx_commit 设置为 2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
“双 1 设置” 适合数据安全性要求非常高，而且磁盘 IO 写能力足够支持业务，比如订单，交易，充值，支付消费系统。双 1 模式下，当磁盘 IO 无法满足业务需求时 比如 11.11 活动的压力。推荐的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N 为 500 或 1000) 且使用带蓄电池后备电源的缓存 cache，防止系统断电异常。

四、小结

系统性能和数据安全是业务系统高可用稳定的必要因素。我们在对系统的优化需要寻找一个平衡点，合适的才是最好的，根据不同的业务场景需求，可以将两个参数做组合调整，以便是 db 系统的性能达到最优化。
案例分享 1：一条 insert 语句的执行，耗时 40ms 原因剖析
背景：一个简单的带有主键的 insert 语句，执行起来居然要耗时 40ms ，实在是难以忍受！排查分析过程如下：

因此需要关注的是数据从插入落地的 IO 中间都干了什么？

一、MySQL 的文件

首先简单介绍一下 MySQL 的数据文件，MySQL 数据库包含如下几种文件类型：
1）数据文件 (datafile)
存放表中的具体数据的文件。
2）数据字典
记录数据库中所有 innodb 表的信息。
3）重做日志 (redolog)
记录数据库变更记录的文件，用于系统异常 crash (掉电) 后的恢复操作，可以配置多个 (配置这个参数 inodb_log_files_in_group) 比如 ib_logfile0、 ib_logfile1。
4）回滚日志 (undolog)
也存在于 mysql 的 ibdata 文件，用户记录事务的回滚操作。注在 mysql5.6 以上版本可以拆开出来，单独文件夹存在。
5）归档日志 (binlog)
事务提交之后，记录到归档日志中。
6）中继日志 (relaylog)
从 master 获取到 slave 的中转日志文件，sql_thread 则会应用 relay log 并重放于从机器。
7）其他日志 slowlolg, errorlog, querylog
这里慢日志也经常用。可以结合 pt-query-digest 工具和 anemometer 一起展示出来。
对于以上文件的 IO 访问顺序可以分为顺序访问 比如 binlog ，redolog ，relay log 是顺序读写，datafile，ibdata file 是随机读写，这些 IO 访问的特点决定了在 os 配置磁盘信息时候，如何考虑分区 ，比如顺序写可以的 log 可以放到 SAS 盘 ，随机读写的数据文件可以放到 ssd 或者 fio 高性能的存储。

二、写操作

为了保证数据写入操作的安全性，数据库系统设置了 undo，redo 保护机制，避免因为 os 或者数据库系统异常导致的数据丢失或者不一致的异常情况发生。
1）先写 undo log。
2）在内存更新数据，这步操作就在内存中形成了脏页，如果脏页过多，checkpoint 机制进行刷新，innodb_max_dirty_pages_pct 决定了刷新脏页比例。innodb_io_capacity 参数可以动态调整刷新脏页的数量，innodb_lru_scan_depth 这个参数决定了刷新每个 innodb_buffer_pool 的脏页数量。
3）记录变更到 redo log，prepare 这里会写事务 id。innodb_flush_log_at_trx_commit 决定了事务的刷盘方式。为 0 时，log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush (刷到磁盘) 操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。为 1，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且 flush (刷到磁盘) 中去。为 2，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file. 但是 flush (刷到磁盘) 操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush (刷到磁盘) 操作。
4） 写入 binlog 这里会写入一个事务 id 这里有个 sync_binlog 参数决定多个事务进行一次性提交。
5）redo log 第二阶段，这里会进行判断前 2 步是否成功，成功则默认 commit，否则 rollback。刷入磁盘操作。这里是先从脏页数据刷入到内存 2M 大小的 doublewrite buffer，然后是一次性从内存的 doublewrite buffer 刷新到共享表空间的 doublewrite buffer，这里产生了一次 IO。然后从内存的内存的 doublewrite buffer 刷新 2m 数据到磁盘的 ibd 文件中，这里需要发生 128 次 io。然后校验，如果不一致，就由共享表空间的副本进行修复。这里有个参数 innodb_flush_method 决定了数据刷新直接刷新到磁盘，绕过 os cache。
6）返回给 client。
如果有 slave，第 4 步之后经过 slave 服务线程 io_thread 写到从库的 relay log ，再由 sql thread 应用 relay log 到从库中。

三、关于性能

写 undo redo log ，binlog 的过程中都是顺序写，都会很快的完成，随机写操作，inset_buffer 功能。
对于非聚集类索引的插入和更新操作 (5.5 版本及以上支持 Update/Delete/Purge 等操作的 buffer 功能)，不是每一次都直接插入到索引页中，而是先插入到内存中。具体做法是：如果该索引页在缓冲池中，直接插入；否则，先将其放入插入缓冲区中，再以一定的频率和索引页合并，就可以将同一个索引页中的多个插入合并到一个 IO 操作中，改随机写为顺序写，大大提高写性能。
关于数据安全，这是数据库写入的重点？
1，2，3 过程失败就是事务失败，因为此时还未写入磁盘，对磁盘中的数据无影响，返回事务失败给 client，从库也不会受到影响。 4，5 过程失败的时候或者已经将写成功返回给客户，可以根据 redo log 的记录来进行恢复，如果出现部分写失效请参考《double write》。
MySQL 的写 redo log 的第一个阶段会把所有需要做的操作做完，记录数据变更，第二阶段的工作比较简单 ，只做事务提交确认。如果写入 binlog 成功，而第二阶段失败，MySQL 启动时也会将事务进行重做，最终更新到磁盘中。MySQL 5.5 + 的 smei sync 可以更好的保障主从的事务一致性。

四、文件访问方式

IO 访问的方式分为两种顺序读写和随机读写， 在 MySQL 的 io 过程中可以以此来将数据库文件分类。
顺序读写：重做日志 ib_logfile*，binlog file。
随机读写：innodb 表数据文件，ibdata 文件。
根据系统的访问类型，对硬件做如下分类：读多（SSD+RAID）、写多 FIO (flashcache)、容量密集（fio + flashcache）。
由于随机 io 会严重降低系统的性能，在当前的硬件水平下，可以考虑选择奖数据库服务器配置 ssd/fusionio。

五、影响 IO 的参数和策略

影响 mysql io 的参数有很多个，这里罗列几个重要的参数。
innodb_buffer_pool_size
该参数控制 innodb 缓存大小，用于缓存应用访问的数据，推荐配置为系统可用内存的 80%。
binlog_cache_size
该参数控制二进制日志缓冲大小，当事务还没有提交时，事务日志存放于 cache，当遇到大事务 cache 不够用的时，mysql 会把 uncommitted 的部分写入临时文件，等到 committed 的时候才会写入正式的持久化日志文件。
innodb_max_dirty_pages_pct
该参数可以直接控制 Dirty Page 在 BP 中所占的比率，当 dirty page 达到了该参数的阈值，就会触发 MySQL 系统刷新数据到磁盘。
innodb_flush_log_at_trx_commit
该参数确定日志文件何时 write、flush。
为 0，log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush (刷到磁盘) 操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
为 1，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且 flush (刷到磁盘) 中去.
为 2，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file. 但是 flush (刷到磁盘) 操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush (刷到磁盘) 操作。
注意：由于进程调度策略问题，这个 “每秒执行一次 flush (刷到磁盘) 操作” 并不是保证 100% 的 “每秒”。
sync_binlog
sync_binlog 的默认值是 0，像操作系统刷其他文件的机制一样，MySQL 不会同步到磁盘中去而是依赖操作系统来刷新 binary log。
当 sync_binlog =N (N>0) ，MySQL 在每写 N 次 二进制日志 binary log 时，会使用 fdatasync () 函数将它的写二进制日志 binary log 同步到磁盘中去。
innodb_flush_method
该参数控制日志或数据文件如何 write、flush。可选的值为 fsync，o_dsync，o_direct，littlesync，nosync。
数据库的 I/O 是一个很复杂和细致的知识层面，涉及数据库层和 OS 层面的 IO 写入策略，也和硬件的配置有关。

案例分享 2: 同一条 sql 语句，有时插入块，有时插入慢原因剖析

背景：同一条 sql ，有时插入时间几毫秒，有时插入时间却是几十毫秒！为什么呢？ 分析过程如下：
Sql 角度：简单 insert
表角度: 一个主键
系统参数角度：
开启了双 1 策略。
也就意味着每次事物就会有刷新磁盘
关闭双 1 ，设置为 0 100 ，或者 2 100 ，会极大提升性能。这是因为不刷硬盘了，但不能解决为什么时快时慢问题。
操作系统角度
使用 “iostat -xmd 1“ 命令看磁盘使用情况

以上看出磁盘明显不够快，读写 0.15M 就使用了 7%

上面可以看出，来个顺序文件拷贝操作，30M 使用就使用了 100%，离散读写更慢了！
使用 “sar -B 1“ 命令可以查看页面交换

pgpgin/s: 表示每秒从磁盘或 SWAP 置换到内存的字节数 (KB)
pgpgout/s: 表示每秒从内存置换到磁盘或 SWAP 的字节数 (KB)
fault/s: 每秒钟系统产生的缺页数，即主缺页与次缺页之和 (major + minor)
majflt/s: 每秒钟产生的主缺页数.
pgfree/s: 每秒被放入空闲队列中的页个数
pgscank/s: 每秒被 kswapd 扫描的页个数
pgscand/s: 每秒直接被扫描的页个数
pgsteal/s: 每秒钟从 cache 中被清除来满足内存需要的页个数
% vmeff: 每秒清除的页 (pgsteal) 占总扫描页 (pgscank+pgscand) 的百分比
以上表示内存和 swap 进行了频繁的数据交换！

那个进程在使用 swap 呢？
下面截图中命令是 for i in $(ls /proc | grep “^[0-9]” | awk ‘$0>100’); do awk ‘/Swap:/{a=a+$2}END{print ‘“$i”‘,a/1024”M”}’ /proc/$i/smaps;done| sort -k2nr | head

在经过几个小时后 ，mysql 使用 swap 由 88M 变成了 104M ，这说明一直在使用和增加的。

问题基本定位
1）首先是磁盘性能不高，顺序写才 30M , 离散写会降低 10 倍
2）其次是 mysql 又使用了 swap 空间，这就使得性能更差
3） Mysql 开启了双 1 验证，就会等待数据刷磁盘，
磁盘使用频率不稳定，导致了 mysql 的插入时间会时快时慢

如何解决？
1）减少 mysql 使用 swap 方式。即把 swapness 设置为 1。
即执行 “sysctl vm.swappiness=1”, 并且在 /etc/sysctl.conf 文件中也要设置为 1；
2）降低内存，比如设置 innodb_buffer_pool_size =4G，原来设置的是 6G , 这样可以节约一部分内存空间；
3）开启 innodb_numa_interleave = ON 来操作 numa；
4）更换 SSD 或者不用开启双 1，改成 2 100
只调整操作系统参数，不更换硬件，依然开启双一，重启 mysql 之后呢？

可以看到 mysql 已经不再使用 swap 空间了。但是因为双一参数的使用，每次事物都会刷磁盘，而这个机械磁盘的性能在随机读写的情况下不稳定。会依然存在时快时慢的问题。