I use Percona Server, with InnoDB to run several database servers, some of which have dozens of databases in them that all run separate instances of our product; a web app running on LAMP.
My question is: because each query that allows query cache (in other words all of those that don’t have SQL_NO_CACHE in the query) has to acquire a cache lock to write itself into… does this effectively create a single point of lockup for a MySQL Server, across all databases?
I had a situation today where 300 queries were all waiting on one to finish up with the query cache… they said " Waiting for query cache lock"; I killed the one query that was “updating” because I knew it was safe, and then all of the other queries completed.
Other than carefully choosing those queries that don’t benefit from cache and adding SQL_NO_CACHE to them, is there anything I can do to eliminate this single point of lockup? Or, is there something about the query cache that I’m missing?