This blog post was motivated by an internal discussion about how to fully disable query cache in MySQL.
According to the manual, we should be able to disable “Query Cache” on the fly by changing query_cache_type to 0, but as we will show this is not fully true. This blog will show you how to properly disable “query cache,” and how common practices might not be as good as we think.
Can we just disable it by changing variables, or does it requires a restart to avoid the global mutex? Let’s see how it works.
Some Query Cache context
The query cache stores the text of a “Select” statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
But cacheable queries take out an “exclusive lock” on MySQL’s query cache. In addition, any insert, update, delete or other modifications to a table causes any relevant entries in the query cache to be flushed. If you see many “Waiting for query cache lock” in the processlist, you might be suffering from this exclusive lock. In this blog post, you can see how this global mutex in high concurrency can cause performance degradation.
If we are facing this situation, how can we disable it?
Disabling Query Cache
There are two options that you can change: query_cache_type and query_cache_size.
So if we change query_cache_size to “0”, does it means the cache is disabled? Or we also have to change query_cache_type? Or both? And does MySQL require a restart to avoid the global mutex?
The source code shows us this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | int Query_cache::send_result_to_client(THD *thd, const LEX_CSTRING &sql) { ulonglong engine_data; Query_cache_query *query; #ifndef EMBEDDED_LIBRARY Query_cache_block *first_result_block; #endif Query_cache_block *result_block; Query_cache_block_table *block_table, *block_table_end; char *cache_key= NULL; size_t tot_length; Query_cache_query_flags flags; DBUG_ENTER("Query_cache::send_result_to_client"); /* Testing 'query_cache_size' without a lock here is safe: the thing we may loose is that the query won't be served from cache, but we save on mutex locking in the case when query cache is disabled. See also a note on double-check locking usage above. */ if (is_disabled() || thd->locked_tables_mode || thd->variables.query_cache_type == 0 || query_cache_size == 0) goto err; ... |
MySQL is going to check if the query cache is enabled before it locks it. It is checking four conditions, and one of them has to be true. The last three could be obvious, but what is the “is_disabled()” function? Following the source code, we can find the next: sql_cache.h
1 2 3 | void disable_query_cache(void) { m_query_cache_is_disabled= TRUE; } ... bool is_disabled(void) { return m_query_cache_is_disabled; } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | void Query_cache::init() { DBUG_ENTER("Query_cache::init"); mysql_mutex_init(key_structure_guard_mutex, &structure_guard_mutex, MY_MUTEX_INIT_FAST); mysql_cond_init(key_COND_cache_status_changed, &COND_cache_status_changed); m_cache_lock_status= Query_cache::UNLOCKED; initialized = 1; /* If we explicitly turn off query cache from the command line query cache will be disabled for the reminder of the server life time. This is because we want to avoid locking the QC specific mutex if query cache isn't going to be used. */ if (global_system_variables.query_cache_type == 0) query_cache.disable_query_cache(); DBUG_VOID_RETURN; } |
If the global_system_variables.query_cache_type == 0 condition is true it is going to call the disable_query_cache function, which sets m_query_cache_is_disabled = True, so is_disabled going to be “True”. That means if we are setting query_cache_type to 0 in runtime, that should eliminate the global mutex. Let’s run some tests to confirm this and see if the global mutex disappears after changing query_cache_type to 0.
Running tests
Context on the tests:
- We ran simple OLTP tests using sysbench as follows:
1 | sysbench --test="/usr/share/doc/sysbench/tests/db/oltp.lua" --report-interval=1 --max-time=120 --oltp-read-only=off --max-requests=0 --num-threads=4 --oltp-table-size=2000000 --mysql-host=localhost --mysql-db=test --db-driver=mysql --mysql-user=root run |
- Important portion of my.cnf file:
1 2 3 4 | query_cache_type =1 query_cache_limit = 1M query_cache_size =1G performance_schema_instrument='wait/synch/%Query_cache%=COUNTED' |
So basically the tests were run for two minutes each while playing with query_cache_type and query_cache_size.
- Started MySQL with query_cache_type = 1 and query_cache_size=1G.
- Change query_cache_type to 0. As we can see nothing changed, MySQL is still using the query cache.
- But when we stopped sysbench and started again (closing and opening new connections), we can see there are no more inserts going into query cache. But we still can see the queries like “Not Cached” that means changing the query_cache_type applies only for the new connections, and we still can see some mutex.
- Restarted MySQL with query_cache_type = 0 and query_cache_size=0. Finally, we disabled the query cache and all the mutex is disappeared.
- Restarted MySQL with query cache enabled.
- We changed query_cache_size=0 and it almost worked, we could disable query cache on the fly, but as we can see there is still some mutex activity.
- Changing query_cache_type=0 and restarting sysbench does not have any effect on the mutex.
So the only way to stop any activity around query cache requires restarting MySQL with query_cache_type = 0 and query_cache_size=0. Disabling it or even set it to “0” on runtime is not completely stopping mutex activity.
But why do we still need query_cache_size while in theory query_cache_type should be enough?
As referenced above, the manual says if query_cache_type = 0:
Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.
Based on our test, if we change query_cache_type to 0, it still hits the cache.
So you might think “well, I don’t enable the query cache and use defaults to keep it disabled.” Keep reading, because you might be wrong. According to manual, starting from 5.6.8 query_cache_type=0 is set by default, but query_cache_size= 1048576 (1MB). This means that if we keep default configuration, we will still see activity in the query cache as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql -e "show global status like 'qca%';" +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031320 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 423294 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ |
But if we just add query_cache_size=0 to my.cnf and check again (of course after restarting server):
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql -e "show global status like 'qca%';" +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | +-------------------------+-------+ |
We finally get no query cache related activity at all. How much overhead is caused by this? We’re not fully sure because we didn’t perform benchmarks, but we like to see no activity when we don’t want to.
Now we’re wondering if this case requires a bug report. Stay tuned, we will publish results in the post soon.
Digging more code
Let’s have a look at store_query function. MySQL uses this function to store queries in the query cache. If we read the code we can find this:
1 2 | if (thd->locked_tables_mode || query_cache_size == 0) DBUG_VOID_RETURN; |
It only checks the query_cache_size, it does not check the type. Store_query is called in handle_query, which also does not check the query_chache_type.
Conclusion
There is some contradiction between checking the query cache and storing the data in the query cache, which needs further investigation. But as we can see it is not possible to fully disable the query cache on the fly by changing query_cache_type or/and query_cache_size to 0. Based on the code and the tests, if you want to make sure the query cache is fully disabled, change query_cache_size and query_cache_type to 0 and restart MySQL.
Is a known fact that query cache can be a big point of contention, and we are not trying to benchmark the performance overhead since this mostly depends on the workload type. However, we still can see some overhead if the query cache is not fully disabled when MySQL is started.
Great post! It would be great to see a similar post for AHI
Which version of MySQL is the source code you guys reviewed?
Hi Daniel,
I checked 5.6,5.7 but the links and copies are from 5.7.
Great post! Thx!
You show the qcache activity if MySQL started with the defaults, but the graph of mutex contention doesn’t have a section for starting with defaults (query_cache_type=0, query_cache_size=1M).
If the defaults result in mutex contention, it definitely warrants a bug, since the documentation states: “By default, the query cache is disabled. This is achieved using a default size of 1M, with a default for query_cache_type of 0.” (src: http://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html)
Also some discussion from 2013 in this bug: https://bugs.mysql.com/bug.php?id=65336 Last comment: “If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all,…*snip*”
Derek, you’re right I didn’t add that test to graphic so I ran it a quick test and I can’t see any activity reported in performance_schema (where Grafana reads data from) but I still see status counters increasing. As mentioned we didn’t check the overhead and performance impact but IMO manual is not fully clear about how defaults works because the only way we stopped any activity (even performance counters) was by setting both variables to 0.
HI Tibor,
How can I draw graphs using metrics.sh output scripts ?
Using cacti
Hi –
In your profiling test, you don’t have a step where you set both the size and type to 0 while not restarting. According to the source code flow, that should be sufficient, however you don’t test this.
Your step 4 should have been split, first setting both to 0 and checking the mutex, THEN restarting (if the mutex was still occurring).
As it is now, you don’t have that data. Therefore, claim that the server has to be restarted for the mutex to disappear is not warranted by the evidence provided. It’s one step away.
We know the query cache is being deprecated, but currently people are still referring to resources regarding the query cache so it’s possibly worth updating the post.