Posted by & filed under Geek, Tutorials.

So lately I’ve been reading into MySQL and its in-built cache facilities, and so I wanted to share some of the basics with those of you who hadn’t started to use these features yet.

MySQL has an in-built query cache which caches the results of SELECT queries, however we need to enable this functionality before MySQL can use it as by default its turned off. The logic behind this is that frequently used database queries will run much faster, mainly because the data resultset will be read from the cache instead of having to run the query again (*Cool Beans*). The MySQL query cache is available from MySQL 4.0.1.

So whenever tables in the database are modified the relevant entries in the query cache are flushed so you can be certain that even with the query cache enabled only up to date data is returned. Now that sounds great, so lets enable this on our MySQL Server!

Let’s get cache’in!

First off we need to make sure query cache is disabled, So we need to see what parameters are set, by running the following query in MySQL:

SHOW VARIABLES LIKE '%query_cache%'

Below is an example result for the above query, This shows that the query cache engine is available, but the query cache size is set to zero and therefore nothing will be cached, and the query cache engine will not actually be used! So lets get it enabled!

+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+

If you want to, It is possible to set the query_cache_size variable without actually restarting the MySQL server, by running the following SQL query.
In this example, we are enabling a 50MB query cache.
SET GLOBAL query_cache_size = 50*1024*1024;

Now Running the following line again to see what our server is running at.
SHOW VARIABLES LIKE '%query_cache%'

Output:
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 52428800 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+

Great! We’ve now got 50Mb of cache availble in MySQL in our Memory. However with this method, the next time the MySQL server is restarted the setting will be lost and query cache will no longer be used. So ideally we need to look at how to make the change permanent, No problem!

Setting it in the MySQL config

Setting the MYSQL query cache size in my.cnf

In order to make the query_cache_size setting permanent the MySQL server configuration file must be modified to enable this from mysqld startup. These settings are stored in the mysql config file which for those of you that dont know, is called “my.cnf”. This file is typically stored on a Linux system at /etc/my.cnf or sometimes at /etc/mysql/my.cnf (Ubuntu/Debian based distro’s). If it’s not at either of those locations you can try running locate my.cnf or find / -name my.cnf, although note the latter will take a while if you haven’t run an #updatedb command for a while.

To enable the query cache with a 50MB query cache in MySQL you need to add the following line to the my.cnf file.
Be sure to add it under the [mysqld] section otherwise it won’t work.

query-cache-size = 50M

I hope that helps anyone who is seeing stupid amounts of slowdown with their MySQL server.

Still want to learn more?

Purchase MySQL Admin Cookbook LITE Configuration, Server Monitoring, Managing Users

MySQL Admin Cookbook LITE Configuration, Server Monitoring, Managing Users

This cookbook presents solutions to problems in the form of recipes. Each recipe provides the reader with easy step-by-step descriptions of the actions necessary to accomplish a specific task. Example values and code samples are used throughout the recipes, which makes adaptation for individual needs easy. This book is for ambitious MySQL users as well as professional data center database administrators. Beginners as well as experienced administrators will benefit from this cookbook and get fresh ideas to improve their MySQL environments.










Leave a Reply

Your email address will not be published. Required fields are marked *