科普文：软件架构数据库系列之【MySQL：innodb buffer pool功能特性及其关键参数】

一、概述

InnoDB的Buffer Pool是其存储引擎中非常重要的一个组件,它的主要功能是缓存数据和索引页,以减少磁盘I/O操作,从而提高查询性能。

科普文：软件架构数据库系列之【MySQL5.7的InnoDB引擎存储结构分析：buffer+disk】_mysql 5.7 innodb存储引擎架构-CSDN博客

科普文：软件架构数据库系列之【MySQL存储引擎InnoDB】-CSDN博客

科普文：软件架构数据库系列之【MySQL5.6体系结构】_mysql 5.6 存储结构-CSDN博客

科普文：软件架构数据库系列之【MySQL前世今生及其体系结构概叙】_mysql oltp-CSDN博客

科普文：软件架构数据库系列之【详解InnoDB双写（Doublewrite Buffer）】-CSDN博客

下面只对innodb的特性和功能做一个总结，想要详细了解的，可以自行阅读上面文章。

‌InnoDB的Buffer Pool具有以下四大特性‌：

1. ‌Change Buffer‌：​ Change Buffer是InnoDB存储引擎中的一个特殊数据结构，用于缓存对不在buffer pool中的二级索引页所做的修改。这些修改可能来自INSERT、UPDATE或DELETE操作（DML）。当这些页被其他读操作加载到buffer pool时，Change Buffer中的修改会被合并到这些页中，从而减少对磁盘的随机I/O操作‌。

2. ‌Double Write‌：Double Write机制用于确保InnoDB在写操作时的数据完整性。在写入磁盘之前，数据首先被写入到两个独立的缓冲区中，然后再从这两个缓冲区写入到最终的磁盘位置。如果其中一个缓冲区写入失败，另一个缓冲区中的数据仍然可以保证数据的一致性，从而防止数据损坏‌。

3. ‌Adaptive Hash Index‌：Adaptive Hash Index（AHI）是InnoDB自动为Buffer Pool中的热点页面构建的哈希索引。这可以加速等值查找操作，提高查询性能。AHI会根据访问模式动态调整，以反映热点页面的变化，从而提高查询效率‌。（MySQL 8.4版本开始，默认关闭自适应哈希索引（Adaptive Hash Index，AHI）特性。）

4. ‌Read Ahead‌：Read Ahead机制预测未来的I/O请求，预先读取数据页到buffer pool中。这样可以减少实际的磁盘I/O操作，提高读取性能。Read Ahead通过预测未来的访问模式，提前加载数据页，从而减少查询时的等待时间‌。

科普文：软件架构数据库系列之【MySQL 8.4 LTS版本20个 InnoDB系统变量默认值修改说明】_mysql 8.4对于8.0-CSDN博客

Buffer Pool的具体功能包括:

1. 缓存数据和索引：Buffer Pool主要用于缓存从磁盘读取的数据页和索引页，减少磁盘I/O操作，提高查询性能。当需要访问某个页面时,InnoDB首先会在Buffer Pool中查找,如果命中则直接从内存读取,避免了磁盘I/O。这大大提高了读取性能。

2. 管理缓存页面：Buffer Pool使用最近最少使用(LRU：Least Recently Used)算法来管理缓存的页面，优先淘汰最近最少使用的页面。当需要为新读入的页面分配空间时,会从LRU列表中淘汰最久未使用的页面。同时也支持多种内存分配机制,以提高缓存命中率。

3. 缓冲区刷新：定期将修改过的脏页刷新回磁盘，保证数据持久性。对于修改过的脏页,Buffer Pool会根据一定策略定期将它们刷新回磁盘,以保证数据持久性。刷新时使用的是检查点(checkpoint)机制,可以有效减少磁盘写操作。

4. 提供内存锁：使用行级锁定机制，提高并发性能。InnoDB使用了行级锁定机制,锁定资源的内存结构就存储在Buffer Pool中。这样就无需在每次访问数据时都从磁盘上读取和写入锁定信息,从而大幅提高并发性能。

5. 支持MVCC：缓存的页面包含行数据的多个版本信息，实现多版本并发控制（MVCC）。Buffer Pool中缓存的页面包含了行数据的多个版本信息,用于实现InnoDB的多版本并发控制(MVCC)机制,支持非锁定读、一致性读等功能。

6. 自适应哈希索引：InnoDB会自动为Buffer Pool中的热点页面构建哈希索引,加速等值查找操作。（MySQL 8.4版本开始，默认关闭自适应哈希索引（Adaptive Hash Index，AHI）特性。）

科普文：软件架构数据库系列之【MySQL引擎Innodb的MVCC特性】_innodb的mvvc-CSDN博客

科普文：软件架构数据库系列之【Innodb的锁和MVCC】-CSDN博客

科普文：软件架构数据库系列之【详解InnoDB恢复recovery过程】-CSDN博客

科普文：软件架构数据库系列之【详解MySQL死锁】_mysql 死锁-CSDN博客

科普文：软件架构数据库系列之【MySQL锁梳理】_一个sql语句相当于一个事务、-CSDN博客

科普文：软件架构数据库系列之【详解MySQL死锁】_mysql 死锁-CSDN博客

科普文：软件架构数据库系列之【MySQL死锁案例分析：网上三 6个死锁案及解决方案 ERROR 1213 (40001): Deadlock】田维常-CSDN博客

二、LRU算法

InnoDB的Buffer Pool中使用LRU(Least Recently Used)算法来管理内存页面的缓存和替换。

科普文：软件架构数据库系列之【 InnoDB 之 Buffer Pool】作者|王康(瀚之)_数据库架构 buffer-CSDN博客

科普文：软件架构数据库系列之【MySQL源码Buf0rea.c--InnoDB缓冲池之LRU算法】-CSDN博客

