SELECT + INSERT +查询缓存= MySQL锁定 [英] SELECT + INSERT + Query Cache = MySQL lock up

问题描述

MySQL服务器似乎不断锁定并停止对某些类型的查询作出响应，最终(在几分钟之内没有响应之后)放弃了错误" MySQL服务器已消失"，然后一次又一次地挂在下一组查询上.服务器被设置为从服务器，以从主机复制到 dbA ，大多数情况下是INSERT语句，大约每秒5-10行.服务器上运行的是基于PHP的应用程序，该应用程序每5-10秒读取一次新复制的数据，对其进行处理并将结果(在重复键更新中插入)存储在单独的数据库 dbB 中.所有表都使用MyISAM引擎.Web应用程序为用户显示后处理的数据.从根本上讲，涉及的处理步骤是将每秒分辨率的时间序列数据压缩为分钟，小时和天的分辨率.

MySQL server seems to constantly lock up and stop responding on certain types of queries and eventually (after couple of minutes of not responding) give up with an error "MySQL server has gone away", then hang again on the next set of queries, again and again. The server is set up as a slave to replicate from a master to dbA, mostly INSERT statements, around 5-10 rows per second. A PHP based application is running on the server that reads the freshly replicated data every 5-10 seconds, processes it and stores (INSERT ON DUPLICATE KEY UPDATE) results in a separate database dbB. All tables use MyISAM engine. A web application displays the post-processed data for the user. In basic terms the processing steps involved are compression of time series data in per second resolution into per minute, hour and day resolutions.

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

When MySQL locks up, I execute SHOW PROCESSLIST command and I see the following queries:

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正在流式传输(未缓冲)新复制的数据以进行处理.最终导致错误" MySQL服务器已消失"的是查询#3，大概是因为它是第一个超时的错误.

The "Time" column will keep ticking away synchronously until some sort of query wait timeout has been reached and then we get error "MySQL server has gone away". In 5-10 seconds when it will be time to process new data again the same lock up will happen. Query #1 is the replication process. Query #2 is the updating of the post-processed data. Query #3 is streaming (unbuffered) the newly replicated data for processing. It is the Query #3 that eventually produces the error "MySQL server has gone away", presumably because it is the first one to timeout.

这看起来有点死锁，但我不明白为什么.在一个数据库中同时进行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查询，而不是在重复键更新中插入.

It looks like some sort of dead lock, but I cannot understand why. Simultaneous SELECT and INSERT in one database seems to cause a dead lock with query cache update by INSERT ON DUPLICATE KEY UPDATE in a different database. If I turn off either the Replication or the Query Cache then the lock up does not happen. Platform: Debian 7, MySQL 5.5.31, PHP 5.4.4 - all standard packages. It may be worth noting that almost the same application is currently working fine on Debian 6, MySQL 5.1.66, PHP 5.3.3, with only difference in that the post-processed data is stored using separate INSERT and UPDATE queries rather than INSERT ON DUPLICATE KEY UPDATE.

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

MySQL configuration (on both the Debian 6 and 7 machines):

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

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

Any hints to why this lock up occurs will be much appreciated!

推荐答案

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

从16M或32M开始，并相应地调整query_cache_limit(256K?)-随着读取性能的提高而向上移动，而不会在写入时达到等待查询缓存锁定".

Try to reduce the query cache size significantly. 1G is probably too big.

Start with 16M or 32M and adjust the query_cache_limit accordingly (256K?) - and move your way up as the read performance increases without reaching "Waiting for query cache lock" on writes.

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

"Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be." http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

这篇关于SELECT + INSERT +查询缓存= MySQL锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！