作者：YongJing(敬勇)

配置InnoDB变更缓冲
当在表上执行INSERT、UPDATE和DELETE操作时，索引列的值(特别是辅助键的值)通常是无序的，这需要大量的I/O来更新辅助索引。InnoDB有一个更改缓冲区，当相关的辅助索引页面不在缓冲池中时，它将缓存辅助索引的改变，这样就不会立即从磁盘读取页面，从而避免了昂贵的I/O操作。当页面加载到缓冲池时，缓冲的更改将被合并，更新后的页面稍后将刷新到磁盘。InnoDB主线程在服务器几乎空闲的时候，或者在缓慢关闭的时候，合并缓冲的变更。

因为它可以减少磁盘读写，所以更改缓冲区特性对于I/O限制的工作负载最有价值，例如具有大量DML操作(如批量插入)的应用程序。

但是，更改缓冲区占用了缓冲池的一部分，减少了可用来缓存数据页的内存。如果工作集几乎可以放进缓冲池，或者如果表的辅助索引相对较少，那么禁用更改缓冲可能会很有用。如果工作集完全适合缓冲区，那么更改缓冲区不会带来额外的开销，因为它只应用于不在缓冲池中的页。

你可以使用innodb_change_buffering配置参数来控制InnoDB执行改变缓冲的程度。您可以为插入、删除操作(当索引记录最初被标记为删除时)和清除操作(当索引记录被物理删除时)启用或禁用缓冲。更新操作是插入操作和删除操作的组合。innodb_change_buffer的默认值为all。

允许的innodb_change_buffer值包括如下所示:
.all
默认值:缓冲区插入、删除标记操作和清除操作

.none
不缓冲任何操作

.inserts
缓冲插入操作。

.deletes
缓冲delete-marking操作

.changes
缓冲插入和删除标记操作

.purges
缓冲发生在后台的物理删除操作

你可以在MySQL选项文件(my.cnf或my.ini)中设置innodb_change_buffer参数，或者使用set GLOBAL命令动态修改，这需要超级权限。更改设置会影响新操作的缓冲;现有缓冲项的合并不会受到影响。

配置更改缓冲区的最大大小
从MySQL5.6.2开始，innodb_change_buffer_max_size配置选项允许你配置变更缓冲区的最大大小占缓冲池总大小的百分比。默认情况下，innodb_change_buffer_max_size设置为25。最大值为50。

你可以考虑在MySQL服务器上增加innodb_change_buffer_max_size，增加大量的插入、更新和删除活动，其中更改缓冲区的合并跟不上新的更改缓冲区条目，导致更改缓冲区达到其最大大小限制。

用典型的工作负载测试不同的设置，以确定最佳配置。innodb_change_buffer_max_size设置是动态的，允许您在不重启服务器的情况下修改该设置

配置InnoDB的内存分配器
在开发InnoDB时，操作系统和运行时库提供的内存分配器通常缺乏性能和可伸缩性。那时，还没有针对多核cpu调优的内存分配器库。因此，InnoDB在mem子系统中实现了自己的内存分配器。这个分配器由一个互斥锁保护，这可能成为瓶颈。InnoDB还围绕系统分配器(malloc和free)实现了一个包装器接口，它同样由一个互斥锁保护。

今天，随着多核系统的广泛应用，以及操作系统的成熟，操作系统所提供的内存分配器已经有了显著的改进。与过去相比，这些新的内存分配器性能更好，可伸缩性更强。大多数工作负载，特别是那些经常分配和释放内存的工作负载(比如多表连接)，比使用一个内部的、特定于innodb的内存分配器更好的内存分配器。

你可以通过在MySQL选项文件(my.cnf或my.ini)中设置系统配置参数innodb_use_sys_malloc的值来控制InnoDB是使用自己的内存分配器还是操作系统的分配器。如果设置为ON或1(默认值)，InnoDB使用底层系统的malloc和free函数，而不是自己管理内存池。该参数为非动态配置，仅在系统启动时生效。如果需要继续使用InnoDB内存分配器，请将innodb_use_sys_malloc设置为0。

当InnoDB内存分配器被禁用时，InnoDB会忽略参数innodb_additional_mem_pool_size的值。InnoDB内存分配器使用一个额外的内存池来满足分配请求，而不必回到系统内存分配器。当InnoDB内存分配器被禁用时，所有这样的分配请求都由系统内存分配器来完成。

在使用动态链接的类似于unixr的系统上，只需让环境变量LD_PRELOAD或LD_LIBRARY_PATH指向实现该分配器的动态库，就可以轻松地替换内存分配器。在其他系统上，可能需要进行一些重新链接。请参考您所选择的内存分配器库的文档。

因为当系统内存分配器被使用时(innodb_use_sys_malloc是ON)， InnoDB不能跟踪所有内存的使用情况，所以在SHOW ENGINE InnoDB STATUS命令的输出中BUFFER POOL AND memory部分只包括Total memory allocation中的BUFFER POOL统计信息。任何使用mem子系统或使用ut_malloc分配的内存都被排除在外。

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

您可以配置InnoDB缓冲池的各个方面来提高性能:
.理想情况下，您可以将缓冲池的大小设置为与实际情况一样大的值，从而为服务器上的其他进程留下足够的内存以运行，而不会造成过多的分页。缓冲池越大，InnoDB的行为就越像内存中的数据库，从磁盘读取数据一次，然后在后续读取时访问内存中的数据。

.对于具有大内存大小的64位系统，可以将缓冲池分成多个部分，以最小化并发操作之间对内存结构的争用。

.您可以将频繁访问的数据保存在内存中，虽然备份或报告等操作的活动突然出现峰值

.您可以控制InnoDB何时以及如何执行预读请求，以异步的方式将页面预取到缓冲池中，预期这些页面很快就会被需要

.您可以控制脏页面何时发生后台刷新，以及InnoDB是否根据工作负载动态调整刷新速率

.您可以对InnoDB缓冲池刷新行为进行微调，以提高性能

.您可以配置InnoDB如何保存当前的缓冲池状态，以避免服务器重启后的长时间预热。您还可以在服务器运行时保存当前缓冲池状态

InnoDB缓冲池LRU算法
InnoDB以列表的形式管理缓冲池，使用最近最少使用(LRU)算法的变体。当需要空间向池中添加一个新页面时，InnoDB会删除最近使用最少的页面，并将新页面添加到列表的中间。这个中点插入策略将列表视为两个子列表:
.顶部是最近访问的新(或年轻)页面的子列表
.在尾部，是最近访问较少的旧页面的子列表。

这种算法将查询大量使用的页面保留在新的子列表中。旧的子列表包含较少使用的页面;这些页面可能会被驱逐。

缺省情况下，LRU算法的操作如下:
.3/8的缓冲池用于旧的子列表。

.列表的中点是新子列表的尾部与旧子列表的头部的边界

.当InnoDB将一个页面读入缓冲池时，它最初会将页面插入到中点(旧子列表的头部)。可以读入一个页面，因为它是用户指定操作(如SQL查询)所需要的，或者是InnoDB自动执行的预读操作的一部分

.访问旧子列表中的页面会使其年轻，将其移动到缓冲池的头部(新子列表的头部)。如果因为需要而读入该页，则立即进行第一次访问，并使该页年轻。如果由于预读而读入该页，则不会立即进行第一次访问(可能在该页被驱逐之前根本不会进行第一次访问)。

