MySQL Bugs: #114238: MYSQL Salve Semaphore wait has lasted

Bug #114238	MYSQL Salve Semaphore wait has lasted > 600 seconds
Submitted:	6 Mar 2024 6:58	Modified:	7 Mar 2024 11:45
Reporter:	leo luo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	CentOS
Assigned to:		CPU Architecture:	x86
Tags:	semaphore wait has lasted > 600 seconds

Description:
mysql slave crash。
here's the  error let me know if you need to see more of the log or more variables in my config file:

============================
InnoDB: ###### Diagnostic info printed to the standard error stream
2024-03-01T21:00:20.336208+08:00 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2024-03-01T21:00:20.336333+08:00 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:557 thread 140375764346624
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
13:00:20 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x46000
/data/zcloud/db/qxba2/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x20e7bdd]
/data/zcloud/db/qxba2/mysql/bin/mysqld(handle_fatal_signal+0x30b) [0xf384fb]
/lib64/libpthread.so.0(+0xf630) [0x7fb171dc3630]
/lib64/libc.so.6(gsignal+0x37) [0x7fb170281387]
/lib64/libc.so.6(abort+0x148) [0x7fb170282a78]
/data/zcloud/db/qxba2/mysql/bin/mysqld() [0xc76c10]
/data/zcloud/db/qxba2/mysql/bin/mysqld() [0x2381bf9]
/data/zcloud/db/qxba2/mysql/bin/mysqld(srv_error_monitor_thread()+0x8f2) [0x232cc92]
/data/zcloud/db/qxba2/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable, void (*)()> > >::_M_run()+0xb9) [0x2266a09]
/data/zcloud/db/qxba2/mysql/bin/mysqld() [0x282daa0]
/lib64/libpthread.so.0(+0x7ea5) [0x7fb171dbbea5]
/lib64/libc.so.6(clone+0x6d) [0x7fb170349b0d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains.

But i notice that there are only 7 free buffers in the innodb bufferr pool.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 21926379520
Dictionary memory allocated 8665546
Buffer pool size   1310602
Free buffers       7
Database pages     1257468
Old database pages 464019
Modified db pages  135051
Pending reads      0
Pending writes: LRU 0, flush list 5, single page 0
Pages made young 1116753851, not young 17524584325
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4466038130, created 106982577, written 6122814410
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 72 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1257468, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

The free buffers dropped from 8192 to 7 within a few minutes.

How to repeat:
i don't kown.

Hi Mr. luo,

Thank you for your bug report.

Your problem is not related to the replication.

Your problem is strictly related to the InnoDB storage engine.

The error that you get "semaphore wait has lasted > 600 seconds", indicates that a problem is not in InnoDB storage engine.

The problem is in your application or in the OS or in the hardware.

That means that, for example, your transaction does not have a series of DML statements that are not waiting on anything else, but are just executing immediately one after another. That means not waiting on user's input, network traffic or latency problems (like between master and slave), some third process, hardware and / or software  overloaded or anything third other then transaction itself.

This is not a bug.

Hi,Master and slave have the same hardware and Mysql configuration. We did not find any problem about the hardware.

Thank you for your reply.And cloud you explain why the free buffers dropped from 8192 to 7 within a few minutes.

Hi,

99 % of the hardware problems are not due to some permanent error in the hardware. 99 % of the errors come from the transient errors that happen in millisecond and then they disappear. Hence, you can not diagnose those problems at all. But, this is a problem  with all commodity hardware. That is why we recommend the usage of ECC parity-checking RAM modules and RAID hard disks.

Regarding the change of free buffers, that is expected behaviour. Number of connections raises and falls ... each SQL statement dynamically allocates and release memory. Hence, MySQL server memory is never constant .......

I got it, thank you.

You are truly welcome !!!