![]() ![]() ![]() Platform: Debian 7, MySQL 5.5.31, PHP 5.4.4 - all standard packages. If I turn off either the Replication or the Query Cache then the lock up does not happen. 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. It looks like some sort of dead lock, but I cannot understand why. It is the Query #3 that eventually produces the error " MySQL server has gone away", presumably because it is the first one to timeout. Query #3 is streaming (unbuffered) the newly replicated data for processing. Query #2 is the updating of the post-processed data. ![]() In 5-10 seconds when it will be time to process new data again the same lock up will happen. 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". When MySQL locks up, I execute SHOW PROCESSLIST command and I see the following queries: N User Time Status SQL queryġ system user XX update INSERT INTO `dbA`.`tableA` (.) VALUES (.)Ģ ? XX Waiting for query cache lock INSERT INTO `dbB`.`tableB` (.) VALUES (.) ON DUPLICATE KEY UPDATE. In basic terms the processing steps involved are compression of time series data in per second resolution into per minute, hour and day resolutions. A web application displays the post-processed data for the user. 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. The server is set up as a slave to replicate from a master to dbA, mostly INSERT statements, around 5-10 rows per second. 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |