线上一台mysql 5.7.17版本实例,系统centos 6.4 ,96G内存,SSD磁盘,buffer pool 60G,buffer pool instance 8,24 CPU。实例访问情况如下:
不定期的夯住重启,每次状态都是一致,error log如下输出:
nnoDB: ###### Diagnostic info printed to the standard error stream
2017-05-01T19:21:24.095658+08:00 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139927055582976 has waited at buf0flu.cc line 1209 for 303.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f448b83aa70 created in file buf0buf.cc line 1459
a writer (thread id 139928763602688) 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 3652
Last time write locked in file /export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/storage/innobase/btr/btr0cur.cc line 299
2017-05-01T19:21:24.095746+08:00 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139927087052544 has waited at buf0flu.cc line 1209 for 303.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f463e8eeb08 created in file buf0buf.cc line 1459
a writer (thread id 139928763602688) 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 3652
Last time write locked in file /export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/storage/innobase/btr/btr0cur.cc line 315
2017-05-01T19:21:24.095798+08:00 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139921740433152 has waited at row0ins.cc line 2481 for 303.00 seconds the semaphore:
S-lock on RW-latch at 0x7f49d5fc2be8 created in file buf0buf.cc line 1459
a writer (thread id 139928763602688) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0ins.cc line 2481
Last time write locked in file /export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/storage/innobase/include/mtr0mtr.ic line 153
2017-05-01T19:21:24.095871+08:00 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139921726056192 has waited at row0ins.cc line 2481 for 303.00 seconds the semaphore:
S-lock on RW-latch at 0x7f49d5fc2be8 created in file buf0buf.cc line 1459
a writer (thread id 139928763602688) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0ins.cc line 2481
Last time write locked in file /export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/storage/innobase/include/mtr0mtr.ic line 153
2017-05-01T19:21:24.095917+08:00 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139921718867712 has waited at row0ins.cc line 2481 for 303.00 seconds the semaphore:
S-lock on RW-latch at 0x7f49d5fc2be8 created in file buf0buf.cc line 1459
a writer (thread id 139928763602688) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0ins.cc line 2481
Last time write locked in file /export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/storage/innobase/include/mtr0mtr.ic line 153
2017-05-01T19:21:24.095959+08:00 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139927097542400 has waited at buf0flu.cc line 1209 for 303.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f49d5fc2be8 created in file buf0buf.cc line 1459
a writer (thread id 139928763602688) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0ins.cc line 2481
Last time write locked in file /export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/storage/innobase/include/mtr0mtr.ic line 153
从日志看,是长时间的信号等待触发了重启,之前5.7.13版本也出现,升级到5.7.17还是一样。
最开始怀疑是系统信号集设置不够,从250 32000 32 128 调整到2500 3200000 3200 1280,
还是出现了重启。从日志中看都在等待139928763602688 线程,从日志的下文可以得到该线程只是一条简单的insert语句,并没有其他异常查询,而且每次夯住时的语句并不相同。此外该实例之前运行在5.5版本下,没出现过该问题。请问该问题应该如何处理?
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。