Should you disable or enable the MySQL query cache?
I - What is a query cache?The MySQL query cache was introduced as part of version 4.0 of MySQL. Its main function is simple: it is a giant hash table that associates bulk SQL queries with a group of results.
It signifies that if you add a space or change a capital letter in your SQL query, it will be different from the query cache point of view.
This simple approach also signifies that with a small modification/writing in a table, all the query cache entries concerning the table in question will be invalidated.
In applications that use the reading in an intensive fashion, without a lot of writing, it seems that enabling the query cache is a good idea.
II - Why disable the Query Cache?With the democratization of multi-cpu/multi-core systems, the scalability problems are more and more critical from a MySQL perspective.
The query cache is unfortunately known for its implementation using a global mutex that totally reduces MySQL performances.
It seems counter intuitive to enable it. But is it in practice?
We tested the deactivation of the query cache (configured @128MB) on an IBM Power 8 S822LC system with 2x10 cores @2.92GHz running in SMT8 mode (160 threads), with a Prestashop workload running MariaDB 10.1: You can see this workload takes a real benefit from the query cache, even on a massively multithreaded hardware. However, you need to keep the query cache size low. By raising it to 256MB, the number of locks due to the mutex explode...
III - ConclusionIn conclusion, the query cache can be really useful, even in highly multithreaded environment. It should be noted that certain MySQL forks, like Drizzle, have decided to totally delete the query cache from their code due to problems it created.
The query cache was also disabled by default starting from MySQL 5.6.
It could worth a try reenabling it, but in all cases, keep a very low query cache value (between 32 and 128 MB) to limit as much as possible its impact.