MySQL数据库会莫名其妙地重新启动（adaptive hash index）

一次排除因为自适应哈希索引（adaptive hash index）造成的MySQL数据库崩溃的经历，并探讨MySQL在何种情况下应该激活自适应哈希索引。

01

—

MySQL数据库崩溃

一个客户的MySQL数据库隔一段时间就会莫名其妙地重新启动，在错误日志对应的时间点里下面的记录：

--Thread 140508672222976 has waited at row0purge.cc line 113 for 241 seconds the semaphore:
X-lock on RW-latch at 0x7fcac68e7b80 created in file buf0buf.cc line 1474
a writer (thread id 140508724832000) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0sea.cc line 1037
Last time write locked in file build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/buf/buf0flu.cc line 1213
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0


=====================================
2024-01-31 18:09:35 0x7fcaba7f0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 118905 srv_active, 0 srv_shutdown, 878621 srv_idle
srv_master_thread log flush and writes: 997526
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1346616
--Thread 140502119106304 has waited at row0upd.cc line 2882 for 236 seconds the semaphore:
X-lock on RW-latch at 0x7fcac6937708 created in file buf0buf.cc line 1474
a writer (thread id 140508724832000) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0sea.cc line 1037
Last time write locked in file build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/buf/buf0flu.cc line 1213
--Thread 140508680615680 has waited at log0log.cc line 1746 for 198 seconds the semaphore:
S-lock on RW-latch at 0x579b7e0 created in file log0log.cc line 846
a writer (thread id 140508680615680) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file log0log.cc line 1746
Last time write locked in file build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/log/log0log.cc line 1638
--Thread 140508672222976 has waited at row0purge.cc line 113 for 242 seconds the semaphore:
X-lock on RW-latch at 0x7fcac68e7b80 created in file buf0buf.cc line 1474
a writer (thread id 140508724832000) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0sea.cc line 1037
Last time write locked in file build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/buf/buf0flu.cc line 1213
OS WAIT ARRAY INFO: signal count 1471450
RW-shared spins 0, rounds 971756, OS waits 214261
RW-excl spins 0, rounds 7944761, OS waits 111771
RW-sx spins 68624, rounds 1162189, OS waits 17279
Spin rounds per wait: 971756.00 RW-shared, 7944761.00 RW-excl, 16.94 RW-sx
------------
TRANSACTIONS
------------

从出错信息看，是锁竞争造成线程等待引起的，查了一下资料，在官方文档的关于自适应哈希索引（https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html）中有下面的信息：

You can monitor adaptive hash index use and contention in the SEMAPHORES
section of SHOW ENGINE INNODB STATUS
output. If there are numerous threads waiting on rw-latches created in btr0sea.c
, consider increasing the number of adaptive hash index partitions or disabling the adaptive hash index.

MySQL的错误日志中的确有很多关于btr0sea.cc的锁：

Last time read locked in file btr0sea.cc line 1037

使用下面的命令把自适应哈希索引封住：

mysql> set global innodb_adaptive_hash_index=0;

然后故障排除。注意还要修改MySQL的配置文件my.cnf，使下次启动后修改继续生效。

02

—

适应哈希索引的适用场景

在分析了自适应哈希索引的原理后，姚远提醒您在使用这个特性时需要注意下面的问题：

• 只适用查询语句，并且判断条件是等于，不适用于like和between等非等于的操作符。
• 对于修改语句因为要维护自适应哈希索引的架构，反而增加了操作的成本。

自适应哈希索引默认是激活的，因为MySQL最初设计的场景是针对互联网的应用，互联网的应用的特点就是主要是查询操作，很少有修改。因为如果您的应用不是此类应用建议您关闭这个特性，方法是在启动时增加--skip-innodb-adaptive-hash-index这个参数。关于号主，姚远：

• Oracle ACE（Oracle和MySQL数据库方向）

• 华为云最有价值专家

• 《MySQL 8.0运维与优化》的作者

• 拥有 Oracle 10g、12c和19c OCM等数十项数据库认证

• 曾任IBM公司数据库部门经理

• 20+年DBA经验，服务2万+客户

• 精通C和Java，发明两项计算机专利

欢迎关注我的公众号，一起学习数据库技术👇

推荐文章👇

从国内外IT人的差异谈如何破除35岁魔咒

试看号主的拙作《MySQL 8.0运维与优化》（清华大学出版社）

托业890分的Oracle ACE为您翻译国际大佬的雄文（合集）

晒一下号主的19个Oracle认证（OCP+OCM），欢迎PK