MariaDB Mysqltuner

Finally, mysqltuner 1.6.7 recommends disabling the Query Cache. It's better later than never.

Code:

[OK] Query cache is disabled by default due to mutex contention.

---

 

If you use Innodb, the Query Cache will always be competing with the Innodb Buffer because of the changes in the query results. But that is just one case.
Two good articles from percona regarding the Query Cache:

Should we give a MySQL Query Cache a second chance ? - MySQL Performance Blog
Write contentions on the query cache - MySQL Performance Blog

 

Yes, in a board with many concurrent requests that i maintain, the performance is better and load is lower.
You will notice this more in a forum like this one, where the query results are always changing, so the cache needs also to be changed

 

Just another thing for us to understand better. This post that im making, will go to the Buffer Pool. This post also change the result set of a lot of queries in the Query_Cache. For example, the querie responsible for the New Posts button.
The Query Cache don't have a cache of this new post, so they will ask the buffer pool and then it will need to update every query that this new post will mess with.
Now imagine a forum with a lot of new posts every minute, and the Query Cache always competing with the buffer pool because of that

From my knowledge, the Query Cache is good in sites where almost there is no updates or inserts to the Database. For example a Wordpress Blog with comments disabled.

 

In version 1.6.11 of Mysqltuner, its again recommending to turn on Query Cache if MariaDB version is superior than 10.1.2.

You can check it here: possible wrong suggestion in newer versions: [!!] Query cache should be disabled by default due to mutex contention. · Issue #190 · major/MySQLTuner-perl · GitHub

I checked some benchmarks, and it really seems MariaDB have done very big improvements regarding the Query Cache in 10.1.



While in Mysql 5.7 the performance is degraded when we enable Query Cache, that does not happen with MariaDB, in fact its the opposite.

MariaDB 10.1 vs MySQL 5.7: Real-World Performances - Softizy Blog

 

I keep it disabled.
Having a Query Cache of 5Gb will not be good for performance, as if some change happens, it will need to update many queries inside that big 5Gb of cache.

Normally, 128Mb is the maximum we should set the Query Cache. But the best thing is probably just to disable it.

Mysqltuner now says in multiprocessor:

PS: Query Cache will be removed from MySQL 8.0.