InnoDB内存结构

目录

 

缓冲池

更改缓冲区

自适应哈希索引

日志缓冲区

---

缓冲池

缓冲池是主内存中的一个区域，InnoDB在访问表和索引数据时将其缓存。缓冲池允许直接从内存中处理经常使用的数据，这加快了处理速度。在专用服务器上，多达80%的物理内存通常分配给缓冲池。

为了提高大容量读取操作的效率，缓冲池被划分为可能包含多行的页。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表；很少使用的数据使用LRU算法的变体从缓存中老化。

了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL优化的一个重要方面。

缓冲池LRU算法

缓冲池作为列表进行管理，使用最近使用最少（LRU）算法的变体。当需要空间将新页添加到缓冲池时，将收回最近使用最少的页，并将新页添加到列表的中间。此中点插入策略将列表视为两个子列表：

• 在头部，最近访问的新（“年轻”）页的子列表
• 在尾部，是一个旧页的子列表，这些旧页最近访问的次数较少

Buffer Pool List

该算法在新的子列表中保留了大量的页面。旧的子列表包含较少使用的页；这些页是要逐出的候选页。

默认情况下，算法的操作如下：

• 缓冲池的3/8用于旧的子列表。
• 列表的中点是新子列表的尾部与旧子列表的头部相交的边界。
• 当InnoDB将一个页面读入缓冲池时，它首先将其插入到中点（旧子列表的头部）。可以读取页面，因为它是用户发起的操作（如SQL查询）所必需的，或者是InnoDB自动执行的预读操作的一部分。
• 访问旧子列表中的页面会使其“年轻”，并将其移动到新子列表的头部。如果由于用户启动的操作需要读取该页，则会立即进行第一次访问，并使该页变为年轻页。如果由于预读操作而读取了该页，则第一次访问不会立即发生，而且在收回该页之前可能根本不会发生。
• 当数据库运行时，缓冲池中不被访问的页面会“老化”到列表的尾部。新的和旧的子列表中的页面随着其他页面的更新而老化。旧子列表中的页面也会随着页面插入到中点而老化。最终，一个未使用的页面到达旧子列表的尾部并被逐出。

默认情况下，查询读取的页面会立即移动到新的子列表中，这意味着它们在缓冲池中停留的时间更长。例如，为mysqldump操作或不带WHERE子句的SELECT语句执行的表扫描可以将大量数据引入缓冲池，并收回等量的旧数据，即使新数据再也不会使用。类似地，由预读后台线程加载并且只访问一次的页面将移动到新列表的头部。这些情况会将经常使用的页面推送到旧的子列表中，在那里它们会被逐出。有关优化此行为的信息，see Section 14.8.3.3, “Making the Buffer Pool Scan Resistant”, and Section 14.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.

InnoDB Standard Monitor输出包含缓冲池和内存部分的几个字段，这些字段与缓冲池LRU算法的操作有关。有关详细信息，请参阅使用InnoDB标准监视器监视缓冲池。Monitoring the Buffer Pool Using the InnoDB Standard Monitor.

缓冲池配置

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

• 理想情况下，您可以将缓冲池的大小设置为尽可能大的值，为服务器上的其他进程留下足够的内存，以便在不进行过度分页的情况下运行。缓冲池越大，InnoDB就越像内存中的数据库，从磁盘读取一次数据，然后在随后的读取过程中从内存访问数据 See Section 14.8.3.1, “Configuring InnoDB Buffer Pool Size”.
• 在具有足够内存的64位系统上，可以将缓冲池分成多个部分，以最小化并发操作之间的内存结构争用。 see Section 14.8.3.2, “Configuring Multiple Buffer Pool Instances”.
• 您可以将频繁访问的数据保存在内存中，而不必考虑将大量不常访问的数据带入缓冲池的操作所导致的活动突然激增。see Section 14.8.3.3, “Making the Buffer Pool Scan Resistant”.
• 您可以控制何时以及如何执行预读请求，以异步将页预取到缓冲池中，以预期不久将需要这些页。 see Section 14.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
• 您可以控制何时进行后台刷新，以及是否根据工作负载动态调整刷新速率。see Section 14.8.3.5, “Configuring Buffer Pool Flushing”.
• 您可以配置InnoDB如何保留当前缓冲池状态，以避免服务器重启后长时间的预热。see Section 14.8.3.6, “Saving and Restoring the Buffer Pool State”.

使用InnoDB标准监视器监视缓冲池

