Shock with innodb_doublewrite_pages in MySQL 8.0.20+

In a current publish, The Quirks of Index Upkeep in Open Supply Databases, I in contrast the IO load generated by open supply databases whereas inserting rows in a desk with many secondary indexes. Due to its change buffer, InnoDB was essentially the most environment friendly resolution. Nonetheless, that’s not the tip of the story.

Evolution of the InnoDB doublewrite buffer

Up till a couple of years in the past, the InnoDB doublewrite buffer was a bottleneck below excessive write load. Basically, the doublewrite buffer is a file used to forestall torn pages. InnoDB makes use of 16KB pages, which might simply be partially written (torn) in case of a crash (Instance: The storage gadget makes use of 4KB blocks. This requires 4 IO to put in writing a web page. If a crash happens after 2 IO, then a whole web page was not written, and is now torn). When a unclean web page within the InnoDB buffer must be flushed to storage, the next steps are executed:

1. First, a slot (or many) from the doublewrite buffer is acquired
2. The soiled web page is first written to the doublewrite buffer file
3. The doublewrite buffer file is fsync’d
4. The web page is then written to its tablespace file
5. The tablespace file is fsync’d
6. Lastly, the doublewrite buffer slot is launched

Above, I mentioned flushing a single web page, however more often than not, multiple web page is flushed collectively. The primary challenge is that there are solely about 120 slots, and between steps one and 6, one or many slots are locked. That primarily limits the variety of write operations in-flight. On storage units with a major latency, like network-attached cloud storage, this represents a significant bottleneck for writes.

To repair this, Percona launched in Percona Server for MySQL 5.7.11 the parallel doublewrite buffer, the place each buffer pool occasion obtained a full doublewrite buffer with 120 slots. This enormously improved the flushing capability, permitting a vastly superior variety of in-flight write operations. Realizing the advantages of the Percona parallel doublewrite buffer, Oracle applied an identical function in MySQL 8.0.20. Their implementation has the added benefit of permitting you to set the scale of the doublewrite buffers. It’s, nevertheless, with this added diploma of liberty {that a} poor selection of a default worth was made. As a substitute of going with a selection much like the Percona implementation of 120, they selected to default to the variety of write IO threads. The default variety of write IO threads is 4, a tiny worth for the doublewrite buffer.

The Oracle implementation was proven by Dimitry (right here) to have very optimistic impacts. The efficiency outcomes are significantly better than the previous single doublewrite buffer. However, as proven right here by Vadim, there’s nonetheless a bottleneck across the doublewrite buffer. The repair is simple: simply set innodb_doublewrite_pages to 128 (jfgagne and MySQL 8.0 doc).

Impacts on the IO outcomes

So, what can be the impacts on my earlier outcomes? Nicely, the impacts are laborious to overlook…

The variety of write IOPs is down by greater than half (55%), and the variety of learn IOPs is lowered by 10%. This huge distinction is probably going brought on by the low concurrency I used to generate the IOPS, a single insert thread. Nonetheless, replication is commonly additionally pretty low on concurrency.

This leaves me puzzled. I do know the default worth challenge is mounted in 8.4.x, however I nonetheless have many shoppers who’ve simply accomplished their migration to eight.0.x. Why would a one-liner patch to eight.0 not be included within the 8.0.43 launch final July? Vadim’s publish Extra on Checkpoints in InnoDB MySQL 8, describing the problem dates from 2020; nothing is new right here. Given the shortage of will to handle the problem upstream, regardless of acknowledging it within the documentation, I filed a request in our personal Jira (PS-10236) to a minimum of repair Percona Server for MySQL 8.0.x.

In regards to the Writer

Yves Trudeau
Yves is a Principal Architect at Percona, specializing in distributed applied sciences resembling MySQL Cluster, Pacemaker and XtraDB cluster. He was beforehand a senior guide for MySQL and Solar Microsystems. He holds a Ph.D. in Experimental Physics.