.在数据库操作时，缓冲池中未被访问的页面会向列表的尾部移动。新子列表和旧子列表中的页面都会随着其他页面的更新而老化。旧子列表中的页面也随着页面插入到中点而老化。最终，长时间未使用的页面会到达旧的子列表的尾部并被移除。

.默认情况下，通过查询读取的页面会立即移动到新的子列表中，这意味着它们在缓冲池中停留的时间更长。一个表扫描(如mysqldump操作，或没有WHERE子句的SELECT语句)可以将大量数据引入缓冲池，并驱逐等量的旧数据，即使新数据永远不会再次使用。类似地，由预读后台线程加载并只访问一次的页面将移到新列表的头部。这些情况会将经常使用的页面推到旧的子列表中，在那里它们会被清除

InnoDB Standard Monitor输出包含了BUFFER POOL AND MEMORY部分的几个字段，这些字段与缓冲池LRU算法的操作有关。

InnoDB缓冲池配置选项
有几个配置选项会影响InnoDB缓冲池的不同方面。
.innodb_buffer_pool_size
指定缓冲池的大小。如果缓冲池很小，而您有足够的内存，那么扩大缓冲池可以通过减少在查询访问InnoDB表时所需的磁盘I/O数量来提高性能。innodb_buffer_pool_size选项是动态的，它允许您在不重启服务器的情况下配置缓冲池大小

.innodb_buffer_pool_chunk_size
定义InnoDB缓冲池调整操作的块大小。

.innodb_buffer_pool_instances
将缓冲池划分为用户指定数量的独立区域，每个区域都有自己的LRU列表和相关的数据结构，以减少并发内存读写操作期间的争用。只有将innodb_buffer_pool_size设置为1GB或更大时，该选项才会生效。指定的总大小将分配到所有缓冲池。为了获得最佳效率，指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为1GB。

.innodb_old_blocks_pct
指定InnoDB用于旧块子列表的缓冲池的大约百分比。取值范围为5 ~ 95。默认值是37(即池的3/8)。

.innodb_old_blockts_time
指定插入旧子列表的页面在第一次访问后必须停留在旧子列表中的时间(以毫秒为单位)，然后才能移动到新子列表。如果该值为0，则插入到旧子列表中的页面在第一次被访问时将立即移动到新子列表中，无论插入后多久发生访问。如果该值大于0，页面将保留在旧的子列表中，直到在第一次访问之后至少那么多毫秒才会发生访问。例如，值为1000会导致页面在第一次访问后在旧的子列表中停留1秒，然后才有资格移动到新的子列表。

设置innodb_old_blocks_time大于0可以防止一次性表扫描仅用于扫描的页面淹没新子列表。为了扫描而读入的页中的行将被快速连续多次访问，但之后该页就不再使用了。如果innodb_old_blocks_time设置为大于处理页面所需时间的值，那么页面将保留在旧的子列表中，并且会被老化到列表的尾部，以便快速被移除。这样，只用于一次性扫描的页面不会损害新子列表中大量使用的页面。

innodb_old_blocks_time可以在运行时设置，因此可以在执行表扫描和转储等操作时临时更改:

SET GLOBAL innodb_old_blocks_time = 1000;
... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;

如果您的目的是用表的内容填充缓冲池，则不适用此策略。例如，基准测试通常在服务器启动时执行表或索引扫描，因为数据在正常使用一段时间后通常会在缓冲池中。在这种情况下，让innodb_old_blocks_time设置为0，至少在预热阶段完成之前。

.innodb_read_ahead_threshold
控制InnoDB用于将页面预取到缓冲池中的线性预读的灵敏度。

.innodb_random_read_ahead
启用随机预读技术，将页面预取到缓冲池中。随机预读是一种技术，它可以根据缓冲池中已经存在的页面预测何时可能需要页面，而不管这些页面的读取顺序如何。Innodb_random_read_ahead默认禁用。

.innodb_adaptive_flushing_lwm
低水位标志表示启用自适应刷新的重做日志容量的百分比

.innodb_flush_nei***ors
指定从缓冲池刷新页是否也会刷新同一区段中的其他脏页

.innodb_flushing_avg_loops
InnoDB保持先前计算的刷新状态快照的迭代次数，控制自适应刷新响应不断变化的工作负载的速度。

.innodb_lru_scan_depth
影响缓冲池刷新操作的算法和启发式算法的参数。主要用于调优I/ o密集型工作负载的性能专家。它指定，每个缓冲池实例，缓冲池LRU列出的page_cleaner线程扫描在多远的位置寻找要刷新的脏页。

.innodb_max_dirty_pages_pct
InnoDB会尝试从缓冲池中刷新数据，这样脏页的百分比就不会超过这个值。请指定范围为0 ~ 99的整数。默认值为75。

.innodb_max_dirty_pages_pct_lwm
低水位标记表示启用预冲洗的脏页的百分比，以控制脏页比率。默认值0完全禁用预刷新行为

.innodb_buffer_pool_filename
指定保存innodb_buffer_pool_dump_at_shutdown或innodb_buffer_pool_dump_now生成的表空间id和页id列表的文件名称。

.innodb_buffer_pool_dump_at_shutdown
指定MySQL服务器关闭时是否记录缓冲池中缓存的页面，以缩短下次重启时的预热过程

.innodb_buffer_pool_load_at_startup
指定在MySQL服务器启动时，缓冲池通过加载它以前持有的相同页面自动升温。通常与innodb_buffer_pool_dump_at_shutdown一起使用。

.innodb_buffer_pool_dump_now
立即记录缓冲池中缓存的页面

.innodb_buffer_pool_load_now
通过加载一组数据页，无需等待服务器重新启动，即可立即预热缓冲池。对于在基准测试期间将缓存内存恢复到已知状态，或者在运行报告或维护查询后让MySQL服务器恢复正常工作负载，都是很有用的。通常与innodb_buffer_pool_dump_now一起使用。

.innodb_buffer_pool_dump_pct
指定每个缓冲池要读出和转储的最近使用的页的百分比。取值范围是1 ~ 100。

.innodb_buffer_pool_load_abort
中断innodb_buffer_pool_load_at_startup或innodb_buffer_pool_load_now触发的缓冲区内容恢复过程。

配置InnodDB Buffer Pool大小
您可以在服务器运行时离线(启动时)或在线配置InnoDB缓冲池大小

增加或减少innodb_buffer_pool_size时，以块为单位进行操作。Chunk大小是由innodb_buffer_pool_chunk_size配置选项定义的，默认值是128M。

缓冲池大小必须等于或等于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设置为4G, innodb_buffer_pool_instances设置为8。innodb_buffer_pool_chunk_size为默认值128M。

4G是有效的innodb_buffer_pool_size值，因为8G是innodb_buffer_pool_instances=8 * innodb_buffer_pool_chunk_size=128M的倍数，即1G。

[mysql@localhost mysql]$ mysqld --innodb_buffer_pool_size=4G --innodb_buffer_pool_instances=8


[mysql@localhost ~]$ mysql -uroot -pxxzx7817600
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           4.000000000000 |
+------------------------------------------+
1 row in set (0.00 sec)

本例中，innodb_buffer_pool_size设置为3G, 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调整为4G,这是下一个的倍数innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances不小于指定的缓冲池大小。