InnoDB标准监视器输出（可以使用SHOW ENGINE InnoDB STATUS访问）提供有关缓冲池操作的度量。缓冲池度量位于InnoDB Standard Monitor输出的缓冲池和内存部分，类似于以下内容：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 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: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

下表描述了InnoDB标准监视器报告的缓冲池度量。

Note

InnoDB标准监视器输出中提供的每秒平均值是基于自上次打印InnoDB标准监视器输出以来经过的时间。

InnoDB缓冲池度量

 

Name	Description
Total memory allocated	The total memory allocated for the buffer pool in bytes.
Dictionary memory allocated	The total memory allocated for the InnoDB data dictionary in bytes.
Buffer pool size	The total size in pages allocated to the buffer pool.
Free buffers	The total size in pages of the buffer pool free list.
Database pages	The total size in pages of the buffer pool LRU list.
Old database pages	The total size in pages of the buffer pool old LRU sublist.
Modified db pages	The current number of pages modified in the buffer pool.
Pending reads	The number of buffer pool pages waiting to be read into the buffer pool.
Pending writes LRU	The number of old dirty pages within the buffer pool to be written from the bottom of the LRU list.
Pending writes flush list	The number of buffer pool pages to be flushed during checkpointing.
Pending writes single page	The number of pending independent page writes within the buffer pool.
Pages made young	The total number of pages made young in the buffer pool LRU list (moved to the head of sublist of “new” pages).
Pages made not young	The total number of pages not made young in the buffer pool LRU list (pages that have remained in the “old” sublist without being made young).
youngs/s	The per second average of accesses to old pages in the buffer pool LRU list that have resulted in making pages young. See the notes that follow this table for more information.
non-youngs/s	The per second average of accesses to old pages in the buffer pool LRU list that have resulted in not making pages young. See the notes that follow this table for more information.
Pages read	The total number of pages read from the buffer pool.
Pages created	The total number of pages created within the buffer pool.
Pages written	The total number of pages written from the buffer pool.
reads/s	The per second average number of buffer pool page reads per second.
creates/s	The per second average number of buffer pool pages created per second.
writes/s	The per second average number of buffer pool page writes per second.
Buffer pool hit rate	The buffer pool page hit rate for pages read from the buffer pool memory vs from disk storage.
young-making rate	The average hit rate at which page accesses have resulted in making pages young. See the notes that follow this table for more information.
not (young-making rate)	The average hit rate at which page accesses have not resulted in making pages young. See the notes that follow this table for more information.
Pages read ahead	The per second average of read ahead operations.
Pages evicted without access	The per second average of the pages evicted without being accessed from the buffer pool.
Random read ahead	The per second average of random read ahead operations.
LRU len	The total size in pages of the buffer pool LRU list.
unzip_LRU len	The total size in pages of the buffer pool unzip_LRU list.
I/O sum	The total number of buffer pool LRU list pages accessed, for the last 50 seconds.
I/O cur	The total number of buffer pool LRU list pages accessed.
I/O unzip sum	The total number of buffer pool unzip_LRU list pages accessed.
I/O unzip cur	The total number of buffer pool unzip_LRU list pages accessed.

Notes:

• youngs/s度量仅适用于旧页。它基于对页面的访问次数，而不是页面数量。对给定页面可以有多个访问，所有访问都被计算在内。如果在没有进行大型扫描时看到非常低的youngs/s值，则可能需要减少延迟时间或增加用于旧子列表的缓冲池百分比。增加百分比会使旧的子列表更大，因此该子列表中的页面移动到尾部所需的时间更长，这会增加再次访问这些页面并使其变年轻的可能性。
•  non-youngs/s指标仅适用于旧页。它基于对页面的访问次数，而不是页面数量。对给定页面可以有多个访问，所有访问都被计算在内。如果在执行大型表扫描时未看到较高的 non-youngs/s 值（以及较高的youngs/s 值），请增加延迟值。
• young-making  rate负责访问所有缓冲池页面，而不仅仅是访问旧子列表中的页面。 young-making 速率和非速率通常不等于总的缓冲池命中率。旧子列表中的页面点击会导致页面移动到新子列表，但新子列表中的页面点击只会导致页面移动到列表的标题，前提是它们与标题之间有一定的距离。
• not（young making rate）是由于innodb_old_blocks_time定义的延迟未满足，或者由于新子列表中的页面点击未导致页面移动到页眉而导致页面访问未导致页面年轻化的平均命中率。此速率说明了对所有缓冲池页的访问，而不仅仅是对旧子列表中页的访问。

缓冲池服务器状态变量和INNODB_Buffer_pool_STATS表提供了INNODB标准监视器输出中的许多相同的缓冲池度量。see Example 14.10, “Querying the INNODB_BUFFER_POOL_STATS Table”.

更改缓冲区

更改缓冲区是一种特殊的数据结构，当辅助索引页不在缓冲池中时，它会将更改缓存到辅助索引页。由插入、更新或删除操作（DML）引起的缓冲更改将在稍后由其他读取操作将页加载到缓冲池中时合并。

Change Buffer

与聚集索引不同，二级索引通常是非均匀的，插入二级索引的顺序相对随机。类似地，删除和更新可能会影响索引树中不相邻的辅助索引页。稍后，当受影响的页被其他操作读取到缓冲池中时，合并缓存的更改可避免从磁盘将辅助索引页读取到缓冲池中所需的大量随机访问I/O。

当系统大部分处于空闲状态或在缓慢关闭期间运行的清除操作会定期将更新的索引页写入磁盘。与将每个值立即写入磁盘相比，清除操作可以更有效地写入一系列索引值的磁盘块。

当有许多受影响的行和许多要更新的辅助索引时，更改缓冲区合并可能需要几个小时。在此期间，磁盘I/O会增加，这可能会导致磁盘绑定查询的速度显著减慢。提交事务之后，甚至在服务器关闭并重新启动之后，更改缓冲区合并也可能继续发生 (see Section 14.22.2, “Forcing InnoDB Recovery” for more information).

在内存中，更改缓冲区占据了缓冲池的一部分。在磁盘上，更改缓冲区是系统表空间的一部分，当数据库服务器关闭时，索引更改将被缓冲。

缓存在更改缓冲区中的数据类型由innodb_change_buffering变量控制。 For more information, see Configuring Change Buffering.还可以配置最大更改缓冲区大小。 For more information, see Configuring the Change Buffer Maximum Size.

如果索引包含降序索引列或主键包含降序索引列，则不支持对辅助索引进行更改缓冲。

有关更改缓冲区的常见问题解答，see Section A.16, “MySQL 5.7 FAQ: InnoDB Change Buffer”.

配置变更缓冲

当在表上执行插入、更新和删除操作时，索引列的值（特别是辅助键的值）通常是按未排序的顺序排列的，需要大量的I/O来更新辅助索引。当相关页不在缓冲池中时，更改缓冲区缓存对辅助索引项的更改，从而避免了昂贵的I/O操作，因为不会立即从磁盘读取该页。当页面加载到缓冲池中时，缓冲的更改被合并，更新的页面稍后刷新到磁盘。InnoDB主线程在服务器接近空闲时以及在缓慢关闭期间合并缓冲的更改。

由于更改缓冲区功能可以减少磁盘读写，因此对于I/O绑定的工作负载（例如具有大量DML操作（如大容量插入）的应用程序）来说，更改缓冲区功能最有价值。

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

您可以使用InnoDB_change_buffering配置参数控制InnoDB执行更改缓冲的范围。您可以启用或禁用插入缓冲、删除操作（当索引记录最初标记为删除时）和清除操作（当索引记录被物理删除时）。更新操作是插入和删除的组合。默认的innodb_change_缓冲值是all。

允许的innodb_change_缓冲值包括：

• all

默认值：缓冲区插入、删除标记操作和清除。

• none

不要缓冲任何操作。

• inserts

缓冲区插入操作。

• deletes

缓冲区删除标记操作。

• changes

缓冲插入和删除标记操作。

• purges

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

您可以在MySQL选项文件（my.cnf或my.ini）中设置innodb_change_buffering参数，也可以使用set GLOBAL语句动态更改它，这需要足够的权限来设置全局系统变量。See Section 5.1.8.1, “System Variable Privileges”.更改设置会影响新操作的缓冲；不影响现有缓冲项的合并。

配置更改缓冲区最大大小

innodb_change_buffer_max_size变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。默认情况下，innodb_change_buffer_max_size设置为25。最大设定值为50。

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

考虑使用用于报告的静态数据减少MySQL服务器上innodb_change_buffer_max_的大小，或者如果change buffer占用了与缓冲池共享的太多内存空间，从而导致页面比预期的更早地从缓冲池老化。

使用具有代表性的工作负载测试不同的设置以确定最佳配置。innodb_change_buffer_max_size设置是动态的，允许在不重新启动服务器的情况下修改设置。

监视变更缓冲区

以下选项可用于更改缓冲区监视：

• InnoDB标准监视器输出包括更改缓冲区状态信息。要查看监视器数据，请发出该SHOW ENGINE INNODB STATUS语句。

