MySQL Bugs: #100383: Improve InnoDB buffer pool initialization

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	buffer pool, innodb

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

I've uploaded the patch, it can be applied to 8.0.21.
Please evaluate it, thanks.

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.

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

Would you please reopen this bug? I'll updated the contribution patch.
Thanks

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.

I've updated the patch, please evaluate it. Thanks.

Thank you for the feedback.

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.

Thank you, Marcin.