LRU算法的核心思想是：优先淘汰那些最近最少使用的页面,而保留最近使用过的热点页面留在内存中。这种策略可以有效提高缓存命中率。

InnoDB的LRU算法实现方式是:将Buffer Pool按照最近使用时间划分为新数据空间(new sublist)和旧数据空间(old sublist)两个链表。

1. 新数据空间(new sublist)

   • 存放最近使用过的热点数据页面
   • 当从磁盘读入一个新页面时,会先放入新数据空间
   • 新数据空间空间有限,放不下时会将最旧的页面移动到旧数据空间中

2. 旧数据空间(old sublist)

   • 存放较久未使用的冷数据页面
   • 当需要为新读入页面分配空间时,会从旧数据空间中淘汰最久未使用的页面
   • 如果旧数据空间列表为空,会从新数据空间头部移动部分数据页过来

3. 列表头部是最新访问的热点数据,尾部是最久未使用的冷数据

4. 每次数据从磁盘读入内存时,对应页面会被移动到新数据空间的头部

5. 当有数据页被访问时,会将该页面移动到新数据空间的头部

这样通过新旧数据空间的划分,以及页面在链表中的移动,LRU算法就可以很好地将热点数据保留在内存中,而将冷数据换出内存。

除了基本的LRU算法,InnoDB还支持其他一些缓存管理策略,如:

• midpoint insertion策略,将新读入的页面放在新旧空间的中间位置
• 自适应哈希索引,为热点页面构建哈希索引加速访问

通过合理的页面缓存和替换策略,LRU算法可以在有限内存情况下,最大化缓存命中率,从而提高InnoDB的整体查询性能。

三、监控buffer pool

 SHOW ENGINE INNODB STATUS

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 632904
Buffer pool size   8192
Free buffers       6609
Database pages     1572
Old database pages 560
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1695, not young 5405
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1378, created 1201, written 1278
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: 1572, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------

解读如下：
--------------
1. `Total large memory allocated 0` - 分配的大内存块总数为0
2. `Dictionary memory allocated 632904` - 数据字典使用了632904字节的内存
3. `Buffer pool size 8192` - Buffer Pool的大小为8192页(默认一页为16KB)
4. `Free buffers 6609` - 当前有6609个空闲Buffer页
5. `Database pages 1572` - 当前Buffer Pool中缓存了1572个数据页
6. `Old database pages 560` - 其中560个页是旧的数据页(较久未被使用)
7. `Modified db pages 0` - 当前没有被修改过的脏页
8. `Pending reads 0` - 没有等待读取的页
9. `Pending writes: LRU 0, flush list 0, single page 0` - 没有等待写入的页
10. `Pages made young 1695, not young 5405` - 1695个页被标记为young(最近使用过),5405个页没有被标记为young
11. `0.00 youngs/s, 0.00 non-youngs/s` - 每秒被标记为young和非young的页数为0
12. `Pages read 1378, created 1201, written 1278` - 从磁盘读取了1378个页,创建了1201个新页,写入了1278个页
13. `0.00 reads/s, 0.00 creates/s, 0.00 writes/s` - 每秒读取、创建和写入页的速率都为0
14. `No buffer pool page gets since the last printout` - 自上次输出后没有获取Buffer Pool页
15. `Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s` - 预读页面、未访问就被换出和随机预读的速率都为0
16. `LRU len: 1572, unzip_LRU len: 0` - LRU列表长度为1572,解压LRU列表长度为0
17. `I/O sum[0]:cur[0], unzip sum[0]:cur[0]` - I/O总量为0,当前为0;解压总量为0,当前为0

四、buffer pool相关参数

1、参数

| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |

2、innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size是InnoDB存储引擎的一个配置参数,用于控制InnoDB在分配Buffer Pool内存时的粒度。具体来说,该参数指定了InnoDB一次从操作系统申请的连续内存空间的大小,单位是字节。

例如,如果设置innodb_buffer_pool_chunk_size=134217728(128MB),那么InnoDB在启动时就会一次性从操作系统申请128MB的内存空间,作为Buffer Pool的一部分。如果Buffer Pool的总大小超过128MB,那么InnoDB会继续申请下一个128MB的内存块,以此类推。

设置一个合理的innodb_buffer_pool_chunk_size值很重要,因为它影响了以下几个方面:

1. 内存分配效率
   较大的chunk size可以减少向操作系统申请内存的次数,从而提高分配效率。但是过大的值可能会增加内存浪费。

2. 内存可用性
   如果单个chunk size设置得太大,可能会在某些情况下耗尽系统可用内存,导致操作系统无法分配足够的内存。

3. Buffer Pool扩展灵活性
   较小的chunk size使得Buffer Pool可以更加平滑地增长,避免出现内存不足的情况。

4. Buffer Pool内存区域个数
   每个chunk size对应一个独立的Buffer Pool内存区域,区域越多,InnoDB访问Buffer Pool时就需要更多的内存映射操作。

一般来说,默认的innodb_buffer_pool_chunk_size设置为128MB是一个不错的折中,可以兼顾内存分配效率和Buffer Pool灵活扩展。但是如果服务器内存资源非常充足,可以适当增加该值以提高分配效率。反之,如果内存资源较少,可以减小该值以增加灵活性。

3、innodb_buffer_pool_instances

innodb_buffer_pool_instances是InnoDB存储引擎中另一个重要的参数,它控制了InnoDB Buffer Pool被划分为几个独立的子池(instances)。

具体来说,该参数的作用包括:

1. 并行化Buffer Pool访问
   通过将Buffer Pool划分为多个实例,可以减少多线程同时访问Buffer Pool时的竞争,提高并行访问效率。每个实例都有自己的元数据区域、自由链表等,不同线程可以并行访问不同的实例。

2. 提高内存分配效率
   每个实例内部的内存都是连续的,可以减少内存分配和管理的开销。

