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

What is change buffer in mysql, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

First of all: in normal insert, it is also necessary to determine which physical page (including data page and index page) can be insert, then read this physical block to buffer pool, then perform insert operation in memory, and then refresh it to disk

0) Why does Change Buffer improve performance

It caches the operation of an index page dml, rather than a specific data page, the specific data page is modified directly, and change buffer can only be used when modifying non-aggregated and non-unique index pages. When there is no index page in buffer pool and you need dml to manipulate the data of this index page, you do not need to read the index page from disk to buffer pool first, but record the operation directly to cahnge buffer, so that a random io is reduced, and then the next time the business needs to access the index page, the page is read into buffer poo, and then the operation recorded by change buffer is merge with the index page just read in buffer pool.

Another point to improve the performance is that for the update or insert operation of a non-clustered and non-unique index, instead of inserting directly into the index page after each insert or update operation, it is first determined whether the inserted nonclustered index page is in the buffer pool, if so, it is inserted directly, and if not, it is put into change buffer, and then the merge (merge) operation of change buffer and secondary index leaves is carried out with a certain frequency and situation. At this point, you can usually combine the dml operations of multiple index leaves into one operation (because in a single index page), which also improves performance.

To sum up, the performance improvement of cahnge buffer lies in two points:

One: when the index page to be modified is not in buffer pool, there is no need to read the index page to buffer pool first. Here, io is reduced.

Second: merge the dml operations of multiple index leaves into one operation (because in a single index page), batch merge, which also improves performance

1) content of change buffer

Change Buffer is a special data structure that caches changes to secondary index pages and those pages are not in Buffer Pool (note that changes to index leaves are cached, not specific index leaves). Cached changes may be caused by the results of Insert, Delete, and Update. Merge 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.

3) change buffer is only for secondary indexes

Invalid for primary key index; changes to secondary index leaf nodes are possible with change buffer, and if there is no index on this column, then change buffer is not available!

4) it's nice to have change buffer information in the buffer pool, but it's not just a part of the cache pool. Change buffer is like a data page. Change buffer is a B + tree, which is placed in a shared table space, and the default is idbata1. Writing data to change buffer also requires random io, but with change buffer, when there are no leaf blocks in buffer that need to be modified. You don't need to read the leaf blocks into the innodb_buffer_cahce (the io here is reduced) just write the changes into the cahnge buffer, and then sort in cahnge buffer and finally wait for the real leaf node data to be merged! Batch merge! So if the update operation is infrequent and the page needs to be read immediately after the update, the change buffer mechanism will increase part of the io consumption! Change buffer reduces the random read IO (data page) operation of reading the hard disk from memory, and the batch sequential merge,redo log reduces the random write log operation.

5)。 The reason why the index needs to be satisfied is a secondary index and is not unique

Because when inserting the buffer, the database does not look up the index page to determine the uniqueness of the inserted record, if you look for it, there will certainly be discrete reads (random io), resulting in change buffer meaningless!

6) related parameters of change buffer

1 by default, innodb_change_buffering supports all DML operations on all.

2 innodb_change_buffer_max_size, which defaults to 25, which is 1 innodb_change_buffer_max_size 4 of the buffer pool. The maximum can be set to 50, and the default can be used.

7)。 The merge operation is carried out in buffer pool

The next time the page needs to be loaded, that is, when there is a need for the page, the changes in Change Buffer will be merged into Buffer Pool, and then when the server is idle, the change will be brushed to disk (disk), or merge will be performed when the server is not busy. At this time, the merger operation also occurs in buffer pool.

8)。 Why does change buffer store operations on index pages that are not in buffer pool?

Because if you are in buffer pool, then you will directly dml the corresponding index page. Change buffer exists so that when the index page you need to operate is not in buffer pool, and then you do not need to read the index page to disk immediately, change buffer can be used only if it does not exist in buffer pool.

9) apart from the access to the data page, what other scenarios trigger the data in the brush buffer?

There are also several situations in which the data in the buffer is overwritten:

(1) when there is a background thread that thinks that the database is idle

(2) when the database buffer pool is insufficient

(3) when the database is closed normally

(4) when redo log is full

Voiceover: there is almost no redo log full, and the entire database is in an unavailable state that cannot be written.

10) what business scenario is suitable to enable the write buffering mechanism of InnoDB?

First of all, when is not appropriate, as analyzed above, when:

(1) all databases are unique indexes.

(2) or, as soon as a data is written, it will be read

In these two kinds of scenarios, when the write operation is going on (after the write operation), the page reading will be carried out, and the corresponding pages will be put into the buffer pool, but the write cache will become a burden and increase the complexity.

When is appropriate to use write buffering, if:

(1) most of the databases are non-unique indexes.

(2) the business is written more and read less, or not read immediately after writing.

Write buffering can be used to optimize periodic bulk writes to disks by using SQL, which originally requires disk IO for each write.

Voiceover: for example, the billing pipeline business.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.