MySQL Doublewrite Buffer and business evaluation, doublewritebuffer

MySQL Doublewrite Buffer and business evaluation, doublewritebuffer

1. Summary of Doublewrite Buffe

Doublewrite Buffer: the original intention of Doublewrite Buffer is to prevent dirty pages in the buffer pool from being refreshed to the disk. Some write problems occur. The innodb page size is generally 16 KB, in Linux, the block size is generally 4 k. In this way, if a crash or power failure occurs in the OS during the refresh process, only part of the 16 K page blocks will be refreshed to the disk, while others will not, which will lead to data inconsistency. Doublewrite Buffer is placed in the system tablespace, with a size of 100 pages. When innodb refreshes the pages in the buffer pool to the disk, it will first copy to Doublewrite Buffer and then refresh to the disk. If a problem occurs during disk refresh, innodb uses the page stored in doublewrite buffer to restore data. If a problem occurs during the refreshing of doublewrite buffer, the raw page and redo log on the physical disk are used to restore the data.

Enabling Doublewrite Buffer will reduce the overall performance of MySQL by 5-10%. You can set it through the innodb_doublewrite parameter, which is enabled by default.

Percona has a separate parameter to set the storage location of Doublewrite Buffer, innodb_doublewrite_file.

 

2. Calculate and export machine-related indicators based on the following:

Response time query and operation request return in ms.

The total data volume is about GB in one year.

There are requests per second.

The read/write ratio is.

Critical level Core System, P1 faults.

Others indicate that the data record length is about 1 kb, and data operations are performed frequently within one week.

 

Derivation process:

Step 1: about 500 GB of data within one year

Result: The amount of data generated per second is approximately 500*1024*1024/(365*24*60*60) = 17KB/s.

Step 2: requests per second, with a read/write ratio

Result: The number of read requests per second is 3 w/2 = 1.5 w/s, and the number of write requests per second is 3 w/2 = 1.5 w/s.

Step 3: The record length is approximately 1 kb.

Result: according to the result obtained by step 1, the insert rate per second is 17 times/s. According to step 2, The Write Request per second is 1.5 w/s. We can see that 14983 delete and update operations are performed per second. Since MySQL reads and writes data according to the page, the page size is 16 KB. Assume that the pages of each operation are different. The write operation data volume per second is 16 KB * 1.5 w = 234 MB/s. The read operation data volume per second is 16 KB * 1.5 w = 234 MB/s.

Step 4: frequent data operations within one week

Result: The Hot data volume is: (500/365) * 7 = 9.6 GB.

Step 5: Return in ms

If the operation returns results in milliseconds, You need to load hot data to the memory as much as possible. Based on the memory hit rate close to 100%, Innodb buffer is about 9.6 GB, while other memory requirements are about 1 ~ 2 GB, so the memory overprovisioning is about 12 GB. Based on the overprovisioning principle, the write bandwidth (wBPS) is limited to 300 MB/s, And the read bandwidth (rBPS) is limited to 300 MB/s.