3. 避免争用热点数据
   当Buffer Pool被划分为多个实例时,不同实例缓存的数据很可能不同,这样可以减少多个线程访问同一个热点页面的争用情况。

4. 控制最大脏页比例
   每个实例都有自己独立的脏页比例控制,可以更加精细地控制脏页刷新行为。

一般来说,innodb_buffer_pool_instances的推荐设置值是服务器的CPU核心数量。设置多个实例可以提高并行访问效率,但是实例数量过多也会增加一些额外的内存开销。

此外,innodb_buffer_pool_instances参数需要与innodb_buffer_pool_chunk_size参数相配合设置。每个实例都是由一个或多个chunk组成,实例的大小必须是chunk size的整数倍。

4、innodb_buffer_pool_size

innodb_buffer_pool_size是InnoDB存储引擎中最关键的一个参数,它用于设置InnoDB Buffer Pool的总大小。

Buffer Pool是InnoDB存储引擎用于缓存数据和索引页的内存区域,它的大小直接影响着查询的内存命中率和磁盘IO开销。一般来说,Buffer Pool越大,能够缓存的热数据就越多,查询效率就越高。

设置innodb_buffer_pool_size时需要考虑以下几个因素:

1. 服务器物理内存容量
   Buffer Pool的大小不能超过服务器的物理内存容量,否则会导致操作系统过度使用交换区,从而严重影响整体性能。一般建议Buffer Pool占用50%-80%的物理内存。

2. 数据量大小
   如果数据量较大,需要分配更大的Buffer Pool来缓存热点数据。但也需要预留足够的内存给操作系统和其他组件使用。

3. 工作负载特征
   如果工作负载以读查询为主,可以适当增加Buffer Pool大小;如果以写为主,则不需要过大的Buffer Pool。

4. 内存分配粒度
   Buffer Pool的大小必须是innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances配置的整数倍。

5. 内存分配策略
   如果Buffer Pool占用了大部分内存,可能导致内存不足时MySQL无法再申请新内存,从而引发性能问题。

一般来说,在确保其他组件有足够内存的情况下,Buffer Pool越大越好。但也需要根据实际情况进行权衡,避免过度占用内存资源。MySQL 8.0版本中,Buffer Pool的默认大小是128MB,但是对于大多数生产环境来说,这个值都过小了,需要根据服务器内存大小和数据量适当增加该参数的值。

5、innodb_buffer_pool_dump_at_shutdown

innodb_buffer_pool_dump_at_shutdown是一个InnoDB存储引擎的配置参数,用于控制在MySQL服务器正常关闭时,是否将当前的Buffer Pool数据存储到文件系统的文件中。

当该参数设置为ON时,在服务器正常关闭的过程中,InnoDB会将当前热数据页面缓存在Buffer Pool中的数据存储到一个指定的文件中,文件的名称为ib_buffer_pool。该文件默认存储在MySQL数据目录下。

在下次MySQL服务器启动时,InnoDB会自动检查是否存在ib_buffer_pool文件。如果存在,InnoDB会将该文件的内容重新加载到Buffer Pool中,这样可以避免在重启服务器后,需要从磁盘重新加载热数据页面到内存中的过程,从而加快数据库的启动速度。

需要注意的是,该功能只有在MySQL服务器正常关闭时才会生效。如果服务器异常终止或者发生崩溃,那么ib_buffer_pool文件将无法正确写入,Buffer Pool中的数据也就无法持久化。

使用该参数的好处是可以减少MySQL重启后的预热时间,提高可用性。但也有一些需要考虑的缺点:

1. 写入ib_buffer_pool文件会增加服务器关闭时的耗时
2. 该文件可能会占用大量磁盘空间,需要有足够的磁盘空间
3. 在并行复制等场景下,从节点可能无法利用该功能获得性能提升

因此,是否启用该参数需要根据具体的应用场景和需求来权衡。对于对启动时间要求较高、Buffer Pool较大、拥有足够磁盘空间的应用,可以考虑启用该功能。而对于启动时间要求不高或磁盘空间紧缺的场景,则可以关闭该参数。

6、innodb_buffer_pool_dump_now

innodb_buffer_pool_dump_now是一个InnoDB存储引擎的状态变量,用于手动将当前InnoDB Buffer Pool中的数据页面刷新到磁盘文件中。
当执行SET GLOBAL innodb_buffer_pool_dump_now=ON;语句时,InnoDB存储引擎会立即将当前Buffer Pool中已缓存的数据页面刷新到磁盘上的一个文件中,该文件的默认名称为ib_buffer_pool。

这个操作类似于innodb_buffer_pool_dump_at_shutdown参数在MySQL正常关闭时的行为,但它是一个手动触发的在线操作,不需要重启MySQL服务器。

通常在以下几种情况下,可以考虑使用innodb_buffer_pool_dump_now手动刷新Buffer Pool:

1. 热备份前
   在对InnoDB表数据进行热备份之前,可以先执行该操作,将热数据刷新到磁盘,这样可以减少备份期间需要读取的数据页面,从而加快备份速度。

2. 切换Buffer Pool配置
   如果你需要调整innodb_buffer_pool_size或innodb_buffer_pool_instances等参数,可以先执行该操作将当前Buffer Pool数据刷新到磁盘文件中,然后重启MySQL生效新的配置,重启后InnoDB会自动从该文件重载Buffer Pool数据。

需要注意的是,执行该操作时,MySQL实例会被阻塞一段时间,直到Buffer Pool数据被完全刷新到磁盘文件。因此,在执行该操作前,最好先评估一下Buffer Pool的大小,并选择在数据库负载相对较低的时候执行。

7、innodb_buffer_pool_dump_pct

innodb_buffer_pool_dump_pct是一个InnoDB存储引擎的配置参数,用于控制在执行innodb_buffer_pool_dump_now操作时,需要刷新到磁盘的Buffer Pool数据比例。