[mysql@localhost mysql]$ mysqld --innodb_buffer_pool_size=3G --innodb_buffer_pool_instances=16

[mysql@localhost ~]$ mysql -uroot -pxxzx7817600
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           4.000000000000 |
+------------------------------------------+
1 row in set (0.00 sec)

配置InnoDB Buffer Pool Chunk大小
innodb_buffer_pool_chunk_size可以以1MB(1048576字节)为单位增加或减少，但只能在启动时修改，在命令行字符串或MySQL配置文件中。

命令行设置如下:

mysqld --innodb_buffer_pool_chunk_size=134217728

配置文件设置如下:
[mysqld]
innodb_buffer_pool_chunk_size=134217728

当修改innodb_buffer_pool_chunk_size时适用以下条件:
.如果新innodb_buffer_pool_chunk_size值乘以innodb_buffer_pool_instances比当前的buffer pool大小大。innodb_buffer_pool_chunk_size被截断为innodb_buffer_pool_size/innodb_buffer_pool_instances

例如，如果buffer pool的初始大小为2GB,4个buffer pool instances和chunk size为1GB，那么chunk size会被截断为innodb_buffer_pool_size/innodb_buffer_pool_instances值:

[mysql@localhost ~]$ mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4 --innodb_buffer_pool_chunk_size=1073741824;


[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+
1 row in set (0.00 sec)
#Chunk size was set to 1GB (1073741824 bytes) on startup but was truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+
1 row in set (0.00 sec)

.buffer pool的大小必须总是等于或是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的倍数，并且不会小于当前的buffer pool大小。当buffer pool被初始化时进行调理。下面演示这个例子:

# The buffer pool has a default size of 128MB (134217728 bytes)
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+
1 row in set (0.00 sec)

# The chunk size is also 128MB (134217728 bytes)
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       134217728 |
+---------------------------------+
1 row in set (0.00 sec)

# There is a single buffer pool instance
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)



# Chunk size is decreased by 1MB (1048576 bytes) at startup  (134217728 - 1048576 = 133169152):
[mysql@localhost ~]$ mysqld --innodb_buffer_pool_chunk_size=133169152

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       133169152 |
+---------------------------------+
1 row in set (0.00 sec)

# Buffer pool size increases from 134217728 to 266338304
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
# that is not less than current buffer pool size


mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 266338304 |
+---------------------------+
1 row in set (0.00 sec)

下面使用多个buffer pool instances来演示这种行为:

# The buffer pool has a default size of 2GB (2147483648 bytes)
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+
1 row in set (0.01 sec)


# The chunk size is .5 GB (536870912 bytes)
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+
1 row in set (0.00 sec)


# There are 4 buffer pool instances
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+
1 row in set (0.00 sec)


# Chunk size is decreased by 1MB (1048576 bytes) at startup (536870912 - 1048576 = 535822336):
[root@localhost ~]# mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4 --innodb_buffer_pool_chunk_size=535822336

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       535822336 |
+---------------------------------+
1 row in set (0.00 sec)



# Buffer pool size increases from 2147483648 to 4286578688
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
# that is not less than current buffer pool size of 2147483648
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                4286578688 |
+---------------------------+
1 row in set (0.00 sec)

当改变innodb_buffer_pool_chunk_size的大小时应该要小心仔细，因为改变这个值可能会增加buffer pool的大小，比如上面的两个示例。在改变innodb_buffer_pool_chunk_size之前，需要计算对innodb_buffer_pool_size的影响确保对buffer pool的改变结果可以接受。

注意:为了避免潜在的性能问题，chunk的数量(innodb_buffer_pool_size/innodb_buffer_pool_chunk_size)不应该超过1000。

联机配置InnoDB Buffer Pool大小
innodb_buffer_pool_size配置选项也可以使用set语句来进行动态设置，在不重启服务器的情况下来重置buffer pool的大小。例如:

mysql> set global innodb_buffer_pool_size=402653184;
Query OK, 0 rows affected (0.00 sec)

通过InnoDB APIs执行的活动事务与操作应该在重置buffer pool大小之前完成执行。当初始化重置操作时，直到所有活动的事务完成之前重置操作不会启动。一旦重置操作正在执行时，直到重置操作完成之前请求访问buffer pool的新事务与操作必须等待。这个规则的例外是，当缓冲池的大小减小并且页面被撤销时，允许对缓冲池进行并发访问。允许并发访问的一个缺点是，当页面被撤销时，它可能会导致可用页面的临时短缺。

监控联机buffer pool重置进程

innodb_buffer_pool_resize_status用于报告buffer pool重置进程。例如:
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 220120 11:13:11. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.04 sec)

buffer pool重置进程也会被记录到服务器错误日志中。上面的例子当增加buffer pool大小时日志信息如下:

2022-01-20T03:13:11.574147Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 402653184 (unit=134217728).
2022-01-20T03:13:11.574149Z 2 [Note] InnoDB: Requested to resize buffer pool. (new size: 402653184 bytes)
2022-01-20T03:13:11.574320Z 0 [Note] InnoDB: Disabling adaptive hash index.
2022-01-20T03:13:11.600728Z 0 [Note] InnoDB: disabled adaptive hash index.
2022-01-20T03:13:11.600820Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2022-01-20T03:13:11.611701Z 0 [Note] InnoDB: Latching whole of buffer pool.
2022-01-20T03:13:11.611904Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 1 to 3.
2022-01-20T03:13:11.652012Z 0 [Note] InnoDB: buffer pool 0 : 2 chunks (16384 blocks) were added.
2022-01-20T03:13:11.652085Z 0 [Note] InnoDB: Resizing hash tables.
2022-01-20T03:13:11.653337Z 0 [Note] InnoDB: buffer pool 0 : hash tables were resized.
2022-01-20T03:13:11.653413Z 0 [Note] InnoDB: Resizing also other hash tables.
2022-01-20T03:13:11.665535Z 0 [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
2022-01-20T03:13:11.665594Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 402653184.
2022-01-20T03:13:11.665626Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2022-01-20T03:13:11.665662Z 0 [Note] InnoDB: Completed resizing buffer pool at 220120 11:13:11.

下面显示了当减少buffer pool大小的日志信息如下:

mysql> set global innodb_buffer_pool_size=209715200;
Query OK, 0 rows affected, 1 warning (0.01 sec)



2022-01-20T03:25:22.635263Z 0 [Note] InnoDB: Resizing buffer pool from 402653184 to 268435456 (unit=134217728).
2022-01-20T03:25:22.635442Z 0 [Note] InnoDB: Disabling adaptive hash index.
2022-01-20T03:25:22.639442Z 0 [Note] InnoDB: disabled adaptive hash index.
2022-01-20T03:25:22.639531Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2022-01-20T03:25:22.639581Z 0 [Note] InnoDB: buffer pool 0 : start to withdraw the last 8192 blocks.
2022-01-20T03:25:22.642058Z 0 [Note] InnoDB: buffer pool 0 : withdrawing blocks. (8192/8192)
2022-01-20T03:25:22.642104Z 0 [Note] InnoDB: buffer pool 0 : withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192/8192).
2022-01-20T03:25:22.642168Z 0 [Note] InnoDB: buffer pool 0 : withdrawn target 8192 blocks.
2022-01-20T03:25:22.642206Z 0 [Note] InnoDB: Latching whole of buffer pool.
2022-01-20T03:25:22.642260Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 3 to 2.
2022-01-20T03:25:22.645518Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were freed.
2022-01-20T03:25:22.645577Z 0 [Note] InnoDB: Completed to resize buffer pool from 402653184 to 268435456.
2022-01-20T03:25:22.645612Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2022-01-20T03:25:22.645660Z 0 [Note] InnoDB: Completed resizing buffer pool at 220120 11:25:22.

