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

This article introduces the relevant knowledge of "how to master double write in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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, My SQL 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

The number of page merged with each write operation = InnoDB_dblwr_pages_written/InnoDB_dblwr_writes "how to master double write in MySQL" is introduced here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.