读书笔记：第二章 innodb存储引擎

来源：互联网发布：mac三国志13战斗好卡编辑：程序博客网时间：2024/05/15 10:41

innodb是事务安全的MySQL存储引擎。设计上采用类似于oracle数据库的架构。通常来讲，innodb存储引擎是OLTP应用中首选的存储引擎。

2.1 innodb存储引擎概述

从MySQL5.5版本开始，innodb存储引擎就被当做默认的表存储引擎（之前版本只有在windows下才是默认的表存储引擎）。该存储引擎是第一个完整支持ACID事务的mysql存储引擎（BDB 是第一个支持事务的MySQL存储引擎，现在已经停止开发）。

其特点是：行锁设计、支持MVCC（多版本并发控制）、支持外键、提供一致性非锁定读。同时被设计用来最有效利用和使用内存和CPU。

innodb存储引擎已经被许多大型网站使用，如用户熟知的谷歌，雅虎，facebook，YouTube，flickr。网游中有魔兽世界。

2.2 innodb存储引擎版本

innodb 存储引擎被包含于所有MySQL数据库的二进制发行版本中。早期其版本随着mysql数据库的更新而更新，

MySQL5.1以后，MySQL数据库允许innodb存储引擎开发商以动态方式加载引擎，使innodb存储引擎的更新不再受到

mysql版本的限制，所以在mysql5.1中可以支持两个innodb版本，一种是静态编译的innodb版本，可以将其视为老版

本innodb。一种是动态加载的的，官方称其为innodb plugin。可以将其视为innodb1.0.X版本。mysql5.5 升级到

1.1.X。 mysql5.6升级到1.2.X 。

MySQL 数据库及其内部INNODB引擎版本对照表：

Mysql版本 innodb引擎版本

5.1.x 1.0.x版本(官方称为InnoDB Plugin)

5.5.x 1.1.x版本

5.6.x 1.2.x版本

表 innodb各版本功能对比

版本功能老版本innodb支持ACID、行锁设计、MVCCInnodb1.0.X（innodb plugin）继承上述版本所有功能，增加了compress和dynamic页格式innodb1.1.X继承上述版本所有功能，增加了linux AIO、多回滚段innodb1.2.X继承上述版本所有功能，增加了全文索引的支持、在线索引添加

由于不支持多回滚段，innodb plugin支持的最大支持并发事务数量也被限制在1023.，随着mysql5.5版本的发布，innodb plugin也成为了历史产品。

2.3 innodb体系架构

innodb存储引擎体系架构

由图可见，innodb存储引擎有多个内存块，可以认为些内存块组成一个大的内存池。负责如下工作：

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

2.缓存磁盘上的数据，磁盘上的数据在更改前，缓存在内存池中

3.redo log 的缓冲

。。。

后台线程的主要作用：

1.负责刷新内存中的数据，保证内存中的数据是最新的。

2.将内存中已修改的数据刷新到磁盘中。

3.保证数据库发生异常情况，innodb能恢复到正常运行状态。

2.3.1 后台线程

innodb存储引擎是多线程模型。因此，其后台有多个不同的后台线程，负责处理不同的任务。

master thread

master thread是一个非常核心的后台线程。

1.主要负责将数据异步刷新到磁盘中，保持数据的一致性。

2.包括脏页的刷新，合并插入缓存（insert buffer），undo页的回收等等。

2.IO thread

在innodb存储引擎中大量使用AIO（Async IO）来处理写IO请求。这样可以极大提高数据库性能。而IO thread 的主要作用是负责这些IO 请求的回调（call back）处理。

innodb 1.0版本以前共有4个io thread ，分别是write、read、insert buffer和log io thread。在linux平台下，io thread 的数量不能进行调整，但是windows平台下，可以通过参数innodb_file_io_threads 来增大IO thread。从innodb 1.0.X版本开始，read thread 和write thread 分别增大到4个，并且不再使用 innodb_file_io_threads参数，而是分别使用innodb_write_threads 和 innodb_read_io_threads参数进行设置。

mysql> show variables like 'innodb_%io_threads'\G;

*************************** 1. row ***************************

Variable_name: innodb_read_io_threads

Value: 4

*************************** 2. row ***************************

Variable_name: innodb_write_io_threads

Value: 4

2 rows in set (0.05 sec)

观察innodb中的io thread：

mysql> show engine innodb status\G;

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

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)

可以看到io thread 0 为 insert buffer thread。io thread 1 为 log thread。之后根据参数 innodb_read_io_thread 和innodb_write_io_thread 来设置的读写线程。并且，读线程ID总是小于写线程。

3.purge thread（净化线程，整肃线程）

事务被提交之后，其所使用的undolog可能不在需要，需要用到purge thread 来回收已经使用并分配的这些undo 页。innodb1.1以前 purge操作是在master thread 中的，1.1以后，purge thread 成为一个独立的线程，减轻master thread的压力，同时也可以提升CPU的使用率和存储引擎的性能。

在配置文件中启用独立的purge thread：

[mysqld]

innodb_purge_threads=1

设置大于1，启动时也会将其设为1.

innodb1.2 开始，innodb支持多个purge thread，目的是为了进一步加快undo页的回收。同时由于purge thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。如用户可以设置4个

purge thread：

mysql> select version()\G;

*************************** 1. row ***************************

version(): 5.7.12-log

1 row in set (0.03 sec)

mysql> show variables like 'innodb_purge_thread%'\G;

*************************** 1. row ***************************

Variable_name: innodb_purge_threads

Value: 4

1 row in set (0.04 sec)

4.page cleaner thread

page cleaner thread是在innodb1.2.X版本中引入的。其作用是将原先版本中的脏页刷新操作都放到单独的一个线程中完成。而其目的是为了减轻原master thread的工作及对于用户查询线程的阻塞，进一步提升innodb存储引擎的性能。

2.3.3 内存

1.缓冲池

innodb存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此，可将其视为基于磁盘的数据库系统（disk-base database）。由于CPU速度和磁盘速度之间的鸿沟。基于磁盘的数据库系统通常采用缓冲池来提升数据库的整体性能。

缓冲池简单来说就是一个内存区域，通过内存的速度来弥补磁盘速度较慢对数据库系统的影响。在数据库中进行读取页的操作。首先，将从磁盘读取到的数据页放到缓冲池中，这个过程称为将页‘fix’在缓冲池中。下次再读取相同页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接

读取该页，否则就在磁盘中读取。

对于数据库中页的修改操作，则首先修改在缓冲池中的页，再以一定的频率刷新到磁盘中。这里需要注意的是，页从缓冲池中刷新到磁盘的操作并不是在页更新时触发，而是通过一种称为checkpoint的机制刷新回磁盘。这是为了提升数据库的整体性能。

综上所述，缓冲池的大小直接影响着数据库的整体性能。由于32位操作系统的限制，在该系统下，该值最多设置为3G。此外，用户可以通过打开PAE选项来使操作系统获得最大64G内存的支持。最好使用64位操作系统以使数据库使用更多内存。

对于innodb存储引擎而言，其缓冲池的配置通过参数: innodb_buffer_pool_size 来设置。

mysql> show variables like 'innodb_buffer_pool_size'\G;

*************************** 1. row ***************************

Variable_name: innodb_buffer_pool_size

Value: 104857600

1 row in set (0.04 sec)

测试库中只有100M 的缓冲内存

innodb存储引擎的内存结构情况如下图所示：

缓冲池中大部分是数据页和索引页

缓冲池中缓存数据类型：索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、innodb存储的锁信息（lock info）、数据字典信息（data dictionary）等。

从innodb 1.0.X 版本开始，允许多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。可以通过innodb_buffer_pool_instances（instances：实例）来进行设置，该值默认值是1 。

mysql> show variables like 'innodb_buffer_pool_instances'\G;

*************************** 1. row ***************************

Variable_name: innodb_buffer_pool_instances

Value: 1

1 row in set (0.04 sec)