联机buffer pool重置内部构件
重置操作是由一个后台线程执行的。当增加buffer pool的大小时，重置操作执行以下操作：
.向chunks中添加页(chunk大小由innodb_buffer_pool_chunk_size决定)

.转换哈希表，列表和指针来使用内存中的新地址

.向可用列表中增加新的内存页

当这些操作正在处理时，访问buffer pool的其它线程会被阻塞。

当减小buffer pool的大小时，重置操作执行以下操作:
.对buffer pool进行碎片整理并回收内存页
.删除chunks中的内存页(chunk大小由innodb_buffer_pool_chunk_size决定)
.转换哈希表，列表和指针来使用内存中的新地址

在这些操作中，只有对缓冲池进行碎片整理和收回页面才允许其他线程并发地访问缓冲池

配置多个buffer pool实例
对于缓冲池在几GB范围内的系统，通过减少不同线程读写缓存页面时的争用，将缓冲池划分为单独的实例可以提高并发性。这个特性通常适用于缓冲池大小在几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。

Making the Buffer Pool Scan Resistant
InnoDB没有使用严格的LRU算法，而是使用了一种技术来最小化被带入缓冲池并且不再被访问的数据量。这样做的目的是确保频繁访问(热)的页面保持在缓冲池中，即使预读和全表扫描会带来新的块，这些块之后可能会被访问，也可能不会被访问。

新读的块被插入到LRU列表的中间。所有新读的页面被插入到一个位置，默认是3/8从LRU列表的尾部。当在缓冲池中第一次访问这些页面时，它们被移动到列表的前面(最近使用的端)。因此，那些从未被访问过的页面永远不会出现在LRU列表的前面，并且会比严格的LRU方法更早老化。这种安排将LRU列表分为两个段，其中插入点下游的页面被认为是旧的，是LRU驱逐的理想对象

你可以控制LRU列表中的插入点，并选择InnoDB是否对通过表扫描或索引扫描进入缓冲池的块应用相同的优化。配置参数innodb_old_blocks_pct控制LRU列表中旧块的百分比。innodb_old_blocks_pct默认值为37，对应原来的3/8固定比例。取值范围是5(缓冲池中的新页面会很快老化)到95(只有5%的缓冲池被预留给热页面，这使得算法接近于我们熟悉的LRU策略)。

使缓冲池不因预读而被搅动的优化可以避免由于表或索引扫描而出现的类似问题。在这些扫描中，一个数据页面通常会被快速连续地访问几次，并且再也不会被碰触。innodb_old_blocks_time配置参数指定了页面在第一次访问后，该页面可以被访问且不被移动到LRU列表的前面(最近使用的端)的时间窗口(以毫秒为单位)。innodb_old_blocks_time默认值为1000。增加这个值会使越来越多的块从缓冲池中更快地老化。

innodb_old_blocks_pct和innodb_old_blocks_time都是动态的，全局的，可以在MySQL选项文件(my.cnf或my.ini)中指定，或者在运行时用SET global命令更改。更改设置需要超级权限。

为了帮助您评估设置这些参数的效果，可以使用SHOW ENGINE INNODB STATUS命令报告缓冲池统计信息

由于这些参数的影响可能会根据硬件配置、数据和工作负载的细节发生很大的变化，所以在任何对性能至关重要的环境或生产环境中更改这些设置之前，始终要进行基准测试来验证其有效性。

在混合的工作负载中，大多数活动是OLTP类型和周期性的批量报告查询，导致大的扫描，设置innodb_old_blocks_time的值在批量运行期间可以帮助保持正常工作负载的工作集在缓冲池中。

当扫描的大表不能完全放入缓冲池时，将innodb_old_blocks_pct设置为一个小值，可以使只读取一次的数据不会占用缓冲池的很大一部分。例如，设置innodb_old_blocks_pct=5可以将只读取一次的数据限制为缓冲池的5%。

当扫描适合内存的小表时，在缓冲池中移动页面的开销更小，所以你可以保留innodb_old_blocks_pct的默认值，甚至更高，比如innodb_old_blocks_pct=50。

innodb_old_blocks_time参数的效果比innodb_old_blocks_pct参数更难预测，innodb_old_blocks_pct参数相对较小，并且随着工作负载的变化而变化。如果通过调整innodb_old_blocks_pct提高的性能还不够，那么可以进行自己的基准测试，以获得最佳值。

配置InnoDB缓冲池预取(预读)
预读请求是一个I/O请求，在缓冲池中异步预取多个页面，预计这些页面很快就会被需要。请求把一个extent内所有页面读取缓冲池。InnoDB使用两种预读算法来提高I/O性能:线性预读是一种技术，它可以根据缓冲池中被顺序访问的页面来预测哪些页面可能很快会被需要。你可以使用配置参数innodb_read_ahead_threshold，通过调整触发异步读请求所需的连续页面访问次数来控制InnoDB何时执行预读操作。在添加这个参数之前，InnoDB只会在读取当前extent的最后一页时，计算是否对整个下一个区段发出异步预取请求。

配置参数innodb_read_ahead_threshold控制InnoDB在检测顺序页面访问模式时的敏感度。如果从一个区顺序读取的页面数大于或等于innodb_read_ahead_threshold, InnoDB会启动一个后续整个区的异步预读操作。Innodb_read_ahead_threshold可以设置为0-64之间的任意值。缺省值是56。值越高，表示访问模式检查越严格。例如，如果您将该值设置为48，则只有在当前区段中连续访问了48个页面时，InnoDB才会触发一个线性预读请求。如果该值为8,InnoDB即使在区段中只有8个页面被顺序访问也会触发异步提前读。您可以在MySQL配置文件中设置该参数的值，或者使用set GLOBAL命令动态更改它，该命令需要超级权限。

随机预读是一种根据缓冲池中已经存在的页面来预测何时可能很快需要这些页面，而不考虑这些页面被读取的顺序的一种技术。如果在缓冲池中发现了来自同一区段的连续13个页面，InnoDB会异步发出一个请求来预取区段的剩余页面。要启用此特性，请将配置变量
innodb_random_read_ahead设置为ON。

SHOW ENGINE INNODB STATUS命令用来查看相关统计信息，以帮助您评估预读算法的有效性。统计信息包括以下全局状态变量的计数器信息

 Innodb_buffer_pool_read_ahead
 Innodb_buffer_pool_read_ahead_evicted
 Innodb_buffer_pool_read_ahead_rnd

这些信息在微调innodb_random_read_ahead设置时非常有用

配置InnoDB缓冲池刷新
InnoDB在后台执行一些任务，包括从缓冲池刷新脏页(那些已经更改但还没有写入数据库文件的页)。

当缓冲池中脏页的百分比达到innodb_max_dirty_pages_pct_lwm设置的低水位时，InnoDB开始刷新缓冲池页面。这个选项的目的是控制缓冲池中脏页的比例，并在理想情况下防止脏页的百分比达到innodb_max_dirty_pages_pct。如果缓冲池中脏页的百分比超过了innodb_max_dirty_pages_pct, InnoDB就会开始主动刷新缓冲池页面。

