InnoDB内存结构之更改缓冲区

一只小码农正在路过
105 阅读7分钟

InnoDB内存结构之更改缓冲区

更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池中时，它们 会缓存这些更改 。当页面通过其他读取操作加载到缓冲池中时，可能由INSERT， UPDATE或 DELETE操作（DML）导致的缓冲更改 将在以后合并。

与聚簇索引不同，二级索引通常是不唯一的，并且二级索引中的插入以相对随机的顺序发生。同样，删除和更新可能会影响索引树中不相邻的二级索引页。当稍后通过其他操作将受影响的页读入缓冲池时，合并缓存的更改将避免从磁盘将辅助索引页读入缓冲池所需的大量随机访问I / O。

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

当有许多受影响的行和许多辅助索引要更新时，更改缓冲区合并可能需要几个小时。在此期间，磁盘I / O会增加，这可能会导致磁盘绑定查询的速度大大降低。提交事务之后，甚至在服务器关闭并重新启动之后，更改缓冲区合并也可能继续发生（ 有关更多信息，请参见“强制InnoDB恢复”）。

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

更改缓冲区中缓存的数据类型由 innodb_change_buffering变量控制。有关更多信息，请参见 配置变更缓冲。您还可以配置最大更改缓冲区大小。有关更多信息，请参见 配置更改缓冲区最大大小。

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

有关更改缓冲区的常见问题的答案，请参见“ MySQL 5.7 FAQ：InnoDB更改缓冲区”。

配置变更缓冲

在表上执行，和 操作时INSERT， 索引列的值（尤其是辅助键的值）通常处于未排序的顺序，需要大量的I / O才能使辅助索引保持最新状态。该 变化缓冲区时，相关的辅助索引条目缓存变化 页不在 缓冲池，从而通过从磁盘上的页面不会立即读避免了昂贵的I / O操作。当页面加载到缓冲池中时，缓冲的更改将合并，更新的页面随后将刷新到磁盘。的UPDATEDELETEInnoDB当服务器接近空闲时以及在缓慢关闭期间，主线程会合并缓冲的更改 。

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

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

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

允许的innodb_change_buffering 值包括：

  • all

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

  • none

    不要缓冲任何操作。

  • inserts

    缓冲区插入操作。

  • deletes

    缓冲区删除标记操作。

  • changes

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

  • purges

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

您可以innodb_change_buffering在MySQL选项文件（my.cnf或 my.ini）中设置 参数，也可以使用SET GLOBAL 语句动态更改参数，该 语句要求具有足以设置全局系统变量的特权。请参见 “系统变量特权”。更改设置会影响新操作的缓冲。现有缓冲条目的合并不受影响。

配置更改缓冲区最大大小

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

考虑innodb_change_buffer_max_size在具有大量插入，更新和删除活动的MySQL服务器上进行增加， 在这种情况下，更改缓冲区合并不能跟上新的更改缓冲区条目，从而导致更改缓冲区达到其最大大小限制。

考虑innodb_change_buffer_max_size在使用静态数据进行报告的MySQL服务器上减少 存储空间，或者更改缓冲区消耗的缓冲池共享的内存空间过多，从而导致页面比预期的更快地退出缓冲池。

使用代表性的工作负载测试不同的设置，以确定最佳配置。该 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

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

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

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

    有关INNODB_METRICS表用法的信息，请参见 第14.16.6节“ InnoDB INFORMATION_SCHEMA指标表”。

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

    警告

    查询该INNODB_BUFFER_PAGE 表可能会带来很大的性能开销。为避免影响性能，请重现要在测试实例上调查的问题，然后在测试实例上运行查询。

    例如，您可以查询该 INNODB_BUFFER_PAGE表以确定缓冲池页面总数中所包含的IBUF_INDEX和 的大概数量 IBUF_BITMAP。

    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表提供的其他数据的信息 ，请参见 “ INFORMATION_SCHEMA INNODB_BUFFER_PAGE表”。有关相关用法信息，请参见 “ InnoDB INFORMATION_SCHEMA缓冲池表”。

  • Performance Schema 提供更改缓冲区互斥锁等待检测，以进行高级性能监视。要查看更改缓冲区检测，请发出以下查询：

    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 互斥锁等待的信息，请参见 “使用性能模式监视InnoDB Mutex等待”。

    更多内容欢迎关注我的个人公众号“韩哥有话说”，100G人工智能学习资料，大量后端学习资料等你来拿。

qrcode_for_gh_3214f9e3470a_258.jpg

avatar
文章
阅读
粉丝