• 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

#1
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?

 

Walter

Well-known member
#3
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?
 

Brogan

XenForo moderator
Staff member
#5
#6
That's not correct.

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

http://xenforo.com/community/threads/mysql-myisam-or-innodb.388/page-2#post-134517
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?
 

Deebs

Well-known member
#7
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?
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:

query_cache_size
query_cache_type
 
#8
The table engine in use does not affect the query cache.
This was my thoughts as well; was just trying to get back to my initial question.

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).
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.

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

query_cache_size

query_cache_type
Code:
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       |
+------------------------------+-----------+
 

Deebs

Well-known member
#9
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.

Code:
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      |
+------------------------------+-----------+
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 :)

http://www.mysqlperformanceblog.com/2011/04/10/should-we-give-a-mysqlquery-cache-a-second-chance/
http://www.mysqlperformanceblog.com...ezes-could-be-the-query-cache/#comment-516145
http://bugs.mysql.com/bug.php?id=45544
http://www.fromdual.com/mysql-query-cache-does-not-work-with-complex-queries-in-transactions

Can you also dump out your query counters.