将innodb_buffer_pool_instances 设置为大于1的值就可以得到多个缓冲池实例。

InnoDB: Adjusting innodb_buffer_pool_instances from 2 to 1 since innodb_buffer_pool_size is less than 1024 MiB （如果单个缓冲池实例小于1G，就不能设置大于1）

2.LRU list 、free list 和 flush list

简单来说，数据库中的缓存池是通过LRU(latest recent used，最近最少使用)的算法进行管理的。即最频繁使用的页在LRU列表的前端，最少使用的页在LRU列表尾端。当缓冲池不能存放新读取到的页时，将首先释放尾端的页。

在innodb存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法对缓冲池进行管理。但是又对传统LRU算法进行了优化。innodb中的LRU列表还加入了midpoint位置。新读取到的页就放在midpoint位置（在innodb下称为 midpoint insertion strategy）。在默认配置下，该位置在LRU列表长度的5/8位置处，同时midpoint位置可由参数 innodb_old_blocks_pct控制，如：

mysql> show variables like 'innodb_old_blocks_pct'\G;

*************************** 1. row ***************************

Variable_name: innodb_old_blocks_pct

Value: 37

从上面的例子可以看到，参数innodb_old_blocks_pct 默认值为37，即插入到尾部37%的位置（3/8）。在innodb存储引擎中将midpoint之后（即最后37%）的列表称为 old列表，之前的表称为NEW列表，可以理解为new列表中的页都是最为活跃的热点数据。

那为什么要对LRU算法做这样的改动呢？

原因是mysql中有很多操作，例如索引或扫描等操作，这些操作有时需要用到很多表，但是这类操作需要的页又可能仅仅针对此次索引或扫描，如果将其放到前端，可能就将真正的热点数据给刷新出列表。下次就只能从硬盘读取了。

为了解决这个问题，innodb引入了另一个参数来进一步管理LRU列表，这个参数是 innodb_old_blocks_time ，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的前端：

所以就有2种情况：

1、如果在业务中做了大量的全表扫描，那么你就可以将innodb_old_blocks_pct设置减小，增到innodb_old_blocks_time的时间（这些无用的页可能在等待期间就已经被刷回磁盘了），不让这些无用的查询数据进入new区域，尽量不让缓存再new区域的有用的数据被立即刷掉。（这也是治标的方法，大量全表扫描就要优化sql和表索引结构了）

2、如果在业务中没有做大量的全表扫描，那么你就可以将innodb_old_blocks_pct增大，减小innodb_old_blocks_time的时间，让有用的查询缓存数据尽量缓存在innodb_buffer_pool_size中，减小磁盘io，提高性能。

设置：

mysql> set global innodb_old_blocks_pct = 40;

Query OK, 0 rows affected (0.00 sec)

LRU列表用来管理已经读取的页，但当数据库刚启动是，LRU列表是空的，即没有任何的页。这时页都存放在Free 列表中。当需要从缓存池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从free列表中删除，放入LRU列表中。LRU列表中old 变为 new ，称为page made young，而因为inndb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。

mysql> show engine innodb status\G;

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 107380736

Dictionary memory allocated 475369

Buffer pool size 6400

Free buffers 1

Database pages 6393

Old database pages 2568

Modified db pages 0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 221, not young 5083764

0.00 youngs/s, 0.00 non-youngs/s

Pages read 4937, created 12436, written 13624

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 6393, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

--------------

buffer pool size 共有6400个页，即 6400*16K =100M ，总共100M的缓冲池。

free buffers 表示当前free列表中页的数量。

database pages 表示LRU列表中页的数量

（可能的情况是free buffers 加上database pages之和不等于buffer pool size。因为缓冲池中的页还可能会分配给自适应哈希索引、lock信息、insert buffer等页，而这一部分页不需要LRU算法进行维护，因此不存在LRU表中）

pages made young 显示了LRU列表中页移动到前端的次数，0.00 youngs/s, 0.00 non-youngs/s 每秒这两类的操作次数。

