How To Clear Cache In Mysql Workbench
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 ;
Subscribe to:
Post Comments (Atom)
|
0 Response to "How To Clear Cache In Mysql Workbench"
Post a Comment