0

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.

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 ...

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.

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 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!

dezlov
  • 840
  • 8
  • 20
  • The same question was posted in [DBA](http://dba.stackexchange.com/q/47634/26770) section but it had no responses after a week. Perhaps it is better suited here. – dezlov Aug 12 '13 at 10:05
  • 1
    I'm having the same problem (a bit less severe), the only "solution" I have found so far is to disable query cache... – Vatev Aug 12 '13 at 10:26
  • @Vatev Thanks sharing! I too disabled the query cache in the meantime, as it's the only workaround at the moment. I'm still puzzled by why this is happening... – dezlov Aug 12 '13 at 13:59

1 Answers1

3

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.

"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

Olivier PEPIN
  • 31
  • 3
  • This is not a useful answer. The question is not about efficiency of query cache, but rather a specific dead lock problem associated with the use of cache. You should invest more time in understanding the question rather than using your copy&paste skills with a hope that it helps. – dezlov Nov 28 '14 at 13:33
  • @dezlov the dead lock "Waiting for query cache lock" is usually coming from write contention on the query cache, because it's too big and mysql spend more time to invalidate that cache, and therefore reducing the size of the cache to the lowest performing value is the way to go. – Olivier PEPIN May 27 '15 at 12:22
  • I want to tell you 2 important points about Mysql a) Locks: check [this](https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html) to know if select insert and update cause a lock. b) Query cache: If query cache is too large it leads to performance degradation because of cache overhead and lock. So, larger the query cache, the more system time is used for locks, flushes, and overhead until cache management and this negates any benefit of query cache and diminishes database throughput. And if you’re having random lockups, try disabling the query cache, and see if that solves it. –  Jul 28 '17 at 12:33