该参数的取值范围是5-100之间的整数,代表需要刷新到磁盘的Buffer Pool数据页面的百分比。例如,如果设置为25,那么在执行innodb_buffer_pool_dump_now操作时,InnoDB会将当前Buffer Pool中最近使用的25%的数据页面刷新到磁盘文件中。

设置该参数的目的是为了平衡Buffer Pool数据刷新到磁盘所需的时间和所能获得的性能收益。通常情况下:

• 设置较低的值,可以减少innodb_buffer_pool_dump_now操作所需的时间,但是只能刷新较少的"热"数据到磁盘。
• 设置较高的值,能够将更多的"热"数据刷新到磁盘,在重启后可以加快数据加载速度,但也需要更长的刷新时间。

因此,应该根据具体的应用场景和Buffer Pool的大小来设置合适的innodb_buffer_pool_dump_pct值:

• 对于Buffer Pool较大,并且对重启后的数据加载速度要求较高的应用,可以设置较高的值,如80%或90%。
• 对于Buffer Pool较小,或者对重启后的数据加载速度要求不太高的应用,可以设置较低的值,如25%或30%。

需要注意的是,该参数仅在执行innodb_buffer_pool_dump_now操作时才会生效。如果是在MySQL服务器正常关闭时(由innodb_buffer_pool_dump_at_shutdown控制),则会将整个Buffer Pool数据全部刷新到磁盘文件中。

8、innodb_buffer_pool_filename

innodb_buffer_pool_filename是一个InnoDB存储引擎的配置参数,用于指定InnoDB Buffer Pool数据在磁盘上的存储文件名。

当执行innodb_buffer_pool_dump_now操作或者在MySQL服务器正常关闭时(由innodb_buffer_pool_dump_at_shutdown控制),InnoDB会将当前Buffer Pool中的数据页面刷新到一个磁盘文件中。这个磁盘文件的名称默认为ib_buffer_pool,但是我们可以通过设置innodb_buffer_pool_filename参数来指定一个自定义的文件名。

该参数的语法为:

innodb_buffer_pool_filename = 'filename'

其中,filename是你希望使用的文件名,可以包含路径信息。如果只指定文件名而没有路径,则该文件会被存储在MySQL的数据目录下。

例如,如果你希望将Buffer Pool数据存储在/var/lib/mysql/buffer_pool.dat文件中,可以这样设置:

innodb_buffer_pool_filename = '/var/lib/mysql/buffer_pool.dat'

需要注意的是,该参数的配置必须在MySQL实例启动之前就完成,否则将不会生效。另外,为了避免与其他文件名冲突,建议使用一个相对独特的文件名。

9、innodb_buffer_pool_in_core_file

innodb_buffer_pool_in_core_file是一个InnoDB存储引擎的配置参数,用于控制InnoDB Buffer Pool在内核崩溃转储(core dump)文件中是否包含Buffer Pool数据。

当一个进程异常终止时,操作系统会生成一个核心转储文件(core dump file),该文件包含了进程在崩溃时的内存映像。对于MySQL实例,如果发生了崩溃,核心转储文件可以用于调试和问题诊断。

默认情况下,InnoDB Buffer Pool数据不会包含在核心转储文件中,因为Buffer Pool通常占用了大量内存,会导致生成一个非常大的核心转储文件。但是,如果需要对Buffer Pool数据进行调试和分析,可以将innodb_buffer_pool_in_core_file参数设置为ON,这样Buffer Pool数据就会包含在核心转储文件中。

该参数的语法为:

innodb_buffer_pool_in_core_file = ON|OFF

• 设置为ON,表示Buffer Pool数据会包含在核心转储文件中。
• 设置为OFF(默认值),表示Buffer Pool数据不会包含在核心转储文件中。

需要注意的是,将该参数设置为ON会导致生成的核心转储文件变得非常大,可能会占用大量磁盘空间。因此,只有在真正需要对Buffer Pool数据进行调试和分析时,才应该启用该参数。在正常运行时,应该将其设置为OFF,以避免生成过大的核心转储文件。

另外,即使将该参数设置为ON,也不能保证在所有情况下都能获取完整的Buffer Pool数据。如果发生了严重的内存损坏或者操作系统无法生成完整的核心转储文件,Buffer Pool数据可能会被截断或者丢失。

10、innodb_buffer_pool_load_abort

innodb_buffer_pool_load_abort是一个InnoDB存储引擎的配置参数,用于控制在重启MySQL实例时,是否中止从磁盘文件加载InnoDB Buffer Pool数据的操作。

在MySQL实例正常关闭时,InnoDB会将当前Buffer Pool中的"热"数据页面刷新到磁盘文件中(由innodb_buffer_pool_dump_at_shutdown参数控制)。当MySQL实例重新启动时,InnoDB会尝试从这个磁盘文件中加载数据,以重建Buffer Pool的状态,从而加快数据访问的速度。

但是,在某些情况下,从磁盘加载Buffer Pool数据可能会耗费大量时间,导致MySQL实例启动时间过长。这种情况通常发生在Buffer Pool非常大、磁盘I/O性能较差或者存在其他I/O压力时。

innodb_buffer_pool_load_abort参数就是用来控制这种情况的,它的语法为:

innodb_buffer_pool_load_abort = ON|OFF

• 设置为ON,表示如果从磁盘加载Buffer Pool数据的操作耗时超过了一定阈值(目前硬编码为600秒),则InnoDB会中止该操作,直接使用空Buffer Pool启动。
• 设置为OFF(默认值),表示无论从磁盘加载Buffer Pool数据需要多长时间,InnoDB都会一直等待,直到加载完成。

