Details
-
Bug
-
Status: Stalled (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.6, 10.11, 11.1(EOL), 11.2, 11.4, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL), 11.3(EOL)
Description
NOTE: the following description is obsolete. See these comments for the current description of the bug:
- https://jira.mariadb.org/browse/MDEV-28804?focusedCommentId=278951&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-278951
- https://jira.mariadb.org/browse/MDEV-28804?focusedCommentId=278955&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-278955
Found while working on MDEV-28803. Performance regression after 10.3 for debug builds:
SET GLOBAL innodb_buffer_pool_size=12*1024*1024; |
CREATE TABLE t1 (d DOUBLE); |
INSERT INTO t1 VALUES (0x0061),(0x0041),(0x00E0),(0x00C0),(0x1EA3),(0x1EA2),(0x00E3),(0x00C3),(0x00E1),(0x00C1),(0x1EA1),(0x1EA0); |
INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6; |
Will complete quite quickly on 10.3 (less than 30 sec):
10.3.36 9d10b7107cf022b939dc61cedf8fc8985443c880 (Debug) |
10.3.36-dbg>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6;
|
Query OK, 2985984 rows affected (29.357 sec)
|
Records: 2985984 Duplicates: 0 Warnings: 0
|
But is much slower on other releases, with varying results (in order of "speed"):
10.7.5 61727fa40f914370e843ff89a76eba7ef785c5fe (Debug) |
10.7.5-dbg>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6;
|
Query OK, 2985984 rows affected (3 min 4.646 sec)
|
Records: 2985984 Duplicates: 0 Warnings: 0
|
10.6.9 05d049bdbe6814aee8f011fbd0d915f9d82a30ee (Debug) |
10.6.9-dbg>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6;
|
Query OK, 2985984 rows affected (3 min 10.007 sec)
|
Records: 2985984 Duplicates: 0 Warnings: 0
|
10.4.26 ebbd5ef6e2902a51a46e47dbb8a8667593cb25e7 (Debug) |
10.4.26-dbg>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6;
|
Query OK, 2985984 rows affected (4 min 25.718 sec)
|
Records: 2985984 Duplicates: 0 Warnings: 0
|
10.5.17 2840d7750db11a8d2ab3f212a05f5afefaef6d4d (Debug) |
10.5.17-dbg>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6;
|
Query OK, 2985984 rows affected (4 min 34.130 sec)
|
Records: 2985984 Duplicates: 0 Warnings: 0
|
10.8 to 10.9: I gave up waiting after 3000 seconds (100x slowdown compared with 10.3):
10.8.4 0e0a3580efdae313fab340bbb308d371fa36c021 (Debug) |
10.8.4-dbg>show processlist;
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
| 4 | root | localhost | test | Query | 3023 | Sending data | INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6 | 0.000 |
|
| 5 | root | localhost | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
2 rows in set (0.000 sec)
|
10.9.2 6ec17142dcfb1e9d9f41211ed1b6d82e062d1541 (Debug) |
10.9.2-dbg>show processlist;
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
| 4 | root | localhost | test | Query | 3070 | Sending data | INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6 | 0.000 |
|
| 5 | root | localhost | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
2 rows in set (0.001 sec)
|
Same for 10.10
10.10.0 081a284712bb661349e2e3802077b12211cede3e (Debug) |
10.10.0-dbg>show processlist;
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
| 4 | root | localhost | test | Query | 512 | Sending data | INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6 | 0.000 |
|
| 5 | root | localhost | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------+----------+
|
2 rows in set (0.000 sec)
|
In regards 10.8 to 10.10, potentially researching MDEV-28805 can provide a clue.
Attachments
Issue Links
- blocks
-
MDEV-33324 insert ... select from joins hangs or result in 1206: The total number of locks exceeds the lock table size
- Open
- relates to
-
MDEV-24813 Locking full table scan fails to use table-level locking
- In Review
-
MDEV-28803 ERROR 1206 (HY000): The total number of locks exceeds the lock table size
- Open
-
MDEV-28805 SET GLOBAL innodb_buffer_pool_size=12*1024*1024 has different outcomes depending on version
- Confirmed
-
MDEV-28800 SIGABRT due to running out of memory for InnoDB locks
- Closed