InnoDB会根据重做日志的生成速度和当前的刷新速度来估算所需的刷新速度。其目的是通过确保缓冲区刷新活动与保持缓冲池清洁的需求保持一致，从而平滑整体性能。当过多的缓冲池刷新限制了用于普通读写活动的I/O容量时，自动调整刷新速率可以帮助避免吞吐量的突然下降

InnoDB以循环的方式使用它的日志文件。在重用日志文件的一部分之前，InnoDB会将包含该部分日志的重做条目的所有脏缓冲池页面刷新到磁盘上，这个过程称为尖锐检查点(sharp checkpoint)。如果工作负载是写密集型的，那么它会生成大量的重做信息，所有这些信息都会写入日志文件。如果日志文件中的所有可用空间都用完，就会出现一个尖锐的检查点，导致吞吐量暂时下降。即使没有达到innodb_max_dirty_pages_pct，这种情
况也会发生。

InnoDB使用一种基于启发式的算法来避免这种情况，通过测量缓冲池中脏页的数量和重做生成的速率。根据这些数据，InnoDB决定每秒钟从缓冲池中刷新多少脏页。这种自适应算法能够应对工作负荷的突然变化。

内部基准测试表明，该算法不仅能在一段时间内保持吞吐量，而且还能显著提高总体吞吐量。

因为自适应刷新可以显著影响工作负载的I/O模式，所以innodb_adaptive_flushing配置参数允许关闭该特性。innodb_adaptive_flushing默认值为“ON”，开启自适应刷新算法。您可以在MySQL选项文件(my.cnf或my.ini)中设置该参数的值，或者使用set GLOBAL命令动态更改它，该命令需要超级权限。

Fine-tuning InnoDB Buffer Pool Flushing(微调InnoDB Buffer Pool Flushing)
配置选项innodb_flush_nei***ors和innodb_lru_scan_depth可以让你优化InnoDB缓冲池的刷新过程。

innodb_flush_nei***ors
指定从缓冲池刷新页是否也会刷新同一区段内的其他脏页。当表数据存储在传统的HDD存储设备上时，与在不同时间刷新单个页面相比，在一个操作中刷新邻居页面可以减少I/O开销(主要用于磁盘寻道操作)。对于存储在SSD上的表数据，寻道时间不是一个重要因素，您可以禁用此设置来分散写操作。

innodb_lru_scan_depth
指定每个缓冲池实例，在缓冲池LRU列表的最下方有多远的页面清理线程扫描要刷新的脏页面。这是一个每秒执行一次的后台操作。

这些选项主要帮助编写密集型工作负载。对于大量的DML活动，如果不够激进，刷新可能会落后，导致缓冲池中过多的内存使用;或者，如果这种机制过于激进，由刷新引起的磁盘写可能会使I/O容量饱和。理想的设置取决于您的工作负载、数据访问模式和存储配置(例如，数据是存储在HDD还是SSD设备上)。

对于具有持续的高负载或波动较大的负载的系统，有几个配置选项可以让您调整InnoDB表的刷新行为:

innodb_adaptive_flushing_lwm
innodb_max_dirty_pages_pct_lwm
innodb_io_capacity_max
innodb_flushing_avg_loops

这些选项提供给innodb_adaptive_flushing选项使用的公式

innodb_adaptive_flushing, innodb_io_capacity和innodb_max_dirty_pages_pct选项受以下选项的限制或扩展

innodb_adaptive_flushing_lwm
innodb_io_capacity_max
innodb_max_dirty_pages_pct_lwm

InnoDB自适应刷新机制并不是在所有情况下都适用。当重做日志有被填满的危险时，它能提供最大的好处。innodb_adaptive_flushing_lwm选项指定重做日志容量的低水位百分比;当超过这个阈值时，即使没有指定innodb_adaptive_flushing选项，InnoDB打开自适应刷新。

如果刷新活动远远落后，InnoDB可以比innodb_io_capacity指定的刷新更积极。innodb_io_capacity_max表示在这种紧急情况下使用的I/O容量上限，因此I/O峰值不会消耗服务器的所有容量。

InnoDB会尝试从缓冲池刷新数据，以保证脏页的百分比不超过innodb_max_dirty_pages_pct的值。innodb_max_dirty_pages_pct的默认值是75。

innodb_max_dirty_pages_pct设置为刷新活动建立的一个目标。它不影响冲洗速度。

innodb_max_dirty_pages_pct_lwm选项指定了一个低水位标值，表示启用预刷新以控制脏页比例的脏页百分比，并在理想情况下防止脏页百分比达到innodb_max_dirty_pages_pct。innodb_max_dirty_pages_pct_lwm=0的值禁用预刷新行为。

上面提到的大多数选项最适用于长时间运行写量大的工作负载的服务器，并且几乎没有减少负载时间来赶上等待写入磁盘的更改。

innodb_flushing_avg_loops定义了InnoDB保存之前计算的刷新状态快照的迭代次数，它控制了自适应刷新对前台负载变化的响应速度。innodb_flushing_avg_loops设置的值越高，意味着InnoDB将保持之前计算的快照时间越长，因此自适应刷新的响应速度会越慢。高值也会减少前台和后台工作之间的积极反馈，但当设置一个高值时它最重要的是要确保InnoDB重做日志利用率不到75%(异步刷新的硬编码限制开始),innodb_max_dirty_pages_pct设置使脏页的数量水平适合工作负载。

如果系统的工作负载一致，innodb_log_file_size比较大，并且没有达到75%的重做日志空间利用率，那么应该使用较高的innodb_flushing_avg_loops值来保持刷新尽可能平滑。对于负载峰值极大的系统或日志文件不提供大量空间的系统，考虑使用较小的
innodb_flushing_avg_loops值。较小的值允许刷新密切跟踪负载，并有助于避免达到75%的重做日志空间利用率。

保存和恢复缓冲池状态
为了减少服务器重启后的预热时间，InnoDB在服务器关闭时为每个缓冲池保存最近使用的页面的百分比，并在服务器启动时恢复这些页面。最近使用的页面的存储百分比由innodb_buffer_pool_dump_at_shutdown配置选项定义。

重新启动一个繁忙的服务器后，通常会有一个预热期，吞吐量会稳步增加，因为缓冲池中的磁盘页会被重新放入内存(查询、更新相同的数据，等等)。在启动时恢复缓冲池的能力，通过重新加载重新启动前缓冲池中的磁盘页，而不是等待DML操作访问相应的行，缩短了预热时间。此外，I/O请求可以批量执行，使整体I/O速度更快。页面加载发生在后台，不会延迟数据库启动。

除了在关闭时保存缓冲池状态并在启动时恢复它之外，您还可以在服务器运行时的任何时候保存和恢复缓冲池状态。例如，在稳定的工作负载下达到稳定的吞吐量后，可以保存缓冲池的状态。您还可以在运行报告或维护作业之后恢复以前的缓冲池状态，这些报告或维护作业将数据页引入到缓冲池中(这些操作只需要这些数据页)，或者在运行其他一些非典型工作负载之后恢复以前的缓冲池状态。

