Doublewrite_MySQL for mysql

1. doublewritebuffer (mysql official introduction) InnoDBusesanovelfileflushtechniquecalleddoublewrite. Beforewritingpagestothedatafiles, InnoDBfirstwritesthemtoaco... 1. doublewrite buffer (mysql official introduction)

InnoDB uses a novel file flush technique called doublewrite. before writing pages to the data files, InnoDB first writes them to a contiguous area called the doublewrite buffer. only after the write and the flush to the doublewrite buffer have completed, does InnoDB write the pages to their proper positions in the data file. if there is an operating system, storage subsystem, or mysqld process crash in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during crash recovery.

Although data is always written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as same I/O operations. data is written to the buffer itself as a large sequential chunk, with a single fsync () call to the operating system.

To turn off the doublewrite buffer, specify the option innodb_doublewrite = 0.

2. partial page write

The Page Size of InnoDB is generally 16 kB, and its data verification is also calculated for this 16 kB. writing data to the disk is performed in Page units. In extreme cases (such as power-off), computer hardware and operating systems do not guarantee the atomicity of this operation. when 16 K data is written to 4 K, in the case of system power failure/OS crash, only some of the writes are successful, which is a partial page write problem.

Many DBAs will think that after the system is restored, MySQL can be restored based on the redo log, while mysql is checking the checksum of page during the recovery process, and checksum is the last transaction number of pgae, when the partial page write problem occurs, the page is damaged and the transaction number in the page cannot be found.

Therefore, when the page is damaged, the application redo is meaningless. in this case, you cannot use redo to restore the page because the original page is damaged and data is lost.

3. doublewrite

When InnoDB flush the Dirty Page in BP to the disk, it first refreshes the (memcpy function) Page to an area in InnoDB tablespace, we call this region Double write Buffer (2 MB in size, 1 MB in each write ). After successfully writing data to the Double write Buffer, the second step is to copy the data to the corresponding location of the data file.

When a fault occurs in step 2, that is, partial page write occurs. When restoring, check whether the checksum on the page is the same or not. if they are inconsistent, they will be restored directly from doublewrite.

1) If dw buffer writing fails. The data will not be written to the disk. innodb will load the original data on the disk and compare it with the redo log, and refresh it to the dw buffer.

2) If the dw buffer is successfully written but fails to be refreshed to the disk, innodb will not recover the data through transaction logs, but directly refresh the data in the dw buffer.

4. impact on performance

The system needs to Write data in two copies. generally, Double Write will reduce the system performance. Peter guessed that there may be 5-10% performance loss, but it is worthwhile to achieve data consistency. Mark Callaghan believes that this should be solved at the storage layer, and the database layer will undoubtedly sacrifice a lot of performance.

In fact, the impact of Double Write on the performance has not been imagined (the Write performance should be reduced by 50% twice, right ?) . In BP, many Dirty pages are flushed at the same time. Double Write converts these Write operations from random writes to sequential writes. In the second phase of Double Write, because the Double Write Buffer has accumulated a lot of Dirty pages, many Write operations may be merged when writing data to a real data file, this may reduce the number of Fsync calls.

Based on the above reasons, Double Write is not as bad as you think. In addition, after testing, Dimitri found that opening and disabling Double Write has little impact on efficiency.

5. doublewrite parameters

mysql> show variables like "%double%";+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| innodb_doublewrite | ON    |+--------------------+-------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE "%innodb_dblwr%";+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Innodb_dblwr_pages_written | 447   || Innodb_dblwr_writes        | 38    |+----------------------------+-------+2 rows in set (0.00 sec)

As can be seen from the above, Flush 447 times to doublewrite buffer, 38 write files in total, then 447/38 flush is merged for each write operation.