InnoDB存储引擎

首先以一张图简单展示 InnoDB 的存储引擎的体系架构.

 从图中可见, InnoDB 存储引擎有多个内存块,这些内存块组成了一个大的内存池,主要负责如下工作:

  • 维护所有进程/线程需要访问的多个内部数据结构

  • 缓存磁盘上的数据, 方便快速读取, 同时在对磁盘文件修改之前进行缓存

  • 重做日志(redo log)缓冲

后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存的是最新数据;将已修改数据文件刷新到磁盘文件;保证数据库发生异常时 InnoDB 能恢复到正常运行 的状态

后台线程

InnoDB 使用的是多线程模型, 其后台有多个不同的线程负责处理不同的任务

1. Master Thread

这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括赃页的刷新、合并插入缓冲、UNDO 页的回收等.

2. IO Thread

在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO 请求的回调.

可以通过命令来观察 InnoDB 中的 IO Thread:

复制代码
mysql> show engine innodb statusG
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2017-07-22 00:16:26 7f4a37451700 INNODB MONITOR OUTPUT
=====================================
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
188 OS file reads, 27180 OS file writes, 26031 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
复制代码

 可以看到, InnoDB 共有10个 IO Thread, 分别是 4个 write、4个 read、1个 insert buffer和1个 log thread.

 3. Perge Thread

事务被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用比分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收InnoDB 引擎默认设置为1个 Purge Thread:

复制代码
mysql> SHOW VARIABLES LIKE "innodb_purge_threads";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 1     |
+----------------------+-------+
1 row in set (0.00 sec)
复制代码

4. Page Cleaner Thread

 Page Cleaner Thread 是新引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成,这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞

内存

1. 缓冲池

InnoDB 存储引擎是基于磁盘存储的,其中的记录按照页的方式进行管理,由于 CPU 速度和磁盘速度之间的鸿沟, InnoDB 引擎使用缓冲池技术来提高数据库的整体性能.

缓冲池简单来说就是一块内存区域.在数据库中进行读取页的操作,首先将从磁盘读到的页存放在缓冲池中,下一次读取相同的页时,首先判断该页是不是在缓冲池中,若在,称该页在缓冲池中被命中,直接读取该页.否则,读取磁盘上的页.

对于数据库中页的修改操作,首先修改在缓冲池中页,然后再以一定的频率刷新到磁盘,并不是每次页发生改变就刷新回磁盘.

缓冲池的大小直接影响数据库的整体性能,对于 InnoDB 存储引擎而言,缓冲池配置通过参数 innodb_buffer_pool_size 来设置. 下面显示本机虚拟机上一台 MySQL 数据库配置:

复制代码
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.00 sec)
复制代码

缓冲池中缓存的数据页类型有:索引页、数据页、 undo 页、插入缓冲、自适应哈希索引、 InnoDB 的锁信息、数据字典信息等.索引页和数据页占缓冲池的很大一部分.下图显示 InnoDB 存储引擎总内存的结构情况.

 

2. 重做日志缓冲

 InnoDB 存储引擎先将重做日志信息放入这个缓冲区,然后以一定频率将其刷新到重做日志文件.重做日志文件一般不需要设置得很大,因为在下列三种情况下重做日志缓冲中的内容会刷新到磁盘的重做日志文件中.

  1. Master Thread 每一秒将重做日志缓冲刷新到重做日志文件

  2. 每个事物提交时会将重做日志缓冲刷新到重做日志文件

  3. 当重做日志缓冲剩余空间小于1/2时,重做日志缓冲刷新到重做日志文件

3. 额外的内存池

在 InnoDB 存储引擎中, 对一些数据结构本身的内存进行分配时,需要从额外的内存池中进行申请.例如,分配了缓冲池,但是每个缓冲池中的帧缓冲还有对应的缓冲控制对象,这些对象记录以一些诸如 LRU, 锁,等待等信息,而这个对象的内存需要从额外的内存池中申请.

REDO和UNDO

01 – Undo Log
Undo Log 是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制(简称：MVCC)。

- 事务的原子性(Atomicity)
事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。

- 原理
Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
除了可以保证事务的原子性，Undo Log也可以用来辅助完成事务的持久化。

- 事务的持久性(Durability)
事务一旦完成，该事务对数据库所做的所有修改都会持久的保存到数据库中。为了保证持久性，数据库系统会将修改后的数据完全的记录到持久的存储上。

- 用Undo Log实现原子性和持久化的事务的简化过程
假设有A、B两个数据，值分别为1,2。
A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录B=2到undo log.
E.修改B=4.
F.将undo log写到磁盘。
G.将数据写到磁盘。
H.事务提交
这里有一个隐含的前提条件：‘数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘’。

之所以能同时保证原子性和持久化，是因为以下特点：
A. 更新数据前记录Undo log。
B. 为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。
C. Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的,可以用来回滚事务。

D. 如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。
如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一
种机制来实现持久化，即Redo Log.

02 – Redo Log
- 原理
和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，
不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

- Undo + Redo事务的简化过程
假设有A、B两个数据，值分别为1,2.
A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.
E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。
I.事务提交

- Undo + Redo事务的特点
A. 为了保证持久性，必须在事务提交前将Redo Log持久化。
B. 数据不需要在事务提交前写入磁盘，而是缓存在内存中。
C. Redo Log保证事务的持久性。
D. Undo Log保证事务的原子性。
E. 有一个隐含的特点，数据必须要晚于redo log写入持久存储。

- IO性能

Undo + Redo的设计主要考虑的是提升IO性能。虽说通过缓存数据，减少了写数据的IO. 但是却引入了新的IO，即写Redo Log的IO。如果Redo Log的IO性能不好，就不能起到提高性能的目的。

为了保证Redo Log能够有比较好的IO性能，InnoDB 的 Redo Log的设计有以下几个特点：