即使一个缓冲池的大小可以是很大(许多GB大小）, InnoDB保存到磁盘的缓冲池数据相比之下是非常小的。只有定位适当页面所需的表空间id和页面id被保存到磁盘。这个信息来自INNODB_BUFFER_PAGE_LRU INFORMATION_SCHEMA表。默认情况下，表空间ID和页面ID数据保存在一个名为ib_buffer_pool的文件中，该文件保存在InnoDB数据目录中。文件名和位置可以使用innodb_buffer_pool_filename配置参数修改。

因为数据会像常规数据库操作一样在缓冲池中缓存和老化，所以如果磁盘页最近更新了，或者DML操作涉及到尚未加载的数据，那么没有问题。加载机制会跳过不再存在的请求页面。

底层机制涉及一个后台线程，该线程被分派来执行转储和加载操作。

压缩表中的磁盘页以压缩形式加载到缓冲池中。在DML操作期间访问页面内容时，像往常一样对页面进行不压缩。因为解压缩页面是一个cpu密集型进程，所以对于并发性来说，在连接线程中执行操作比在执行缓冲池恢复操作的单个线程中执行操作更有效。

保存和恢复缓冲池状态的相关操作介绍如下主题:
设置缓冲池页面转储百分比
在关机时保存缓冲池状态，在启动时恢复它
在线保存和恢复缓冲池状态
显示缓冲池转储进度
显示缓冲池加载进度
中止缓冲池加载操作
使用性能方案监视缓冲池负载进度

设置缓冲池页面转储百分比
在从缓冲池转储页面之前，您可以通过设置innodb_buffer_pool_dump_pct选项来配置最近使用的要转储的缓冲池页面百分比。如果您计划在服务器运行时转储缓冲池页面，则可以动态配置该选项:

mysql> SET GLOBAL innodb_buffer_pool_dump_pct=40;
Query OK, 0 rows affected (0.00 sec)

如果您计划在服务器关闭时转储缓冲池页面，请在配置文件中设置innodb_buffer_pool_dump_pct。

[mysqld]
innodb_buffer_pool_dump_pct=40

当innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup默认启用时，innodb_buffer_pool_dump_pct的默认值从100(转储所有页面)改为25(转储最近使用的25%的页面)。

在关机时保存缓冲池状态，在启动时恢复它要在服务器关闭时保存缓冲池的状态，请在关闭服务器之前发出以下语句

mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
Query OK, 0 rows affected (0.00 sec)

Innodb_buffer_pool_dump_at_shutdown默认开启。

要在服务器启动时恢复缓冲池状态，需要在服务器启动时指定——innodb_buffer_pool_load_at_startup选项

mysqld --innodb_buffer_pool_load_at_startup=ON;

Innodb_buffer_pool_load_at_startup默认开启

在线保存和恢复缓冲池状态
要在MySQL服务器运行时保存缓冲池的状态，执行以下语句:

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
Query OK, 0 rows affected (0.01 sec)

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

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;
Query OK, 0 rows affected (0.01 sec)

显示缓冲池转储进度
要在将缓冲池状态保存到磁盘时显示进度，请发出以下语句:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 220124 11:07:41 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.01 sec)

如果操作尚未启动，则返回not started。如果操作完成，则打印完成时间(例如110505 12:18:02完成)。如果操作正在进行中，会提供状态信息(例如dump buffer pool 5/7, page 237/2873)。

中止缓冲池加载操作
要中止缓冲池加载操作，发出以下语句:

mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;
Query OK, 0 rows affected (0.01 sec)

使用性能方案监视缓冲池负载进度
您可以使用Performance Schema监视缓冲池加载进度。

下面的例子演示了如何启用stage/innodb/buffer pool load阶段活动工具和相关的消费表来监视缓冲池加载进度。
1.启用stage/innodb/buffer pool加载工具

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
    -> WHERE NAME LIKE 'stage/innodb/buffer%';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

2. 启用阶段事件消费者表，其中包括events_stages_current、events_stages_history和events_stages_history_long。

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
    -> WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

3.通过启用innodb_buffer_pool_dump_now转储当前的缓冲池状态。

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
Query OK, 0 rows affected (0.00 sec)

4.检查缓冲池转储状态，确保操作完成

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 220124 11:27:10
1 row in set (0.01 sec)

5.通过启用innodb_buffer_pool_load_now来加载缓冲池

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;
Query OK, 0 rows affected (0.01 sec)

6.通过查询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 |            153 |            167 |
+-------------------------------+----------------+----------------+
1 row in set (0.00 sec)

如果缓冲池加载操作已经完成，则events_stages_current表返回一个空集。在这种情况下，您可以检查events_stages_history表来查看已完成事件的数据。例如

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 |            190 |            190 |
+-------------------------------+----------------+----------------+
1 row in set (0.00 sec)

当使用innodb_buffer_pool_load_at_startup在启动时加载缓冲池时，您也可以使用性能模式监视缓冲池的加载进度。在这种情况下，必须在启动时启用stage/innodb/ buffer pool加载工具和相关的消费者

用InnoDB标准监视器监视缓冲池
InnoDB Standard Monitor输出，可以通过SHOW ENGINE InnoDB STATUS访问，提供了InnoDB缓冲池操作的指标。Buffer pool metrics位于InnoDB Standard Monitor输出的Buffer pool AND MEMORY部分，类似如下所示:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 274857984
Dictionary memory allocated 427952
Buffer pool size   16383
Free buffers       15907
Database pages     476
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 442, created 34, written 39
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: 476, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------

下表描述了InnoDB Standard Monitor报告的InnoDB缓冲池指标
注意:在InnoDB标准监视器输出中提供的每秒平均数据是基于上次打印InnoDB标准监视器输出的时间。

InnoDB Buffer Pool指标
Total memory allocated: 分配给缓冲池的总内存(以字节为单位)
Dictionary memory allocated:分配给InnoDB数据字典的总内存(以字节为单位)
Buffer pool size:分配给缓冲池的页面的总大小
Free buffers:缓冲池空闲列表的总页大小。
Database pages:uffer pool LRU列表的总页面大小
Old database pages:buffer pool old LRU子列表的总页面大小。
Modified db pages:缓冲池中当前修改的页面数。
Pending reads:等待读入缓冲池的缓冲池页数。
Pending writes LRU:缓冲池中从LRU列表底部写入的旧脏页数。
Pending writes flush list:检查点期间要刷新的缓冲池页数
Pending writes single page:缓冲池中挂起的独立页写的次数。
Pages made young:缓冲池LRU列表中年轻的总页数(移动到新页子列表的头部)。
Pages made not young:缓冲池LRU列中不是年轻的总页数（仍然保留在旧子列表中而且没有被设置为年轻的总页数)
youngs/s:对缓冲池LRU列表中旧页面的每秒平均访问次数，导致页面变年轻。有关更多信息，请参见该表后面的注释。
non-youngs/s:对缓冲池LRU列表中旧页面的每秒平均访问次数，导致页面不年轻。有关更多信息，请参见该表后面的注释。
Pages read:从缓冲池读取的总页数
Pages created:在缓冲池中创建的页面总数
Pages written:从缓冲池写入的总页数。
reads/s:每秒读取缓冲池页面的平均次数。
creates/s:每秒创建的缓冲池页面的平均数量。
writes/s:每秒缓冲池页写的平均次数。
Buffer pool hit rate:从缓冲池内存读取的页面与从磁盘存储读取的页面的缓冲池页面命中率
young-making rate:页面访问的平均命中率导致页面年轻。有关更多信息，请参见该表后面的注释。
not (young-making rate):页面访问的平均命中率并没有导致页面变年轻。有关更多信息，请参见该表后面的注释。
Pages read ahead:每秒预读操作的平均值
Pages evicted without access:没有从缓冲池中访问就被逐出的页面的每秒平均值。
Random read ahead:随机预读操作的每秒平均值
LRU len:buffer pool LRU列表的总页面大小
I/O sum:最后50秒访问的缓冲池LRU列表页面总数
I/O cur:buffer pool LRU列表总访问量
I/O unzip sum:访问的缓冲池unzip_LRU列表页面总数
I/O unzip cur:访问的缓冲池unzip_LRU列表页面总数

