[MDEV-28804] Increased lock objects in 10.6+ leading to significant slowdown when using a limited InnoDB buffer pool, including in release builds - Jira

XML

Word

Printable

Type: Bug
Status: Stalled (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Component/s: Locking, Storage Engine - InnoDB
Labels:
- regression-10.6
- thread_hang

NOTE: the following description is obsolete. See these comments for the current description of the bug:

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.

blocks

MDEV-33324 insert ... select from joins hangs or result in 1206: The total number of locks exceeds the lock table size

relates to

MDEV-24813 Locking full table scan fails to use table-level locking

MDEV-28803 ERROR 1206 (HY000): The total number of locks exceeds the lock table size

MDEV-28805 SET GLOBAL innodb_buffer_pool_size=12*1024*1024 has different outcomes depending on version

MDEV-28800 SIGABRT due to running out of memory for InnoDB locks

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.