通常情况下,保持默认的OFF值就可以了,因为从磁盘加载Buffer Pool数据通常能够加快后续的数据访问速度,从而获得性能提升。但是,如果你发现MySQL实例在启动时总是由于加载Buffer Pool数据而耗费大量时间,那么可以考虑将该参数设置为ON,以牺牲一些初始性能,换取更快的启动速度。

需要注意的是,即使将该参数设置为ON,在大多数情况下,从磁盘加载Buffer Pool数据的操作也不会被中止,因为600秒的阈值相对来说还是比较长的。只有在极端情况下,加载操作才会被中止。

11、innodb_buffer_pool_load_at_startup

innodb_buffer_pool_load_at_startup是一个InnoDB存储引擎的配置参数,用于控制MySQL实例启动时是否从磁盘文件中加载InnoDB Buffer Pool数据。

在MySQL实例正常关闭时,InnoDB会将当前Buffer Pool中的"热"数据页面刷新到磁盘文件中(由innodb_buffer_pool_dump_at_shutdown参数控制)。当MySQL实例重新启动时,InnoDB默认会尝试从这个磁盘文件中加载数据,以重建Buffer Pool的状态,从而加快数据访问的速度。

innodb_buffer_pool_load_at_startup参数就是用来控制这个加载行为的,它的语法为:

innodb_buffer_pool_load_at_startup = ON|OFF

• 设置为ON(默认值),表示在MySQL实例启动时,InnoDB会尝试从磁盘文件中加载Buffer Pool数据。
• 设置为OFF,表示在MySQL实例启动时,InnoDB不会从磁盘文件中加载Buffer Pool数据,而是使用一个全新的空Buffer Pool。

通常情况下,保持默认的ON值就可以了,因为从磁盘加载Buffer Pool数据通常能够加快后续的数据访问速度,从而获得性能提升。但是,在某些特殊情况下,你可能需要将该参数设置为OFF,例如:

1. 你希望在每次MySQL实例启动时,都使用一个全新的Buffer Pool,以避免被旧数据影响。
2. 由于某些原因,磁盘文件中的Buffer Pool数据已经过时或者损坏,需要重新构建Buffer Pool。

需要注意的是,如果将该参数设置为OFF,那么在MySQL实例启动时,InnoDB将不会尝试从磁盘文件中加载Buffer Pool数据,而是直接使用一个空的Buffer Pool。这可能会导致初始数据访问速度较慢,但是随着Buffer Pool被逐渐填充,性能也会逐步提升。

另外,该参数的设置只影响MySQL实例启动时的行为,不会影响InnoDB在运行过程中将Buffer Pool数据刷新到磁盘文件的操作(由innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_dump_now控制)。

12、innodb_buffer_pool_load_now

innodb_buffer_pool_load_now是一个InnoDB存储引擎的状态变量,而不是配置参数。它用于手动从磁盘文件中加载InnoDB Buffer Pool数据。

在MySQL实例正常关闭时,InnoDB会将当前Buffer Pool中的"热"数据页面刷新到磁盘文件中(由innodb_buffer_pool_dump_at_shutdown参数控制)。当MySQL实例重新启动时,InnoDB默认会尝试从这个磁盘文件中加载数据,以重建Buffer Pool的状态(由innodb_buffer_pool_load_at_startup参数控制)。

但是,在某些情况下,你可能需要在MySQL实例运行期间手动从磁盘文件中加载Buffer Pool数据,例如:

1. 你在启动时将innodb_buffer_pool_load_at_startup设置为OFF,导致Buffer Pool没有从磁盘文件中加载数据。
2. 你在运行期间执行了innodb_buffer_pool_dump_now操作,将当前Buffer Pool数据刷新到磁盘文件中。

这时,你可以通过设置innodb_buffer_pool_load_now状态变量来手动从磁盘文件中加载Buffer Pool数据。

设置方式如下:SET GLOBAL innodb_buffer_pool_load_now=ON;

执行该语句后,InnoDB会立即从磁盘文件中加载Buffer Pool数据,并重建Buffer Pool的状态。加载操作完成后,innodb_buffer_pool_load_now的值会自动重置为OFF。

需要注意的是,手动加载Buffer Pool数据的操作可能会消耗大量I/O资源,并影响当前MySQL实例的性能。因此,建议在低峰期或者维护窗口期间执行该操作。

另外,如果你在启动时已经将innodb_buffer_pool_load_at_startup设置为ON(默认值),那么在大多数情况下,你不需要手动执行innodb_buffer_pool_load_now操作,因为InnoDB会自动从磁盘文件中加载Buffer Pool数据。

五、内存调整

1、配置InnoDB缓冲池大小

在MySQL中,InnoDB缓冲池的大小主要由以下两个参数控制:

1. innodb_buffer_pool_size
2. innodb_buffer_pool_instances

1. innodb_buffer_pool_size

这是控制InnoDB缓冲池总大小的主要参数。它指定了InnoDB用于缓存数据和索引的内存空间大小。该参数的单位为字节,但是你也可以使用K、M、G等单位来指定。

一般来说,你应该将该参数设置为系统可用内存的60%~80%。过大的缓冲池可能导致操作系统进行过多的交换,反而降低性能。过小的缓冲池则可能导致频繁的磁盘I/O,也会影响性能。

例如,在一台拥有32GB内存的服务器上,你可以将该参数设置为:

innodb_buffer_pool_size=24G

2. innodb_buffer_pool_instances

这个参数用于控制InnoDB缓冲池的分区数量。在MySQL 5.5及更高版本中,InnoDB缓冲池可以被划分为多个单独的实例,每个实例都有自己的缓存管理和元数据。这样可以减少互斥量竞争,提高并发性能。

该参数的取值范围是1~64。建议将其设置为innodb_buffer_pool_size除以1GB后的值,但不能超过64。如果该值大于64,则自动设置为64。

例如,对于上面innodb_buffer_pool_size=24G的配置,你可以将innodb_buffer_pool_instances设置为:

