Databases
StackOverflow2013
Postsdbo
POSELECT + INSERT + Query Cache = MySQL lock up
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSELECT + INSERT + Query Cache = MySQL lock up
    primarykey
    data
    text
    <p>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 "<em>MySQL server has gone away</em>", 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 <code>dbA</code>, 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 <code>dbB</code>. 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.</p> <p>When MySQL locks up, I execute SHOW PROCESSLIST command and I see the following queries:</p> <pre><code>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 ... </code></pre> <p>The "Time" column will keep ticking away synchronously until some sort of query wait timeout has been reached and then we get error "<em>MySQL server has gone away</em>". 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 "<em>MySQL server has gone away</em>", presumably because it is the first one to timeout.</p> <p>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: <em>Debian 7, MySQL 5.5.31, PHP 5.4.4</em> - all standard packages. It may be worth noting that almost the same application is currently working fine on <em>Debian 6, MySQL 5.1.66, PHP 5.3.3</em>, with only difference in that the post-processed data is stored using separate INSERT and UPDATE queries rather than INSERT ON DUPLICATE KEY UPDATE.</p> <p>MySQL configuration (on both the Debian 6 and 7 machines):</p> <pre><code>key_buffer_size = 2G max_allowed_packet = 16M thread_cache_size = 64 max_connections = 200 query_cache_limit = 2M query_cache_size = 1G </code></pre> <p>Any hints to why this lock up occurs will be much appreciated!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USdezlov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThe 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.
      singulars
      1. POSELECT + INSERT + Query Cache = MySQL lock up
      1. USdezlov
    2. COI'm having the same problem (a bit less severe), the only "solution" I have found so far is to disable query cache...
      singulars
      1. POSELECT + INSERT + Query Cache = MySQL lock up
      1. USVatev
    3. CO@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...
      singulars
      1. POSELECT + INSERT + Query Cache = MySQL lock up
      1. USdezlov
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload