Bug #81853 Make rollback of transactions parallel
Submitted: 15 Jun 2016 6:08 Modified: 30 Jun 2016 6:25
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2016 6:08] Simon Mudd
When doing recovery InnoDB appears to use a single thread.  If there is a lot of data to recover then this can take some time and probably takes longer because of this.

Under normal circumstances InnoDB will allow changes to happen in parallel taking into account the appropriate contention between threads.

I notice that during recovery we see for a large amount of time the CPU "pegged" at 100%, when actually I have a number of cores completely idle.

It's not clear to me how much time could be saved by doing some of this process in parallel, but it strikes me that some gain could be made. It may be argued that during crash recovery things need to be as safe as possible but if it is possible to do part of this recovery process in parallel then that would be good.

How to repeat:
I noticed on recovery the server state:

InnoDB: 1655 transaction(s) which must be rolled back or cleaned up 

So it looks like it's going to have to rollback 1655 transactions. That's quite a large number and it took some time.  Is it really not possible to roll _any_ of these back in parallel? What I seemed to see was each transaction_id being rolled back in reverse order, one by one. Surely it's possible to do some of this in parallel?

Suggested fix:
Look to see if the recovery process can be optimised so some of the stages can be handled in parallel. The main interest here is to reduce the recovery time.

If I have 24 cores or more on my box it should be fine to have at least this number of cores being busy in this task. If you can figure out how many cores are available do so, if not on a modern server having a minimum setting of 16 would be a good starting point I would guess.
[15 Jun 2016 7:42] Daniƫl van Eeden
This probably is a duplicate of:
Bug #81753 	Make crash recovery parallel
[15 Jun 2016 7:45] MySQL Verification Team
Hello Simon,

Thank you for the report.
This is duplicate of Bug #81753

[15 Jun 2016 7:45] MySQL Verification Team
Also, Related - Bug #81648, Bug #80788
[30 Jun 2016 6:25] MySQL Verification Team
Per [30 Jun 6:20] Naga Satyanarayana Bodapati

We should change title of your bug to "Make rollback of transactions parallel"