mysql - SELECT + INSERT + 查询缓存 = MySQL 锁定

Question

MySQL 服务器似乎不断锁定并停止响应某些类型的查询，最终(几分钟未响应后)放弃并出现错误“MySQL 服务器已消失”，然后再次卡在下一组查询上，一次又一次。服务器设置为从属服务器，用于从主服务器复制到 dbA，主要是 INSERT 语句，每秒大约 5-10 行。基于 PHP 的应用程序在服务器上运行，每 5-10 秒读取一次新复制的数据，对其进行处理并将结果存储(插入重复 key 更新)到单独的数据库 dbB 中。所有表都使用MyISAM引擎。 Web 应用程序向用户显示经过后处理的数据。基本而言，所涉及的处理步骤是将每秒分辨率的时间序列数据压缩为每分钟、小时和天的分辨率。

当 MySQL 锁定时，我执行 SHOW PROCESSLIST 命令并看到以下查询:

N  User          Time   Status                         SQL query
1  system user   XX     update                         INSERT INTO `dbA`.`tableA` (...) VALUES (...)
2  ????          XX     Waiting for query cache lock   INSERT INTO `dbB`.`tableB` (...) VALUES (...) ON DUPLICATE KEY UPDATE ...
3  ????          XX     Writing to net                 SELECT ... FROM `dbA`.`tableA` WHERE ... ORDER BY ...

“时间”列将继续同步计时，直到达到某种查询等待超时，然后我们收到错误“MySQL 服务器已消失”。 5-10 秒后，当再次处理新数据时，将会发生相同的锁定。查询#1 是复制过程。查询#2 是更新后处理数据。查询 #3 正在流式传输(无缓冲)新复制的数据以进行处理。查询 #3 最终产生错误“MySQL 服务器已消失”，大概是因为它是第一个超时的。

看起来像是某种死锁，但我不明白为什么。在一个数据库中同时进行 SELECT 和 INSERT 似乎会导致另一数据库中的 INSERT ON DUPLICATE KEY UPDATE 查询缓存更新出现死锁。如果我关闭复制或查询缓存，则不会发生锁定。平台:Debian 7、MySQL 5.5.31、PHP 5.4.4 - 所有标准软件包。值得注意的是，几乎相同的应用程序目前在 Debian 6、MySQL 5.1.66、PHP 5.3.3 上运行良好，唯一的区别在于后处理的数据是使用单独的 INSERT 存储的和 UPDATE 查询而不是 INSERT ON DUPLICATE KEY UPDATE。

MySQL 配置(在 Debian 6 和 7 机器上):

key_buffer_size         = 2G
max_allowed_packet      = 16M
thread_cache_size       = 64
max_connections         = 200
query_cache_limit       = 2M
query_cache_size        = 1G

任何关于为什么会发生这种锁定的提示都将不胜感激!

Answer 1

尝试显着减小查询缓存大小。 1G可能太大了。

从 16M 或 32M 开始，相应地调整 query_cache_limit(256K？) - 随着读取性能的提高而不断提高，而不会在写入时达到“等待查询缓存锁定”。

“对查询缓存的大小设置过大要小心，这会增加维护缓存所需的开销，可能超出启用缓存的好处。数十兆字节的大小通常是有益的。数百兆字节的大小可能不会。” http://dev.mysql.com/doc/refman/5.6/en/query-cache.html