One of the new features in MySQL 4 that has a lot of the geeks drooling is the new query caching process, which promises a substantial performance increase for many situations.
What does MySQL's Query Caching do?
Basically the query cache stores the text and result of a MySQL query so that if an identical query is made afterwards it doesn't have to be executed again repeatedly. Think of it as sort of a buffer for MySQL SELECT queries. (Yes, you uber geeks know it can get a little more involved but this is a simple howto)
This caching will quite literally 'kick @#!@#!@#!' on systems where tables don't change very often and the same queries are typically made over and over again. You can see how many of us are enthusiastic about it, can't you?
How do I enable it?
Easy, almost so easy that this howto is really needless.
Once you have MySQL 4 installed and running on your server, open your mysql configuration file with your favorite text editor (we will assume /etc/my.cnf and pico for this howto):
pico -w /etc/my.cnf
In the last line under the [mysqld] section add the following line:
set-variable = query_cache_size=16M
Here is what my entire [mysqld] section looks like as a point of reference:
[mysqld]
port = 3306
skip-locking
skip-innodb
set-variable = key_buffer=8M
set-variable = max_allowed_packet=2M
set-variable = table_cache=1024
set-variable = sort_buffer=2M
set-variable = join_buffer_size=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=8M
set-variable = max_connections=300
set-variable = max_connect_errors=65535
set-variable = wait_timeout=14400
set-variable = thread_cache_size=256
set-variable = back_log=512
set-variable = connect_timeout=30
set-variable = tmp_table_size=4M
set-variable = query_cache_size=16M
What we've basically done is enabled the query cache and set the cache size all in one variable.
Now ctrl-x and answer Yes when pico asks if you want to save the file.
Now restart MySQL:
/etc/rc.d/init.d/mysql restart
If all goes well you should see:
#Starting mysqld daemon with databases from /var/lib/mysql
And MySQL should be up and running normally
As you can see above I've set my query cache size to 16 Megabytes, this may or may not be optimal for your system depending on how much ram you have and your situation. Details are still sketchy and I've not heard any formulas for calculating an optimal cache size yet, so experiment. Your box may like a much larger or much smaller value.
How can I verify that the data cache is enabled and functioning?
Easy! Do the following:
mysql -uroot -p
Enter your root mysql password and you will be in the CLI mysql client and able to execute queries to check on the status of the cache:
Check to see if the Query Cache is enabled:
mysql> SHOW VARIABLES LIKE 'mysql_query_cache';
Which should return:
CODE
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
Check to see how many queries are currently cached:
mysql> SHOW STATUS LIKE 'Qcache_queries_in_cache';
Returns:
CODE
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 439 |
+-------------------------+-------+
1 row in set (0.00 sec)
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 439 |
+-------------------------+-------+
1 row in set (0.00 sec)
Check to see how many hits the cache has had:
mysql> SHOW STATUS LIKE 'Qcache_hits';
Returns:
CODE
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 11762 |
+---------------+-------+
1 row in set (0.00 sec)
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 11762 |
+---------------+-------+
1 row in set (0.00 sec)
Check to see how much cache memory is currently free:
mysql> SHOW STATUS LIKE 'Qcache_free_memory';
Returns:
CODE
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Qcache_free_memory | 15711224 |
+--------------------+----------+
1 row in set (0.00 sec)
| Variable_name | Value |
+--------------------+----------+
| Qcache_free_memory | 15711224 |
+--------------------+----------+
1 row in set (0.00 sec)
You can also try atatus values like Qcache_inserts, Qcache_lowmem_prunes, Qcache_not_cached, Qcache_free_blocks, and Qcache_total_blocks. There is more info on all these here:
http://www.mysql.com/doc/en/Query_Cache_St...aintenance.html
Oh, you can leave the CLI MySQL client when you are done by hitting ctrl-c!
Well there you have it folks! Where do we go from here? Well I for one would like to know what the optimal query cache size value should be in different given situations. I'm anxious to see what all of your experiences are, good or bad. I hope this silly howto helped a couple of my fellow RackShack customers!