mysql> SHOW ENGINE INNODB STATUS\G

更改缓冲区状态信息位于INSERT BUFFER AND ADAPTIVE HASH INDEX 标题下， 并显示类似以下内容：

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s

有关更多信息，请参见 第14.18.3节“ InnoDB标准监视器和锁定监视器输出”。

• INFORMATION_SCHEMA.INNODB_METRICS表提供了INNODB标准监视器输出中的大多数数据点以及其他数据点。要查看更改缓冲区度量和每个度量的描述，请发出以下查询：

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

有关INNODB_METRICS表的使用信息，see Section 14.16.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.

• INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表提供关于缓冲池中每个页面的元数据，包括更改缓冲区索引和更改缓冲区位图页面。更改缓冲区页按页类型标识。PAGE_TYPE. IBUF_INDEX 是更改缓冲区索引页的页类型，IBUF_BITMAP 是更改缓冲区位图页的页类型。

Warning

查询INNODB_BUFFER_PAGE表会带来显著的性能开销。为了避免影响性能，请在测试实例上重现要调查的问题，并在测试实例上运行查询。

例如，您可以查询NoBudBuffelyPype页表，以确定缓冲区页面的百分比为iBFFI索引和iBFFI位图页面的大致数量。

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, 
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100)) 
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+

有关INNODB_BUFFER_PAGE表提供的其他数据的信息， see Section 24.32.1, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”. 有关使用信息，see Section 14.16.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

性能架构为高级性能监视提供更改缓冲区互斥等待检测。要查看更改缓冲区检测，请发出以下查询：

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
+-------------------------------------------------------+---------+-------+

有关监视InnoDB mutex等待的信息，see Section 14.17.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.

自适应哈希索引

自适应散列索引特性使InnoDB能够在具有适当的工作负载和足够的缓冲池内存的系统上执行更像内存中的数据库，而不牺牲事务特性或可靠性。自适应哈希索引功能由innodb_adaptive_hash_index变量启用，或在服务器启动时由--skip innodb adaptive hash index关闭。

根据观察到的搜索模式，使用索引键的前缀构建哈希索引。前缀可以是任意长度，并且可能只有B树中的某些值出现在哈希索引中。哈希索引是根据需要为经常访问的索引页构建的。

如果一个表几乎完全适合主内存，散列索引可以通过启用对任何元素的直接查找，将索引值转换为某种指针来加快查询速度。InnoDB有一个监视索引搜索的机制。如果InnoDB注意到构建散列索引可以使查询受益，那么它会自动这样做。

对于某些工作负载，哈希索引查找的速度大大超过了监视索引查找和维护哈希索引结构的额外工作。在繁重的工作负载（如多个并发连接）下，对自适应哈希索引的访问有时会成为争用源。使用LIKE运算符和%通配符的查询也往往没有好处。对于不受益于自适应哈希索引功能的工作负载，关闭它可以减少不必要的性能开销。由于很难预先预测自适应哈希索引功能是否适合特定的系统和工作负载，请考虑在启用和禁用该功能的情况下运行基准测试。与早期版本相比，MySQL 5.6中的体系结构更改使其更适合禁用自适应哈希索引功能。

在MySQL 5.7中，自适应散列索引特性是分区的。每个索引都绑定到一个特定的分区，每个分区都由一个单独的锁存器保护。分区由innodb_adaptive_hash_index_parts变量控制。在早期版本中，自适应散列索引功能受单个锁存器的保护，在繁重的工作负载下，该锁存器可能成为争用点。innodb_adaptive_hash_index_parts变量默认设置为8。最大设定值为512。

您可以在SHOW ENGINE INNODB STATUS output的信号量部分监视自适应散列索引的使用和争用。如果有许多线程等待在btr0sea.c中创建的RW锁存，请考虑增加自适应哈希索引分区的数量或禁用自适应哈希索引功能。

有关哈希索引的性能特征的信息，see Section 8.3.8, “Comparison of B-Tree and Hash Indexes”.

日志缓冲区

日志缓冲区是存储区域，用于保存要写入磁盘上的日志文件的数据。日志缓冲区大小由innodb_log_buffer_size变量定义 。默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘I / O。

 innodb_flush_log_at_trx_commit 变量控制如何将日志缓冲区的内容写入并刷新到磁盘。该 innodb_flush_log_at_timeout 变量控制日志刷新频率。

有关相关信息，请参见“ 内存配置 ”和 第8.5.4节“优化InnoDB重做日志”。