Rechercher dans le manuel MySQL
15.5.2 Change Buffer
The change buffer is a special data structure that caches changes to
secondary index pages
when those 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.
Unlike clustered indexes, secondary indexes are usually nonunique, 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 secondary index pages into the buffer pool from disk.
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 many affected rows and numerous secondary indexes to update. 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, and even after a server shutdown and restart (see Section 15.20.2, “Forcing InnoDB Recovery” for more information).
In memory, the change buffer occupies part of the buffer pool. On disk, the change buffer is part of the system tablespace, where index changes are buffered when the database server is shut down.
The type of data cached in the change buffer is governed by the
innodb_change_buffering
variable.
For more information, see
Configuring Change Buffering. You can also
configure the maximum change buffer size. For more information, see
Configuring the Change Buffer Maximum Size.
Change buffering is not supported for a secondary index if the index contains a descending index column or if the primary key includes a descending index column.
For answers to frequently asked questions about the change buffer, see Section A.15, “MySQL 8.0 FAQ: InnoDB Change Buffer”.
Configuring Change Buffering
When INSERT
,
UPDATE
, and
DELETE
operations are performed on
a table, the values of indexed columns (particularly the values of
secondary keys) are often in an unsorted order, requiring
substantial I/O to bring secondary indexes up to date. The
change buffer caches
changes to secondary index entries when the relevant
page is not in the
buffer pool, thus avoiding
expensive I/O operations by not immediately reading in the page
from disk. The buffered changes are merged when the page is loaded
into the buffer pool, and the updated page is later flushed to
disk. The InnoDB
main thread merges buffered
changes when the server is nearly idle, and during a
slow shutdown.
Because it can result in fewer disk reads and writes, the change buffer feature is most valuable for workloads that are I/O-bound, for example applications with a high volume of DML operations such as bulk inserts.
However, the change buffer occupies a part of the buffer pool, reducing the memory available to cache data pages. If the working set almost fits in the buffer pool, or if your tables have relatively few secondary indexes, it may be useful to disable change buffering. If the working data set fits entirely within the buffer pool, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool.
You can control the extent to which InnoDB
performs change buffering using the
innodb_change_buffering
configuration parameter. You can enable or disable buffering for
inserts, delete operations (when index records are initially
marked for deletion) and purge operations (when index records are
physically deleted). An update operation is a combination of an
insert and a delete. The default
innodb_change_buffering
value is
all
.
Permitted innodb_change_buffering
values include:
all
The default value: buffer inserts, delete-marking operations, and purges.
none
Do not buffer any operations.
inserts
Buffer insert operations.
deletes
Buffer delete-marking operations.
changes
Buffer both inserts and delete-marking operations.
purges
Buffer the physical deletion operations that happen in the background.
You can set the
innodb_change_buffering
parameter
in the MySQL option file (my.cnf
or
my.ini
) or change it dynamically with the
SET GLOBAL
statement, which requires privileges sufficient to set global
system variables. See
Section 5.1.9.1, “System Variable Privileges”. Changing the setting
affects the buffering of new operations; the merging of existing
buffered entries is not affected.
The innodb_change_buffer_max_size
variable permits configuring the maximum size of the change buffer
as a percentage of the total size of the buffer pool. By default,
innodb_change_buffer_max_size
is
set to 25. The maximum setting is 50.
Consider increasing
innodb_change_buffer_max_size
on
a MySQL server with heavy insert, update, and delete activity,
where change buffer merging does not keep pace with new change
buffer entries, causing the change buffer to reach its maximum
size limit.
Consider decreasing
innodb_change_buffer_max_size
on
a MySQL server with static data used for reporting, or if the
change buffer consumes too much of the memory space shared with
the buffer pool, causing pages to age out of the buffer pool
sooner than desired.
Test different settings with a representative workload to
determine an optimal configuration. The
innodb_change_buffer_max_size
setting is dynamic, which permits modifying the setting without
restarting the server.
The following options are available for change buffer monitoring:
InnoDB
Standard Monitor output includes change buffer status information. To view monitor data, issue theSHOW ENGINE INNODB STATUS
statement.Change buffer status information is located under the
INSERT BUFFER AND ADAPTIVE HASH INDEX
heading and appears similar to the following:- -------------------------------------
- -------------------------------------
- Ibuf: size 1, free list len 0, seg size 2, 0 merges
- merged operations:
- discarded operations:
For more information, see Section 15.16.3, “InnoDB Standard Monitor and Lock Monitor Output”.
The
INFORMATION_SCHEMA.INNODB_METRICS
table provides most of the data points found inInnoDB
Standard Monitor output, plus other data points. To view change buffer metrics and a description of each, issue the following query:For
INNODB_METRICS
table usage information, see Section 15.14.6, “InnoDB INFORMATION_SCHEMA Metrics 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 byPAGE_TYPE
.IBUF_INDEX
is the page type for change buffer index pages, andIBUF_BITMAP
is the page type for change buffer bitmap pages.WarningQuerying 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 ofIBUF_INDEX
andIBUF_BITMAP
pages as a percentage of total buffer pool pages.- 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, see Section 25.39.1, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”. For related usage information, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.Performance Schema provides change buffer mutex wait instrumentation for advanced performance monitoring. To view change buffer instrumentation, issue the following query:
- +-------------------------------------------------------+---------+-------+
- | NAME | ENABLED | TIMED |
- +-------------------------------------------------------+---------+-------+
- +-------------------------------------------------------+---------+-------+
For information about monitoring
InnoDB
mutex waits, see Section 15.15.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-innodb-change-buffer.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.