【MySQL 5.7 Reference Manual】15.4.2 Change Buffer(变更缓冲)

翻译 2017年01月02日 21:08:18
15.4.2 Change Buffer(变更缓冲)

The change buffer is a special data structure that caches changes to secondary index pages when affected pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

变更缓冲是一个特殊的数据结构,当目标页不在缓冲池中时,变更缓冲负责缓存对二级索引页的变更。被缓冲的变更内容可能是INSERT,UPDATE,或DELETE操作(DML)的结果。在下一次读操作时这些页会被载入缓冲池,之后变更缓冲中的内容将被合并。

Unlike clustered indexes, secondary indexes are usually non-unique, and inserts into secondary indexes happen in a relatively random order. Similarly, deletes and updates may affect secondary index pages that are not adjacently located in an index tree. Merging cached changes at a later time, when affected pages are read into the buffer pool by other operations, avoids substantial random access I/O that would be required to read-in secondary index pages from disk.

与聚簇索引不同,二级索引通常不唯一,并且插入二级索引的顺序比较随机。删除和更新对二级索引页产生类似的影响,这是因为目标页在索引树上的位置并不相邻。被影响的页会通过某些操作被读入缓冲池,这个过程需要从磁盘上读取二级索引页,所以合并已缓存变更内容的操作的会在此过程之后执行,以避免大量的随机I/O。

Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently than if each value were written to disk immediately.

清理操作发生在系统基本空闲或缓慢关闭期间,它会周期性的把更新过的索引页写入磁盘。清理操作可以把索引值批量写入磁盘,这样比把单个值直接写入磁盘更有效率。

Change buffer merging may take several hours when there are numerous secondary indexes to update and many affected rows. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed. In fact, change buffer merging may continue to occur after a server shutdown and restart (see Section 15.21.2, “Forcing InnoDB Recovery” for more information).

当有许多二级索引要更新并且同时影响多行记录时,变更缓冲合并可能需要耗费数个小时。在此期间,磁盘I/O不断增加,这样会造成磁盘读取性能显著下降。变更缓冲合并可能会在事务提交后继续发生。事实上,变更缓冲合并也可能在服务关闭和重启后继续发生(更多信息,请参考15.21.2, “强制InnoDB恢复”)。

In memory, the change buffer occupies part of the InnoDB buffer pool. On disk, the change buffer is part of the system tablespace, so that index changes remain buffered across database restarts.

在内存中,变更缓冲占用了InnoDB缓冲池的部分空间。在磁盘上,变更缓冲是系统表空间的一部分,这使得索引变更即使在系统重启后仍然处于缓冲状态。

The type of data cached in the change buffer is governed by the innodb_change_buffering configuration option. For more information, see Section 15.6.5, “Configuring InnoDB Change Buffering”. You can also configure the maximum change buffer size. For more information, see Section 15.6.5.1, “Configuring the Change Buffer Maximum Size”.

变更缓冲中缓存的数据类型通过innodb_change_buffering配置项进行管理。更多信息,请看15.6.5,“配置InnoDB变更缓冲”。你也可以配置变更缓冲大小的上限。更多信息,请看15.6.5.1,“配置变更缓冲大小上限”。

Monitoring the Change Buffer(监控变更缓冲)

The following options are available for change buffer monitoring:

以下选项可用于变更缓冲监控:

  • InnoDB Standard Monitor output includes status information for the change buffer. To view monitor data, issue the SHOW ENGINE INNODB STATUS command.

在InnoDB标准监视器中会输出变更缓冲的状态信息。要查看监控数据,就输入“SHOW ENGINE INNODB STATUS ”命令。
mysql> SHOW ENGINE INNODB STATUS\G
Change buffer status information is located under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading and appears similar to the following:

变更缓冲的状态信息位于“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
For more information, see Section 15.17.3, “InnoDB Standard Monitor and Lock Monitor Output”.

更多信息,请参考15.17.3,“InnoDB标准监视器与锁定监视器输出”。

  • The INFORMATION_SCHEMA.INNODB_METRICS table provides most of the data points found in InnoDB Standard Monitor output, plus other data points. To view change buffer metrics and a description of each, issue the following query:

INFORMATION_SCHEMA.INNODB_METRICS表提供了在InnoDB标准监视器中输出的大部分数据点,以及其他数据点。要看变更缓冲的指标及其描述,输入以下查询:
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
For INNODB_METRICS table usage information, see Section 15.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.

如何使用INNODB_METRICS表的信息,请参考15.15.6,“InnoDB INFORMATION_SCHEMA指标表 Table”。

  • The INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table provides metadata about each page in the buffer pool, including change buffer index and change buffer bitmap pages. Change buffer pages are identified by PAGE_TYPE. IBUF_INDEX is the page type for change buffer index pages, and IBUF_BITMAP is the page type for change buffer bitmap pages.

INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表提供缓冲池中每一页的元数据,包括变更缓冲索引页和变更缓冲位图页。变更缓冲页是通过一些标记来识别的,PAGE_TYPE. IBUF_INDEX表示页类型是变更缓冲索引页,IBUF_BITMAP表示页类型是变更缓冲位图页。
Warning
Querying the INNODB_BUFFER_PAGE table can introduce significant performance overhead. To avoid impacting performance, reproduce the issue you want to investigate on a test instance and run your queries on the test instance.
For example, you can query the INNODB_BUFFER_PAGE table to determine the approximate number of IBUF_INDEX and IBUF_BITMAP pages as a percentage of total buffer pool pages.

例如,你可以查询INNODB_BUFFER_PAGE,通过计算占总缓冲池页的百分比来确认IBUF_INDEX页和IBUF_BITMAP页的大致数量。
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 |
+---------------------+-------------+-------------------------------+

For information about other data provided by the INNODB_BUFFER_PAGE table, seeSection 23.31.1, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”. For related usage information, see Section 15.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

更多关于INNODB_BUFFER_PAGE表所提供的其他数据的信息,请参考23.31.1,“INFORMATION_SCHEMA INNODB_BUFFER_PAGE表”。相关用法,请参考 15.15.5,“InnoDB INFORMATION_SCHEMA缓冲池表”。

  • Performance Schema provides change buffer mutex wait instrumentation for advanced performance monitoring. To view change buffer instrumentation, issue the following query:

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   |
+-------------------------------------------------------+---------+-------+
For information about monitoring InnoDB mutex waits, see Section 15.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.

更多关于监控InnoDB互斥等待的信息,请参考15.16.2,“使用Performance Schema监控InnoDB互斥等待”。

InnoDB缓冲池预加载在MySQL 5.7中的正确打开方式

2017-06-12 徐肖霞 译 DBAplus社群 DBAplus社群译者:徐肖霞(新炬网络DBA工程师) 译文审核:葛云杰 在这篇文章里,我将讨论在MySQL 5.7里如何使用Inn...
  • u011277123
  • u011277123
  • 2017年06月13日 09:04
  • 320

mysql5.7主从复制

mysql5.7主从复制(最简配置)
  • caicongyang
  • caicongyang
  • 2016年03月06日 15:52
  • 2223

MySQL 5.7版本新特性(修改buffer pool,无需重启服务)

优化online操作,例如修改buffer pool、修改索引名(非主键)、修改REPLICATION FILTER、修改MASTER而无需关闭SLAVE线程 等众多特性。 如果是加大b...
  • haolyj98
  • haolyj98
  • 2016年01月18日 14:28
  • 1030

mysql5.7主从复制--在线变更复制类型

上篇说道mysql5.7基于日志的主从复制  mysql5.7主从复制---基于日志的复制           这里说一下关于如何在线变更复制类型(日志复制到全局事物复制),参考课程:mysql5....
  • u014739462
  • u014739462
  • 2017年04月15日 11:38
  • 319

MySQL 5.7新特性

新增特性Security improvements. mysql.user表新增plugin列,且若某账户该字段值为空则账户不能使用。从低版本MySQL升级至MySQL5.7时要注意该问题,且建议D...
  • zyz511919766
  • zyz511919766
  • 2015年11月25日 15:42
  • 13763

Mysql 5.7密码过期修改密码

今天在运行项目的时候出现:“Your password has expired. To log in you must change it using a client that supports e...
  • u012365843
  • u012365843
  • 2015年09月14日 11:17
  • 2871

mySQL5.6新特性快速预热Buffer_Pool缓冲池

在之前的版本里,如果一台高负荷的机器重启后,内存中大量的热数据被清空,此时就会重新从磁盘加载到Buffer_Pool缓冲池里,这样当高峰期间,性能就会变得很差,连接数就会很高。 在MySQL5....
  • jumewo
  • jumewo
  • 2015年08月02日 19:03
  • 1105

JPEG2000开发SDK及其特点

JPEG2000开发SDK及其特点 说明:JPEG2000被开发来取代JPEG,但因为大量核心算法被专利注册,结果并没有这样发展。但是从性能上讲JPEG2000更好,而开发起来常常需要一些SDK,商用...
  • LG1259156776
  • LG1259156776
  • 2015年10月14日 09:05
  • 1492

mysql内核源代码深度解析 缓冲池 buffer pool 整体概述(bufferpool部分一)

本人原创文章,转载请注明出处。 mysql的内存管理庞大而先进,这在mem0pool.c文件的开头注释中都有说明,粗略的可以分成四部分,包含9大块: buffer pool, parsed and...
  • cjcl99
  • cjcl99
  • 2016年04月05日 10:54
  • 3354

mysql双机热备实现(mysql版本:5.1.7)

mysql双机热备实现(mysql版本:5.1.7)
  • high2011
  • high2011
  • 2016年07月08日 11:15
  • 685
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【MySQL 5.7 Reference Manual】15.4.2 Change Buffer(变更缓冲)
举报原因:
原因补充:

(最多只允许输入30个字)