A. 尽量保持Redo Log存储在一段连续的空间上。因此在系统第一次启动时就会将日志文件的空间完全分配。以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。
B. 批量写入日志。日志并不是直接写入文件，而是先写入redo log buffer.当需要将日志刷新到磁盘时 (如事务提交),将许多日志一起写入磁盘.
C. 并发的事务共享Redo Log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起，以减少日志占用的空间。例如,Redo Log中的记录内容可能是这样的：
记录1: <trx1, insert …>
记录2: <trx2, update …>
记录3: <trx1, delete …>
记录4: <trx3, update …>
记录5: <trx2, insert …>
D. 因为C的原因,当一个事务将Redo Log写入磁盘时，也会将其他未提交的事务的日志写入磁盘。
E. Redo Log上只进行顺序追加的操作，当一个事务需要回滚时，它的Redo Log记录也不会从Redo Log中删除掉。

03 – 恢复(Recovery)
- 恢复策略
前面说到未提交的事务和回滚了的事务也会记录Redo Log，因此在进行恢复时,这些事务要进行特殊的的处理.有2中不同的恢复策略：
A. 进行恢复时，只重做已经提交了的事务。
B. 进行恢复时，重做所有事务包括未提交的事务和回滚了的事务。然后通过Undo Log回滚那些未提交的事务。

- InnoDB存储引擎的恢复机制
MySQL数据库InnoDB存储引擎使用了B策略, InnoDB存储引擎中的恢复机制有几个特点：
A. 在重做Redo Log时，并不关心事务性。 恢复时，没有BEGIN，也没有COMMIT,ROLLBACK的行为。也不关心每个日志是哪个事务的。尽管事务ID等事务相关的内容会记入Redo Log，这些内容只是被当作要操作的数据的一部分。
B. 使用B策略就必须要将Undo Log持久化，而且必须要在写Redo Log之前将对应的Undo Log写入磁盘。
Undo和Redo Log的这种关联，使得持久化变得复杂起来。为了降低复杂度，InnoDB将Undo Log看作数据，因此记录Undo Log的操作也会记录到redo log中。这样undo log就可以象数据一样缓存起来， 而不用在redo log之前写入磁盘了。
包含Undo Log操作的Redo Log，看起来是这样的
记录1: <trx1, Undo log insert <undo_insert …>>
记录2: <trx1, insert …>
记录3: <trx2, Undo log insert <undo_update …>>
记录4: <trx2, update …>
记录5: <trx3, Undo log insert <undo_delete …>>
记录6: <trx3, delete …>
C. 到这里，还有一个问题没有弄清楚。既然Redo没有事务性，那岂不是会重新执行被回滚了的事务？
确实是这样。同时Innodb也会将事务回滚时的操作也记录到redo log中。回滚操作本质上也是对数据进行修改，因此回滚时对数据的操作也会记录到Redo Log中。
一个回滚了的事务的Redo Log，看起来是这样的：
记录1: <trx1, Undo log insert <undo_insert …>>
记录2: <trx1, insert A…>
记录3: <trx1, Undo log insert <undo_update …>>
记录4: <trx1, update B…>
记录5: <trx1, Undo log insert <undo_delete …>>
记录6: <trx1, delete C…>
记录7: <trx1, insert C>
记录8: <trx1, update B to old value>
记录9: <trx1, delete A>
一个被回滚了的事务在恢复时的操作就是先redo再undo，因此不会破坏数据的一致性.

INNODB缓冲池

 InnoDB维护一个称为缓冲池的内存存储区域 ，用于缓存内存中的数据和索引。了解InnoDB缓冲池的工作原理，并利用它来保存内存中经常访问的数据，这是MySQL调优的一个重要方面。

1.1 LRU（least recently used）

InnoDB将buffer pool作为一个list管理，基于LRU算法。当有新的页要读入到buffer pool的时候，buffer pool就将最近最少使用的页从buffer pool中驱逐出去，并且将新页加入到list的中间位置，这就是所谓的“中点插入策略”。一般情况下list头部存放的是热数据，就是所谓的young pages（最近经常访问的数据），list尾部存放的就是old pages（最近不被访问的数据）。这个算法就保证了最近经常使用的page信息会被保存在最近访问的sublist中，相反的不被经常访问的就会保存在old sublist，当新数据写入的时候被old sublist中的page信息会被首先驱逐的。

LRU算法有以下的标准算法：

1）3/8的list信息是作为old list，这些信息是被驱逐的对象。

2）list的中点就是我们所谓的old list头部和new list尾部的连接点，相当于一个界限。

3）新数据的读入首先会插入到old list的头部。

4）如果是old list的数据被访问到了，这个页信息就会变成new list，变成young page，就会将数据页信息移动到new sublist的头部。

5）在数据库的buffer pool里面，不管是new sublist还是old sublist的数据如果不会被访问到，最后都会被移动到list的尾部作为牺牲者。

一般情况下，页信息会被查询语句立马查询到而被移动到new sublist，这就意味着他们会在buffer pool里面保留很长一段时间。表扫描（包括mysqldump或者没有where条件的select等操作）等操作将会刷入大量的数据进入buffer pool，同时也会将更多的buffer pool当中的信息刷出去，即使这个操作可能只会使用到一次而已。同样的，如果read-ahead（线性预读）后台进程读入大量数据的情况下也是会造成buffer pool大量高频的刷新数据页，但是这些操作是可控的，下面3，4会说得到。read-ahead操作简单说一下就是MySQL的一个后台预读进程，能够保证MySQL预读入数据进入buffer pool当中。

当你做backup或者report的时候，可以频繁的往buffer pool里面读取数据，不用有太多的顾虑。

InnoDB采用的是一种不是像LRU那么严格的方法来保证将最近访问的数据写入到buffer pool里面，并且最大可能的降低减少数据的带入量。这个语句是全表扫描或者以后这个数据将不会再被访问到，但是缓冲数据还是会写入到buffer pool里面。

新写入的数据会被插入到LRU list的中间位置，默认会插入到从list尾部算起来的3/8的位置，当这些写入的数据在buffer pool中被第一次访问的时候，在list中的位置就会向前移动，这样其实就会在list保留两个位置，老的位置并不会被立即清除，直到老的LRU list的位置被标记为OLD的时候，才会在下一次插入数据的时候被作为牺牲者清除掉。

