MySQL Bugs: #90643: use different mutex to protect trx_sys->serialisation

Bug #90643	use different mutex to protect trx_sys->serialisation_list
Submitted:	26 Apr 2018 7:52	Modified:	9 Apr 2021 18:17
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.0.11	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

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.