Buffer Pools, once and for all! The working principle of InnoDB buffer pool is introduced.
A quick recap:
(1) MySQL data store consists of memory and disk;
(2) The memory buffer pool caches the hottest data page and index page in the unit of pages.
(3) InnoDB manages buffer pool with variant LRU algorithm, and can solve the problems of “prefetch failure” and “buffer pool pollution”;
Voiceover: For details, seeBuffer pool, get it!”.
There is no doubt that buffer pools can reduce disk IO and improve performance for read requests. The question is, what about writing requests?
Is a
Suppose you want to modify an index page with page number 4 that happens to be in the buffer pool.
As shown in figure 1-2:
(1) Directly modify the page in the buffer pool, a memory operation;
Write redo log to disk sequentially.
This is the most efficient.
Voice-over: Writing in order, like logging, tens of thousands of times per second is no problem.
Is there a consistency problem?
Will not.
(1) read, will hit the buffer pool page;
(2) Buffer pool LRU data obsolete, “dirty pages” will be flushed back to disk;
Redo log (1) Redo log (1) Redo log (2)
When does a page in the buffer pool flush to disk?
Flush disks periodically instead of every time to reduce disk I/O and improve MySQL performance.
* Voiceover: batch write, is a common means of optimization. *
Case 2
Suppose you want to modify an index page with a page number of 40 that is ** * not ** in the buffer pool.
At this point, it is a little more troublesome, as shown in the figure 1-3:
(1) First load the index page that needs to be 40 from the disk to the buffer pool, a random disk read operation;
(2) Modify the page in the buffer pool, a memory operation;
Write redo log to disk sequentially.
If the buffer pool is not hit, at least one disk I/O is generated. Is there room for optimization in the service scenario with more write and less read?
This is InnoDB’s concern, and the change buffer discussed in this article.
* Voiceover: As the name suggests, write buffering is a mechanism to reduce disk IO and improve database write performance. * What is InnoDB write buffer?
Prior to MySQL5.5, it was called the Insert buffer and was optimized for inserts only; The change buffer now works for delete and update as well.
It is used when a non-unique secondary index page is not in the buffer pool, and a write operation is performed on the page. It does not load the disk page into the buffer pool immediately, but only records the buffer changes when the data is read in the future. The technique of merging data back into the buffer pool. Write buffering reduces disk I/OS for write operations and improves database performance.
* Voiceover: R dog, this sentence, so long. *
InnoDB adds write buffer optimization, how will the process change in case 2 above?
Suppose you want to modify an index page with a page number of 40 that happens not to be in the buffer pool.
After adding write buffer optimization, the process optimization is as follows:
(1) Record this operation in the write buffer, a memory operation;
Write redo log to disk sequentially.
The performance is similar to that of this index page in the buffer pool.
* Voiceover: You can see that the page 40 is not loaded into the buffer pool. *
Is there a consistency problem?
Don’t.
Redo log (1) Redo log (1) Redo log (2)
(2) The write buffer is not only a memory structure, it is also flushed into the write buffer system tablespace periodically;
(3) When data is read, there is another process to merge the data into the buffer pool;
Suppose that at a later time there is a request to query the data on index page 40.
The flow is as follows:
(1) The buffer pool is not hit when the index page is loaded, so disk I/O is inevitable.
(2) Read relevant information from the write buffer;
(3) restore the index page to the buffer pool LRU;
* Voiceover: As you can see, the page 40 is not loaded into the buffer pool until it is actually read. *
There is another missing question, why do write buffer optimizations only apply to non-unique plain index pages?
In InnoDB, there are differences and similarities between clustered index and secondary index. In 1 Minute, index Differences between MyISAM and InnoDB are described in detail.
If an index has a unique attribute, InnoDB must perform a unique check before modifying it. That is, even if the index page is not in the buffer pool, page reads on disk cannot be avoided. In this case, you should directly put the corresponding page into the buffer pool and then modify, rather than complete the buffer.
In addition to the data page being accessed, what other scenarios trigger the flushing of data in the buffer?
There are also several cases in which data in the buffer can be erased:
(1) there is a background thread, will consider the database idle;
(2) when the database buffer pool is insufficient;
(3) When the database is normally closed;
(4) Redo log is full;
* Voiceover: There is almost no redo log full, and the database is in an unusable state that cannot write to it. *
What business scenarios are appropriate to enable InnoDB write buffering?
When is it not suitable?
(1) Databases are unique indexes;
(2) Or, when a piece of data is written, it is read immediately;
In both of these scenarios, the write cache becomes a burden and adds complexity when a page is read during (after) a write operation and the corresponding page is about to be pooled.
When is it appropriate to use write buffering if:
(1) Most of the database is non-unique index;
(2) the business is to write more and read less, or not to read immediately after writing;
You can use the write buffer to optimize periodic batch write of SQL that requires DISK I/O each time it is written.
* Voiceover: For example, billing. *
What parameters in InnoDB correspond to the above principles? **
There are two important parameters.
Parameters: innodb_change_buffer_max_size
Description: Configure the write buffer size as a proportion of the buffer pool. The default value is 25%, and the maximum value is 50%.
* Voiceover: if you write too much and read too little, you need to increase this value. If you read too much and write too little, 25% is actually too much. *
Parameters: innodb_change_buffering
Description: Configure which write operations enable write buffers, which can be all/ None /inserts/ Deletes, etc.
I hope you have learned, thinking is more important than conclusion.