时间：2021-07-01 10:21:17 帮助过：17人阅读
在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缓冲池的各个方面来提高性能。
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时适用以下条件：
例如，如果缓冲池初始化大小为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_chunk_size之前，计算innodb_buffer_pool_size以确保生成的缓冲池大小是可接受的。
2.4 在线调整缓冲池内部大小机制
调整大小的操作由后台线程执行，当增加缓冲池的大小时，调整大小操作：
PS：当这些操作正在进行时，阻止其他线程访问缓冲池。
当减小缓冲池的大小时，调整大小操作：
在这些操作中，只有对缓冲池进行碎片整理和撤销页面才允许其他线程同时访问缓冲池。
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在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_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_flush_neighbors， innodb_lru_scan_depth可以让你微调缓冲池刷新过程的某些方面，这些选项主要是帮助写密集型的工作负载。如果DML操作较为严重，如果没有较高的值，则刷新可能会下降，会导致缓冲池中的内存过多。或者，如果这种机制过于激进，磁盘写入将会使你的I/O容量饱和，理想的设置取决于你的工作负载，数据访问模式和存储配置（例如数据是否存储在HDD或SSD设备上）。
InnoDB对于具有不断繁重工作负载的系统或者工作负载波动很大的系统，可以使用下面几个配置选项来调整表的刷新行为：
上面提到的大多数选项最适用于长时间运行写入繁重工作负载的服务器。
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运行时恢复缓冲池状态，请发出以下语句：
mysql> SET GLOBAL innodb_buffer_pool_load_now = ON;
如果要终止buffer pool加载，可以指定运行：
mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;
7.4 显示缓冲池保存和加载进度
要想显示将缓冲池状态保存到磁盘时的进度，请发出以下语句：
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool_dump_status‘; +--------------------------------+------------------------------------+ | Variable_name | Value | +--------------------------------+------------------------------------+ | Innodb_buffer_pool_dump_status | Dumping of buffer pool not started | +--------------------------------+------------------------------------+ 1 row in set (0.03 sec)
要想显示加载缓冲池时的进度，请发出以下语句：
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool_load_status‘; +--------------------------------+--------------------------------------------------+ | Variable_name | Value | +--------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170428 16:13:21 | +--------------------------------+--------------------------------------------------+ 1 row in set (0.00 sec)
而且我们可以通过innodb的performance schema监控buffer pool的LOAD状态，打开或者关闭stage/innodb/buffer pool load。
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES‘ WHERE NAME LIKE ‘stage/innodb/buffer%‘;
启动events_stages_current，events_stages_history，events_stages_history_long表监控。
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = ‘YES‘ WHERE NAME LIKE ‘%stages%‘;
通过启用保存当前的缓冲池状态来获取最近的buffer pool状态。
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 170525 18:41:06 1 row in set (0.01 sec)
通过启用恢复当前的缓冲池状态来获取最近加载到buffer pool状态。
mysql> SET GLOBAL innodb_buffer_pool_load_now=ON; Query OK, 0 rows affected (0.00 sec)
通过查询性能模式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 | +-------------------------------+----------------+----------------+
如果缓冲池加载操作已经完成，该表将返回一个空集合。在这种情况下，你可以检查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 | 7167 | 7167 | +-------------------------------+----------------+----------------+
注意：在使用innodb_buffer_pool_load_at_startup启动时加载缓冲池时，还可以使用performance scheme来监视缓冲池负载进度，在这种情况下，需要开启stage/innodb/buffer pool load。有关更多信息，看：Section 25.3, “Performance Schema Startup Configuration”
需要留意的一点是如果是压缩表的话，在读取到buffer pool的时候还是会保持压缩的格式，直到被读取的时候才会调用解压程序进行解压。
MySQL 5.7.18版本相关参数的默认值如下：
# MySQL 5.7.18; mysql> show global variables like ‘%innodb_buffer_pool%‘; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | 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_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 | +-------------------------------------+----------------+ 10 rows in set (0.00 sec)
InnoDB存储引擎介绍-(3)InnoDB缓冲池配置详解
标签：sed 修改文件 异步 运行 内存结构 least 哈希表 log orm