MySQL Bugs: #81853: Make rollback of transactions parallel

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

This probably is a duplicate of:
Bug #81753 	Make crash recovery parallel

Hello Simon,

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

Thanks,
Umesh

Also, Related - Bug #81648, Bug #80788

Per [30 Jun 6:20] Naga Satyanarayana Bodapati

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