MySQL uses the buffer mechanism to avoid disk I/O for each read/write, improving efficiency:

  • Buffer Pool

  • Change Buffer

  • Log Buffer

The size of a page of buffer for MySQL is 16K, and the size of a page of file system is 4K. That is to say, when MySQL flusher a page of buffer to disk, it has to write four pages of file system.

MySQL =1; MySQL =1; MySQL =1;

So, the question is, this operation is not an atom, and if the power goes off in the middle of it, is there a problem? Yes, this is called “page data corruption”.

MySQL > insert into disk where page=1; insert into disk where page=1; insert into disk where page=1; insert into disk where page=1; insert into disk where page=1;

This type of “page data corruption” exception cannot be fixed unless the page data is correct and the redo log is healthy.

How do you solve this kind of “page data corruption” problem?

An easy way to do this would be to have a “copy” of the original page, stored in the Double Write Buffer.

The Double Write Buffer, however, is different from traditional buffers in that it has two layers of memory and disk architecture.

_ Voiceover: __ Traditional buffers, mostly memory storage; DWB data, however, needs to be landed.

As shown above, when there is page data to flush:

Step 1: memcopy page data to DWB memory;

Step 2: DWB memory will be flushed to DWB disk first;

Step 3: DWB memory, and then brush to the data disk storage;

DWB consists of 128 pages with a capacity of only 2M.

Steps 2 and 3 Write the disk twice, which is where the “Double Write” comes from.

Why does DWB solve the “page data corruption” problem?

If the power fails in Step 2, the disk still contains complete data of 1+2+3+4.

_ Voice-over: _ Data can be restored through redo as long as a page is intact.

In case of power failure in Step 3, DWB stores complete data.

Therefore, there must be no “page data corruption” problem.

_ Voiceover: _ Write twice, there is always a place where the data is OK.

After dozens of experiments, I still failed to reproduce the “page data damage”. I found a graph on the Internet where MySQL used DWB to repair the page data during the restart process of “page data damage”.

As you can see during startup:

(1) InnoDB detects that the last shutdown was abnormal;

(2) Failed to restore IBD data.

(3) Recover half-written pages from DWB;

Page data integrity can be guaranteed with DWB, but after all, DWB writes to disk twice, does that lead to a sharp drop in database performance?

Analyze the three steps DWB performs:

(1) The first step, page data memcopy to DWB memory, fast;

(2) In the second step, DWB’s memory fsync is flushed to DWB’s disk, which is sequential and fast;

(3) The third step, brush disk, random write, originally need to be carried out, does not belong to additional operations;

In addition, 128 pages (16K per page) of 2M DWB will be flushed into disk twice, each time up to 64 pages, that is, 1M data, execution is also very fast.

Above all, performance matters, but not much.

Voice-over:

(1) The high performance of write-ahead-log is due to sequential appending;

(2) There is a third-party evaluation to assess about 10% performance loss;

More specifically, InnoDB has two variables that can be used to view double write buffers:

Innodb_dblwr_pages_written

Records the number of pages written to DWB.

Innodb_dblwr_writes

Record the number of DWB writes.

It can be done through:

show global status like “%dblwr%”

To query.

At the end

MySQL has a strong data security mechanism:

(1) If there is no “page data corruption” during abnormal crashes, data can be recovered through redo;

(2) When “page data is damaged” occurs, the page data can be recovered by double Write Buffer;

Double the write buffer:

(1) It is not a memory buffer, but a two-tier memory/Disk structure, which is an important part of on-disk architecture in InnoDB;

(2) is a mechanism to ensure page integrity by writing twice;

Know what it is and why.

Thinking is more important than conclusion, I hope you have a harvest.

The architect’s Path– Share technical ideas

Related articles:

Buffer Pool

Change Buffer

Log Buffer