MySQL Bugs: #114166: trx_private lock_sql_map item pool reaches upper limit

Bug #114166	trx_private lock_sql_map item pool reaches upper limit
Submitted:	29 Feb 2024 8:15	Modified:	29 Feb 2024 10:56
Reporter:	Zhenyu Jin	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
error.log:

Innodb Memory  usage  trx_private lock_sql_map item pool reaches upper limit

How to repeat:
error.log:

Innodb Memory  usage  trx_private lock_sql_map item pool reaches upper limit

Suggested fix:
error.log:

Innodb Memory  usage  trx_private lock_sql_map item pool reaches upper limit

2024-02-29T15:39:04.582645+08:00 26646109 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(10).
2024-02-29T15:40:02.125072+08:00 27958577 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 37780 usec to assign a trx private lock_sql_map.
2024-02-29T15:40:10.315386+08:00 27958577 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 31490 usec to assign a trx private lock_sql_map.
2024-02-29T15:46:05.599329+08:00 27816726 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(10).
2024-02-29T15:54:38.221290+08:00 27260700 [Warning] [MY-011825] [InnoDB] Temporary tablespace has been truncated because of too large, thread_id: 27260700, space_id: 4243767316, original size: 2709520384 bytes.
2024-02-29T15:56:24.859405+08:00 27961481 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(10).
2024-02-29T16:00:02.297554+08:00 27817497 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 45067 usec to assign a trx private lock_sql_map.
2024-02-29T16:00:02.611124+08:00 27503834 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 38357 usec to assign a trx private lock_sql_map.
2024-02-29T16:00:10.556310+08:00 27503834 [Warning] [MY-011825] [InnoDB] Memory usage of trx_private lock_sql_map item pool reaches upper limit, have you locked or modified too many records? blk_sql in innodb_lock_wait.log may be lost.
2024-02-29T16:00:11.509509+08:00 27817497 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 43280 usec to assign a trx private lock_sql_map.
2024-02-29T16:06:33.015293+08:00 27941351 [Note] [MY-000000] [Server] failed to read or write packet, errno: 32, errmsg: Broken pipe
2024-02-29T16:08:20.118867+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(10).
2024-02-29T16:08:20.119421+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(20).
2024-02-29T16:08:20.119963+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(30).
2024-02-29T16:08:20.120473+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(40).
2024-02-29T16:08:20.121018+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(50).
2024-02-29T16:08:20.121583+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(60).
2024-02-29T16:08:20.122125+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(70).
2024-02-29T16:08:20.122640+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(80).
2024-02-29T16:08:20.123196+08:00 27958577 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(90).
2024-02-29T16:08:43.115430+08:00 27961482 [Note] [MY-000000] [Server] quick_sync_refcount is 2, has wait old thread exit times(10).
2024-02-29T16:19:42.133373+08:00 0 [Note] [MY-060017] [Server] srv_do_purge purged pages: 249203 takes: 14142.179 ms
2024-02-29T16:20:01.483014+08:00 27934251 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 34600 usec to assign a trx private lock_sql_map.
2024-02-29T16:20:02.501949+08:00 27777423 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 39525 usec to assign a trx private lock_sql_map.
2024-02-29T16:20:12.568756+08:00 27777423 [Warning] [MY-011825] [InnoDB] A big transaction is detected by lock_sys, it costs 44086 usec to assign a trx private lock_sql_map.

Hi Mr. Jin,

Thank you for your bug report.

However, first of all, 8.0.25 is a very old release of the version 8.0 and we do not support it any more.

Next, this is a forum for the bug reports with a fully repeatable test cases. A repeatable test case is accepted if it consists of a set of SQL statements that always lead to the results that are reported. Beside SQL statements and only if necessary, you can add your specific configuration settings. You can also use multi-threaded test case, by using our mysqlslap program.

Hence, we do not support 8.0.25 any more and we do not have a test case (as described above) from you, so we can not proceed in analysing what you are reporting.

Can't repeat.