Severe performance regression in MySQL 5.7 crash recovery

PREVIOUS POST
NEXT POST

In this post, we’ll discuss some insight I’ve gained regarding severe performance regression in MySQL 5.7 crash recovery.

Working on different InnoDB log file sizes in my previous post:

What is a big innodb_log_file_size?

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).

PREVIOUS POST
NEXT POST

Share this post

Comments (2)

  • Marko Mäkelä Reply

    This is a regression from WL#7142 (see http://mysqlserverteam.com/innodb-crash-recovery-improvements-in-mysql-5-7/ and http://dev.mysql.com/worklog/task/?id=7142) which aims to improve the recovery performance when you have a large number of *.ibd files, but recovery only needs to apply log to a tiny fraction of them. Before the change, InnoDB would open every *.ibd file to read the tablespace ID from each file, also for files that were not modified recently.

    June 10, 2016 at 4:46 am
  • xiaowei Reply

    [ERROR] InnoDB: The innodb_system data file ‘ibdata1’ must be writable
    [ERROR] InnoDB: The innodb_system data file ‘ibdata1’ must be writable
    [ERROR] InnoDB: Plugin initialization aborted with error Generic error
    [ERROR] Plugin ‘InnoDB’ init function returned error.
    [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
    [ERROR] Failed to initialize plugins.
    [ERROR] Aborting
    I use xtrabackup-24-2.4.3 reduction times error. mysql version is mysql-community-server-5.7.12-1.el6.x86_64
    What should I do these error?
    I have been performed in the data catalogue chown -R mysql:mysql *,

    June 17, 2016 at 3:48 am

Leave a Reply