服务器详细信息
MySQL 5.6.22
256GB RAM
4TB固态硬盘
32核心
背景资料
有一个删除过程，每秒大约执行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
题
我想知道可能是什么原因。我想知道它是否一致，因为DELETE进程正在进行中，但是它是零星的。与dict0dict.cc和row0purge.cc相关的信号量使我认为它与引起争用的DELETES有关。任何对此的想法将是巨大的。
最佳答案
查询缓存锁定意味着存在与查询缓存的争用，似乎delete SQL使查询缓存无效。通常，我会关闭查询缓存以避免此类问题。请尝试禁用查询缓存。