Bug #119877 InnoDB Buffer Pool Flush State Mutex Contention (8.0.42-debug)

• Submitted: 10 Feb 12:42
• Reporter: Jason Tang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 8.0.42+
• OS: Any
• CPU Architecture: Any

[10 Feb 12:42] Jason Tang

Description:
InnoDB hangs for 241 seconds waiting on BUF_POOL_FLUSH_STATE mutex during query execution, causing significant performance degradation. This occurs even with minimal workload and appears to be a mutex contention bug in the buffer pool flushing mechanism.

--Thread 140709280007744 has waited at buf0flu.cc line 2000 for 241 seconds the semaphore:
Mutex at 0x7ff9802e98a8, Mutex BUF_POOL_FLUSH_STATE created buf0buf.cc:1290, locked by 140708094670400
Last time reserved in file /home/tzx/mysql-server-mysql-8.0.42/storage/innobase/buf/buf0buf.cc line 2408
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

How to repeat:
-- Database version: 8.0.42-debug
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
 CREATE TABLE `t0` (
  `c0` decimal(10,0) DEFAULT NULL,
  UNIQUE KEY `i0` (`c0`),
  UNIQUE KEY `i1` (`c0`) USING BTREE,
  UNIQUE KEY `i2` (`c0`),
  KEY `i3` ((-((((0 <> 154256429) is not true) = (_utf8mb4'' = `c0`)))))
) ;
SET GLOBAL innodb_buffer_pool_size = 555124651587611025;

SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (((NULL) IS UNKNOWN) BETWEEN (GREATEST(0.5251766929090004, t0.c0, 0.3416168621393849, 0.03192936666242252)) AND ((t0.c0) BETWEEN (845515916) AND (NULL))) LIKE (((t0.c0) IS FALSE) < (('_2B') IS NOT FALSE));