还有一个重要的观察变量——buffer pool hit rate，表示缓冲池的命中率。通常该值不能小于95%。若发生buffer pool hit rate 的值小于95% 这种情况，用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题。

注意： show engine innodb status 显示的不是某一时刻或者当前的状态，而是过去某个时间范围内innodb存储引擎的状态。 Per second averages calculated from the last 33 seconds 表示是过去33秒数据库的状态

mysql> show engine innodb status\G;

*************************** 1. row ***************************

Type: InnoDB

Name:

=====================================

Per second averages calculated from the last 33 seconds

从 innodb 1.2 版本开始，还可以通过information_scheme下的 innodb_buffer_pool_stats来观察缓冲池的状态：

mysql> select * from information_schema.innodb_buffer_pool_stats\G;

*************************** 1. row ***************************

POOL_ID: 0

POOL_SIZE: 6400

FREE_BUFFERS: 0

DATABASE_PAGES: 6394

OLD_DATABASE_PAGES: 2569

MODIFIED_DATABASE_PAGES: 0

PENDING_DECOMPRESS: 0

PENDING_READS: 0

PENDING_FLUSH_LRU: 0

PENDING_FLUSH_LIST: 0

PAGES_MADE_YOUNG: 221

PAGES_NOT_MADE_YOUNG: 5083770

PAGES_MADE_YOUNG_RATE: 0

PAGES_MADE_NOT_YOUNG_RATE: 0

NUMBER_PAGES_READ: 4938

NUMBER_PAGES_CREATED: 12436

NUMBER_PAGES_WRITTEN: 13624

PAGES_READ_RATE: 0

PAGES_CREATE_RATE: 0

PAGES_WRITTEN_RATE: 0

NUMBER_PAGES_GET: 28153739

HIT_RATE: 0

YOUNG_MAKE_PER_THOUSAND_GETS: 0

NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0

NUMBER_PAGES_READ_AHEAD: 4265

NUMBER_READ_AHEAD_EVICTED: 42

READ_AHEAD_RATE: 0

READ_AHEAD_EVICTED_RATE: 0

LRU_IO_TOTAL: 0

LRU_IO_CURRENT: 0

UNCOMPRESS_TOTAL: 0

UNCOMPRESS_CURRENT: 0

1 row in set (0.00 sec)

此外，还可以通过表 innodb_buffer_page_lru来观察每个LRU列表中每个页的具体信息，例如通过下面语句可以看到缓冲池LRU列表中前10行的表的页类型：

mysql> select table_name,page_number,space,page_type from information_schema.innodb_buffer_page_lru limit 10;

+---------------+-------------+-------+-------------+

+---------------+-------------+-------+-------------+

| NULL | 7 | 0 | SYSTEM |

| NULL | 3 | 0 | SYSTEM |

| NULL | 2 | 0 | INODE |

| NULL | 4 | 0 | IBUF_INDEX |

| `SYS_INDEXES` | 11 | 0 | INDEX |

| NULL | 1 | 0 | IBUF_BITMAP |

| NULL | 5 | 0 | TRX_SYSTEM |

| NULL | 6 | 0 | SYSTEM |

| NULL | 45 | 0 | SYSTEM |

| NULL | 310 | 0 | UNDO_LOG |

+---------------+-------------+-------+-------------+

innodb存储引擎从1.0.X版本开始支持压缩页的功能，就是把原来16KB的页压缩为1KB,2KB,4KB,8KB。而由于页的大小发生了变化，LRU列表也有了些许的改变。对于非16KB的页，是通过unzip_LRU列表进行管理的。通过命令也可以观察

mysql> show engine innodb status\G;

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

LRU len: 1810, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

可以看到LRU列表共有1810 页，而 unzip_LRU 列表中有 0页。这里需要注意的是 LRU包含了unzip_LRU。

对于压缩页的表，每个表的压缩比各不相同。unzip_LRU怎样从缓冲池中分配内存？

首先 unzip_LRU 列表中会对不同压缩页进行分别管理。其次，通过伙伴算法进行内存分配。

