In MySQL 5.7, we have improved the scalability of DML oriented workloads in InnoDB. This is the result of a number of changes, which I will outline below.
(1) Fix index->lock contention
This RW lock protects all indexes, both the cluster and the secondary indexes.
Before 5.7, every modifications to non-leaf pages (every modifications for the tree structure) required to exclude the other threads’ access to the whole index by X-lock, and every concurrent accessing the index tree were blocked. This was the major reason of the index->lock contention in concurrent DML workloads.
In MySQL 5.7 concurrent access is now permitted to the non-leaf pages (internal nodes of the B+Tree) as long as they are not related to the concurrent tree structure modifications (WL#6326). This change reduces the major point of contention.
(2) …
[Read more]