innodb_buffer_pool_instances=24

配置步骤

1. 修改MySQL配置文件(如my.cnf),添加或修改上述两个参数的值。
2. 重启MySQL实例,使新配置生效。

注意事项

1. 修改innodb_buffer_pool_size会导致MySQL重新分配内存,因此需要重启实例。而修改innodb_buffer_pool_instances不需要重启。
2. 如果将innodb_buffer_pool_size设置得过大,可能会导致操作系统过度交换,反而降低性能。因此,需要根据实际可用内存来合理设置。
3. 在设置innodb_buffer_pool_instances时,建议将其设置为innodb_buffer_pool_size除以1GB后的值,但不能超过64。
4. 在MySQL 8.0版本中,引入了一个新的参数innodb_buffer_pool_chunk_size,用于控制每个缓冲池实例的大小。一般情况下,无需手动设置该参数。

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

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name                    | Value                            |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+

mysql> SHOW STATUS WHERE Variable_name='Innodb_buffer_pool_resize_status_code'

0：没有正在进行调整大小操作
1：开始调整大小
2：禁用AHI（自适应哈希索引）
3：提取区块
4：获取全局锁
5：调整池大小
6：调整哈希大小
7：调整大小失败

mysql> SHOW STATUS WHERE Variable_name='Innodb_buffer_pool_resize_status_progress'

SELECT variable_name, variable_value 
 FROM performance_schema.global_status 
 WHERE LOWER(variable_name) LIKE "innodb_buffer_pool_resize%";

2、配置缓冲池实例数

在InnoDB存储引擎中,当需要访问一个数据页时,首先会在缓冲池中查找。如果该页面不在缓冲池中,就需要从磁盘读取。为了找到需要的页面,InnoDB必须扫描整个缓冲池,这个过程称为"Buffer Pool Scan"。

频繁的Buffer Pool Scan会消耗大量CPU资源,降低查询性能。因此,MySQL提供了一种机制,使缓冲池对Buffer Pool Scan更加"耐受"(Scan Resistant),从而减少不必要的扫描。

实现这一目标的关键是利用"Buffer Pool Instance"。从MySQL 5.5版本开始,InnoDB缓冲池可以被划分为多个实例(由innodb_buffer_pool_instances参数控制)。每个实例都有自己的元数据(metadata)和锁,可以独立管理自身的缓存数据。

当需要访问一个页面时,InnoDB会先根据页面的标识(如表空间ID和页号)计算出该页面所属的实例,然后只需要扫描该实例,而不必扫描整个缓冲池。这样就大大减少了扫描的范围,提高了效率。

要启用这一优化机制,需要同时满足以下两个条件:

1. innodb_buffer_pool_instances的值大于1
2. 查询访问的表使用的是单独的表空间(每个表空间会被映射到一个Buffer Pool Instance)

如果上述条件不满足,则InnoDB仍然需要扫描整个缓冲池来查找所需的页面。

总的来说,"Making the Buffer Pool Scan Resistant"通过将缓冲池划分为多个实例,并根据页面标识将其映射到对应的实例,从而减小了扫描范围,提高了查询效率。这种优化技术尤其适用于访问大量表的OLAP类型的查询,可以显著提升查询性能。

但是,过多的实例也会增加元数据管理的开销。因此,在设置innodb_buffer_pool_instances时,需要权衡查询模式和元数据开销,选择一个合理的值。MySQL官方建议将其设置为innodb_buffer_pool_size除以1GB的值,但不超过64。

3、缓冲池抗扫描特性

innodb_old_blocks_pct参数控制了LRU列表中"旧"数据块所占的百分比。默认值为37,相当于原来固定的3/8比例。取值范围从5到95,5表示缓冲池中的新页面很快就会老化淘汰,95表示只有5%的缓冲池空间保留给热数据页面,算法接近传统的LRU策略。

innodb_old_blocks_time参数指定了在第一次访问页面后的时间窗口(以毫秒为单位),在此期间如果再次访问该页面,则不会将它移动到LRU列表的最新使用端(最近最少使用端)。默认值为1000毫秒。增加这个值会使更多的数据块从缓冲池中加速老化淘汰。

这两个参数可以控制数据块在LRU列表中的插入位置,以及InnoDB是否对表扫描或索引扫描引入的数据块应用相同的优化策略。

通过合理设置这些参数,可以避免由于读取预读(read-ahead)或表/索引扫描等操作导致的缓冲池频繁置换(churning)问题。在扫描过程中,一个数据页通常会被快速连续访问几次,之后就不会再被访问了。innodb_old_blocks_time参数可以让这些扫描访问的数据页在一定时间内不会被移动到LRU最新使用端,从而加快它们在缓冲池中老化淘汰的速度。

4、配置InnoDB缓冲池预取

InnoDB Buffer Pool Prefetching (Read-Ahead)是InnoDB存储引擎中一种预读取机制,旨在提高查询性能。

工作原理如下:

1. 当InnoDB从磁盘读取一个数据页到缓冲池时,它会尝试线性读取后续的数据页,并将它们也缓存到缓冲池中。

2. 这种提前读取是基于一个假设:如果一个数据页被访问,与它相邻的数据页在不久之后很可能也会被访问。

3. 通过提前读取相邻的数据页,可以减少将来对这些页面的磁盘IO,从而提高查询效率。

4. 这种预读取行为特别适用于做范围扫描的操作,如索引范围扫描、全表扫描等。

5. InnoDB会自动监控预读取行为的效率,如果效率不佳会自动禁用该功能。

相关参数:

• innodb_read_ahead_threshold: 控制触发预读行为的页面访问模式,默认为56(连续访问56页后会触发预读)。

• innodb_read_io_threads: 控制执行异步预读操作的线程数,默认为4。

