1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF usage of MySQL query cache

Discussion in 'Server Configuration and Hosting' started by Chris H., Sep 18, 2011.

  1. Chris H.

    Chris H. Member

    Since our switch to XF from VB, we've seen a lack of MySQL query cache hits. Here is a graph from our monitoring software. Nothing has changed in the server configuration that should have caused such a dramatic drop. What is it about XF vs. VB that's not allowing the MySQL Query Cache to be used?

  2. akia

    akia Well-Known Member

    What monitoring software do you use?
  3. Walter

    Walter Well-Known Member

    The main difference between vb and xf is that most tables in vb by default are myisam tables and most tables in xf are Innodb tables.
    Is your tool able to measure this?
  4. Chris H.

    Chris H. Member


    All of our XF tables are MyISAM. As were our vb tables.
  5. Brogan

    Brogan XenForo Moderator Staff Member

    That's not correct.

    You need to change the tables back to what they should be.

  6. Chris H.

    Chris H. Member

    Thanks for the heads up. We actually didn't change anything. It seems as though our WHM/CPanel server had skip-innodb in my.cnf, so the tables were silently created as myisam. We'll get to work on making the change.

    Is it your expectation that once we're on innodb table engine we'll start seeing query cache hits?
  7. Deebs

    Deebs Well-Known Member

    The table engine in use does not affect the query cache. Tho the recommended thing to do is to disable the query cache on servers performing lots of selects as most versions of MySQL protect the cache with a single thread/mutex which actually makes it slower to query the cache than perform the query again (in almost all cases).

    Can you check your MySQL configuration for the following two variables:

    ragtek likes this.
  8. Chris H.

    Chris H. Member

    This was my thoughts as well; was just trying to get back to my initial question.

    Interesting. I know when we enabled the cache on vb we saw significant improvement on the load of our db node. So the fact that the cache is not used on XF caused me concern. While load is not as high as vb pre-cache, we are seeing a higher average load than before our switch. I'd love to see some benchmarks supporting disabling the cache.

    mysql> show variables like '%query_cache%';
    | Variable_name                | Value     |
    | have_query_cache             | YES       |
    | query_cache_limit            | 1048576   |
    | query_cache_min_res_unit     | 4096      |
    | query_cache_size             | 536870912 |
    | query_cache_type             | ON        |
    | query_cache_wlock_invalidate | OFF       |
  9. Deebs

    Deebs Well-Known Member

    Hi Chris,

    Take a read of the following links, the first two are from Percona who specialise in High Performance MySQL and they know their stuff :)


    Can you also dump out your query counters.

Share This Page