how to measure query cache by queries ?

  • Filter
  • Time
  • Show
Clear All
new posts

  • how to measure query cache by queries ?

    Recently I build and install special plugin from this source http://rpbouman.blogspot.com/2008/07/inspect-query-cahce-usi ng-mysql.html#links

    This seems to work fine (need some trivial code hacking for my 5.1.37 version, can share if anybody need it).

    desc MYSQL_CACHED_QUERIES;+-------------------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------------------+-------------+------+-----+---------+-------+| STATEMENT_ID | int(21) | NO | | 0 | || SCHEMA_NAME | varchar(64) | NO | | | || STATEMENT_TEXT | longtext | NO | | NULL | || RESULT_BLOCKS_COUNT | int(21) | NO | | 0 | || RESULT_BLOCKS_SIZE | bigint(21) | NO | | 0 | || RESULT_BLOCKS_SIZE_USED | bigint(21) | NO | | 0 | |+-------------------------+-------------+------+-----+---------+-------+

    but if mysql query cache really use LRU http://en.wikipedia.org/wiki/Cache_algorithms#Least_Recently _Used algorithm (there are many sources in internet with this opinion), there must be hits counter or age value or linked list order must be moving frequently .
    But it doesn't : you can select without order and see stable orded. you can produce query hit many times, but nothing changes.

    I think mysql use "Least Recently Inserted" algorithm. Am I right?

    So I have questions:

    Does mysql really use LRU strategy for query cache ?
    Is existing realization good enough for general use ?
    Any other realization of query cache available in public ?

  • #2
    I think you're right, but I don't remember the code very well. However, the code has a novel's worth of comments at the top of the file. It should be easy for you to inspect and see. Post back whatever you learn!


    • #3
      ok, i move forward a bit :
      1. yes, LRU really used by mysql query cache. every block move at top of linked list when cache hit. But this plugin cycle other data structure - query hash. So, for analyze hits I need wrote another plugin code. This is not good.

      2. Many people think than query cache is a performance break. They even make joke site ( http://mituzas.lt/2009/07/08/query-cache-tuning/).
      Why ? My mysql installations are not concurrent enough and I couldn't confirm this "joke". if I turn of cache, this cause increasing small amount of cpu time.

      3. still no other implementation of cache..


      • #4
        On servers with a lot of CPU cores and a highly concurrent load, the query cache mutex becomes a global lock that serializes all queries on the server at various points, and doesn't permit any concurrency. This causes performance to become unstable. The whole server can lock up for periods of time. I see this problem a lot.

        If the query cache is working for you, it probably means it's speeding your queries up enough so that they don't have to run concurrently. When they get longer (more load, more data, ...) they will start to overlap, and performance will get unstable.


        • #5
          I read many articles about this. Is this STILL true ?
          It seems there are many per-query micro locks in code - 5.1.37, not global lock.


          • #6
            It is STILL true. There has been no fundamental change in the query cache. Those who need really high performance don't care about it anyway because they are building much more intelligent caches outside of the database, so all they care about is being able to disable it.