我们本身是可以指定插入LRU list的位置，并且也可以设置当索引扫描或者是全表扫描的时候是不是采用这个相同的优化方法。innodb_old_blocks_pct这个参数设置的是插入的位置，默认的值是37，我们可以设置的值是5-95之间，其余部分并不用来保存热数据。但是还有一个严重的问题就是当一个全表扫描或者索引的扫描经常被访问的时候，就会存储很大的数据到buffer pool里面，我们都知道这是很危险的一件事。所以MySQL给我们以下参数来设置保留在buffer pool里面的数据在插入时候没有被改变list位置的时候的保存时间innodb_old_blocks_time，单位是毫秒，这个值的默认值是1000。如果增大这个值的话，就会让buffer pool里面很多页信息变老的速度变快，这个很好理解把，因为这些数据不会很快被内存中擦除的话，就会变成热数据而挤掉原有缓存的数据。

以上的两个参数都是可以动态设置的，当然也可以在my.cnf里面设置。当然设置这些前一定要对机器配置，表信息，负载情况有充分的了解才能进行设置，生产库尽量不要随便修改。如果OLTP系统中有大量的大查询的话，设置innodb_old_blocks_time能够较大的提供系统的稳定性。如果当一个大查询很大不足够存储到buffer pool当中的时候，我们可以指定innodb_old_blocks_pct的值小一点，以保证这些数据只会被读取一次，比如说设置为5的时候，就限制了一次读取数据最多只能被读取到buffer pool当中5%。当然一些小表并且是经常访问到的数据的话就可以适当设置较大的值，比如50。当然设置这两个值的时候一定要建立在你充分了解你的数据负载的基础上，不然千万不要乱改。

2.2 Buffer Pool

InnoDB缓冲池将表的索引和数据进行缓存，缓冲池允许从内存直接处理频繁使用的数据，这加快了处理速度。在专用数据库服务器上，通常将多达80％的物理内存分配给InnoDB缓冲池。因为InnoDB的存储引擎的工作方式总是将数据库文件按页读取到缓冲池，每个页16k默认（innodb_page_size=16k），在MySQL 5.7中增加了32KB和64KB页面大小的支持，之前版本是不允许大于16k的；但你只能在初始化MySQL实例之前进行配置，一旦设置了一个实例的页面大小，就不能改变它，具体看innodb_page_size参数。

然后按最近最少使用（LRU）算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页（发生修改后，该也即为脏也），然后再按照一定的频率将缓冲池的脏也刷新到文件中。可以通过show engine innodb status来查看innodb_buffer_pool的具体使用情况（默认是8个缓冲池实例），如下:

复制代码
mysql> show engine innodb statusG
Per second averages calculated from the last 38 seconds（以下信息来之过去的38秒）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 59957
Buffer pool size   65536
Free buffers  65371
Database pages  165
Old database pages  3
Modified db pages  9
..........
复制代码

在Buffer pool size中可以看到内存池的使用情况：

Total memory allocated：为缓冲池分配的总内存（以字节为单位）。

Dictionary memory allocated：分配给InnoDB数据字典的总内存（以字节为单位）。

Buffer pool size：分配给缓冲池的页面总数量（数量*页面大小=缓冲池大小），默认每个Page为16k。

Free buffers：缓冲池空闲列表的页面总数量（Buffer pool size -Database pages）。

Database pages：缓冲池LRU LIST的页面总数量（可以理解为已经使用的页面）。

Old database pages：缓冲池旧LRU SUBLIST的页面总大小（可以理解为不经常访问的页面，即将可能被LRU算法淘汰的页面）。

Modified db pages：在缓冲池中已经修改了的页数，所谓脏数据。

所以这里一共分配了63336*16/1024=1G内存的缓冲池，空闲65371个页面，已经使用了165个页面，不经常修改的数据页有3个（一般占用内存的1/3），脏页的页面有2个，这些数据能分析当前数据库的压力值。

二、配置InnoDB缓冲池大小

你可以配置InnoDB缓冲池的各个方面来提高性能。

  • 理想情况下，你将缓冲池的大小设置为尽可能大的值（70%-80%）。缓冲池越大，InnoDB内存数据库的行为越多，从磁盘读取数据一次，然后在后续读取期间从内存访问数据。
  • 对于具有大内存的64位系统，你可以将缓冲池拆分成多个实例（默认8个），以最大限度地减少并发操作中内存结构的争用。

2.1 在线配置InnoDB缓冲池大小

缓冲池支持脱机和联机两种配置方式，当增加或减少innodb_buffer_pool_size时，操作以块（chunk）形式执行。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认值128M。

在线配置InnoDB缓冲池大小，该innodb_buffer_pool_size配置选项可以动态使用设置SET声明，让你调整缓冲池无需重新启动服务器。例如：

mysql> SET GLOBAL innodb_buffer_pool_size=8589934592;

缓冲池大小配置必须始终等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。如果配置innodb_buffer_pool_size为不等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数，则缓冲池大小将自动调整为等于或不小于指定缓冲池大小的innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。

在以下示例中， innodb_buffer_pool_size设置为8G，innodb_buffer_pool_instances设置为16，innodb_buffer_pool_chunk_size是128M，这是默认值。8G是一个有效的innodb_buffer_pool_size值，因为它是innodb_buffer_pool_instances=16乘以innodb_buffer_pool_chunk_size=128M的倍数。

复制代码
mysql> select 8*1024 / (16*128);
+-------------------+
| 8*1024 / (16*128) |
+-------------------+
|            4.0000 |
+-------------------+
1 row in set (0.00 sec)
复制代码

 如果innodb_buffer_pool_size设置为9G，innodb_buffer_pool_instances设置为16，innodb_buffer_pool_chunk_size是128M，这是默认值。在这种情况下，9G不是innodb_buffer_pool_instances=16*innodb_buffer_pool_chunk_size=128M的倍数 ，所以innodb_buffer_pool_size被调整为10G，这是不小于指定缓冲池大小的下一个innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。

