MySQL Bugs: #106768: dummy index optimize: it is better to reuse the index object

Bug #106768	dummy index optimize: it is better to reuse the index object
Submitted:	18 Mar 2022 7:40	Modified:	18 Oct 13:29
Reporter:	alex xing (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
during innodb recover, create and release dummy index frequently  in mlog_parse_index. it is better to reuse the index object

How to repeat:
just read the below patch

Suggested fix:
reuse the index object

a simple patch based on 8.0.19. The same may be true for the latest version of the code. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: dummy_index_optimize.patch (text/plain), 9.79 KiB.

before optimize

Attachment: before_optimize.png (image/png, text), 1.14 MiB.

before optimize

Attachment: before_optimize.png (image/png, text), 1.14 MiB.

after_optimize

Attachment: after_optimize.png (image/png, text), 1.04 MiB.

Hello Alex Xing,

Thank you for the report and contribution.

regards,
umesh

Posted by developer:
 
This issue is handled by WL#17287 "Cache dict_index_t objects while parsing redo log"
http://wl.no.oracle.com/worklog/Server-Sprint/?tid=17287
which is already pushed to mysql-trunk for 26.10.0 release.
The solution in that WL is slightly more elaborate than the one proposed here, as the dict_index_t objects are actually filled with data parsed from the redo-log, and stored in an LRU cache, indexed by the redo log bytes which describe them. The parser can then compare the content of parsing buffer to the prefixes stored in this cache to obtain already parsed object.

Posted by developer:
 
Added the following note to the MySQL Server 26.10.0 release notes:

InnoDB now caches parsed index metadata per thread while parsing
redo logs rather than repeatedly allocating and reconstructing
temporary objects. This improves redo parsing and Read Replica
Applier performance without changing SQL syntax, interfaces, or
file formats.
Our thanks to Alex Xing for the contribution.