Bug #72948 lock_table can be optimized if updating the same table concurrently
Submitted: 11 Jun 2014 3:44 Modified: 30 Jul 2018 17:32
Reporter: zhai weixiang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[11 Jun 2014 3:44] zhai weixiang
Description:
Since most workload of our production environment is  normal DML/SELECT ( and this should be very common on most OLTP workload ). So:
1. most time there's only LOCK_IX / LOCK_IS table lock
2.  LOCK_S/LOCK_X is rarely happen.

 I think we can add some counter to identify if  LOCK_S/LOCK_X was granted and quickly exit function lock_table_other_has_incompatible. This will improve the performance especially when all DML focused on the same table.

I did a simple test: 
one table 
100 concurrent threads
update according to primary key
TPS: 39,000 => 41,400

How to repeat:
read the code of function lock_table

Suggested fix:
I'll attach a simple patch to prove the performance improvement.
[11 Jun 2014 4:42] zhai weixiang
attached patch

Attachment: quick_check.diff (application/octet-stream, text), 3.34 KiB.

[11 Jun 2014 9:04] zhai weixiang
updated patch , based on MySQL5.6.17

Attachment: quick_check.diff (application/octet-stream, text), 3.51 KiB.

[12 Jun 2014 2:30] zhai weixiang
The lock grant operation can also be optimized:
If there isn't any LOCK_S/LOCK_X table lock request and the current lock mode is LOCK_IS/LOCK_IX, then don't need to loop the table lock list, and quickly exit function lock_table_dequeue.
[3 Dec 2014 5:48] zhai weixiang
I think we can optimize innodb table lock like MDL subsystem: fast-path for DML and short-path for DDL. We can even ignore table lock creation for normal DML.  I am currently working on a patch based on this idea.
[20 Apr 2018 11:40] Jakub Lopuszanski
Thank you Zhai Weixiang, for your contribution!
Your idea, slightly adapted, will used and reused in two more places (releasing the locks, had similar problem).
[25 Apr 2018 13:51] Jeb Miller
After commit, throughput increased +24% in DBT2 workload for 100 warehouses at 1000 terminals on Linux EL7 production automated test bed.
[30 Jul 2018 17:32] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.12 release, and here's the changelog entry:

Table locking was optimized for concurrent updates on the same table by
avoiding costly iterations over the table locks queue. 

Thanks to Zhai Weixiang for the contribution.