2.2 监控在线缓冲池调整大小进度

该Innodb_buffer_pool_resize_status报告缓冲池大小调整的进展。例如：

复制代码
mysql> SHOW STATUS WHERE Variable_name ='InnoDB_buffer_pool_resize_status';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_resize_status |       |
+----------------------------------+-------+
1 row in set (0.01 sec)
复制代码

2.3 配置InnoDB缓冲池块（chunk）大小

 innodb_buffer_pool_chunk_size可以在1MB（1048576字节）单位中增加或减少，但只能在启动时，在命令行字符串或MySQL配置文件中进行修改。

[mysqld]
innodb_buffer_pool_chunk_size = 134217728

修改innodb_buffer_pool_chunk_size时适用以下条件：

  • 如果新innodb_buffer_pool_chunk_size值乘以innodb_buffer_pool_instances大于初始化缓冲池大小时， innodb_buffer_pool_chunk_size则截断为innodb_buffer_pool_size / innodb_buffer_pool_instances。

例如，如果缓冲池初始化大小为2GB（2147483648字节）， 4个缓冲池实例和块大小1GB（1073741824字节），则块大小将被截断为等于innodb_buffer_pool_size / innodb_buffer_pool_instances，值为：

复制代码
mysql> select 2147483648 / 4;
+----------------+
| 2147483648 / 4 |
+----------------+
| 536870912.0000 |
+----------------+
1 row in set (0.00 sec)
复制代码
  • 缓冲池大小必须始终等于或不小于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。如果更改innodb_buffer_pool_chunk_size，innodb_buffer_pool_size则会自动调整为等于或不小于当前缓冲池大小的innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。缓冲池初始化时会发生调整。

更改时应小心innodb_buffer_pool_chunk_size，因为更改此值可以增加缓冲池的大小，如上面的示例所示。在更改innodb_buffer_pool_chunk_size之前，计算innodb_buffer_pool_size以确保生成的缓冲池大小是可接受的。

2.4 在线调整缓冲池内部大小机制

调整大小的操作由后台线程执行，当增加缓冲池的大小时，调整大小操作：

  • 添加页面chunks（chunks大小由innodb_buffer_pool_chunk_size定义）。
  • 覆盖哈希表，列表和指针以在内存中使用新的地址。
  • 将新页面添加到空闲列表中。

PS：当这些操作正在进行时，阻止其他线程访问缓冲池。

当减小缓冲池的大小时，调整大小操作：

  • 对缓冲池进行碎片整理并提取空闲页面。
  • 删除页面chunks（chunks大小由innodb_buffer_pool_chunk_size定义）。
  • 转换哈希表，列表和指针以在内存中使用新的地址。

在这些操作中，只有对缓冲池进行碎片整理和撤销页面才允许其他线程同时访问缓冲池。

InnoDB在调整缓冲池大小之前，应完成通过API执行的活动事务和操作。启动调整大小操作时，在所有活动事务完成之前，操作都不会启动。一旦调整大小操作进行中，需要访问缓冲池的新事务和操作必须等到调整大小操作完成，但是允许在缓冲池进行碎片整理时缓冲池的并发访问。缓冲池大小减少时页面被撤销，允许并发访问的一个缺点是在页面被撤回时可能会导致可用页面暂时不足。

三、配置多个缓冲池实例

对于具有多GB级缓冲池的系统，将缓冲池划分为单独的实例可以通过减少不同线程读取和写入缓存页面的争用来提高并发性。此功能通常适用于缓冲池大小在千兆字节范围内的系统。使用innodb_buffer_pool_instances配置选项配置多个缓冲池实例，你也可以调整该 innodb_buffer_pool_size值。

当InnoDB缓冲池大时，可以通过从内存检索来满足许多数据请求。你可能会遇到多个请求一次访问缓冲池的线程的瓶颈。你可以启用多个缓冲池以最小化此争用。使用散列函数，将缓冲池中存储或读取的每个页面随机分配给其中一个缓冲池。每个缓冲池管理自己的空闲列表，刷新列表，LRU和连接到缓冲池的所有其他数据结构，并由其自己的缓冲池互斥锁保护。

要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为大于1（默认值）高达64（最大值）的值。此选项仅在设置innodb_buffer_pool_size为1GB或更大的大小时生效。你指定的总大小在所有缓冲池之间分配，为了获得最佳效率，指定的组合innodb_buffer_pool_instances和innodb_buffer_pool_size，使得每个缓冲池实例是至少为1GB。

四、配置InnoDB缓冲池预读

InnoDB在io的优化上有个比较重要的特性为预读，预读请求是一个i/o请求，它会异步地在缓冲池中预先回迁多个页面，预计很快就会需要这些页面，这些请求在一个范围内引入所有页面。InnoDB以64个page为一个extent，那么InnoDB的预读是以page为单位还是以extent？

这样就进入了下面的话题，InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）

为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

线性预读（linear read-ahead）：它可以根据顺序访问缓冲池中的页面，预测哪些页面可能需要很快。通过使用配置参数innodb_read_ahead_threshold，通过调整触发异步读取请求所需的顺序页访问数，可以控制Innodb执行提前读操作的时间。在添加此参数之前，InnoDB只会计算当在当前范围的最后一页中读取整个下一个区段时是否发出异步预取请求。

线性预读方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数innodb_read_ahead_threshold，可以控制Innodb执行预读操作的时间。如果一个extent中的被顺序读取的page超过或者等于该参数变量时，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64的任何值，默认值为56，值越高，访问模式检查越严格。

复制代码
mysql> show global variables like '%innodb_read_ahead_threshold%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56    |
+-----------------------------+-------+
1 row in set (0.00 sec)
复制代码

例如，如果将值设置为48，则InnoDB只有在顺序访问当前extent中的48个pages时才触发线性预读请求，将下一个extent读到内存中。如果值为8，InnoDB触发异步预读，即使程序段中只有8页被顺序访问。你可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL需要该SUPER权限的命令动态更改该参数。

