A brief analysis of MySQL double write

http://blog.itpub.net/22664653/viewspace-1140915/

Before you introduce double write, it is necessary to understand the partial page write problem:
The page size of InnoDB is generally 16KB, and its data validation is calculated for this 16KB, and writing data to disk is done in page units. While the computer hardware and operating system, in extreme cases (such as power outages) often does not guarantee the atomic nature of this operation, 16K of data, write 4K, the system has a power outage/os crash, only a portion of the write is successful, in this case is the partial page write problem.
Many DBAs will think that after the system resumes, MySQL can recover according to Redolog, while MySQL in the process of recovery is to check page checksum,checksum is Pgae's last transaction number, when a partial page write problem occurs, The page is corrupted and cannot be recovered if it cannot find the transaction number in the page.

What is a double write?
Double Write is the InnoDB on the Tablespace 128 pages (2 extents) is 2MB;
Its principle:
To resolve the partial page write problem, when MySQL flush the dirty data to the data file, it first uses memcopy to copy the dirty data into the double write buffer in memory, followed by the double write buff Er divided 2 times, each write 1MB to the shared tablespace, and then immediately call the Fsync function, synchronization to disk, to avoid the problem of buffering, in this process, doublewrite is sequential write, the cost is not large, after the completion of Doublewrite write, the double Write buffer writes to each tablespace file, which is a discrete write.
In the event of an extreme situation (power outage), when InnoDB is restarted and a page data is found to be corrupted, data recovery from Doublewrite buffer is possible.

What is the disadvantage of two double write?
The double write buffer located on the shared tablespace is actually a file, and writing DWB will cause the system to have more fsync operations, and the Fsync performance of the hard disk, so it will degrade the overall performance of MySQL. But it's not going down to the original 50%. This is mainly because:
1) Double write is a connected storage space, so the hard disk writes the data in sequential order, rather than random write, so the performance is higher.
2) When the data is written from the double write buffer to the real segment, the system automatically merges the way the connection space is refreshed, and can refresh multiple pages at a time;

Three how does a double write work at the time of recovery?
If there ' s a partial page write to the doublewrite buffer itself, the original page would still be on disk in its real loca tion.-
-If the write Doublewrite buffer itself fails, then the data will not be written to the disk, InnoDB will load the original data from the disk, and then through the InnoDB transaction log to calculate the correct data, re-written to Doublewrite buffer.
When InnoDB recovers, it would use the original page instead of the corrupted copy in the Doublewrite buffer. However, if the doublewrite buffer succeeds and the write to the page's real location fails, InnoDB'll use the copy in T He doublewrite buffer during recovery.
--If Doublewrite buffer writes successfully, but the disk fails, the INNODB does not have to be computed through the transaction log, but writes the data in buffer directly again.
InnoDB knows when a page was corrupt because each page had a checksum at the end; The checksum is the last thing to being written, so if the page's contents don ' t match the checksum, the page is corrupt. Upon recovery, therefore, InnoDB just reads each page in the Doublewrite buffer and verifies the checksums. If a page ' s checksum is incorrect, it reads the page from its original location.
--when recovering, InnoDB directly compares the checksum of the page and, if not, loads the original data from the hard disk and starts the transaction log with the correct data. So InnoDB recovery usually takes a long time.

Four do we definitely need a double write?
In some cases, the doublewrite buffer really isn ' t necessary-for example, you might want to disable it on slaves. Also, some filesystems (such as ZFS) do the same thing themselves, so it's redundant for InnoDB-do it. You can disable the Doublewrite buffer by setting Innodb_doublewrite to 0.

five how to use double write
Innodb_doublewrite=1 indicates that a double write is started
The show status like ' innodb_dblwr% ' can query the use of double write;
Related parameters and status
Usage of Double write:
Show status like "%innodb_dblwr%";
The number of Innodb_dblwr_pages_written from BP flush to DBWB
Innodb_dblwr_writes number of write files
Number of merged page per write operation = Innodb_dblwr_pages_written/innodb_dblwr_writes

A brief analysis of MySQL double write