Recently I did some research regarding the MySQL/MariaDB query_cache settings and the risks involved with playing with this value so I figured I'd outline what I found in case anyone else may be looking into the same thing.
The settings I'm referring to are query_cache_type, query_cache_size and query_cache_limit.
- query_cache_type - This setting basically lets you enable the cache. 1 for on and 0 for off. Nothing special here.
- query_cache_size - This setting specifies how large the cache should be.
- query_cache_limit - This setting specifies how large of a result-set should be stored. Any more than this and the query will not be cached.
So what is it? Ok I should probably explain. The Query cache is one of the memory buffers the DB uses and this particular one is used to store the result-set of a query so rather than do the parsing, joins, fetching, etc. which could take time it just fetches it from the cache. This can be particularly useful if the query is complex or a problematic query. In these cases it could provide a significant boost to performance. This cache is shared across all sessions to your DB so keep that in mind.
This sounds great and all but using the query cache is not a perfect solution. There are several things you need to take into consideration before utilizing it because under certain situations it can actually worsen the performance of your site. How? Well I'll go over the things you need to consider one by one.
Tables are updated frequently - A cached result is invalidated if the tables involved have been updated. So if a large majority of what goes on in your system are updates or inserts using a query cache won't do you much good.
Query cache size too large - If your query cache is too large this could incur an overhead of having to rebuild the cache over and over again once tables are updated. During this rebuild process the mutex contention would end up blocking parallel queries which is highly problematic on multicore systems and other concurrent systems. You generally want to start small and work your way up as you fine tune the system but rule of thumb is to not be larger than 100MB to 200MB.
Query cache limit too large - The query_cache_limit is basically how large of a result-set you want to be cached. You want to make efficient use of this cache. If there's query that produces a massive result-set that ends up hogging your entire cache size that generally doesn't get executed very often this might not best use of your cache. Especially if there are many other smaller queries that are run very often that ought to be cached as well. I'd probably chalk this one up to do more experimentation and tests with to see which settings net you the best performance. What I would probably do is get a SQL Profiler like Neor or similar and try to gauge the average size of your most common query and use that as a base-line for this setting. Also, make sure that the tables involved with said query are not updated too often.
Query is context sensitive - What I mean by this is that the results of a query will be cached if it is the exact SAME query. So if there's an extra WHERE clause, or perhaps a different variable depending on the logged in user, or different table name, etc. these will be cached as a separate result-set and take up space in your query_cache_size.
Concurrency - I touched up on this already in the Query cache size section but I figured I'd include it as a separate row and discuss it separately. Basically if your system is highly concurrent or possesses many cores this will worsen the performance of your DB if there is frequent updates to tables and the mutex locks that will incur due to these updates triggering a rebuild of the cache. A good indicator that this could be happening or similar cache issue is if you see too many Waiting for cache lock messages when running SHOW ENGINE INNODB STATUS or when profiling the queries.
Anyway, I think those are the main concerns when using the query cache. Next I think I'll go into some extra tips that might help you with getting the most of the query cache if you decide to use it. To get the current state of your query cache you can run this query SHOW STATUS LIKE "qcache%"; and in the results you will see these variables: Qcache_free_blocks, Qcache_free_memory, Qcache_hits, Qcache_inserts, Qcache_lowmme_prunes, Qcache_not_cached, Qcache_queries_in_cache, and Qcache_total_blocks.
Most of these variables are probably self explanatory except maybe Qcache_lowmme_prunes, Qcache_free_blocks and Qcache_total_blocks. Qcache_lowmme_prunes indicates the number of queries that have been removed from the cache to free up memory for caching new queries. It might be useful for helping decide how large your query cache should be. Free blocks and total blocks refer to the memory blocks of the query cache and may indicates the fragmentation of the cache. Generally, every query requires a minimum of 2 blocks one for the query and the other for it's results. For every table a query uses that's an extra block however queries can share the same block if the same table is involved.
Not all that information might be particularly useful to you right now but I decided I'd include it anyway. Running that query and playing with the settings may help you figure out if your query cache is being utilized efficiently.
If you're really interested in the nitty gritty the header of this file here has a ton of info about how the cache works. It's worth alook.
In general, if your environment is highly concurrent the query cache may not be what you want. It isn't scalable and will do you more harm than good. The cache is deprecated in MySQL 5.7 and has been outright removed in 8.0 due to these issues. You may need to consider something like ProxySQL as an alternative.