在没有该变量之前，当访问到extent的最后一个page的时候，Innodb会决定是否将下一个extent放入到buffer pool中。

随机预读（randomread-ahead）：随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中，由于随机预读方式给Innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置innodb_random_read_ahead为ON。

复制代码
mysql> show global variables like '%innodb_random_read_ahead%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_random_read_ahead | OFF   |
+--------------------------+-------+
1 row in set (0.01 sec)
复制代码

在监控Innodb的预读时候，我们可以通过SHOW ENGINE INNODB STATUS命令显示统计信息，通过Pages read ahead和evicted without access两个值来观察预读的情况，或者通过两个状态值，以帮助您评估预读算法的有效性。

复制代码
mysql> show global status like '%Innodb_buffer_pool_read_ahead%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
+---------------------------------------+-------+
3 rows in set (0.00 sec)
复制代码

而通过SHOW ENGINE INNODB STATUS得到的Pages read ahead和evicted without access则表示每秒读入和读出的pages：Pages read ahead 1.00/s, evicted without access 9.99/s。

当微调innodb_random_read_ahead设置时，此信息可能很有用 。

五、配置InnoDB缓冲池刷新

InnoDB会在后台执行某些任务，包括从缓冲池刷新脏页（那些已更改但尚未写入数据库文件的页）。

InnoDB当缓冲池中脏页的百分比达到定义的低水位设置时，其实就是当缓冲池中的脏页占用比达到innodb_max_dirty_pages_pct_lwm的设定值的时候，就会自动将脏页清出buffer pool，这是为了保证buffer pool当中脏页的占有率，也是为了防止脏页占有率超过innodb_max_dirty_pages_pct的设定值，当脏页的占有率达到了innodb_max_dirty_pages_pct的设定值的时候，InnoDB就会强制刷新buffer pool pages。

InnoDB采用一种基于redo log的最近生成量和最近刷新频率的算法来决定冲洗速度，这样的算法可以保证数据库的冲洗不会影响到数据库的性能，也能保证数据库buffer pool中的数据的脏数据的占用比。这种自动调整刷新速率有助于避免过多的缓冲池刷新限制了普通读写请求可用的I/O容量，从而避免吞吐量突然下降，但还是对正常IO有影响。

我们知道InnoDB使用日志的方式是循环使用的，在重用前一个日志文件之前，InnoDB就会将这个日志这个日志记录相关的所有在buffer pool当中的数据刷新到磁盘，也就是所谓的sharp checkpoint，和sqlserver的checkpoint很像。当一个插入语句产生大量的redo信息需要记录的日志，当前redo log文件不能够完全存储，也会写入到当前的redo文件当中。当redo log当中的所有使用空间都被用完了的，就会触发sharp checkpoint，所以这个时候即使脏数据占有率没有达到innodb_max_dirty_pages_pct，还是会进行刷新。

内部基准测试显示，该算法随着时间的推移可以显著提高整体吞吐量。这种算法是经得住考验的，所以说千万不要随便设置，最好是默认值。但是我们从中也就会知道为什么redo log不能够记录两个事物的redo信息了。因为有这么多的好处，所以innodb_adaptive_flushing的值默认就是true的，默认开启自适应刷新策略。

六、微调InnoDB缓冲池刷新

配置选项innodb_flush_neighbors， innodb_lru_scan_depth可以让你微调缓冲池刷新过程的某些方面，这些选项主要是帮助写密集型的工作负载。如果DML操作较为严重，如果没有较高的值，则刷新可能会下降，会导致缓冲池中的内存过多。或者，如果这种机制过于激进，磁盘写入将会使你的I/O容量饱和，理想的设置取决于你的工作负载，数据访问模式和存储配置（例如数据是否存储在HDD或SSD设备上）。

InnoDB对于具有不断繁重工作负载的系统或者工作负载波动很大的系统，可以使用下面几个配置选项来调整表的刷新行为：

  • innodb_adaptive_flushing_lwm：默认值10，指定重做日志容量的“ 低水位 ”百分比，当该阈值越过时，InnoDB即使没有开启innodb_adaptive_flushing选项也会自动启用自适应刷新。
  • innodb_max_dirty_pages_pct_lwm：默认值0，InnoDB尝试从缓冲池中刷新数据，以使脏页面的百分比不超过该值innodb_max_dirty_pages_pct。默认值为75。该innodb_max_dirty_pages_pct_lwm选项是用来指定“ 低水位 ”值，其表示使用预冲洗来控制脏页比例的百分比，防止脏页的百分比达到innodb_max_dirty_pages_pct的值，innodb_max_dirty_pages_pct_lwm默认0，禁用“ 预冲洗”行为。
  • innodb_io_capacity_max，默认2000，如果刷新动作远远落后，InnoDB可以比指定的innodb_io_capacity刷新动作更积极。innodb_io_capacity_max表示在这种紧急情况下使用的I/O容量的上限，以便I/O中的尖峰消耗不到服务器的所有容量。
  • innodb_flushing_avg_loops，默认30；定义了innodb保留先前计算的刷新状态快照的迭代次数, 它控制了自适应刷新对此前负载更改的响应速度。为innodb_flushing_avg_loops设置高值意味着innodb保留以前计算的快照的时间更长，因此自适应刷新的响应速度更慢，高值还可以减少前台和后台工作之间的正面反馈。但是，在设置高值时，确保innodb重做日志利用率不达到75% (异步刷新开始时的硬编码限制) 和innodb_max_dirty_pages_pct设置将脏页的数量保持为适合于工作负荷的级别是很重要的。

上面提到的大多数选项最适用于长时间运行写入繁重工作负载的服务器。

七、保存和恢复缓冲池状态

7.1 在关闭时保存缓冲池状态并在启动时恢复缓冲池状态

可以配置在MySQL关闭之前，保存InnoDB当前的缓冲池的状态，以避免在服务器重新启动后，还要经历一个预热的暖机时间。通过innodb_buffer_pool_dump_at_shutdown（服务器关闭前设置）来设置，当设置这个参数以后MySQL就会在机器关闭时保存InnoDB当前的状态信息到磁盘上。

