Shulou(Shulou.com)05/31 Report--

Editor to share with you how to configure Change Buffer, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Change Buffer is a special data structure that caches changes to secondary index pages and those pages are not in Buffer Pool. Cached changes may be caused by the results of Insert, Delete, and Update. It is merged later when the page is loaded into Buffer Pool by other read operations.

In short: the main purpose of Change buffer is to cache the data operations on the secondary index, so as to reduce the random IO of the secondary index and achieve the effect of merging operations.

Unlike clustered indexes (ps: primary keys in InnoDB by default, which are clustered and stored), secondary indexes are usually not unique and are inserted in a relatively random order. Deletions and updates may affect secondary index pages that are not adjacent in the index tree. When affected pages are read into the buffer pool by other operations, the cache changes are merged to avoid the large amount of random access I / O required to read secondary index pages from disk to the buffer pool.

In previous versions of MySQL5.5, it was originally called insert buffer because it only supported caching insert operations, but it was changed to change buffer because more operation type caching was supported in later versions, so this article is based on the version after MySQL5.5.

(Secondary Index (secondary index)

1. It can also be called a nonclustered index

2. The leaf node stores index and primary key information

3. After finding the index, get the corresponding primary key, and then go back to the clustered index to find the record corresponding to the primary key (row data).

The interpretation of releasing a secondary index may still be confused, when it is counted as a secondary index, and all indexes except the primary key are secondary indexes (innodb default).

Therefore, according to the primary key to access data (always the best way), the reason is to see the introduction of the secondary index in parentheses above.

Why do you need Change Buffer

The index of the table is stored in the ibd file of the table, and the data is also stored in this file. When the table data is updated, the index data of the corresponding table is also updated, so: for example, when insert a table, it is likely to produce a large number of physical reads (physical reading index data page), insert a table, the index above the corresponding table will change, the index is not often used, resulting in physical read, index order and table inconsistent time-consuming.

(physical read (Physical Reads): the operation of reading data blocks from disk to memory is called physical read. When these data blocks do not exist in the cache, a physical read will occur. If the physical read is too large, the disk I will be higher.)

Therefore, the update records of the index will be stored in Change Buffer, rather than directly called into the index page for update; choose the time to carry out the merge insert buffer operation, the records in insert buffer will be merged (merge) into the real auxiliary index.

Purge operations that run when the system is mostly idle or during a slow shutdown periodically write updated index pages to disk. Purge operations can write disk blocks for a series of index values more efficiently than if each value is written to disk immediately.

When there are many affected rows and many secondary indexes to update, the Change Buffer merge can take several hours. During this period disk I / O increases which results in a significant slowdown in disk binding queries. Change buffer merging may continue to occur after the transaction is committed, even after the server is shut down and restarted

In memory, Change Buffer takes up part of the Buffer Pool. On disk, Change Buffer is part of the system tablespace, where the index changes when the database server is shut down.

Configure Change Buffer

When performing INSERT,UPDATE and DELETE operations on a table, the values of index columns, especially those of secondary keys, are usually sorted in unsorted order, requiring a lot of I / O for the secondary index to be updated. Change Buffer caches this update when the relevant page is not in Buffer Pool, so that the relevant page on disk is not immediately read, avoiding expensive I / O operations. When the page is loaded into the buffer pool, the buffered changes are merged and the updated page is later flushed to disk. The InnoDB main thread merges buffered changes when the server is almost idle and during a slow shutdown.

For ease of understanding: there is a DML operation on a secondary index page, and the page is not in Buffer Pool, so store this operation in Change Buffer (the previous version of MySQL5.5 is called Insert Buffer), ok, then the next time you need to load this page, that is, when there is a need for this page, the changes in Change Buffer will be merged into Buffer Pool, and then when the server is idle This change will be flushed to the disk. So at first the flow of the hard-to-read picture was clear: (trends like yellow arrows)

Because it reduces disk reads and writes, changing the buffer feature is most valuable for I / O-bound workloads, such as applications with a large number of DML operations (such as bulk inserts).

However, Change Buffer takes up part of the Buffer Pool, thus reducing the memory available for caching data pages. Disabling Change Buffer can be useful if the working set is almost suitable for Buffer Pool, or if your table has relatively few secondary indexes.

After all, Change Buffer only applies to pages other than Buffer Pool.

You can use innodb_change_buffering to configure parameters

The allowed innodb_change_buffering values are:

All

Default value: all actions

None

Do not buffer any actions.

Inserts

Buffer insert operation.

Deletes

Buffer delete tag operation.

Changes

Buffer insert and delete tag operations.

Purges

Buffer physical deletions that occur in the background.

Change Pool internal structure

Ibuf stands for Insert Buffer, which can be directly regarded as Change Buffer. Before MySQL 5.5, Change Buffer was called Insert Buffer.

The maximum default of ibuf btree is 25% of buffer pool size. When it exceeds 25%, user threads may be triggered to synchronously reduce ibuf btree. Why associate the size of ibuf btree with the size of buffer pool? One of the more important reasons is to prevent ibuf itself from taking up too much buffer pool resources.

Configure Change Pool maximum size

This innodb_change_buffer_max_size variable allows the maximum size of the Change Buffer to be configured as a percentage of the total size of the buffer pool. By default, innodb_change_buffer_max_size is set to 25. 0. The maximum setting is 50.

Use to test the business performance of different settings to determine the best configuration. The innodb_change_buffer_max_size setting is dynamic and allows you to modify the setting without restarting the server. Similar to innodb_buffer_pool_size can also be changed online.

The above is all the contents of the article "how to configure Change Buffer". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.