) - 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 the hundreds of megabytes might not be." is not a useful answer.
(2 servers version 5.1, and 2 version 5.5) While checking the slave status, i see the seconds_behind_master at 0, and half a second after i see it jumps to 2000, and so fourth. Easiest demonstrated with a slightly simpler replication topology: server1 - server3 If server2 falls behind, and is processing some long-running queries, the following will happen, assuming as start point: : Everyone ok : server1 writes two 10-minute queries to the binlog, no replication delay anywhere : server2 starts processing query one. : server2 is done with query 2, replication delay zero again.
When creating a test table in server 4, checking the relay log in server 1 shows the create statement was copied to the relay log in server 1 instantly, but the table is not created. Servers 1 2 & 4 were having "invalidating query cache entries (table)" stuck in their replication thread.
XX Waiting for query cache lock INSERT INTO `db B`.`table B` (...) VALUES (...) ON DUPLICATE KEY UPDATE ... 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.
I'm running a 4 servers master-master cluster of My Sql. Replication topology: 1 - 1 UPDATE It seems that server 3 has its SBM at 0, while the other servers are jumping up and down. It looks like the server is busy doing something, and there is a huge delay between when the server gets the statement, and when it executes it. After disabling cache, server 4 is ok but 1&2 are still having this issue. id=60696 If anyone knows how to fix it, i would be glad to hear There is one flaw with mysql's seconds_behind_master value: it only takes into account the position relative to one upstream hop away.
In basic terms the processing steps involved are compression of time series data in per second resolution into per minute, hour and day resolutions. It is the Query #3 that eventually produces the error "", presumably because it is the first one to timeout.
When My SQL 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 `db A`.`table A` (...) VALUES (...) 2 ???? It looks like some sort of dead lock, but I cannot understand why.
, mostly INSERT statements, around 5-10 rows per second. In 5-10 seconds when it will be time to process new data again the same lock up will happen. Query #2 is the updating of the post-processed data.
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 . A web application displays the post-processed data for the user. Query #3 is streaming (unbuffered) the newly replicated data for processing.