Bug #82937 Speedup the crash recovery speed by storing the length of log body
Submitted: 10 Sep 2016 2:03 Modified: 12 Sep 2016 6:02
Reporter: zhai weixiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[10 Sep 2016 2:03] zhai weixiang 
Description:
During crash recovery, innodb will parse all log entries from log file, and then store them in hash. 

During parsing stage, it need to fully parse and check if the log rec is completely added to buf. And it will consume a lot of cpu time, for example, creating/destroying dummy table and dummy index. 

I added one extra byte in function mlog_write_initial_log_record_low to store the length of log body. So we could quickly check if the log rec is completely stored. 

How to repeat:
1. Change the source code  (I'll attach the patch later) 

2. Run sysbench, update-non-index.lua under heavy workload. then kill the server in  a sudden. 

3. Copy the log/data directory so we can repeat the test under same scenario.

a.   Time spent on parsing log when the extra byte is not been used. 

2016-09-10T01:36:10.050140Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1307245568
...
2016-09-10T01:36:48.522496Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2617286109

It spent 38 secs

b.  Making use of the extra byte to speedup the parsing speed.   

2016-09-10T01:42:43.409229Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1307245568
2016-09-10T01:42:57.725215Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2617286109

It only spent 14 secs

Suggested fix:
Will attach the patch later.
[10 Sep 2016 6:24] zhai weixiang 
Based on 5.7.15. Note the patch is only written for testing purpose, it's not ready for running on  production

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 57_extra_len.diff (application/octet-stream, text), 26.92 KiB.
[11 Sep 2016 2:41] zhai weixiang 
use two bytes to store length of log body.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 57-redo.diff (application/octet-stream, text), 26.83 KiB.
[12 Sep 2016 6:02] MySQL Verification Team 
Hello Zhai,

Thank you for the report and contribution.

Thanks,
Umesh