Tuning MySQL Server 4.0 Query cache.

Peter Zaitsev

It is always a challenging job to tune database for the best performance. There are plenty of options, which you can set to provide the balance, which provides the best performance for exactly your application. MySQL 4.0 adds more tools to this job by introducing Query Cache, which can also be tuned.

What is the MySQL Query Cache?
Query Cache is an intelligent MySQL feature, which allows to greatly improving the speed of the frequently executed queries by caching query response and returning it to the client instead of real query execution.

In order to adjust Query Cache settings you need to understand how does the query cache work. Let us explain this briefly. As the query arrives to MySQL server it performs a textual comparison with the queries in the Query Cache. This means "SELECT * FROM a" and "Select * From a" will be recognized as different queries. If a query matches one stored in the cache, the cached result is returned. If not, then the query is executed and if parameters allow the query result is stored in the query cache. When a table is modified, queries stored in the cache using the modified table, are removed from query cache.

There are 3 main variables, which can be used to tune Query Cache settings. They are query_cache_size, query_cache_limit and query_cache_type.

query_cache_size:
Setting proper query_cache_size is the most complex task. It sets the amount of memory used for the query cache. One should find the right balance between the benefit you get from query cache and that non-cached queries slow down a bit, basicly due to less amount of memory available for other caches and buffers. Only benchmarking can point out the exactly best balance, but there are some guidelines, how to set this variable. Basic idea is that the more benefit query cache potentially gives you, the more memory it might be worth to allocate for it. Qcache_inserts will show you how many queries are added to query cache while qcache_hits will show you how many query results are taken directly from the query cache instead of executing them. You should compare qcache_hits value to total number of your select queries to see what is your current hit rate. You may increase or degrease query_cache_size to find the value, which provides best performance for you. Note that as the query cache content gets invalidated with update queries, amount of memory which query cache can use is not unbound. Qcache_free_memory shows amount of free memory in query cache, which is not used at the moment. If this value stays constantly high during the load it might be good idea to decrease the query cache size, i.e. to free some memory from the query cache to other caches and buffers.

query_cache_limit:
This variable sets the maximum result size, which would be stored in the query cache. The reason to have this variable is to limit queries having very large result sets to overwrite all the queries in the query set. The way to set this variable is to determine, what is the largest result set size of the queries you wish to cache? In most cases you can find the answer easily. In some cases there are queries of any possible size. In such case you might want to compare benefit from caching this large query with benefit of having in the cache smaller queries, the large query overwrites to make the decision. Examining cache query hit rate is also a good help here. A rule of thumb to start tuning the parameter might be setting query_cache_limit 1/100 to 1/10 of query_cache_size.

query_cache_type:
There are two ways how query cache can be enabled. Setting this variable "on" will attempt to cache all select queries. It is a good setting if you do not want to make any changes in your application, but you want it to start using query cache. Thus this setting allows you later to mark queries you do not want to be cached with SQL_NO_CACHE. Setting query_cache_type value to "DEMAND" is a good solution if in your application most of the queries will not benefit from the caching, as they never repeat or result is invalidated before the query is repeated. With this option you can select those queries, which will benefit from caching by using the SQL_CACHE option.