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

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.4, 11.6(EOL), 11.7(EOL)
Fix Version/s: 10.5, 10.6, 10.11, 11.4
Component/s: Storage Engine - InnoDB
Labels:
- upstream

Description

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 lead to:

10.10.0 081a284712bb661349e2e3802077b12211cede3e (Optimized)
10.10.0-opt>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

In 10.6 to 10.10 only.
Note that in this ticket, unlike ~~MDEV-28800~~, no initial innodb-buffer-pool-size is set, though the buffer pool is resized in the same was as ~~MDEV-28800~~.

Attachments

Issue Links

relates to

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

In Review

MDEV-25342 autosize innodb_buffer_pool_chunk_size

Closed

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

Closed

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

Closed

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

Stalled

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

Open

(1 relates to)

Activity

Ascending order - Click to sort in descending order

View 5 older comments

Marko Mäkelä added a comment - 2022-06-14 13:50

The buffer pool resizing is inaccurate and somewhat nondeterministic by design. I do not think that it can be fixed easily, other than by reimplementing the buffer pool resizing in a different way: Allocate a reasonable maximum amount of contiguous 64-bit virtual address space for the buffer pool, but only map the requested amount of memory for it. I have discussed it with danblack in the past.

I think that what is needed to address this bug report is to ensure that the crashes (~~MDEV-28800~~) are avoided, and that the error message is refined to mention innodb_buffer_pool_size, because the explicit record locks will be allocated from the InnoDB buffer pool. It is a reasonable design, only the enforcement of the maximum allocation size is sloppy and the error message is not helpful for a user.

Marko Mäkelä added a comment - 2022-06-14 13:50 The buffer pool resizing is inaccurate and somewhat nondeterministic by design. I do not think that it can be fixed easily, other than by reimplementing the buffer pool resizing in a different way: Allocate a reasonable maximum amount of contiguous 64-bit virtual address space for the buffer pool, but only map the requested amount of memory for it. I have discussed it with danblack in the past. I think that what is needed to address this bug report is to ensure that the crashes ( MDEV-28800 ) are avoided, and that the error message is refined to mention innodb_buffer_pool_size , because the explicit record locks will be allocated from the InnoDB buffer pool. It is a reasonable design, only the enforcement of the maximum allocation size is sloppy and the error message is not helpful for a user.

Roel Van de Paar added a comment - 2022-06-14 22:16

Understood, thank you.

Roel Van de Paar added a comment - 2022-06-14 22:16 Understood, thank you.

Daniel Black added a comment - 2024-10-20 12:02

can't product any more as mtr test with 12M

like MDEV-33324 - producible with 5M innodb buffer pool size

10.6.20-MariaDB source revision a68e74b5a450c9de5b6b9459fd60e36a2fb0545c
11.6.2-MariaDB source revision b7bca3ff71615ab918410f02ffae74f8d66ff03f

Using 5M pool using lock_memory.test test added in ~~MDEV-28800~~ left (POOL_SIZE - (FREE_BUFFERS + DATABASE_PAGES)) as 9 pages like the test expects.

Daniel Black added a comment - 2024-10-20 12:02 can't product any more as mtr test with 12M like MDEV-33324 - producible with 5M innodb buffer pool size 10.6.20-MariaDB source revision a68e74b5a450c9de5b6b9459fd60e36a2fb0545c 11.6.2-MariaDB source revision b7bca3ff71615ab918410f02ffae74f8d66ff03f Using 5M pool using lock_memory.test test added in MDEV-28800 left (POOL_SIZE - (FREE_BUFFERS + DATABASE_PAGES)) as 9 pages like the test expects.

Roel Van de Paar added a comment - 2024-10-20 20:13

Issue reproducible on current 10.6, 10.11, 11.7 optimized builds with this CLI testcase:

# mysqld options required for replay: --innodb_buffer_pool_chunk_size=2097152

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,t1 t7;

