[Warning] InnoDB: A long semaphore wait

 

mysql版本: 5.7.29

 

大量的日志
2021-08-13T00:59:23.099878Z 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139974037272320 has waited at srv0srv.cc line 1989 for 614 seconds the semaphore:
X-lock on RW-latch at 0x396b1648 created in file dict0dict.cc line 1191
a writer (thread id 139973910796032) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0purge.cc line 882
Last time write locked in file /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/row/row0mysql.cc line 4313
2021-08-13T00:59:23.099912Z 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139972907202304 has waited at buf0flu.cc line 1217 for 334 seconds the semaphore:
SX-lock on RW-latch at 0x7f56938befc0 created in file buf0buf.cc line 1468
a writer (thread id 139973910796032) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.cc line 1343
Last time write locked in file /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/row/row0upd.cc line 2875
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

解决办法：

原来 Linux内核信号量默认设置太小，造成大量等待，
默认
# cat /proc/sys/kernel/sem
250 32000 32 128
说明：
第一列，表示每个信号集中的最大信号量数目。
第二列，表示系统范围内的最大信号量总数目。
第三列，表示每个信号发生时的最大系统操作数目。
第四列，表示系统范围内的最大信号集总数目。

将第三列调大一点，参考网上的数据
echo "kernel.sem=250 32000 100 128">>/etc/sysctl.conf
然后sysctl -p
重启 MySQL

错误不再出现，问题即可解决