Announcement

Announcement Module
Collapse
No announcement yet.

Does Query Cache present a single point of lockup for a MySQL Server?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Does Query Cache present a single point of lockup for a MySQL Server?

    Greetings all,

    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?

  • #2
    Query cache acquires global mutex to guarantee the consistency of the contents in cache and it can generate mutex contention on high concurrency which can generate performance problem. Disable query cache as solution. As per my experience, query cache is not very useful on most of the workloads unless you have very few updates on your database server.
    What version of PS you are using ? There are also some bugs related to query cache verified.

    Comment


    • #3
      Thanks for the response Mirfan!

      We are using : Server version: 5.5.30-30.2 Percona Server (GPL), Release 30.2

      We had previously did some testing to see how useful the cache was, and I think the utilization numbers were high.

      Do you know what variables/status queries I should run to find out how used the query cache really is?

      Comment


      • #4
        Here you will find all answers on this:
        http://www.mysqlperformanceblog.com/...l-query-cache/

        Comment

        Working...
        X