InnoDB key characteristics of double write

# The risk of dirty page brush disk

Two-time principle mechanism of writing

1, solve the problem

2. Usage Scenarios

3, Doublewrite's work flow

4. Crash recovery

# Side Effects of Doublewrite

1. Monitor Doublewrite Load

2. Close the doublewrite scene

One, dirty page brush disk risk

Minimum unit for IO:

1, the minimum unit of database IO is 16K (mysql default, Oracle is 8K)

2, the minimum unit of file system IO is 4K (also 1 k)

3, the minimum unit of disk IO is 512K

Therefore, there is a risk that the IO write causes page corruption:

 

Second, Doublewrite: two times write

Improves InnoDB reliability To resolve partial write failures (partial page write breaks).

1. Double write solves what problem

A data page size is 16K, assuming that in the memory of the dirty page write to the database, write a 2K suddenly power down, that is, the former 2K data is new, after 14K is old, then the disk database this data page is incomplete, is a broken data page. Redo can only add the old, school check the full data page to recover a dirty block, can not repair the broken data page, so this data is lost, may cause inconsistent data, so you need a double write.

2. Usage Scenarios

When the database is writing a data page from memory to disk, the database is down, which causes the page to write only part of the data, which is part write invalidation, it will cause data loss. In this case, the redo log cannot be recovered because the redo log records the physical modification of the page, and if the page itself is corrupted, the redo log is powerless.

3. Double Write workflow

Doublewrite is made up of two parts, part of doublewrite buffer in memory, 2MB in size, and 128 contiguous pages in shared tablespace (Ibdata x) on disk, 2 extents (extent), and 2M in size.

1, when a series of mechanism triggers the dirty page refresh in the data buffer pool, it is not written directly to the disk data file, but is copied to the in-memory doublewrite buffer first;

2, then from two write buffers divided two times to write to the disk share table space (continuous storage, sequential write, high performance), each write 1MB;

3. After the second step is completed, the dirty page data in Doublewrite buffer is written to the actual individual tablespace file (discrete write); (after the dirty page data is cured, the tag corresponding to the Doublewrite data can be overwritten)

4, Doublewrite of the crash recovery

If the operating system crashes during the writing of the page to disk, during the recovery process, the InnoDB storage engine can find a recent copy of the page from the doublewrite of the shared tablespace, copy it to a tablespace file, and then apply Redo log to complete the recovery process.

Because there are replicas, there is no worry about whether the data pages in the tablespace are corrupted.

Q: Why is log write not required for Doublewrite support?

A:

Because Redolog writes a unit of 512 bytes, which is the smallest unit of disk IO, it doesn't matter if the data is corrupted.

Third, the side effect of Doublewrite

1. Write load for double write

1, double write is a buffer, but in fact it is open on the physical file of a buffer, in fact, it is file, so it will cause the system has more fsync operation, and the hard disk Fsync performance is very slow, so it will reduce the overall performance of MySQL.

2, however, Doublewrite buffer to disk shared table space This process is continuous storage, sequential write, high performance, (about the write%10), sacrificing a bit of write performance to ensure the integrity of the data page or is necessary.

2. Monitor double Write workload

Mysql>Show Global Status like '%dblwr%';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|Innodb_dblwr_pages_written| 7     ||Innodb_dblwr_writes| 3     |+----------------------------+-------+2Rowsinch Set(0.00Sec

Focus point:innodb_dblwr_pages_written/innodb_dblwr_writes

When Doublewrite is turned on, each dirty page refresh must first write Doublewrite, and doublewrite exists on the disk is two contiguous zones, each of which consists of contiguous pages, in general, a zone up to 64 pages, So a single IO write should be able to write up to 64 pages.

And according to the above system Innodb_dblwr_pages_written and innodb_dblwr_writes ratio, about 3, far from 64 (if about equals 64, then the system write pressure is very large, there are a lot of dirty pages to write on disk), So it can be seen from this angle that the system write pressure is not high.

3. Close double write-fit scene

1. Massive DML

2, not afraid of data corruption and loss

3, the system writes the load to become the main load

Mysql>Show variables like '%double%';+--------------------+-------+|Variable_name|Value|+--------------------+-------+|Innodb_doublewrite|  on    |+--------------------+-------+1Rowinch Set(0.04Sec

As a key feature of InnoDB, the Doublewrite feature is turned on by default, but some of these special scenarios can also be turned off to improve database write performance. Static parameters, configuration file modification, restart database.

4, why did not write the double write inside the data page inside it?

1, double write inside the data is continuous, if you write directly to the inside data page, and the page of data page is discrete, writing will be very slow.

2, double write the data inside no way to be overwritten, resulting in a double write pressure is very large; a double write overflow may occur within a short period of time.

InnoDB key characteristics of double write