While doing benchmarks on 5.7 and 8.0, I came across a performance regression in MySQL 8.0 over 5.7 and opened a bug (Bug #111353 : 3x Performance Regression from 5.7 to 8.0 on ALTER TABLE FORCE). There has been recent activity on this bug, showing an easy workaround. This, even if it is known since 16 July 2024, has not been talked about much, so this deserves a blog post.
TL&DR : if you are running MySQL 8.0, you might want to consider setting innodb_doublewrite_pages to 128.
The title of my Bug #111353 is self-explanatory : 3x Performance Regression from 5.7 to 8.0 on ALTER TABLE FORCE. I opened this bug in June 2023 and someone else also found this regression, and Bug #114420 was opened in March 2024 (ALTER TABLE operation with Inplace algorithm is slower in 8.0). On 16 July 2024, an explanation was posted in that more recent bug : the default value for innodb_doublewrite_pages (introduced in 8.0.20) is suboptimal and was changed to 128 in 8.4 (there is a comment about the suboptimal value in the 8.0 documentation; link to 8.4 documentation). Also, in the 8.0 documentation about Optimizing InnoDB Disk I/O, we can read :
Consider increasing the value [innodb_doublewrite_pages], which defaults to 128 instead of 4 as of MySQL 8.4.0.
Although this option was introduced in MySQL 8.0.20, its behavior was 120 in MySQL 5.7. This change could cause a performance degradation for some operations, such as using ALTER to rebuild a table with the INPLACE algorithm.
I wish this would have been fixed in 8.0.40 (release on 2024-10-15) with an updated default. LTS stability, the reason given for not changing the default (see comment in Bug #114420 on 16 Jul 2024), is a little light for me. As I wrote in Bug #111353 (see comment on 28 Apr [2025]) : I think such regression justifies changing a default in 8.0, even if this breaks LTS "stability" (in this case, this is a LTS instability / regression, so I would not consider this "breaking" but actually "fixing" / improving stability).
Unless I missed something, I did not see an announcement between July 2024 and now about a default being suboptimal in 8.0. This is now fixed with this blog post.
Thanks for the heads up on this. I was unaware. I also agree that it would have been good to fix this in 8.0. It's clear the risk of the change (which goes back to old behaviour) is minimal.
ReplyDeleteEither way let's not forget that 8.0 will be EOL in April 2026, less than a year ago so everyone should be looking to move away from it to MySQL 8.4 LTS which has this fix applied.