当启动MySQL服务器时要恢复服务器缓冲池状态，请在启动服务器时开启innodb_buffer_pool_load_at_startup参数。个人认为这个值还是需要配置一下的，MySQL 5.7.6版本之前这两个值默认是关闭的，但从MySQL 5.7.7版本开始这两个值就默认为开启状态了。这些数据是从磁盘重新读取到buffer pool当中的，这会花费一些时间，并且恢复时新的DML操作是不能够进行操作的。这些数据是怎么恢复呢？其实INNODB_BUFFER_PAGE_LRU表（INFORMATION_SCHEMA）会记录缓存的tablespace ID和page ID，通过这个来恢复。另外缓冲池状态保存文件默认在数据目录下，名为”ib_buffer_pool”，可以使用innodb_buffer_pool_filename参数来修改文件名和位置。

7.2 配置缓冲池页面保存的百分比

在加载数据进入buffer pool之前，可以通过设置innodb_buffer_pool_dump_pct参数来决定恢复buffer pool中多少数据。MySQL 5.7.6版本之前的默认值是100，恢复全部，从MySQL 5.7.7版本之后默认调整为25了。可以动态设置此参数：

mysql> SET GLOBAL innodb_buffer_pool_dump_pct = 40;

7.3 在线保存和恢复缓冲池状态

要在运行MySQL服务器时保存缓冲池的状态，请发出以下语句：

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

要在MySQL运行时恢复缓冲池状态，请发出以下语句：

checkpoint

检查点的工作机制：

innodb会自动维护一个检查点的机制，叫做 fuzzy checkpointing（当然sharp checkpoint也是检查点之一），fuzzy checkpointing就是将buffer pool当中的数据页信息小批量的刷新到磁盘。但是我们没有必要单批次批次的对buffer pool进行刷新，不然后影响其他正在执行的SQL进程。

 

在crash recovery期间，MySQL也会记录一次检查点信息到log  file当中去。它会记录数据库检查点发生之前的所有修改数据库的操作，这样数据库就会在日志文件当中查找检查点信息，然后往前读日志重新执行（前滚）。

页的修改信息一般都会被记录到buffer pool当中，稍后这些信息就会被刷新到磁盘的数据文件当中，flushing后台进程来负责处理这个事情。所谓的检查点就是记录最后一次修改写入磁盘数据文件的一个记录信息（具体的表现形式就是LSN）。

下面稍微简单的了解一下和检查点相关的MySQL的进程和机制：

fuzzy checkpointing：一个后台进程，定期刷新buffer pool当中一部分的dirty  page到磁盘当中。

sharp checkpoint：一次性将buffer pool当中的所有脏页刷新到磁盘数据文件，在MySQL重用日志文件之前发生。由于MySQL的日志文件是循环利用的，所以通常较高的负载的情况下会频繁发生。

adaptive flushing：通过引起检查点来减轻IO负担的一种算法，取代了一次刷新所有脏页，adaptive flushing每次只刷新一部分脏页落盘，这个算法会根据数据冲洗的速度和频率自动算出最优的刷新周期。

 

flush：将更改刷新到数据文件，也就是所谓的落盘。在INNODB的存储结构当中，定期刷新的有redo log,undo log和buffer pool等。但是flush什么时候会发生呢？一种情况是MySQL内存存储区域已经满了的时候会触动发生flush，因为新的改变发生的话就会需要新的buffer pool空间来保存信息。如果不是立即需要刷新所有的buffer pool信息到磁盘的话，一般情况下将会使用fuzzy checkpointing这个进程一点一点来处理。

看了这么多，到底检查点是如何工作的呢？下面大体的看一下：

关于INNODB  checkpoint的算法并没有太多的文档记载，因为理解起来很难，而且还要去理解很多INNODB的很多其他相关的东西才可以很好的帮助你理解checkpoint。

首先我们要知道的就是检查点分为两种，一种是sharp checkpoint, 另外一种就是 fuzzy checkpoint。

如上面介绍的，sharp checkpoint一次性将buffer pool当中的所有脏页刷新到磁盘数据文件。并且记录LSN（log sequence number ）到最后一个提交的事物的位置。当然，没有提交的事物是不会被刷新到磁盘当中的。这点和sqlserver还是有点不一样的，sqlserver是会将提交和未提交的都给刷新到磁盘当中去，这样看起来就违反了预写日志的规则。恢复以后，REDO LOG就会从最后一个LSN开始，也就是检查点发生的位置。sharp checkpoint将所有的数据刷新到磁盘当中去都是基于一个时间点的，这个LSN就是所谓的检查点发生的位置。

fuzzy checkpoint就更加复杂了，它是在固定的时间点发生，除非他已经将所有的页信息刷新到了磁盘，或者是刚发生过一次sharp checkpoint，fuzzy checkpoint发生的时候会记录两次LSN，也就是检查点发生的时间和检查点结束的时间。但是呢，被刷新的页在并不一定在某一个时间点是一致的，这也就是它为什么叫fuzzy的原因。较早刷入磁盘的数据可能已经修改了，较晚刷新的数据可能会有一个比前面LSN更新更小的一个LSN。fuzzy checkpoint在某种意义上可以理解为fuzzy checkpoint从redo  log的第一个LSN执行到最后一个LSN。恢复以后的话，REDO LOG就会从最后一个检查点开始时候记录的LSN开始。

一般情况下大家可能fuzzy checkpoint的发生频率会远高于sharp checkpoint发生的频率，这个事毫无疑问的。不过当数据库关闭，切换redo 日志文件的时候是会触发sharp checkpoint，一般情况是fuzzy checkpoint发生的更多一些。

一般情况下，执行普通操作的时候将不会发生检查点的操作，但是，fuzzy checkpoint却要根据时间推进而不停的发生。刷新脏页已经成为了数据库的一个普通的日常操作。

