MySQL -- flush

脏页 + 干净页

脏页：内存数据页与磁盘数据页内容不一致
干净页：内存数据页与磁盘数据页内容一致
flush：将内存中的脏页写入磁盘
flush – 刷脏页；purge – 清undolog；merge – 应用change buffer

flush过程

触发flush

redolog写满

当InnoDB的redolog写满，系统会停止所有的更新操作，推进checkpoint
把checkpoint从CP推进到CP’，需要将两点之间的日志（绿色），所对应的所有脏页都flush到磁盘上
然后write pos到CP’之间（红色+绿色）可以再写入redolog

性能影响

InnoDB应该尽量避免，此时所有更新都会被堵住，更新数（写性能）跌为0

内存不足

当需要新的内存页，而内存不够用时，就需要淘汰一些内存数据页（LRU）
如果淘汰的是脏页，就需要先将脏页flush到磁盘
- 该过程不会动redolog，因为redolog在重放的时候
- 如果一个数据页已经flush过，会识别出来并跳过

性能影响

这种情况是常态，InnoDB使用buffer pool管理内存
buffer pool中内存页有3种状态
- 没有被使用
- 已被使用且为干净页
- 已被使用且为脏页
InnoDB的策略是尽量使用内存，对于一个长期运行的库来说，未被使用的内存页很少
当要读入的数据页没有在内存中，必须到buffer pool中申请一个内存页，采用LRU策略淘汰内存页
- 如果淘汰的是干净页，直接释放并复用
- 如果淘汰的是脏页，必须先将脏页flush到磁盘上，变成干净页后才能复用
如果一个查询要淘汰的脏页太多，会导致查询的响应时间明显变长

其他情况

系统空闲
正常关闭

脏页控制策略

主机IO能力

需要配置主机的IO能力，InnoDB才知道全力刷脏页时，可以刷多快
控制参数innodb_io_capacity，建议设置为磁盘的IOPS（可以通过fio测试）
如果该值设置过小，InnoDB会认为主机的IO能力很差，从而控制刷脏页的速度，甚至低于脏页的生成速度
- 造成脏页累积，影响查询和更新性能

mysql> SHOW VARIABLES LIKE '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 2000  |
| innodb_io_capacity_max | 4000  |
+------------------------+-------+

$ fio -filename=fio.txt -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=2048M -numjobs=10 -runtime=10 -group_reporting -name=mytest
...
Jobs: 10 (f=10): [m(10)][90.9%][r=155MiB/s,w=155MiB/s][r=9907,w=9896 IOPS][eta 00m:01s]
mytest: (groupid=0, jobs=10): err= 0: pid=4867: Thu Jan 31 11:26:20 2019
   read: IOPS=11.2k, BW=175MiB/s (184MB/s)(1754MiB/10002msec)
   ...
   bw (  KiB/s): min=14285, max=24256, per=10.01%, avg=17978.35, stdev=2020.97, samples=190
   iops        : min=  892, max= 1516, avg=1123.32, stdev=126.33, samples=190
  write: IOPS=11.2k, BW=176MiB/s (184MB/s)(1756MiB/10002msec)
    ...
   bw (  KiB/s): min=14211, max=24480, per=10.01%, avg=17990.18, stdev=2278.29, samples=190
   iops        : min=  888, max= 1530, avg=1124.08, stdev=142.44, samples=190

Run status group 0 (all jobs):
   READ: bw=175MiB/s (184MB/s), 175MiB/s-175MiB/s (184MB/s-184MB/s), io=1754MiB (1839MB), run=10002-10002msec
  WRITE: bw=176MiB/s (184MB/s), 176MiB/s-176MiB/s (184MB/s-184MB/s), io=1756MiB (1841MB), run=10002-10002msec

flush speed

刷脏页速度慢的后果
- 内存脏页太多
- redolog写满
因素：_脏页比例_ + redolog写盘速度
控制参数innodb_max_dirty_pages_pct：脏页比例上限
F1(M)：InnoDB会根据当前的脏页比例M，计算出一个[0,100]的值，伪代码如下所示
F2(N)：N越大，F2越大
- InnoDB每次写入的日志都有一个序号
- N：write pos对应的序号与checkpoint对应的序号之间的差值
算法
- R = max(F1(M) , F2(N))
- flush speed = innodb_io_capacity * R%

mysql> SHOW VARIABLES LIKE '%innodb_max_dirty_pages_pct%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_max_dirty_pages_pct     | 75    |
| innodb_max_dirty_pages_pct_lwm | 0     |
+--------------------------------+-------+

F1(M){
    if M>=innodb_max_dirty_pages_pct then
        return 100;
    return M/innodb_max_dirty_pages_pct * 100;
}

脏页比例

平时要多关注脏页比例：Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
不要经常接近innodb_max_dirty_pages_pct

mysql> SHOW GLOBAL STATUS LIKE '%Innodb_buffer_pool_pages_%';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| Innodb_buffer_pool_pages_data    | 633395     |
| Innodb_buffer_pool_pages_dirty   | 99         |
| Innodb_buffer_pool_pages_flushed | 1286891803 |
| Innodb_buffer_pool_pages_free    | 8191       |
| Innodb_buffer_pool_pages_misc    | 13774      |
| Innodb_buffer_pool_pages_total   | 655360     |
+----------------------------------+------------+

# 1. Innodb_buffer_pool_pages_data
#   The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
# 2. Innodb_buffer_pool_pages_dirty
#   The current number of dirty pages in the InnoDB buffer pool.
# 3. Innodb_buffer_pool_pages_flushed
#   The number of requests to flush pages from the InnoDB buffer pool.
# 4. Innodb_buffer_pool_pages_free
#   The number of free pages in the InnoDB buffer pool.
# 5. Innodb_buffer_pool_pages_misc
#   The number of pages in the InnoDB buffer pool that are busy because they have been allocated for administrative overhead, such as row locks or the adaptive hash index.
#   Innodb_buffer_pool_pages_misc = Innodb_buffer_pool_pages_total − Innodb_buffer_pool_pages_free − Innodb_buffer_pool_pages_data.
#   13774 = 655360 - 8191 - 633395
# 6. Innodb_buffer_pool_pages_total
#   The total size of the InnoDB buffer pool.

# Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total = 0.015%

flush neighbor

mysql> SHOW VARIABLES LIKE '%innodb_flush_neighbors%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_flush_neighbors | 0     |
+------------------------+-------+