例如现在需要从缓冲池中申请大小为4KB的页：

1.检查unzip_LRU列表中是否存在可用的4KB的空闲页；

2.有则直接使用，若无，则3；

3.检查是否有8KB的空闲页，有，则将页分成2个4KB的页，存放到4KB的unzip_LRU列表中。

4.若无，从LRU列表中申请一个16KB的页，将其分成一个8KB，两个4KB的页，分别存放到对应的unzip_LRU列表中。

同样可以通过information_schema架构下的innodb_buffer_page_lru 来观察unzip_LRU列表中的页，如：

mysql> select table_name,space,page_number,page_type,compressed_size from information_schema.innodb_buffer_page_lru where compressed_size <> 0 limit 10;

<>不等于的意思

flush列表

也可以通过 mysql>show engine innodb status\G;来观察

Buffer pool size 32768

Free buffers 30955

Database pages 1813

Old database pages 689

Modified db pages 0 （表明了脏页数量）

Pending reads 0

也可以通过原数据表来观察，不过要加入 oldest_modification > 0的sql查询条件：

mysql> select table_name,space,page_number,page_type,compressed_size from information_schema.innodb_buffer_page_lru where oldest_modification> 0 limit 10;

table_name 为null表示该页属于系统表空间。

3.重做日志缓冲

innodb存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲（redo log buffer）。innodb存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。

重做日志缓存一般不需要设置很大，因为每一秒会将重做日志缓冲刷新到日志文件中，只要保证重做日志缓存的大小够这一秒的事务量就行。该值可由配置文件参数 innodb_log_buffer_size控制，默认为8M。

mysql> show variables like 'innodb_log_buffer_size'\G

*************************** 1. row ***************************

Variable_name: innodb_log_buffer_size

Value: 16777216

1 row in set (0.00 sec)

[root@localhost ~]# cd /data/mysql_3306

[root@localhost mysql_3306]# ll

total 253068

-rw-r----- 1 mysql mysql 56 May 8 14:25 auto.cnf

drwxr-x--- 2 mysql mysql 4096 May 9 14:18 employees

-rw-r----- 1 mysql mysql 399796 May 9 12:31 error.log

-rw-r----- 1 mysql mysql 12780 May 9 12:31 ib_buffer_pool

-rw-r----- 1 mysql mysql 12582912 May 9 14:33 ibdata1

-rw-r----- 1 mysql mysql 50331648 May 9 14:33 ib_logfile0 （重做日志文件）

-rw-r----- 1 mysql mysql 50331648 May 9 14:19 ib_logfile1 （重做日志文件）

在通常情况下，8M的重做日志缓存足够满足绝大多数应用需求，因为有一下三种情况能够触发重做日志缓存刷新到重做日志文件中：

每一秒，master thread会将重做日志缓存刷新到重做日志文件
每个事务提交前，会将重做日志缓存刷新到重做日志文件中。
当重做日志缓存剩余空间小于1/2时，重做日志缓存刷新到重做日志文件。

4.额外的内存池（5.7.4 版本之后被移除）

innodb存储引擎对内存的管理是一种称为内存堆（heap）的方式进行。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中申请，当该区域内存不够时，再从缓存池中申请。例如：现在分配好了两个缓冲池，但是每个缓冲池中的帧缓存，还有对应的缓冲控制对象（buffer control block），这些对象记录了一些诸如LRU、锁、等待等信息，需要从额外缓冲池中申请。

帧缓存：帧缓冲存储器(Frame Buffer)：简称帧缓存或显存，它是屏幕所显示画面的一个直接映象，又称为位映射图(Bit Map)或光栅。帧缓存的每一存储单元对应屏幕上的一个像素，整个帧缓存对应一帧图像。

引入和移除该参数的原因

早期操作系统的内存分配器性能和可伸缩性较差，并且当时没有适合多核心CPU的内存分配器。所以，InnoDB 实现了一套自己的内存分配系统，做为内存系统的参数之一，引入了innodb_additional_mem_pool_size。

