Bug #100383 Improve InnoDB buffer pool initialization
Submitted: 30 Jul 2020 15:34 Modified: 1 Sep 2020 15:08
Reporter: Zheng Lai (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: buffer pool, innodb

[30 Jul 2020 15:34] Zheng Lai
Parallel initialization of InnoDB buffer pool is introduced in MySQL8.0.11, it can speedup the startup procedure, especially for big buffer pool.
But when we test this feature, we found that it still takes seconds to init the big buffer pool.
Finally, we found that most of time is cost in create block->mutex and block->lock.

How to repeat:
Startup with big buffer pool size, like > 500G, and 8 buffer pool instances it'll take >20s to initialize the buffer pool.

Suggested fix:
Our team Tencent Cloud-Native Database Team(CDB&CynosDB) found a lazy approach to improve this, which means block->mutex and block->lock can be created when getting the block at first time. 
We're willing to contribute this patch.
[30 Jul 2020 16:37] Zheng Lai
I've uploaded the patch, it can be applied to 8.0.21.
Please evaluate it, thanks.
[31 Jul 2020 12:57] MySQL Verification Team
Hello Mr. Lai,

Thank you for your report and especially, thanks a lot for your patch.

We truly like your patch and we will ask our Development to take a good look at it.

We will verify this performance improvement report as soon as you sing the OCA agreement.

Thanks again.
[1 Sep 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[1 Sep 2020 1:50] Zheng Lai
Would you please reopen this bug? I'll updated the contribution patch.
[1 Sep 2020 1:57] Zheng Lai
patch for init buffer pool lazily

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

Contribution: init_buffer_pool.patch (application/octet-stream, text), 7.71 KiB.

[1 Sep 2020 1:58] Zheng Lai
I've updated the patch, please evaluate it. Thanks.
[1 Sep 2020 15:08] MySQL Verification Team
Thank you for the feedback.
[4 Sep 2020 17:33] Marcin Babij
Thank you Mr. Lai for the idea and the patch.
We are currently evaluating a different approach that fixes this problem.
I will post updates in this bug.
[21 Sep 2020 12:55] MySQL Verification Team
Thank you, Marcin.