Shulou(Shulou.com)06/01 Report--

The introduction of double write technology is to improve the reliability of data writing. When writing part of the data, such as 512bytes. Why didn't Oracle solve this problem, while MySQL solved it through double write?

Doublewrite is stored in the ibdata1 shared tablespace by default, and the default size is 2m. Before writing, the dirty page is written to doublewrite buffer (2m) in innodb buffer, and the 2m buffer data is directly written to the doublewrite segment of the shared tablespace. When the doublewrite of the shared tablespace fails, it does not matter, because the data in the data file ibd is clean and in a consistent state, and can be recovered through redo. [write the double of the shared tablespace first and then write the data file ibd], doublewrite is overwritten, and only 2m of the shared tablespace is saved for doublewrite. Pages that can be recovered by Redo must be clean, complete and consistent, because mysql will have partial write, so it introduces the doublewrite mechanism.

Double write buffer à double write (ibdata1) à ibd

Does doublewrite have a big impact on performance?

If the page size is 16k, there are 128pages (1m) to write, but the writing of 128pages to the shared table space is done by IO once, which means the doublewrite write cost is 1x128 times. 128 of them are write data file tablespaces.

Doublewrite writes are sequential, and the performance overhead depends on the number of writes, usually 5% Mel 25% of the performance impact.

25% reduction in performance overhead when the system load is very high

Corresponding database parameters

Double write technology is transparent to users, we can only pay attention to the following parameters on or off.

Mysql > show variables like'% double%write%' +-+-+ | Variable_name | Value | +-+-+ | innodb_doublewrite | ON | +-+-+ 1 row in set (0.00 sec) when the doublewrite feature can be disabled

1. Fursion-io atom write, if each write 16k is 16k, each write is 16k, there will be no partial partial write writing 4k. The benefits of equipment.

2. A specific file system that supports atomic writing. B-tree file system

Before introducing double write, it is necessary to understand the partial page write problem:

The Page Size of InnoDB is generally 16KB, and its data check is also calculated for this 16KB. Writing data to disk is operated in units of Page. While the computer hardware and operating system, in extreme cases (such as power outage) often can not guarantee the atomicity of this operation. When 16K data is written to 4K, the system power outage / os crash occurs, and only part of the writing is successful. In this case, it is the partial page write problem.

Many DBA will think that after the system is restored, MySQL can recover according to redolog, while mysql checks that the checksum,checksum of page is the last transaction number of pgae. When a partial page write problem occurs, the page has been damaged and cannot be recovered if the transaction number in the page cannot be found.

What is a double write?

Double write is the 128page (2 extents) of InnoDB on tablespace is 2MB

Its principle is:

In order to solve the partial page write problem, when mysql flush the dirty data to data file, first use memcopy to copy the dirty data to the doublewrite buffer in memory, then write the 1MB to the shared tablespace twice through doublewrite buffer, and then immediately call the fsync function to synchronize to the disk to avoid the problems caused by buffering. in this process, doublewrite is written sequentially, with little overhead, after completing the doublewrite write. When double write buffer is written to each tablespace file, it is a discrete write.

If there is an extreme situation (power outage) and after InnoDB starts again, it is found that a Page data has been corrupted, then the data can be recovered from doublewrite buffer at this time.

What are the disadvantages of double write?

Double write buffer on the shared tablespace is actually a file, writing DWB will cause the system to have more fsync operations, while the fsync performance of the hard disk, so it will reduce the overall performance of mysql. But it will not be reduced to 50%. This is mainly because:

1) double write is a connected storage space, so the hard disk writes data sequentially instead of randomly, so it has better performance.

2) when the data is written from double write buffer to the real segment, the system will automatically merge the connection space refresh mode. How does the pages; three double write work during recovery?

If there's a partial page write to the doublewrite buffer itself, the original page will still be on disk in its real location.-

If writing doublewrite buffer itself fails, then the data will not be written to disk. 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.

When InnoDB recovers, it will 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 will use the copy in the doublewrite buffer during recovery.

If doublewrite buffer writes successfully, but fails to write to disk, InnoDB will not be calculated from the transaction log, but will be written again with buffer data.

InnoDB knows when a page is corrupt because each page has a checksum at the end; the checksum is the last thing to be 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.

During recovery, InnoDB compares the checksum of the page directly. If not, the original data is loaded from the hard disk, and the correct data is deduced from the transaction log. Therefore, the recovery of InnoDB usually takes a long time. Fourth, do we necessarily need 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 is redundant for InnoDB to do it. You can disable the doublewrite buffer by setting InnoDB_doublewrite to 0.

5 how to use double write

InnoDB_doublewrite=1 means to start doublewrite

Show status like 'InnoDB_dblwr%' can query the usage of double write

Related parameters and status

Usage of Double write:

Number of show status like "% InnoDB_dblwr%"; InnoDB_dblwr_pages_written from bp flush to DBWB

The number of times InnoDB_dblwr_writes wrote the file

Number of page merged per write operation = InnoDB_dblwr_pages_written/InnoDB_dblwr_writes

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.