随着多核心CPU的广泛应用和操作系统的成熟，操作系统能够提供性能更高、可伸缩性更好的内存分配器，包括 Hoard、libumem、mtmalloc、ptmalloc、tbbmalloc 和 TCMalloc 等。InnoDB 实现的内存分配器相比操作系统的内存分配器并没有明显优势，所以在之后的版本，会移除 innodb_additional_mem_pool_size 和 innodb_use_sys_malloc 两个参数，统一使用操作系统的内存分配器

2.4 checkpoint 技术

内存中有了脏页，即缓冲池中的页的版本比磁盘中的新，数据库需要将脏页刷新到磁盘。但是又不能每一次一出现脏页就要刷新回磁盘，这样消耗太多性能。如果热点数据集中在某几个页当中，那么数据库的性能就会变得非常差。同时，如果在从缓冲池中将脏页刷回磁盘的时候发生宕机，那么缓冲内存中的脏页数据就恢复不了了。

所以就采用了write ahead log的策略。即当事务提交时，先写重做日志，在进行修改页。当发生宕机，此时的重做日志文件中，已经有了宕机发生时刻的重做日志，可以通过重做日志来恢复。

这也是事务ACID中D （Durability持久性）的要求。

如果重做日志可以无限增大，同时缓冲池也足够大，能够缓存所有数据库的数据，那么是不需要讲缓存池中的脏页刷新回磁盘。因为即使发生宕机，缓冲池中的数据丢失了，也可以从重做日志文件中恢复整个数据库数据到宕机发生的时刻。

但是这两个条件都是不可能的，随着业务的增大，容量总有用尽的一刻。同时成本也太高了。还有一点就是，回复时间会非常长。

因此，就有了checkpoint（检查点）技术，用以解决以下几个问题;

1.缩短数据库恢复时间；

2.缓冲池不够用时，将脏页刷新到磁盘；

3.重做日志不可用时，刷新脏页。

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

此外，当缓冲池不够用时，根据LRU算法，会将最尾端的数据页踢出内存，若此页为脏页，那么需要强制执行checkpoint，将脏页刷新回磁盘。

重做日志不可用的情况是，重做日志文件是循环使用的，而不是任其无限增长的。数据库系统将那些已经不再需要的日志文件（即数据库宕机时，恢复操作不需要用到的日志文件）覆盖重用。若此时重做日志还需要使用，那么必须强制产生checkpoint，将缓冲池中的页至少刷新到当前重做日志位置。

对于innodb存储引擎而言，是通过LSN（log sequence【顺序】 number）来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中有LSN，checkpoint也有LSN。可以通过命令：show engine innodb status\G;

---

LOG

---

Log sequence number 738312947

Log flushed up to 738312947

Pages flushed up to 738312947

Last checkpoint at 738312938

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

innodb内部有两种checkpoint，

sharp checkpoint
fuzzy checkpoint

sharp checkpoint发生在数据库关闭时，将所有缓存池中的脏页刷回磁盘，这是默认的工作方式，即参数：innodb_fast_shutdown = 1。

但是，如果在数据库运行中也使用sharp checkpoint ，那么数据库的性能将受到很大影响。因此，就有了 fuzzy checkpoint进行页的刷新，即只刷新一部分脏页，而不是所有。

innodb存储引擎中可能发生的 fuzzy checkpoint ：

master thread checkpoint
flush_lru_list checkpoint
async/sync flush checkpoint
dirty page too much checkpoint

对于master thread 中发生的checkpoint，差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时innodb可以进行其他操作，用户查询线程不会阻塞。

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

而从mysql 5.6 开始，也就是innodb 1.2.x 版本开始，这个检查就被放到单独的线程 page cleaner thread 中进行，可以通过参数 innodb_lru_scan_depth控制 LRU列表中可用页的数量，默认值为1024，

mysql> show variables like 'innodb_lru_scan%'\G

*************************** 1. row ***************************

Variable_name: innodb_lru_scan_depth

Value: 1024

1 row in set (0.04 sec

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。

阅读全文

0 0