Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.2.9
-
None
-
Linux
Description
Deleting records on a table blocks all other nodes from writing to any database or table after the DELETE finished on the node for a time that is proportional to the number of records.
On a i3 4 cores, SSD, 12GB box , deleting just 500.000 records cause the other nodes to be blocked for 30s+
Query status depend on the type of query, INSERT will be stuck in "Query end", DDL will be stuck in "checking permissions":
3526 root localhost:39454 mysqlslap Query 40 query end INSERT INTO t1 VALUES (1946710049,599529154,'hmu3lkoxjtvgLu5xKHSTTtJuGE5F5QqmCcppCTmvFZScRZQgim93gSx 0.0003
|
OR
3365 root localhost:38408 NULL Query 31 checking permissions DROP SCHEMA IF EXISTS `mysqlslap` 0
|
To reproduce:
NODE 1:
Import attached data in db1
use db1; |
# Drop or not the FK constraints, it does not change anything |
DELETE FROM IT9a932d5519; |
#Wait for it to finish |
Now on NODE 2 or 3: (this does not happen on NODE 1)
use test; |
CREATE TABLE p (id int); |
INSERT INTO p VALUES (123); |
Either will be blocked for long time, if you already created t1, it will be the INSERT.
Bonus problem
In some cases I have got:
MariaDB [test]> SELECT NOW(); insert into p values (122); SELECT NOW();
|
+---------------------+
|
| NOW() |
|
+---------------------+
|
| 2017-10-03 14:09:55 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
 |
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
|
+---------------------+
|
| NOW() |
|
+---------------------+
|
| 2017-10-03 14:10:42 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
In such case another simple INSERT was happening on another node on the same table.
MariaDB [test]> show create table p\G
|
*************************** 1. row ***************************
|
Table: p
|
Create Table: CREATE TABLE `p` (
|
`id` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
1 row in set (0.00 sec)
|