Why MySQL 5.7 Partition Tables Slow Down: Uncovering an InnoDB Lock Regression

Background

MySQL 5.7 introduced many performance improvements, yet after upgrading a test environment from 5.6.21 to 5.7.18 the team observed more lock‑timeout incidents, especially on partitioned tables. The degradation was not due to configuration changes, prompting a deeper investigation.

Problem Description

Developers reported that the upgraded database exhibited slower performance and frequent lock timeouts. The affected tables were all partitioned, and updates always used the primary key. Initial tests showed:

Database version 5.7.18 with partitioned tables → performance drops. Database version 5.7.18 with non‑partitioned tables → performance normal. Database version 5.6.21 with partitioned tables → performance normal.

Reproducing the Issue

A minimal test case was built to reproduce the lock contention:

CREATE TABLE `t2`(
  `id` INT(11) NOT NULL,
  `dt` DATETIME NOT NULL,
  `data` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`dt`),
  KEY `idx_dt`(`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p20170218 VALUES LESS THAN (736744) ENGINE=InnoDB,
 PARTITION p20170219 VALUES LESS THAN (736745) ENGINE=InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE=InnoDB) */;

INSERT INTO t2 VALUES (1, NOW(), '1');
INSERT INTO t2 VALUES (2, NOW(), '2');
INSERT INTO t2 VALUES (3, NOW(), '3');

-- Session 1
BEGIN; UPDATE t2 SET data='12' WHERE id=1;
-- Session 2 (runs concurrently)
BEGIN; UPDATE t2 SET data='21' WHERE id=2;

Session 2’s UPDATE blocks indefinitely, even though it touches a different primary‑key row. Querying information_schema.innodb_locks reveals that the first transaction holds a lock on a page that also contains the second row, causing the deadlock‑like wait.

Rolling back to MySQL 5.6.21 eliminates the blockage, confirming the regression is version‑specific.

Deep Dive Analysis

Stack traces from the InnoDB memory engine showed that lock information is written to innodb_locks via fill_innodb_locks_from_cache, which pulls data from a cache object. Tracing the cache usage led to the function add_lock_to_cache, which ultimately receives a lock_t object.

Further inspection of RecLock::lock_add revealed that row locks are added to the transaction’s lock list. The critical path was identified in Partition_helper::handle_ordered_index_scan, where the variable m_part_spec.end_part determines how many rows are locked during an index scan.

Investigating the origin of end_part showed it is set in get_partition_set. For each single‑row UPDATE on a partitioned table, InnoDB locks a number of rows equal to the total number of partitions, not just the target row. This excessive locking explains the observed lock‑timeout behavior.

Verification

To confirm the hypothesis, two more rows (id 4 and 5) were inserted, extending the partition count beyond the three used in the original test. Updating id 1 in Session 1 and id 4 in Session 2 proceeded without blocking, because id 4 resides in a partition beyond the locked range.

INSERT INTO t2 VALUES (4, NOW(), '4');
INSERT INTO t2 VALUES (5, NOW(), '5');

-- Session 1
BEGIN; UPDATE t2 SET data='12' WHERE id=1;
-- Session 2
BEGIN; UPDATE t2 SET data='44' WHERE id=4;

In real deployments where partition counts can reach dozens or hundreds, each UPDATE would lock many rows, dramatically increasing lock contention and reducing concurrency.

Conclusion

The root cause is a regression in MySQL 5.7 where updates on partitioned tables lock a number of rows equal to the partition count, leading to unnecessary lock contention. The issue has been reported as a bug to the MySQL open‑source community, and Oracle has acknowledged it for further investigation.