CS 10.6.20 cd97caef84a842cf388866cfc0a0ec32b86a9c13 (Optimized)
10.6.20-opt>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

CS 10.11.10 8a6a4c947a0ca3d2fdca752d7440bdc5c6c83e37 (Optimized)
10.11.10-opt>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

CS 11.7.0 4016c905cbabea7f29ed282dc2125254c7c0d419 (Optimized)
11.7.0-opt>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

All versions produce output similar to the following in the error log:

CS 11.7.0 4016c905cbabea7f29ed282dc2125254c7c0d419 (Optimized)
2024-10-21 6:47:04 0 [Note] /test/MD141024-mariadb-11.7.0-linux-x86_64-opt/bin/mariadbd: ready for connections.
Version: '11.7.0-MariaDB' socket: '/test/MD141024-mariadb-11.7.0-linux-x86_64-opt/socket.sock' port: 11392 MariaDB Server
2024-10-21 6:47:21 0 [Note] InnoDB: Resizing buffer pool from 128.000MiB to 12.000MiB (unit = 2.000MiB).
2024-10-21 6:47:21 0 [Note] InnoDB: Disabling adaptive hash index.
2024-10-21 6:47:21 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2024-10-21 6:47:21 0 [Note] InnoDB: Start to withdraw the last 7308 blocks.
2024-10-21 6:47:21 0 [Note] InnoDB: Withdrawing blocks. (7308/7308).
2024-10-21 6:47:21 0 [Note] InnoDB: Withdrew 7308 blocks from free list. Tried to relocate 0 blocks (7308/7308).
2024-10-21 6:47:21 0 [Note] InnoDB: Withdrawn target: 7308 blocks.
2024-10-21 6:47:21 0 [Note] InnoDB: Latching entire buffer pool.
2024-10-21 6:47:21 0 [Note] InnoDB: Resizing buffer pool from 64 chunks to 6 chunks.
2024-10-21 6:47:21 0 [Note] InnoDB: 58 Chunks (7308 blocks) were freed.
2024-10-21 6:47:21 0 [Note] InnoDB: Resizing other hash tables.
2024-10-21 6:47:21 0 [Note] InnoDB: Resized hash tables: lock_sys, adaptive hash index, and dictionary.
2024-10-21 6:47:21 0 [Note] InnoDB: Completed resizing buffer pool from 134217728 to 12582912 bytes.
2024-10-21 6:47:56 4 [Warning] InnoDB: Over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. innodb_buffer_pool_size=11M. Starting the InnoDB Monitor to print diagnostics.

=====================================
2024-10-21 06:47:56 0x14a5966006c0 INNODB MONITOR OUTPUT
=====================================

Not reproducible in MTR.

