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

Editor to share with you what the concept of mysql innodb double write is, I hope you have something to gain after reading this article, let's discuss it together!

The schematic diagram of double write is as follows:

By introducing the scheme of doublewrite buffer, every time innodb prepares to write a page, it first writes page to doublewrite buffer. If an accident occurs while writing doublewrite buffer, but the original page in the data file is not affected, it can be restored through innodb's redolog the next time it starts. If the doublewrite buffer is written successfully, mysql will write the contents of the doublewrite buffer to the data file, if it occurs again in the process

Accidentally, it doesn't matter. After rebooting, mysql can find a good page from doublewrite buffer, and then use the good page to overwrite the bad page on the disk.

So under normal circumstances, when mysql writes data page, it will write to disk twice, the first time to write to doublewrite buffer, and the second time to write from doublewrite buffer to the real data file.

In order to solve the partial page write problem, when mysql flush dirty data to data file, first use memcopy to copy the dirty data to the double write buffer in memory, then write 1MB to the shared tablespace twice through double write buffer, and then immediately call the fsync function to synchronize it to disk to avoid buffering problems.

Writing twice requires the addition of two additional parts:

1) two write buffers in memory (doublewrite buffer) with a size of 2MB

2) share the continuous 128pages in the tablespace on disk, and the size is also 2MB. Of these, 120 are used for bulk writing dirty pages, and the other 8 are used for Single Page Flush. The reason for the distinction is that batch scrubbing is done by background threads and does not affect foreground threads. Single page flush is initiated by the user thread and needs to clean the page and replace a free page as soon as possible.

Explanation of relevant parameters:

(root@localhost)-[11:35:25]-[(none)] > show status like "% InnoDB_dblwr%"

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_dblwr_pages_written | 882384812 | |

| | Innodb_dblwr_writes | 61236457 | |

+-+ +

2 rows in set (0.01sec)

Total number of pages written by InnoDB_dblwr_pages_written doublewrite

The number of files written by InnoDB_dblwr_writes doublewrite

Because the write unit of dirty pages flushed to disk is less than the size of a single page, if the database suddenly goes down during the write process, the write of the data page may not be completed.

Causes damage to the data page. What is recorded in redo log is the physical operation of the page, and if the data page is corrupted, it cannot be recovered through redo log.

Therefore, in order to ensure the security of writing data pages, double write is introduced. The implementation of double write is divided into two parts, one is the 2m memory block in the buffer pool.

Size, one is 128 consecutive pages in the shared tablespace, and the size is 2m. When a dirty page is refreshed from flush list, it is not refreshed directly to disk but is called first.

Function (memcpy), copy the dirty page into the double write buffer, and then refresh the double write buffer to the

Disk double write area, and then call the fsync operation to synchronize to the disk.

If writing to doublewrite buffer itself fails, the data will not be written to disk, and innodb will load the original data from disk at this time, and then calculate the correct data through innodb's transaction log and rewrite it to doublewrite buffer.

If the innodb_dblwr_pages_written:innodb_dblwr_writes is much less than 64:1 during the peak business period, the writing pressure of the system is not great.

Although double write buffer is written sequentially when flushing to disk, there is still a performance penalty.

If the system itself supports page security (partial write failure prevention mechanism), such as ZFS, then you can disable this feature (skip_innodb_doublewrite).

After reading this article, I believe you have a certain understanding of "what is the concept of mysql innodb double write". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.