XF usage of MySQL query cache

[Chris H.]

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?

 

[akia]

What monitoring software do you use?

 

[Walter]

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?

 

[Chris H.]

What monitoring software do you use?

Munin

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?

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

 

[Paul B]

All of our XF tables are MyISAM

That's not correct.

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

The only tables which are MyISAM are:

xf_import_log
xf_search_index
xf_session
xf_session_admin

The rest are InnoDB apart from 3 which are MEMORY:

xf_attachment_view
xf_session_activity
xf_thread_view

http://xenforo.com/community/threads/mysql-myisam-or-innodb.388/page-2#post-134517

 

[Chris H.]

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]

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

 

[Chris H.]

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

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]

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      |
+------------------------------+-----------+

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.