Roel Van de Paar added a comment - 2024-10-20 20:13 Issue reproducible on current 10.6, 10.11, 11.7 optimized builds with this CLI testcase: # mysqld options required for replay: --innodb_buffer_pool_chunk_size=2097152 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,t1 t7; CS 10.6.20 cd97caef84a842cf388866cfc0a0ec32b86a9c13 (Optimized) 10.6.20-opt>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7; ERROR 1206 (HY000): The total number of locks exceeds the lock table size CS 10.11.10 8a6a4c947a0ca3d2fdca752d7440bdc5c6c83e37 (Optimized) 10.11.10-opt>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7; ERROR 1206 (HY000): The total number of locks exceeds the lock table size CS 11.7.0 4016c905cbabea7f29ed282dc2125254c7c0d419 (Optimized) 11.7.0-opt>INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7; ERROR 1206 (HY000): The total number of locks exceeds the lock table size All versions produce output similar to the following in the error log: CS 11.7.0 4016c905cbabea7f29ed282dc2125254c7c0d419 (Optimized) 2024-10-21 6:47:04 0 [Note] /test/MD141024-mariadb-11.7.0-linux-x86_64-opt/bin/mariadbd: ready for connections. Version: '11.7.0-MariaDB' socket: '/test/MD141024-mariadb-11.7.0-linux-x86_64-opt/socket.sock' port: 11392 MariaDB Server 2024-10-21 6:47:21 0 [Note] InnoDB: Resizing buffer pool from 128.000MiB to 12.000MiB (unit = 2.000MiB). 2024-10-21 6:47:21 0 [Note] InnoDB: Disabling adaptive hash index. 2024-10-21 6:47:21 0 [Note] InnoDB: Withdrawing blocks to be shrunken. 2024-10-21 6:47:21 0 [Note] InnoDB: Start to withdraw the last 7308 blocks. 2024-10-21 6:47:21 0 [Note] InnoDB: Withdrawing blocks. (7308/7308). 2024-10-21 6:47:21 0 [Note] InnoDB: Withdrew 7308 blocks from free list. Tried to relocate 0 blocks (7308/7308). 2024-10-21 6:47:21 0 [Note] InnoDB: Withdrawn target: 7308 blocks. 2024-10-21 6:47:21 0 [Note] InnoDB: Latching entire buffer pool. 2024-10-21 6:47:21 0 [Note] InnoDB: Resizing buffer pool from 64 chunks to 6 chunks. 2024-10-21 6:47:21 0 [Note] InnoDB: 58 Chunks (7308 blocks) were freed. 2024-10-21 6:47:21 0 [Note] InnoDB: Resizing other hash tables. 2024-10-21 6:47:21 0 [Note] InnoDB: Resized hash tables: lock_sys, adaptive hash index, and dictionary. 2024-10-21 6:47:21 0 [Note] InnoDB: Completed resizing buffer pool from 134217728 to 12582912 bytes. 2024-10-21 6:47:56 4 [Warning] InnoDB: Over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. innodb_buffer_pool_size=11M. Starting the InnoDB Monitor to print diagnostics. ===================================== 2024-10-21 06:47:56 0x14a5966006c0 INNODB MONITOR OUTPUT ===================================== Not reproducible in MTR.

Roel Van de Paar added a comment - 2024-10-20 21:02 - edited

Using the testcase from the last comment, the issue is reproducible from 10.5 to 11.7 (current release versions tested), as well as in MySQL 5.7, 8.0 and 9.1. For MySQL 5.5 and 5.6 it is also reproducible (without using the there-unsupported --innodb_buffer_pool_chunk_size) by setting --innodb_buffer_pool_size=12582912 at server startup (not dynamic).

Current summary

Marko mentioned earlier "I think that what is needed to address this bug report is ... (removed already fixed item) ... and that the error message is refined to mention innodb_buffer_pool_size, because the explicit record locks will be allocated from the InnoDB buffer pool. It is a reasonable design, only the enforcement of the maximum allocation size is sloppy and the error message is not helpful for a user." which looks to be the best way forward for this ticket.

(And the excessive locking issue remains as MDEV-24813)

Roel Van de Paar added a comment - 2024-10-20 21:02 - edited Using the testcase from the last comment, the issue is reproducible from 10.5 to 11.7 (current release versions tested), as well as in MySQL 5.7, 8.0 and 9.1. For MySQL 5.5 and 5.6 it is also reproducible (without using the there-unsupported --innodb_buffer_pool_chunk_size ) by setting --innodb_buffer_pool_size=12582912 at server startup (not dynamic). Current summary Marko mentioned earlier "I think that what is needed to address this bug report is ... (removed already fixed item) ... and that the error message is refined to mention innodb_buffer_pool_size, because the explicit record locks will be allocated from the InnoDB buffer pool. It is a reasonable design, only the enforcement of the maximum allocation size is sloppy and the error message is not helpful for a user." which looks to be the best way forward for this ticket. (And the excessive locking issue remains as MDEV-24813 )

MariaDB Server

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration