[MySQL] InnoDB features-two writes

[MySQL] InnoDB features-two writes today we will introduce the second feature of the InnoDB Storage engine-two writes (doublewrite). If the insert buffer is used to improve the write performance, therefore, the two writes sacrifice a little bit of write performance to improve the reliability. Partial write Failure Imagine this scenario. When the database is writing a data page from the memory to the disk, the database goes down, causing this page to write only part of the data, this means that some writes are invalid, which may cause data loss. In this case, the redo log cannot be restored because the redo log records physical modifications to the page. If the page itself is damaged, the redo log cannot be restored. From the analysis above, we know that when some write operations fail, we need a copy of the original page before applying the redo log, the two writes are designed to solve this problem. The following is its schematic diagram:

Two additional writes are required: 1) Two write buffers in the memory (doublewrite buffer); 2 MB in size) the disk shares 128 consecutive pages in the tablespace, the size is also 2 MB. The principle is as follows: 1) when the dirty page of the buffer pool is refreshed, it is not directly written to the data file, but copied to the two write buffers in the memory. 2) then, the two write buffers are used to separate the two write data into the disk shared tablespace. Each write is 1 mb3) after Step 2 is completed, writing the buffer to the data file twice can solve the above-mentioned write failure problem, because there is a copy of the data page in the disk shared tablespace, if the database goes down while writing data files to the page, you can find the copy of the page from the shared tablespace when the instance recovers, and copy it to overwrite the original data page, apply the redo log. Step 1 is an additional performance overhead, but the overhead is not large because the disk shared tablespace is continuous. You can use the skip_innodb_doublewrite parameter to disable the write twice function. This function is enabled by default. We strongly recommend that you enable this function.