Invalidating query cache mysql
Whenever My SQL performs a SELECT operation, it either increments com_select or the qcache_hits status variables. show status like 'qcache_hits'; --------------- ------------ | Variable_name | Value | --------------- ------------ | Qcache_hits | 1446235295 | --------------- ------------ 1 row in set (0.05 sec) mysql So get the hit ratio by this formula: qcache_hits / (qcache_hits com_select) which gives us .9999 or 99.99%. Check qcache_free_memory status variable, is it also high? Imagine, each query that is cached must be a minimum of query_cache_min_res_unit.
This means that the first time you retrieve data it will always be slower than when you retrieve it the second time as the data will be cached.So the main things to keep in mind when tuning the query cache are (a) the total size of the query cache (b) the average size of query result sets, and (c) the size of queries above which you don't want to keep cached.Each of these three things can be measured, and once set - assuming your application footprint doesn't change dramatically - should remain fairly static. The query_cache_type variable should be set to ON, and the query_cache_size should be non-zero.Keeping it optimized and used efficiently can make a big difference in the overall throughput of your application, so it's worth taking a look under the hood, understanding it, and then keeping it tuned optimally.A lot of database engines can cache queries, usually this allows them to avoid a hard parse of the query each time.I suspect the performance impact is even higher on My SQL 5.7. A single global mutex just doesn’t scale to 4 cores, let alone 24 cores at 8 threads per core.
My SQL's query cache is an impressive piece of engineering if sometimes misunderstood.
memcached is widely used, and has clients for many programming languages and platforms.
Tangent Org have developed a memcached client in the form of My SQL UDFs (User Defined Functions).
The subsequent calls to the queries, even using different values for bind variables, will use the same cached query. My SQL does of course have various buffer caches for caching blocks, specifically when you're using Inno DB, however they are self-contained, and completely separate from the query cache. It caches the parsed query and the complete result set.
If you have lots of small queries that return small result sets such as with web-based applications, it can be a real godsend to have the query cache working for you.
Instead of having all my application code (PHP, Java etc.) access memcached separately, they can all access one single My SQL node, which gets to access memcached.