前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL数据库会莫名其妙地重新启动(adaptive hash index)

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

作者头像
用户8196625
发布2024-02-26 15:56:32
960
发布2024-02-26 15:56:32
举报
文章被收录于专栏:oracleaceoracleace

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

01

MySQL数据库崩溃

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

代码语言:javascript
复制
--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的锁:

代码语言:javascript
复制
Last time read locked in file btr0sea.cc line 1037
代码语言:javascript
复制
使用下面的命令把自适应哈希索引封住:
代码语言:javascript
复制
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,发明两项计算机专利
本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2024-02-15,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 oracleace 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档