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

Editor to share with you what the change buffer in MySQL Buffer pool is, I believe 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!

4 change buffer

4.1 basic concepts

Change buffer is a special data structure that caches changes to secondary index pages when these pages are not in the buffer pool. Buffer changes that may be caused by an INSERT,UPDATE or DELETE operation (DML) will be merged later when the page is loaded into the buffer pool through other read operations.

Related free learning recommendation: mysql video tutorial

As you can see in the image above, change buffer uses memory in buffer pool, so it cannot grow indefinitely. The change buffer size can be set dynamically through the parameter innodb_change_buffer_max_size.

For example, the size set to 50:change buffer can only occupy up to 50% of buffer pool.

When you need to update a data page:

Page in memory, update directly

The page is not in memory, and without affecting data consistency, InooDB caches these updates in change buffer without having to read the page from disk

The next time the query accesses the data page, the data page is read into memory, and then the actions related to the page in change buffer are performed. In this way, the correctness of the data logic can be guaranteed.

Change buffer is actually persistent data, that is, it is not only copied in memory, but also written to disk.

4.2 merge

The process of applying operations in change buffer to the original data page to get the latest results.

4.2.1 trigger time

Access the data page

System background threads merge periodically

The process of database shutdown (shutdown)

If the update operation can be recorded in change buffer first, and the disk reading is reduced, the execution speed of the statement will be significantly improved. And data reading memory needs to take up buffer pool, so it can also reduce the memory footprint and improve the memory utilization.

4.3 when to use change buffer

For a unique index, the update operation first determines whether the operation violates the uniqueness constraint:

For example, to insert (4400) a record, you must read the data page into memory to determine whether a kryp4 record has been stored in the table. If it has been read into memory, it is naturally very fast to update memory directly, so there is no need to use change buffer.

Therefore, updates to unique indexes cannot use change buffer, only normal indexes can be used.

4.4 applicable scenarios

Is it possible that all scenarios in a general index can be accelerated using change buffer?

Note that merge is the real time to update the data, and change buffer mainly caches the recorded change actions. So before a data page merge, the more change buffer records change (that is, the more times the data page needs to be updated), the greater the benefit.

Write more and read less business, the probability that the page will be accessed immediately after writing is less, and change buffer works best. The common systems are billing and log systems.

Immediately after writing, the query will be newly recorded in change buffer, but then because the data page is accessed immediately and merge is triggered quickly, the number of random visits to IO will not be reduced, but will increase the cost of change buffer maintenance, and change buffer has a side effect.

The above is all the content of the article "what is change buffer in MySQL Buffer pool?" 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.