作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
服务器详细信息
背景信息
有一个删除进程,每秒大约执行 3 次删除。每秒 2-3k QPS。主要是 SELECT。没有长时间运行的查询。
问题
有时,查询会在 1-3 秒范围内开始运行,而不是毫秒。其中大多数处于“等待查询缓存锁”。
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count -2033262178
--Thread 6060 has waited at row0purge.cc line 754 for 1.00 seconds the semaphore:
S-lock on RW-latch at 00007FF62B1C9F60 created in file dict0dict.cc line 983
a writer (thread id 9580) 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 754
Last time write locked in file ..\..\..\mysqlcom-pro-5.6.22\storage\innobase\row\row0mysql.cc line 3838
--Thread 5204 has waited at row0purge.cc line 754 for 1.00 seconds the semaphore:
S-lock on RW-latch at 00007FF62B1C9F60 created in file dict0dict.cc line 983
a writer (thread id 9580) 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 754
Last time write locked in file ..\..\..\mysqlcom-pro-5.6.22\storage\innobase\row\row0mysql.cc line 3838
--Thread 5556 has waited at row0purge.cc line 754 for 1.00 seconds the semaphore:
S-lock on RW-latch at 00007FF62B1C9F60 created in file dict0dict.cc line 983
a writer (thread id 9580) 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 754
Last time write locked in file ..\..\..\mysqlcom-pro-5.6.22\storage\innobase\row\row0mysql.cc line 3838
--Thread 6112 has waited at row0purge.cc line 754 for 1.00 seconds the semaphore:
S-lock on RW-latch at 00007FF62B1C9F60 created in file dict0dict.cc line 983
a writer (thread id 9580) 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 754
Last time write locked in file ..\..\..\mysqlcom-pro-5.6.22\storage\innobase\row\row0mysql.cc line 3838
--Thread 5868 has waited at dict0dict.cc line 1037 for 1.00 seconds the semaphore:
Mutex at 000000279A10EDA8 created file dict0dict.cc line 974, lock var 1
waiters flag 1
--Thread 5236 has waited at buf0flu.cc line 2077 for 0.00 seconds the semaphore:
Mutex at 00000000345B7F68 created file buf0buf.cc line 1270, lock var 1
waiters flag 1
OS WAIT ARRAY INFO: signal count -1213535755
Mutex spin waits 8166969384, rounds 34032056620, OS waits 750640991
RW-shared spins 3891056500, rounds 50813966471, OS waits 764118585
RW-excl spins 9963649634, rounds 77579501565, OS waits 718551543
Spin rounds per wait: -80.46 mutex, -125.80 RW-shared, 56.47 RW-excl
问题
我想知道可能是什么原因。我知道它是否一致,因为删除过程正在进行,但它非常零星。与 dict0dict.cc 和 row0purge.cc 相关的信号量让我假设它与导致争用的 DELETES 相关。对此的任何想法都会很棒。
最佳答案
查询缓存锁定意味着查询缓存存在争用,看来删除 SQL 会使查询缓存失效。通常我会关闭查询缓存以避免此类问题。请尝试禁用查询缓存。
关于MySQL 5.6 信号量争用 row0purge.cc dict0dict.cc,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/35928648/
我是一名优秀的程序员,十分优秀!