MySQL Query Caching Part II

by Scott McMillan on October 16, 2008

For those wondering how to do query caching here is a quick run down.

1) Edit the MySQL conf at the following to /etc/my.cnf for a 26M query cache use:

query_cache_type = 1
query_cache_size = 26214400

2) Restart MySQL

/etc/init.d/mysqld restart

3) Login to MySQL and run the following:

mysql> SET GLOBAL query_cache_size = 26214400;

4) Check if it was loaded:

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 26214400 | 
+------------------+----------+
1 row in set (0.00 sec)

5) Check the cache “hits”:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1079    | 
| Qcache_free_memory      | 4635984 | 
| Qcache_hits             | 210991  | 
| Qcache_inserts          | 52877   | 
| Qcache_lowmem_prunes    | 17821   | 
| Qcache_not_cached       | 23497   | 
| Qcache_queries_in_cache | 2273    | 
| Qcache_total_blocks     | 6990    | 
+-------------------------+---------+
8 rows in set (0.00 sec)

6) Compare that to the questions:

mysql> SHOW STATUS LIKE ‘Questions%’;
+—————+——–+
| Variable_name | Value |
+—————+——–+
| Questions | 354695 |
+—————+——–+
1 row in set (0.00 sec)

from this we can determine that at that point roughly 59% 210991/354695 of the database queries are “cached”.

{ 0 comments… add one now }

Leave a Comment

You can use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>