INNODB维护了一个大的缓冲区，以保证被修改的数据不会被立即写入磁盘。她会将这些修改过的数据先保留在buffer pool当中，这样在这些数据被写入磁盘以前可能会经过多次的修改，我们称之为写结合。这些数据页在buffer pool当中都是按照list来管理的，free list会记录那些空间是可用的，LRU list记录了那些数据页是最近被访问到的。flush list则记录了在LSN顺序当中的所有的dirty page信息，最近最少修改信息。

这里着重看一下flush list，我们知道innodb的缓存空间是有限的。如果buffer pool空间使用完毕，再次读取新数据就会发生磁盘读，也就是会发生flush操作，所以说就要释放一部分没有被使用的空间来保证buffer pool的可用性。由于这样的操作是很耗时的，所以说INNODB是会连续按照时间点去执行刷新操作，这样就保证了又足够的clean page来作为交换，而不必发生flush操作。每一次刷新都会将flush list的最老的信息驱逐，这样才能够保证数据库缓冲命中率是很高的一个值。这些老数据的选取是根据他们在磁盘的位置和LSN（最后一次修改的）号来确认数据新旧。

MySQL数据的日志都是混合循环使用的，但是如果这些事物记录的页信息还没有被刷新到磁盘当中的话是绝对不会被覆盖写入的。如果还没被刷新入磁盘的数据被覆盖了日志文件，那数据库宕机的话岂不是所有被覆盖写入的事物对应的数据都要丢失了呢。因此，数据修改也是有时间限制的，因为新的事物或者正在执行的事物也是需要日志空间的。日志越大，限制就越小。而且每次fuzzy checkpoint都会将最老最不被访问的数据驱逐出去，这也保证了每次驱逐的都是最老的数据，在下次日志被覆盖写入的时候都是已经被刷盘的数据的日志信息。最后一个老的,不被访问的数据的事物的LSN就是事务日志的 low-water标记，INNODB一直想提高这个LSN的值以保证buffer pool又足够的空间刷入新的数据，同时保证了数据库事务日志文件可以被覆盖写入的时候有足够的空间使用。将事务日志设置的大一些能够降低释放日志空间的紧迫性，从而可以大大的提高性能。

当innodb刷新 dirty page落盘的时候，他会找到最老的dirty  page对应的LSN并且将其标记为low-water，然后将这些信息记录到事物日志的头部，因此，每次刷新脏页都是要从flush  list的头部进行刷新的。在推进最老的LSN的标记位置的时候，本质上就是做了一次检查点。

当INNODB宕机的时候，他还要做一些额外的操作，第一：停止所有的数据更新等操作，第二：将dirty page in  buffer 的数据刷新落盘，第三：记录最后的LSN，因为我们上面也说到了，这次发生的是sharp checkpoint，并且，这个LSN会写入到没一个数据库文件的头部，以此来标记最后发生检查点的时候的LSN位置。

我们知道，刷新脏页数据的频率如果越高的话就代表整个数据库的负载很大，越小当然代表数据库的压力会小一点。将LOG 文件设置的很大能够再检查点发生期间减少磁盘的IO,总大小最好能够设置为和buffer pool大小相同，当然如果日志文件设置太大的话MySQL就会再crash recovery的时候花费更多的时间（5.5之前）。

checkpoint2

思考一下这个场景：如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。

但是这需要两个前提条件：1、缓冲池可以缓存数据库中所有的数据；2、重做日志可以无限增大

因此Checkpoint（检查点）技术就诞生了，目的是解决以下几个问题：1、缩短数据库的恢复时间；2、缓冲池不够用时，将脏页刷新到磁盘；3、重做日志不可用时，刷新脏页。

  • 当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。

  • 当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。

  • 当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的，重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

对于InnoDB存储引擎而言，是通过LSN（Log Sequence Number）来标记版本的。

LSN是8字节的数字，每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。可以通过命令SHOW ENGINE INNODB STATUS来观察：

复制代码
mysql> show engine innodb status G
 
---
LOG
---
Log sequence number 34778380870
Log flushed up to   34778380870
Last checkpoint at  34778380870
0 pending log writes, 0 pending chkp writes
54020151 log i/o's done, 0.92 log i/o's/second
复制代码

Checkpoint发生的时间、条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘，不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint。

二、Checkpoint分类

在InnoDB存储引擎内部，有两种Checkpoint，分别为：Sharp Checkpoint、Fuzzy Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数innodb_fast_shutdown=1。但是若数据库在运行时也使用Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在InnoDB存储引擎内部使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

Fuzzy Checkpoint：1、Master Thread Checkpoint；2、FLUSH_LRU_LIST Checkpoint；3、Async/Sync Flush Checkpoint；4、Dirty Page too much Checkpoint

1、Master Thread Checkpoint 

以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的，此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。

2、FLUSH_LRU_LIST Checkpoint

因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。在InnoDB1.1.x版本之前，需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中，显然这会阻塞用户的查询操作。倘若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint。

而从MySQL 5.6版本，也就是InnoDB1.2.x版本开始，这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024，如：

复制代码
mysql>  SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_lru_scan_depth | 1024  |
+-----------------------+-------+
复制代码

3、Async/Sync Flush Checkpoint

指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的LSN记为redo_lsn，将已经刷新回磁盘最新页的LSN记为checkpoint_lsn，则可定义：

checkpoint_age = redo_lsn - checkpoint_lsn

再定义以下的变量：

async_water_mark = 75% * total_redo_log_file_size

sync_water_mark = 90% * total_redo_log_file_size

若每个重做日志文件的大小为1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为2GB。那么async_water_mark=1.5GB，sync_water_mark=1.8GB。则：

当checkpoint_age<async_water_mark时，不需要刷新任何脏页到磁盘；

当async_water_mark<checkpoint_age<sync_water_mark时触发Async Flush，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<async_water_mark；

checkpoint_age>sync_water_mark这种情况一般很少发生，除非设置的重做日志文件太小，并且在进行类似LOAD DATA的BULK INSERT操作。此时触发Sync Flush操作，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<async_water_mark。

