Shulou(Shulou.com)06/02 Report--

This article mainly explains the "explanation of the principle of MYSQL change buffer". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the principle of MYSQL change buffer".

Talk about MYSQL's change buffer, why, because it can speed up your write operations, which means operations such as insert update delete, especially if your table has a pile of indexes, it must be set up specially.

There may be a voice immediately asking why it is necessary to have a special setting if the table has a bunch of indexes.

1 Index and table are closely related, insert, update, delete a record, will trigger the operation of the index related to the table, you can think of them as a transaction, if any of the operation on the index fails, then your data operation on the table should also fail.

2 the operation of the index attached to the table will inevitably affect the operation speed of the data of the source table, and the DML operation will affect the performance of data reading and transaction isolation, and then cause a chain reaction, table insert, change, delete, slow, then the performance of the SELECT of the table will inevitably be affected.

3 when insert, update, and delete operations are performed on a table, the values of the index (nonclustered index) columns are usually unordered, which requires a large number of Icano to update the secondary index. When the related page is not in the buffer pool, changing the buffer caches the changes to the index, thus avoiding the expensive IUnio operation by not immediately reading the page from disk. When the page is loaded into the buffer pool, the cached changes are merged and the updated page is flushed to disk.

The advantage is that if MYSQL undertakes a large number of DML operations, change buffer is indispensable. Its existence is to minimize the consumption of change buffer O, merge data through memory, and turn multiple operations into a small number of Imax O operations as far as possible. The downside is that change buffer uses innodb_buffer space, so if you have less memory, then. Observe 3 minutes of silence.

Speaking of this thing, it is naturally necessary to adjust the parameter configuration in MY.CNF.

There are two specific parameters.

1 innodb_change_buffering

This parameter determines the use of your change buffer, whether you want to eat salty or sweet, it's all in it. Specific options

None

Inserts

Deletes

Changes

Purges

All

2 innodb_change_buffer_max_size

Another option is to set the percentage of memory in buffer_pool. The default is 25%. You can set a maximum of 50%.

3 innodb_change_buffering_debug

If there is a problem with change buffer, we also need to learn from ERRORLOG as soon as possible, so it is recommended to set it to at least 1.

Here's the MYSQL code that doesn't talk about this part (take a good look at the stupid code sometime, or see if the code belongs to an idiot)

1 UNIV_PAGE_SIZE is the size of the data page

2 buf_pool_get_curr_size is the size of the current innodb_buffer_pool

Finally, the largest setting should be the percentage of blocks in terms of page size.

Thank you for your reading, the above is the content of the "explanation of the principle of MYSQL change buffer", after the study of this article, I believe you have a deeper understanding of the principle of MYSQL change buffer to explain this problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.