Bug #90643 use different mutex to protect trx_sys->serialisation_list
Submitted: 26 Apr 7:52 Modified: 26 Apr 16:36
Reporter: zhai weixiang (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0.11 OS:Any
Assigned to: Allen Lai CPU Architecture:Any

[26 Apr 7:52] zhai weixiang

Currently innodb uses trx_sys->mutex to protect operation on trx_sys->serialisation_list. So while opening read view, it will get the min trx no from the list which decides the low limit no of undo log that can be purged.   

So, in my opinion, the trx_t::no is something that only relates to undo purging, and we can use another mutex rather than trx_sys->mutex to protect it.  Meanwhile, we can also keep tracking the min trx_t::no on list to reduce mutex contention.   

How to repeat:
read the code 

Suggested fix:
I'll attach a POC patch later
[26 Apr 13:21] zhai weixiang
a prof-of-concept patch based on 8.0.11, not fully tested but just verify the performance improvement 

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

Contribution: trx_no_8.diff (text/x-patch), 7.84 KiB.

[26 Apr 13:22] zhai weixiang
while binlog is disabled, I can see almost *2 performance improvement with the patch.
[26 Apr 16:36] Sinisa Milivojevic

I have scrutinised your analysis and I think that it is correct.

Verifed as a feature request for the stand-alone servers.