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

This article introduces the relevant knowledge of "how to understand MySQL change buffer". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Change buffer is a new feature added by MySQL5.5, change buffer is an enhancement of insert buffer, insert buffer is effective only for insert, and change buffer is effective for insert, delete, update (delete+insert) and purge. When modifying the data of an index block (secondary index), the index block does not exist in buffter pool, and the modification information will be cache in change buffer. When the required index block is read to buffer pool through index scanning, it will be merged with the modification information in change buffer, and then written back to disk. The goal is also to reduce the performance loss caused by random IO, to be clear: turn random IO into sequential IO as much as possible.

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.

Change buffer is the cache that stores the secondary index without the change page in buffer pool, and the changed buffer is caused by operations such as insert,update,delete. Pages in change buffer will be merged after the pages are loaded into buffer pool.

Secondary indexes are usually non-unique, insertions are random, and updates and deletions are not in the vicinity, so change buffer avoids a lot of random io. The puge operation periodically writes the change page to disk when the system is idle or slowly shuts down.

Change buffer merging can take a long time when there are a large number of secondary index page updates or many affected rows. Change buffer takes up some of the space of innodb buffer pool, and on disk, change buffer takes up system table space, so index changes are still cached after the database is restarted. The data cached in change buffer can be controlled by innodb_change_buffering, or we can resize the innodb_change_buffer_max_size configuration change buffer.

The influence of Index on insert

1. Table insert, insert is required for all indexes on the corresponding table

2. Assuming that these indexes are not often used, it is easy to produce physical reads.

3. The order of the index is completely inconsistent with that of the table.

Principle: there should be no more than 6 indexes on a table.

Save the update records to the index in insert buffer instead of calling the index page directly to update; choose the machine to perform merge insert buffer operation and merge the records in insert buffer into the real auxiliary index.

The problem of excessive physical reading of insert table data is solved.

Insert Buffer when to merge

There are two situations in which innodb will merge Insert Buffer to disk

1), master loop thread active merge

If the amount of Insert buffer O occurring in the past 1 s is less than 5% of the system's IUnip O capacity, then take the initiative to perform a merge operation of the system. The number of pages in Merge is 5% of the capacity of system Iramp O, and every 10 seconds, an insert buffer merge action must be triggered.

The number of pages in Merge is still 5% of the system's IAccord O capability (system capability refers to innodb_io_capacity)

2) if you read the data and find that the page has not been merge in Insert Buffer, then innodb will first do a merge operation, so the reading speed will slow down.

When the system load is high, merge operations will be very few, and when the system is idle, merge operations will be very frequent, so when your system load suddenly becomes low, DB may also do IO operations for a long time.

Under what circumstances might the operation of merge insert buffer occur:

Before merge insert buffer, insert buffer data was stored in memory. In order to prevent data loss caused by unexpected database downtime, the system periodically wrote insert buffer data to the shared tablespace.

1. The secondary index page is read into buffer pool

For example, when a normal select query operation is performed, the index page is called into memory, and the merge operation occurs when the index page corresponds to the index change record in insert buffer.

2. When the insert buffer bitmap page traces to the secondary index page that has no free space,

Save in ibd file (table data file)

Record the number of rows corresponding to each index page in insert buffer

How to look at the effect of insert buffer

1. If the insert buffer occupies too much space, it will affect the buffer performance.

2. The amount of data processed by each merge

1) if the merges is high, it means that the insert buffer has been reduced, and that the index has been overbuilt.

2) when IDU the table in batch, it may cause the insert buffer to increase rapidly.

Pay attention to the proportion of change buffer in innodb buffer pool

Mysql > show variables like'% change_buffer%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_change_buffer_max_size | 25 | |

| | innodb_change_buffering | all |

+-+ +

2 rows in set (0.01sec)

1. Innodb_change_buffer_max_size: indicates the maximum percentage of change buffer in buffer pool. Default is 25%, and maximum is 50%.

2. Innodb_change_buffering: indicates the index column merge object. All means that all the IDU index columns are merge. If you only want to merge the insert index column, change the all to inserts.

Adjustment recommendations:

1. If there are serious insert, update and active delete in the system, increase the max_size.

2. For the pure report system that does not change the data, you can reduce the parameter value.

This is the end of "how to understand MySQL change buffer". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.