MySQL - Query Caching

MySQL supports a cool feature called Query caching. ie, MySQL can be configured to cache the result set of a select statement. After that, whenever the query is executed, the data is fetched from the memory, rather than searching the database. When one of the tables in the query is updated, the cached query is invalidated.

When the number of select statements is very large than the number of update statements, this can produce a significant change in performance. The best thing about MySQL Query Caching is that you don't have to make any changes to your query to activate this feature (there is an alternate method that selectively caches the query, which requires changing the query).

How to enable Query Caching.

If the number of SELECT queries is much higher than the number of update statements, then this is the best method as this requires no change in your program.
Edit your my.cnf file(usually located at /etc/my.cnf).

$ vi /etc/my.cnf Under the section [mysqld] add # Enable Mysql Query Caching # 0 => Caching is disabled # 1 => Caching is enabled for all queries # 2 => This requires adding the keyword SQL_CACHE to the queries that are to be cached query_cache_type = 1 # This is the total memory in bytes allocated for Query Caching query_cache_size = 32000000 # This is the maximum permitted size in bytes for a single result set. If the size of the result set is greater than this, then the query is not cached query_cache_limit = 1000000 Now restart mysql. In most of the unix systems, the following will work $ /etc/init.d/mysqld restart and in Mac, try this $ /Library/StartupItems/MySQLCOM/MySQLCOM restart

Thats it. Now mysql query caching is enabled for all your queries. To see the status of caching login to mysql

$ mysql -u username -p password

If your login was successful, you can see the mysql prompt. Type the following query in the mysql prompt

mysql> SHOW VARIABLES LIKE 'query%'; That will show a table with the values you specified in the my.cnf file +------------------------------+----------+ | Variable_name                | Value    | +------------------------------+----------+ | query_alloc_block_size       | 8192     | | query_cache_limit            | 1000000  | | query_cache_min_res_unit     | 4096     | | query_cache_size             | 32000000 | | query_cache_type             | ON       | | query_cache_wlock_invalidate | OFF      | | query_prealloc_size          | 8192     | +------------------------------+----------+ Now see the status of the query caching enter mysql> SHOW STATUS LIKE 'QC%'; That will show you a table like the one below +-------------------------+----------+ | Variable_name           | Value    | +-------------------------+----------+ | Qcache_free_blocks      | 1        | | Qcache_free_memory      | 31982456 | | Qcache_hits             | 0        | | Qcache_inserts          | 0        | | Qcache_lowmem_prunes    | 0        | | Qcache_not_cached       | 2        | | Qcache_queries_in_cache | 0        | | Qcache_total_blocks     | 1        | +-------------------------+----------+ 8 rows in set (0.00 sec) now exit from mysql and perform an apache benchmark $ ab -n 50 -c 5 http://yoursite/ Now login back into mysql $ mysql -u username -p password mysql > SHOW STATUS LIKE 'QC%'; +-------------------------+----------+ | Variable_name           | Value    | +-------------------------+----------+ | Qcache_free_blocks      | 1        | | Qcache_free_memory      | 31863864 | | Qcache_hits             | 778      | | Qcache_inserts          | 17       | | Qcache_lowmem_prunes    | 0        | | Qcache_not_cached       | 109      | | Qcache_queries_in_cache | 17       | | Qcache_total_blocks     | 39       | +-------------------------+----------+ 8 rows in set (0.00 sec)

Qcache_inserts shows the number of queries inserted into the cache and Qcache_hits shows the number of requests served from the cache.

Btw, I hope you saw the result of the apache benchmark :). This was the result I obtained
Percentage of the requests served within a certain time (ms)
50% 151
66% 173
75% 200
80% 389
90% 4471
95% 4507
98% 4507
99% 4507
100% 4507 (longest request)

4507 ms was for the first query, when the cache was not available and 151 ms was for the cached request.


BTW, DON'T USE THIS METHOD IF THE FREQUENCY OF UPDATION IS EQUIVALENT TO THE FREQUENCY OF SELECTION, BECAUSE THE OVERHEAD CAUSED BY THE CACHE INVALIDATION WILL DEGRADE THE PERFORMANCE.