Bug #90643 use different mutex to protect trx_sys->serialisation_list

Description:

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

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.

while binlog is disabled, I can see almost *2 performance improvement with the patch.

Hi,

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

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

Posted by developer:
 
Fixed as of the upcoming 8.0.25 release, and here's the proposed changelog entry from the documentation team:

The trx_sys_t::serialisation_mutex was introduced to reduce contention on
the on the trx_sys_t::mutex. The new mutex protects the
trx_sys_t::serialisation_list when a transaction number is assigned, which
was previously protected by the trx_sys_t::mutex. 

Thanks to Zhai Weixiang for the contribution.

Posted by developer:
 
Changelog entry moved to 8.0.26 release notes.