.young /s的标准只涉及到旧的页面。它基于对页面的访问次数，而不是页面的数量。一个给定的页面可以有多个访问，所有的访问都被计算在内。如果您在没有发生大型扫描时看到非常低的youngs/s值，您可能需要减少延迟时间或增加用于旧子列表的缓冲池的百分比。增加百分比会使旧子列表变大，因此该子列表中的页面移动到尾部和被驱逐的时间会变长。这增加了页面再次被访问的可能性，并使其年轻

.non-youngs/s指标只与旧页面相关。它基于对页面的访问次数，而不是页面的数量。一个给定的页面可以有多个访问，所有的访问都被计算在内。如果在执行大型表扫描时没有看到很多非young /s值(而且young /s值很多)，那么就增加延迟值。

.young-making速率表示对所有缓冲池页面的访问，而不仅仅是对旧子列表中的页面的访问。young-making率和not率通常不会加到总的缓冲池命中率。旧子列表中的页面点击会导致页面移动到新的子列表中，但是新子列表中的页面点击只有当它们与列表头部有一定的距离时才会导致页面移动到列表头部，

.not(young-making率)的平均命中率是指由于定义延迟的innodb_old_blocks_time没有被满足页面访问并没有导致使页面年轻,或者由于新子列表页面点击没有导致页面被搬到了头。这个速率用于访问所有缓冲池页面，而不仅仅是访问旧子列表中的页面。

配置InnoDB为只读操作
通过在服务器启动时启用–innodb-read-only配置选项，您现在可以查询MySQL数据目录在只读介质上的InnoDB表。

如何启用只读
要为只读操作准备实例，请确保在将数据文件存储到只读介质之前将所有必要的信息刷新到数据文件。以禁用改变缓冲区的方式运行MySQL(innodb_change_buffering=0)服务器，并缓慢关闭。

要为整个MySQL实例启用只读模式，请在服务器启动时指定以下配置选项
.–innodb-read-only=1

.如果实例位于只读介质(如DVD或CD)上，或者/var目录不是所有人都可写的:
–pid-file=path_on_writeable_media和–event-scheduler=disabled

.–innodb_temp_data_file_path。该选项指定InnoDB临时表空间数据文件的路径、文件名和文件大小。默认设置为ibtmp1:12M;autoextend，在数据目录下创建ibtmp1临时表空间数据文件。为只读操作准备一个实例，需要将innodb_temp_data_file_path设置为数据目录之外的位置。该路径必须相对于数据目录;例如：
–innodb_temp_data_file_path=../../../tmp/ibtmp1:12M:autoextend

使用场景
这种操作方式适用于以下情况:
.分发一个MySQL应用程序或一组MySQL数据到只读存储介质上，如DVD或CD。

.通常在数据仓库配置中,多个MySQL实例同时查询相同的数据目录。您可以使用这种技术来避免重载MySQL实例可能出现的瓶颈，或者您可以为不同的实例使用不同的配置选项来针对特定类型的查询调优每个实例。

.查询由于安全或数据完整性原因而处于只读状态的数据，如已归档的备份数据。

注意:这个特性主要是为了实现分发和部署中的灵活性，而不是基于只读方面的原始性能。

工作原理
当服务器通过–innodb-read-only选项以只读模式运行时，某些InnoDB特性和组件会减少或完全关闭:
.没有进行更改缓冲，特别是没有从更改缓冲区进行合并。当你准备实例进行只读操作时，为了确保更改缓冲区是空的，禁用更改缓冲区(innodb_change_buffering=0)，并先做一个缓慢的关闭。

.在启动时没有崩溃恢复阶段。实例必须在进入只读状态之前执行了缓慢关闭。

.因为重做日志不用于只读操作，所以在使实例只读之前，可以将innodb_log_file_size设置为最小的大小(1MB)。

.除I/O读线程外的所有后台线程都被关闭。因此，只读实例不会遇到任何死锁。

.关于死锁、监视器输出等信息不会写入临时文件。因此，SHOW ENGINE INNODB STATUS不会产生任何输出。

.如果MySQL服务器以–innodb-read-only启动，但数据目录仍然在可写介质上，root用户仍然可以执行DCL操作，如GRANT和REVOKE。

.对配置选项设置的更改通常会改变写操作的行为，但在服务器处于只读模式时没有影响。

.用于MVCC处理的隔离级别被强制关闭。所有查询都读取记录的最新版本，因为不可能进行更新和删除。

.未使用undo日志。禁用innodb_undo_tablespaces和innodb_undo_directory配置选项的任何设置。

InnoDB内存配置
MySQL将内存分配给各种缓存和缓冲区，以提高数据库操作的性能。当为InnoDB分配内存时，总是考虑操作系统需要的内存，分配给其他应用程序的内存，以及分配给其他MySQL缓冲区和缓存的内存。例如，如果您使用MyISAM表，请考虑分配给键缓冲区的内存量(key_buffer_size)。

InnoDB特定的缓冲区使用以下参数配置：
.innodb_buffer_pool_size定义缓冲池的大小，缓冲池是存放InnoDB表、索引和其他辅助缓冲区缓存数据的内存区域。缓冲池的大小对系统性能很重要，通常建议将innodb_buffer_pool_size配置为系统内存的50%到75%。默认的缓冲池大小是128MB。

在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性。缓冲池实例的数量由innodb_buffer_pool_instances选项控制。默认情况下，InnoDB创建一个缓冲池实例。缓冲池实例的数量可以在启动时配置。

.innodb_log_buffer_size以字节为单位定义InnoDB所要写入磁盘日志文件中的日志缓存区大小。默认大小为16MB。大型日志缓冲区允许运行大型事务，而不需要在事务提交之前将日志写入磁盘。如果有更新、插入或删除许多行的事务，可以考虑增加日志缓冲区的大小以节省磁盘I/O。Innodb_log_buffer_size可以在启动时配置

警告:
在32位GNU/Linux x86上，注意不要将内存占用设置得太高。glibc可能允许进程堆堵塞线程堆栈，这会导致服务器崩溃。如果分配给mysqld进程的全局和每个线程的缓冲区和缓存的内存接近或超过2GB，这是一个风险。

一个类似于下面的公式，计算MySQL的全局和每个线程的内存分配，可以用来估计MySQL的内存使用。您可能需要修改公式，以考虑到MySQL版本和配置中的缓冲区和缓存。
innodb_buffer_pool_size+ key_buffer_size+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)+ max_connections*2MB

每个线程使用一个堆栈(通常是2MB，但在Oracle公司提供的MySQL二进制文件中只有256KB)，在最坏的情况下还使用sort_buffer_size +read_buffer_size的额外内存

