In this post, we’ll discuss some insight I’ve gained regarding severe performance regression in MySQL 5.7 crash recovery.Working on with different
Working on different InnoDB log file sizes in my previous post:
I tried to understand how we can make InnoDB crash recovery faster, but found a rather surprising 5.7 crash recovery regression.
Basically, crash recovery in MySQL 5.7 is two times slower, due to this issue: https://bugs.mysql.com/bug.php?id=80788 . InnoDB now performs the log scan twice, compared to a single scan in MySQL 5.6 (no surprise that there is performance degradation).
Fortunately, there is a proposed patch for MySQL 5.7, so I hope it will be improved soon.
As for general crash recovery improvement, my opinion is that it would be much improved by making it multi-threaded. Right now it is significantly limited by the single thread that reads and processes log entries one-by-one. With the current hardware, consisting of tens of cores and fast SSD, we can improve crash recovery by utilizing all the resources we have.
One small improvement that can be made is to disable PERFORMANCE_SCHEMA during recovery (these stats are not needed anyway).