• innodb_random_read_ahead: 控制对于非顺序读取是否执行预读取,默认为OFF。

Buffer Pool Prefetching利用了数据的局部性原理,通过预读取可能被访问的相邻数据页,减少将来的磁盘IO,提高查询效率。但是过度的预读取也可能浪费资源,因此需要根据实际工作负载进行参数调优。这是InnoDB中一个自动化的IO优化机制。

5、配置缓冲池刷新

InnoDB存储引擎提供了几个与缓冲池刷新(Buffer Pool Flushing)相关的配置参数,可用于优化缓冲池的写入效率和磁盘IO模式。主要涉及以下参数:

1. innodb_max_dirty_pages_pct
   该参数控制缓冲池中可以脏页面(modified pages)的最大比例,默认值为75%。当脏页面比例超过这个值时,InnoDB会主动刷新部分脏页面到磁盘。较高的值可以提高缓冲池利用率,但可能增加恢复时间。

2. innodb_io_capacity
   该参数设置了InnoDB每秒可以执行的I/O操作数量(IOPS),默认为200。InnoDB会根据该值自动调整刷新速率,以避免过度使用磁盘资源。可根据磁盘性能适当调高该值。

3. innodb_flush_neighbors
   控制在刷新一个脏页面时,是否顺带刷新相邻的脏页面,默认为开启(1)。开启该选项可以更好地利用磁盘的顺序写特性,提高效率。

4. innodb_flush_method
   控制InnoDB将脏页面刷新到磁盘时使用的方法,根据操作系统和存储设备不同可选用不同的值,以获得最佳性能。常见值包括fsync、O_DSYNC、littlesync等。

5. innodb_flush_sync
   控制在提交事务时,是否进行同步刷新缓冲池到磁盘。默认值为ON,表示每次提交都会触发同步刷新。可将其设置为0或OFF,以允许后台线程异步刷新,从而提高吞吐量。但这会增加数据丢失风险。

6. innodb_page_cleaners
   控制InnoDB中执行页面刷新操作的线程数量。页面刷新线程负责将缓冲池中的脏页面(modified pages)刷新回磁盘,以确保数据持久性并释放缓冲池空间。默认情况下,innodb_page_cleaners的值为4,即有4个线程同时执行页面刷新操作。增加页面刷新线程数可以提高脏页面的刷新速度,缩短脏页面在内存中的停留时间,从而降低恢复时间。但过多线程也会增加CPU开销。减少页面刷新线程数可以降低CPU开销,但可能导致脏页面在内存中停留时间过长,从而增加恢复时间。页面刷新线程数量还需要与innodb_max_dirty_pages_pct相配合,以确保及时刷新脏页面。对于写密集型工作负载,可适当增大innodb_page_cleaners,以加快脏页面的刷新速度。对于读密集型工作负载,可适当减小innodb_page_cleaners,以降低不必要的CPU开销。如果过大，会与innodb_buffer_pool_instances相同。

7. innodb_max_dirty_pages_pct_lwm
   它设置了一个脏页面(modified pages)比例的下限值。当缓冲池中的脏页面比例低于这个值时,InnoDB会主动刷新一些干净页面(clean pages)出缓冲池,以确保有足够的空闲空间容纳新的脏页面。 默认值是0,表示不启用低水位线机制。设置一个合理的低水位线值(如25%)可以防止缓冲池被干净页面占满,从而确保有足够空间存放新的脏页面,避免频繁的页面置换和磁盘IO。如果低水位线设置过高,可能导致过多页面被刷新出缓冲池,降低缓冲池命中率。如果低水位线设置过低,可能无法有效地为新脏页面预留空间,导致更频繁的页面置换和磁盘IO。通常建议将innodb_max_dirty_pages_pct_lwm设置为innodb_max_dirty_pages_pct的50%~75%左右,以在命中率和磁盘IO之间取得平衡。该参数需要与innodb_max_dirty_pages_pct(高水位线)、innodb_page_cleaners(页面刷新线程数)等参数相配合,共同控制缓冲池的刷新策略。

8. innodb_lru_scan_depth
   控制InnoDB在LRU列表中扫描多少个页面,以查找可以刷新到磁盘的冷数据页面。默认值是1024,表示每次扫描1024个页面。增大该值可以让InnoDB更彻底地扫描LRU列表,找到更多可以刷新的冷数据页面,从而为新的热数据页面提供更多缓冲池空间。但过大的值也会增加CPU开销。减小该值可以减少CPU开销,但可能无法及时释放缓冲池中的冷数据页面空间,导致热数据页面无法进入缓冲池。对于读写混合的工作负载,可适当增大innodb_lru_scan_depth,以确保缓冲池中有足够空间存放新的热数据页面。对于读密集型工作负载,可适当减小innodb_lru_scan_depth,以降低不必要的CPU开销。该参数需要与innodb_max_dirty_pages_pct等其他缓冲池刷新相关的参数相配合,共同确定缓冲池的刷新策略。

9. innodb_adaptive_flushing_lwm
   innodb_adaptive_flushing_lwm是InnoDB存储引擎中用于控制自适应刷新(Adaptive Flushing)低水位线(Low Water Mark)的一个参数。
   设置了一个页面刷新的低水位线百分比。当缓冲池中脏页面(modified pages)的比例低于这个百分比时,InnoDB将减少页面刷新活动。 默认值是10,表示当脏页面比例低于10%时,InnoDB将减少页面刷新活动。增大该值可以让InnoDB在更高的脏页面比例下仍保持较高的刷新活动,从而更快地将脏页面刷新到磁盘,提高数据持久性。但过高的值也可能导致不必要的磁盘IO开销。减小该值可以让InnoDB在较低的脏页面比例下就开始减少刷新活动,从而降低磁盘IO开销。但过低的值可能会影响数据持久性。该参数需要与innodb_max_dirty_pages_pct、innodb_adaptive_flushing等其他缓冲池刷新相关的参数相配合,共同确定缓冲池的自适应刷新策略。对于写密集型工作负载,可适当增大innodb_adaptive_flushing_lwm,以提高数据持久性。 对于读密集型工作负载,可适当减小innodb_adaptive_flushing_lwm,以降低不必要的磁盘IO开销。

