» MySQL Query Caching | Tom Milewski

MySQL query caching is probably one of the quickest and easiest ways for someone to speed up a sluggish website.

There are many situations where an application will call the database repeatedly with the same queries only to find that they return the exact same data sets time and time again. MySQL Query Caching caches the result set therefore removing the overhead of having to run through the database each time a specific query is called.

Initial Settings

Before you do anything, you will need to set the query cache type.

The settings are as follows:
0 – Do Not Cache
1 – Cache All
2 – Cache On Demand

Cache All
You may disable query caching for a specific query by setting SQL_NO_CACHE.

SELECT SQL_NO_CACHE * FROM tutorial;

Cache ON Demand
You may enable query caching for a specific query by setting SQL_CACHE.

SELECT SQL_CACHE * FROM tutorial;

Set Up

For this tutorial, I am simply going to utilize Cache All and to do this you must run the following query:

SET SESSION query_cache_type = 1;

This will instantaneously start the query cache and you should see the cache start to fill up.

SHOW STATUS LIKE '%Qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11421    |
| Qcache_free_memory      | 82725432 |
| Qcache_hits             | 4467043  |
| Qcache_inserts          | 243158   |
| Qcache_lowmem_prunes    | 2066     |
| Qcache_not_cached       | 4183     |
| Qcache_queries_in_cache | 17831    |
| Qcache_total_blocks     | 47247    |
+-------------------------+----------+
8 rows in set (0.00 sec)

Query Cache Size
You may set your query cache size by using the following query:

SET GLOBAL query_cache_size = 52428800;

(The value is set in bytes. I have it set at 50Mb)

Read / Write Ratio
Make sure that you check up on your query cache because in some cases it might actually be detrimental to your site.

If your Qcache_inserts are close your Qcache_hits you might want to rethink using query caching as this means that you are nearing a 1:1 read/write ratio. Every time you write to the database, MySQL has to update the query cache and if those two numbers are close then Qcache might be doing the complete opposite of what it was designed to do.

Conclusion
With MySQL query caching enabled, you will generally see an increase in page loading times between five and fifteen percent. You will see more dramatic results on query-intensive pages.

Tom Milewski

MySQL Query Caching

Recent Posts

Archives

Categories