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!