10. innodb_adaptive_flushing
    innodb_adaptive_flushing是InnoDB存储引擎中用于控制自适应刷新(Adaptive Flushing)行为的一个配置参数。
    该参数控制是否启用自适应刷新机制。可选值为ON或OFF,默认值为ON。当设置为ON时,InnoDB会根据当前的工作负载动态调整缓冲池的刷新率。如果工作负载以写为主,它会增加刷新率以提高数据持久性;如果工作负载以读为主,它会降低刷新率以减少磁盘IO开销。当设置为OFF时,InnoDB将使用传统的刷新机制,即使用innodb_max_dirty_pages_pct和innodb_max_dirty_pages_pct_lwm等参数控制刷新行为,不会根据工作负载进行动态调整。启用自适应刷新可以让InnoDB更好地应对不同的工作负载场景,提高整体性能和数据持久性。但是,它也会增加一些CPU开销,因为InnoDB需要持续监控工作负载并调整刷新策略。 对于写密集型工作负载,建议启用自适应刷新以提高数据持久性。对于读密集型工作负载,也可以启用自适应刷新,因为它会自动降低刷新率,减少不必要的磁盘IO开销。 如果工作负载比较稳定,也可以考虑关闭自适应刷新,使用传统的刷新机制,以减少CPU开销。

11. innodb_flushing_avg_loops
    在InnoDB中,有一个后台线程负责将缓冲池中的脏页面(modified pages)刷新到磁盘,以确保数据的持久性。这个刷新操作是通过循环执行的,每次循环都会尝试刷新一定数量的页面。
    它设置了每次刷新循环尝试刷新页面的平均次数。 默认值为30,表示每次刷新循环平均会尝试刷新30次。 增大该值可以让InnoDB在每次刷新循环中尝试刷新更多的页面,从而提高数据持久性,但也可能增加磁盘IO开销。减小该值可以减少每次刷新循环的尝试次数,从而降低磁盘IO开销,但可能会影响数据持久性。 该参数需要与innodb_max_dirty_pages_pct、innodb_adaptive_flushing等其他缓冲池刷新相关的参数相配合,共同确定缓冲池的刷新策略。对于写密集型工作负载,可适当增大innodb_flushing_avg_loops,以提高数据持久性。对于读密集型工作负载,可适当减小innodb_flushing_avg_loops,以降低不必要的磁盘IO开销。

12. innodb_idle_flush_pct
    innodb_idle_flush_pct是InnoDB存储引擎中用于控制空闲时的刷新行为的一个参数。
    它设置了在系统空闲时,InnoDB尝试刷新的脏页面比例上限。 默认值为100,表示在系统空闲时,InnoDB会尝试刷新所有的脏页面。 减小该值可以限制InnoDB在系统空闲时刷新的脏页面比例,从而降低空闲时的磁盘IO开销。 增大该值可以让InnoDB在系统空闲时尝试刷新更多的脏页面,提高数据持久性。 该参数需要与innodb_max_dirty_pages_pct、innodb_adaptive_flushing等其他缓冲池刷新相关的参数相配合,共同确定缓冲池的刷新策略。 对于写密集型工作负载,可保持innodb_idle_flush_pct的默认值100,以确保在系统空闲时尽可能地刷新脏页面,提高数据持久性。 对于读密集型工作负载,可适当减小innodb_idle_flush_pct,以降低空闲时的磁盘IO开销。但是,过低的值可能会影响数据持久性。

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

1、配置缓冲池页面的转储百分比

SET GLOBAL innodb_buffer_pool_dump_pct=40;

[mysqld]
innodb_buffer_pool_dump_pct=40

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

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

mysqld --innodb-buffer-pool-load-at-startup=ON;

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

SET GLOBAL innodb_buffer_pool_dump_now=ON;

SET GLOBAL innodb_buffer_pool_load_now=ON;

4、显示缓冲池转储进度

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

5、显示缓冲池加载进度

SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

6、中止缓冲池加载操作

SET GLOBAL innodb_buffer_pool_load_abort=ON;

7、使用性能模式监控缓冲池加载进度\

1. 启用stage/innodb/buffer pool load

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 
       WHERE NAME LIKE 'stage/innodb/buffer%';

1. 启用阶段事件使用者表，其中包括 events_stages_current、 events_stages_history和 events_stages_history_long

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
       WHERE NAME LIKE '%stages%';

1. 通过启用转储当前缓冲池状态 innodb_buffer_pool_dump_now

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

1. 检查缓冲池转储状态以确保操作已完成

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G
*************************** 1. row ***************************
Variable_name: Innodb_buffer_pool_dump_status
        Value: Buffer pool(s) dump completed at 150202 16:38:58

1. 通过启用以下方式加载缓冲池 innodb_buffer_pool_load_now

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

1. 通过查询 Performance Schema 表来检查缓冲池加载操作的当前状态 events_stages_current。该WORK_COMPLETED列显示加载的缓冲池页数。该 WORK_ESTIMATED列提供了剩余工作的估计

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_current;
+-------------------------------+----------------+----------------+
| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
+-------------------------------+----------------+----------------+
| stage/innodb/buffer pool load |           5353 |           7167 |
+-------------------------------+----------------+----------------+

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED 
       FROM performance_schema.events_stages_history;
+-------------------------------+----------------+----------------+
| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
+-------------------------------+----------------+----------------+
| stage/innodb/buffer pool load |           7167 |           7167 |
+-------------------------------+----------------+----------------+