During our last Webinar on Scalable Web Architecture, I was asked a question about the MySQL Query Cache.

The question was whether the cache was holding the result of the query or the content of some tables looked after by the query. Before answering, let me give some background about how MySQL Query Cache relates to scalability.

As the number of requests that a MySQL instance receives is growing, it may hit many different limiting factors that will make the response time increase and the overall performance of the database decrease. If the database receives a lot of read requests that do not modify the content of the database tables, one solution to keep good performance - hence to scale - is to hold the result of the requests into a cache. When MySQL receives twice the same request, the second occurrence generates a cache hit, and instead of processing the request, MySQL returns the content that is in the cache. This alleviates the load on the database itself.


Now the answer to the question is that the cache holds the exact bits returned after processing the request for the first time. The cache is not a copy of some tables or sub-parts of tables.

It is also important to note that the cache keeps track of the tables involved in the processing of requests so that if a table is modified, the content attached to requests using these table is invalidated.

Last but not least, using MySQL Query Cache does not require any code modification at the application level. The application does not need to know that the cache is active. The MySQL server appears to be responding as if there were no cache.










More...