MySQL现象

1.因为发生时间在凌晨基本业务流量
2.Show processlist的结果显示大量的线程状态为 Opening tables

错误日志

--Thread 139667773060864 has waited at trx0sys.ic line 80 for 241 seconds the semaphore:
X-lock on RW-latch at 0x7f085bcf7800 created in file buf0buf.cc line 778
a writer (thread id 139668311451392) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file trx0undo.ic line 143
Last time write locked in file /export/home/pb2/build/sb_0-38523192-1585233826.98/rpm/BUILD/mysql-8.0.20/mysql-8.0.20/storage/innobase/
buf/buf0flu.cc line 1330
2021-11-02T00:35:35.452008+08:00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait:
--Thread 139667707242240 has waited at row0upd.cc line 3038 for 241 seconds the semaphore:
X-lock on RW-latch at 0x7f0727730810 created in file buf0buf.cc line 778
a writer (thread id 139668311451392) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file row0sel.cc line 3458
Last time write locked in file /export/home/pb2/build/sb_0-38523192-1585233826.98/rpm/BUILD/mysql-8.0.20/mysql-8.0.20/storage/innobase/
buf/buf0flu.cc line 1330
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

=====================================
2021-11-02 00:35:43 0x7f07075a3700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12864293 srv_active, 0 srv_shutdown, 2072908 srv_idle
srv_master_thread log flush and writes: 0
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5018111
--Thread 139667707442944 has waited at trx0trx.cc line 531 for 111 seconds the semaphore:
Mutex at 0x7f086c3521b0, Mutex TRX_SYS created trx0sys.cc:508, lock var 1

--Thread 139667708245760 has waited at trx0trx.cc line 531 for 134 seconds the semaphore:
Mutex at 0x7f086c3521b0, Mutex TRX_SYS created trx0sys.cc:508, lock var 1

--Thread 139667712395008 has waited at trx0trx.cc line 531 for 132 seconds the semaphore:
Mutex at 0x7f086c3521b0, Mutex TRX_SYS created trx0sys.cc:508, lock var 1

--Thread 139667706238720 has waited at trx0trx.cc line 531 for 102 seconds the semaphore:
Mutex at 0x7f086c3521b0, Mutex TRX_SYS created trx0sys.cc:508, lock var 1

--Thread 139666166765312 has waited at trx0trx.cc line 531 for 91 seconds the semaphore:
Mutex at 0x7f086c3521b0, Mutex TRX_SYS created trx0sys.cc:508, lock var 1

--Thread 139666163418880 has waited at trx0trx.cc line 531 for 90 seconds the semaphore:
Mutex at 0x7f086c3521b0, Mutex TRX_SYS created trx0sys.cc:508, lock var 1

故障恢复

当时因为mysql已经无法响应任何读写操作，且业务流量也比较小就直接选择了重启后恢复

原因分析

解决方案

1.系统层面
[root@vm10-218-23-165 ~]# cat /proc/sys/kernel/sem
250 32000 32 128
[root@vm10-218-23-165 ~]# echo "kernel.sem=250 32000 100 128" >> /etc/sysctl.conf
[root@vm10-218-23-165 ~]# sysctl -p
2.MySQL层面
关闭自适应hash索引
set global innodb_adaptive_hash_index=off;