可见，Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性。在InnoDB 1.2.x版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，而Sync Flush Checkpoint会阻塞所有的用户查询线程，并且等待脏页刷新完成。从InnoDB 1.2.x版本开始——也就是MySQL 5.6版本，这部分的刷新操作同样放入到了单独的Page Cleaner Thread中，故不会阻塞用户查询线程。

MySQL官方版本并不能查看刷新页是从Flush列表中还是从LRU列表中进行Checkpoint的，也不知道因为重做日志而产生的Async/Sync Flush的次数。但是InnoSQL版本提供了方法，可以通过命令SHOW ENGINE INNODB STATUS来观察，如：

复制代码
mysql> show engine innodb status G
 
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2058485760; in additional pool allocated 0
Dictionary memory allocated 913470
Buffer pool size   122879
Free buffers       79668
Database pages     41957
Old database pages 15468
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15032929, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15075936, created 366872, written 36656423
0.00 reads/s, 0.00 creates/s, 0.90 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 41957, unzip_LRU len: 0
I/O sum[39]:cur[0], unzip sum[0]:cur[0]
复制代码

4、Dirty Page too much

即脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制：

复制代码
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_max_dirty_pages_pct' ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_max_dirty_pages_pct | 75    |
+----------------------------+-------+
复制代码

innodb_max_dirty_pages_pct值为75表示，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘。在InnoDB 1.0.x版本之前，该参数默认值为90，之后的版本都为75。

三、Checkpoint机制

在Innodb事务日志中，采用了Fuzzy Checkpoint，Innodb每次取最老的modified page(last checkpoint)对应的LSN，再将此脏页的LSN作为Checkpoint点记录到日志文件，意思就是“此LSN之前的LSN对应的日志和数据都已经flush到redo log

当mysql crash的时候，Innodb扫描redo log，从last checkpoint开始apply redo log到buffer pool，直到last checkpoint对应的LSN等于Log flushed up to对应的LSN，则恢复完成

那么具体是怎么恢复的呢？

如上图所示，Innodb的一条事务日志共经历4个阶段：

  • 创建阶段：事务创建一条日志；

  • 日志刷盘：日志写入到磁盘上的日志文件；

  • 数据刷盘：日志对应的脏页数据写入到磁盘上的数据文件；

  • 写CKP：日志被当作Checkpoint写入日志文件；

对应这4个阶段，系统记录了4个日志相关的信息，用于其它各种处理使用：

  • Log sequence number（LSN1）：当前系统LSN最大值，新的事务日志LSN将在此基础上生成（LSN1+新日志的大小）；

  • Log flushed up to（LSN2）：当前已经写入日志文件的LSN；

  • Oldest modified data log（LSN3）：当前最旧的脏页数据对应的LSN，写Checkpoint的时候直接将此LSN写入到日志文件；

  • Last checkpoint at（LSN4）：当前已经写入Checkpoint的LSN；

对于系统来说，以上4个LSN是递减的，即： LSN1>=LSN2>=LSN3>=LSN4.

具体的样例如下（使用show innodb status G命令查看，Oldest modified data log没有显示）：

复制代码
LOG
---
Log sequence number 34822137537
Log flushed up to   34822137537
Last checkpoint at  34822133028
0 pending log writes, 0 pending chkp writes
54189288 log i/o's done, 3.00 log i/o's/second　
复制代码

四、日志保护机制

mysql crash的时候，Innodb有日志刷盘机制，可以通过innodb_flush_log_at_trx_commit参数进行控制，这里说的是如何防止日志覆盖导致日志丢失

Innodb的checkpoint和redo log有哪些紧密关系？有几上名词需要解释一下：

  • Ckp age（动态移动）: 最老的dirty page还没有flush到数据文件，即没有做last checkpoint的范围

  • Buf age（动态移动）: modified page information没有写到log中，但已在log buffer

  • Buf async（固定点）: 日志空间大小的7/8，当buf age移动到Buf async点时，强制把没有写到log中的modified page information开始写入到log中，不阻塞事务

  • Buf sync（固定点）: 日志空间大小的15/16，当写入很大的，buf age移动非常快，一下子到buf sync的点，阻塞事务，强制把modified page information开始写入到log中。如果不阻塞事务，未做last checkpoint的redo log存在覆盖危险

  • Ckp async（固定点）: 日志空间大小的31/32，当ckp age到达ckp async，强制做last checkpoint，不阻塞事务

  • Ckp sync(固定点）:日志空间大小，当ckp age到达ckp sync，强制做last checkpoint，阻塞事务，存在redo log覆盖的危险

接下分析4种情况

  • 如果buf age在buf async和buf sync之间

  • 如果buf age在buf sync之后（当然这种情况是不存在，mysql有保护机制）

  • 如果ckp age在ckp async和ckp sync之间（这种情况是不存在）

  • 如果ckp age在ckp sync之后（这种情况是不存在）

第一种情况：

当写入量巨大时，buf age移动到buf async和buf sync之间，触发写出到log中，mysql把尽量多的log写出，如果写入量减慢，buf age又移回到“图一”状态。如果写入量大于flush log的速度，buf age最终会和buf sync重叠，这时所有的事务都被阻塞，强制将2*(Buf age-Buf async)的脏页刷盘，这时IO会比较繁忙。

第二种情况：

当然这种情况是不可能出现，因为如果出现，redo log存在覆盖的可能，数据就会丢失。buf age会越过log size，buf age的大小可能就超过log size,如果要刷buf age，那么整个log size都不够容纳所有的buf age。

第三种和第四种情况不存在分析：

ckp age始终位于buf age的后面(左边)，因为ckp age是last checkpoint点，总是追赶buf age(将尽可能多的modified page flush到磁盘)，所以buf age肯定是先到达到buf sync。

ckp async及ckp sync存在意义？

mysql中page cache也存在high water及low water，当dirty page触到low water时，os是开始flush dirty page到磁盘，到high water时，会阻塞一切动作，os会疯狂的flush dirty page，磁盘会很忙，存在IO Storm。

链接 http://www.cnblogs.com/chenpingzhao/p/5107480.html

链接 www.cnblogs.com/ilifeilong

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/mydba-j/p/9712645.html