在Linux上，如果内核启用了大页支持，InnoDB可以使用大页为其缓冲池分配内存。

InnoDB页面大小配置
innodb_page_size选项指定了MySQL实例的所有InnoDB表空间的页面大小。这个值是在创建实例时设置的，之后保持不变。有效值为64K,32K,16K(默认值 ),8k和4k。另外，也可以以字节为单位来指定页面大小(65536,32768,16384,8192,4096)。

默认页面大小为16k适合于各种工作负载，特别是涉及表扫描的查询和涉及批量更新的DML操作。对于涉及许多小写的OLTP工作负载来说，较小的页面大小可能更有效，在这种情况下，当单个页面包含许多行时，争用可能是一个问题。对于SSD存储设备，较小的页面可能也很有效，SSD存储设备通常使用较小的块大小。保持InnoDB页面大小接近存储设备块大小，可以最大限度地减少被重写到磁盘的未更改数据量。

InnoDB内存配置
MySQL将内存分配给各种缓存和缓冲区，以提高数据库操作的性能。当为InnoDB分配内存时，总是考虑操作系统需要的内存，分配给其他应用程序的内存，以及分配给其他MySQL缓冲区和缓存的内存。例如，如果您使用MyISAM表，请考虑分配给键缓冲区的内存量(key_buffer_size)。

InnoDB临时表空间配置
默认情况下，InnoDB将在innodb_data_home_dir目录中创建一个名为ibtmp1且自动扩展大小略大小12MB的临时表侬间数据文件。默认的临时表空间数据文件配置可以在启动时使用innodb_temp_data_file_path配置选项来进行修改。

innodb_temp_data_file_path选项指定InnoDB临时表空间数据文件的路径，文件名和文件大小。一个文件的完整目录路径是由innodb_data_home_dir和innodb_temp_data_file_path拼接而成的。文件大小以KB,MG或GB为单位进行指定，表示方式为K,M或G。文件的总大小要略大于12MB。

innodb_data_home_dir默认值为MySQL数据目录(datadir)。

InnoDB Undo表空间配置
默认情况下,InnoDB undo日志是系统表空间的一部分。然后，可以选择在一个或多个单独的undo表空间来存储(表空间通常存储在不同的存储设备上)InnoDB undo日志。

innodb_undo_directory配置选项定义了InnoDB为存储undo日志所创建单独表空间的目录路径。这个选项通常与innodb_rollback_segments和innodb_undo_tablespaces选项联合使用，它决定了unod日志在系统表空间这外的磁盘布局。

注意:innodb_undot_tablespaces将在未来的版本中被丢弃和删除。

InnoDB日志文件配置
默认情况下，InnoDB将在MySQL数据目录(datadir)中创建大小为48MB名为ib_logfile0和ib_logfile1的两个日志文件。

下面的选项可以被用来修改默认配置:
.innodb_log_group_home_dir 定义InnoDB日志文件（重做日志）的目录路径。如果这个选项没有被配置，InnoDB日志文件将会在MySQL数据目录（datadir)中创建。

可以使用这个选项将InnodDB日志文件与InnoDB数据文件存储在不同的物理存储位置来避免潜在的I/O资源冲突。例如:

[mysqld]
innodb_log_group_home_dir=/dr3/iblogs

注意:InnoDB不会创建目录，因此要确保在启动MySQL服务器之前日志目录已经存在。要确保MySQL服务器对日志目录有正确权限来创建日志文件。

.innodb_log_files_in_group定义日志组中的日志文件数。默认与建议值为2.

.innodb_log_file_size定义日志组中每个日志文件的大小以字节为单位。日志文件的总大小(innodb_log_file_size*innodb_log_files_in_group)不能超过最大值512GB，也就是略小于512GB。一对大小255GB大小的日志文件，就是略接近最大值但不超过它。默认的日志文件大小为48MB。通常，日志文件的合并大小应该足够大，以便服务器能够平滑工作负载活动的高峰和低谷，这通常意味着有足够的重做日志空间来处理超过一个小时的写活动。该值越大，缓冲池中需要的检查点刷新活动就越少，从而节省磁盘I/O。

系统表空间数据文件配置
系统表空间数据文件是通过innodb_data_file_path和innodb_data_home_dir配置选项来进行配置的。

innodb_data_file_path配置选项被用来配置InnoDB系统表空间数据文件。innodb_data_file_path应该是一个或多个数据文件的规范列表。如果要命名多个数据文件，使用分号（;)来进行分隔：
innodb_data_file_path=datafile_spec1[;datafile_spec2]…

例如，下面的设置创建一个最小大小的系统表空间：

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend

上面的设置配置一个12MB大小命名为ibdata1且自动扩展的数据文件。这里没有指定文件路径，因此，InnoDB将在MySQL数据目录中创建该文件。

数据文件大小可以通过K,M,或G后缀来指示KB，MG或GB。

如果一个表空间包含一个名为ibdata1固定大小为50MB和一个名为ibdata2大小为50MB自动扩展的数据文件可以进行以下配置：

[mysqld]
innodb_data_file_path=ibdata1:50;ibdata2:50:autoextend

数据文件规范的完整语法包括文件名，文件大小和多个选项属性：

file_name:file_size[:autoextend[:max:max_file_size]]

autoexten和max属性只能用于innodb_data_file_path行中的最后一个数据文件

如果对最后一个数据文件指定了autoextend，当表空间中没有可用空间时InnoDB会扩展数据文件大小。默认情况下是一次扩展64MB。为了修改增量，可以修改innodb_autoextend_increment系统变量。

如果存储表空间数据文件的磁盘空间填满，可以在其实磁盘上给表空间增加数据文件。

InnoDB不知道文件系统的最大文件大小，因此要注意文件系统的最大文件大小，比如2GB。为了给自动扩展的数据文件指定一个最大大小，在autoextend属性后面使用max属性。只在限制磁盘使用至关重要的情况下使用max属性，因为超过最大大小会导致致命的错误，可能包括崩溃。下面的设置允许ibdata1增长到限制所指定的500MB：

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

默认情况下InnoDB在MySQL数据目录(datadir)中创建表空间文件。为了显式指定存储位置，使用innodb_data_home_dir选项。例如，为了在名为myibdata目录中创建两个名为ibdata1和ibdata2的数据文件，使用如下配置：

[mysqld]
innodb_data_home_dir=/path/to/myibdata/
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

注意:在给innodb_data_home_dir指定值时需要使用斜杆。

InnoDB不会创建目录，因此确保在启动MySQL服务器之前指定的myibdata目录已经存在。还要确保MySQL服务器对所指定的目录有正确的权限来创建文件。更通俗地说，服务器必须在需要创建数据文件的任何目录中具有访问权限。

InnoDB通过将innodb_data_home_dir的值与数据文件名进行文本连接来形成每个数据文件的目录路径。如果在my.cnf中没有指定innodb_data_home_dir选项，那么默认值是“dot”目录./，意思是MySQL数据目录。（MySQL服务器在开始执行时将其当前工作目录更改为数据目录。）

如果将innodb_data_home_dir指定为空字符串，则可以为innodb_data_file_path值中列出的数据文件指定绝对路径。下面的示例等价于上面的设置：

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/path/to/myibdata/ibdata1:50M;/path/to/myibdata/ibdata2:50M:autoextend