Invalidating query cache entries key mysql
This cache holds a single lock to perform changes and invalidations in a coarse grain, which is not as efficient as it could be.This can end up causing contention problems as well as stalls, which, unfortunately, was the case with our SQL server.In the old days, servers were single core with limited amount of memory.Nowadays, we have multicore servers with higher amounts of RAM, and, in our case, faster storage with SSD devices.Among its features, it is ubiquitous, it provides native support for new technologies, it is flexible and it is quite simple to use and get it running.It offers various storage engines with different characteristics, so one can choose the engine that best suits the workload at hand.
The query cache retrieves queries that exactly match prior queries. However, looking at how query cache works, we discovered a trade off between caching queries and the overhead of providing atomic access and invalidating entries in the cache.
As soon as we disabled the cache, we could see the usage of some My SQL parameters started to increase. As an example, on this first graph, we can see how select queries drastically grew once the query cache was turned off (on the 7th evening).
Yet, we also saw latency time dropped, increasing our response time.
The Inno DB Row Lock Waits graph shows the number of times (in milliseconds) that Inno DB has waited to grant row locks.
By reducing the lock waits, requests were served faster.