Point deletes get locked when partition pruning in effect
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.6 |
Triaged
|
High
|
Unassigned | |||
5.7 |
Invalid
|
High
|
Unassigned |
Bug Description
It seems that issuing a DELETE that does partition pruning causes all other deletes to get locked in toku_db_
Please consider the following table:
CREATE TABLE `BIG_STORAGE_00` (
`HASH_ID` char(64) NOT NULL,
`SERIALIZATIO
`COMPRESSION_
`RAW_DATA` mediumblob NOT NULL,
`LAST_UPDATE` datetime NOT NULL,
`EXPIRE_DATE` datetime NOT NULL,
KEY `EXPIRE_DATE_IX` (`EXPIRE_DATE`),
KEY `HASH_ID` (`HASH_ID`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(
(PARTITION p652 VALUES LESS THAN (736739) ENGINE = TokuDB,
PARTITION p653 VALUES LESS THAN (736740) ENGINE = TokuDB,
PARTITION p654 VALUES LESS THAN (736741) ENGINE = TokuDB,
PARTITION p655 VALUES LESS THAN (736742) ENGINE = TokuDB,
PARTITION p656 VALUES LESS THAN (736743) ENGINE = TokuDB,
PARTITION p657 VALUES LESS THAN (736744) ENGINE = TokuDB,
PARTITION p658 VALUES LESS THAN (736745) ENGINE = TokuDB,
PARTITION p659 VALUES LESS THAN (736746) ENGINE = TokuDB,
....
The following DELETEs will behave correctly:
DELETE FROM testdb1.
The following DELETEs will take a long time and cause all other DELETEs to be locked in toku_db_
DELETE FROM testdb1.
The difference is only in partition pruning (first DELETE type does not prune, second one does).
In the second case, issuing a show processlist shows that the first DELETE that gets executed is taking a long time:
| 9456 | sb | localhost | sbtest | Query | 63 | Queried about 3420000 rows | DELETE FROM testdb1.
This is reproducibile with sysbench, any number of threads will do, even two:
| 9742 | sb | localhost | sbtest | Query | 3 | updating | DELETE FROM testdb1.
| 9743 | sb | localhost | sbtest | Query | 7 | Queried about 440000 rows | DELETE FROM testdb1.
Below is the stack dump that shows the locking problem (sysbench running with 100 threads). We are being hit by this in production.
Server version: 5.6.35-80.0-log Percona Server (GPL), Release 80.0, Revision f113994f31
2304 pthread_
903 pthread_
99 pthread_
18 libaio:
4 pthread_
3 pthread_
2 select(
2 pthread_
1 sigwait(
1 sigwaitinfo(
1 select(
1 pthread_
1 pthread_
1 pthread_
1 pthread_
1 poll(libc.
1 poll(libc.
1 nanosleep(
summary: |
- TokuDB: possible scalability issue with many inserts in multiple - schemas + TokuDB: deletes by range get locked in toku_db_get_range_lock() |
description: | updated |
tags: | added: upstream |
Forget to mention...
Server version: 5.6.35-80.0-log Percona Server (GPL), Release 80.0, Revision f113994f31