Performance Tuning in MySQL with Query Caching

When you run a SELECT query, MySQL “remembers” both the query and the results it returns. This is accomplished by storing the result set in a special cache (called the query cache) each time a SELECT query is executed. Then, the next time you ask the server for the same query, MySQL will retrieve the results from the cache instead of running the query again. As you can imagine, this speeds up the process considerably.

Although enabled by default, you must always verify that query caching is turned on, which can be done by checking the server variables. The following example illustrates:

mysql> SHOW VARIABLES LIKE '%query_cache%';

6 rows in set (0.00 sec)

• The first variable, have_query_cache, indicates the server was configured for query caching when it was installed (the default).
• The query_cache_size variable indicates the amount of memory allotted for the cache in bytes. If this value is 0, query caching will be off.
• The values for the query_cache_type variable range from 0 to 2. A value of 0 or OFF indicates that query caching is turned off. ON or 1 means that query caching is turned on, with the exception of SELECT statements using the SQL_NO_CACHE option. DEMAND or 2 provides query caching on demand for SELECT statements running with the SQL_CACHE option.
• The query_cache_limit variable specifies the maximum result set size that should be cached. Result sets larger than this value will not be cached.

You can alter any of these variables using the SET GLOBAL or SET SESSION statements, as shown:

mysql> SET GLOBAL query_cache_size = 16777216;
Query OK, 0 rows affected (0.00 sec)

To see for yourself what impact the query cache is having on performance, run the same query with and without query caching to compare the performance difference. Here’s the version without using the query cache:

mysql> SELECT SQL_NO_CACHE r.RouteID, a1.AirportCode, a2.AirportCode,
    -> r.Distance, r.Duration, r.Status FROM route AS r,
    -> airport AS a1, airport AS a2
    -> WHERE r.From LIKE a1.AirportID
    -> AND r.To LIKE a2.AirportID
    -> AND r.RouteID IN
    ->   (SELECT f.RouteID
    ->   FROM flight AS f, flightdep AS fd
    ->   WHERE f.FlightID = fd.FlightID
    ->   AND f.RouteID = r.RouteID
    ->   AND fd.DepTime BETWEEN '00:00' AND '04:00');

2 rows in set (0.21 sec)

Now perform the same query with the cache:

mysql> SELECT SQL_CACHE r.RouteID, a1.AirportCode,
    -> a2.AirportCode, r.Distance, r.Duration, r.Status FROM
    -> route AS r, airport AS a1, airport AS a2
    -> WHERE r.From LIKE a1.AirportID
    -> AND r.To LIKE a2.AirportID
    -> AND r.RouteID IN
    ->   (SELECT f.RouteID
    ->   FROM flight AS f, flightdep AS fd
    ->   WHERE f.FlightID = fd.FlightID
    ->   AND f.RouteID = r.RouteID
    ->   AND fd.DepTime BETWEEN '00:00' AND '04:00');

Dramatic improvements in performance aren’t unusual if query caching is enabled on frequent queries.

Speed Up Your Web Site With MySQL Query Caching

One of the best ways to speed up your web application is to enable query caching in your database, which caches commonly used SQL queries in memory for virtually instant access by the next page that makes the same request.

The reason this method is so powerful is that you don’t have to make any changes to your web application, you just have to sacrifice a little bit of memory. This isn’t going to fix all of your problems, but it definitely can’t hurt.

Note: if your application updates tables frequently, then the query cache will be constantly purged and you won’t get much or any benefit from this. This is ideal for an application that mostly does reads against the database, such as a WordPress blog. This also won’t work if you are running on shared hosting.

Enable Caching with Server Running

The first thing you’ll want to do is make sure that your installation of MySQL actually has query caching support available. Most distributions do, but you should check anyway.

You’ll want to run this command from your MySQL console, which will tell you if query caching is available.

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Don’t mistake this as meaning that query caching is actually enabled, because most hosting providers aren’t going to enable this by default. Oddly enough, my Ubuntu Feisty installation already had it enabled…

Next we’ll need to check and see if query caching is enabled. We’ll need to check more than one variable, so we may as well do it all at once by checking for the variable query%

mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+

Here’s the important items in the list and what they mean:

• query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.
• query_cache_type – This value must be ON or 1 for query caching to be enabled by default.
• query_cache_limit – This is the maximum size query (in bytes) that will be cached.

If the query_cache_size value is set to 0 or you just want to change it, you’ll need to run the following command, keeping in mind that the value is in bytes. For instance, if you wanted to allocate 8MB to the cache we’d use 1024 * 1024 * 8 = 8388608 as the value.

SET GLOBAL query_cache_size = 8388608;

Similarly, the other options can be set with the same syntax:

SET GLOBAL query_cache_limit = 1048576;

SET GLOBAL query_cache_type = 1;

Now how do we tell if it’s actually working? You can use the SHOW STATUS command to pull all the variables that start with “Qc” to take a look at what is going on under the hood.

mysql> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 65     | 
| Qcache_free_memory      | 201440 | 
| Qcache_hits             | 18868  | 
| Qcache_inserts          | 2940   | 
| Qcache_lowmem_prunes    | 665    | 
| Qcache_not_cached       | 246    | 
| Qcache_queries_in_cache | 492    | 
| Qcache_total_blocks     | 1430   | 
+-------------------------+--------+
8 rows in set (0.00 sec)

You’ll notice in the stats that I have plenty of free memory left. If your server shows a lot of lowmem prunes, you might need to consider increasing this value, but I wouldn’t spend too much memory on query caching for a web server… you need to leave memory available for apache, php, ruby, or whatever you are using.

Enable in Config File

If you want these changes to survive a reboot or restart of the mysql server, you’ll need to add them into your /etc/mysql/my.cnf configuration file for MySQL. Note that it might be in a different location on your installation.

Open up the file using a text editor in sudo or root mode, and then add these values if they don’t already exist in the file. If they do exist, just uncomment them.

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

Query caching can significantly improve the speed of your web application, especially if your application does mostly reads. Monitor the status using the methods above and see how it works over time.

What is MySQL Query Caching?

As we know, caching is used to improve performance. It will increase loading speed of the website or application. There are various types of caching mechanisms available. The Query Caching is one of the caching mechanism used to improve performance.

For MySQL, there is a general query cache which can help tremendously. The MySQL is one of the important feature in MySQL and an inevitable part of query optimization. After the caching, results will be set in a memory cache like memcached or cassandra. The query caching is normally used with the content caching. As with content caching, it is most effective in read-heavy scenarios. The MySQL query cache in particular should not be relied upon for performance as it is easy to invalidate large segments of the cache with minor changes to data. The MySQL query cache is a global one shared among the sessions.

Configuration Directives

The responses from the MySQL server can be slows sometimes. By using the query caching, the response from this MySQL servers can be optimized. This is the benefit of query caching. In this article, we are going to see how to enable the query caching in an MySQL server. In order to enable the query caching, we need to add the following configuration directives.

1) query_cache_size=SIZE

2) query_cache_type=OPTION

query_cache_size=SIZE

The first directive that is needed to be enabled to enable query caching in MySQL servers is the “query_cache_size=SIZE”. This directive enables us to set the size of memory or the amount of memory allocated for caching query results. In a typical server, the default value for this directive will be ‘0’. It denotes the query cache is disabled. The query caching will be disabled by default. To enable the query caching we need to set some value to this. We should set the value according to how much memory we are planning to allocate for query caching.

query_cache_type=OPTION

The next configuration directive that is needed to be set to enable query caching is “query_cache_type=OPTION”. Using this directive, we specify which type of query cache we are setting. There are 3 possible options that can be set for this directive. They are listed and explained below.

1) The zero “0”

2) The one “1”

3) The two “2”

These are the values that can be set for this directive. Let’s have a look on what these values represent.

The zero “0”

The zero tells the server the following: Don’t cache the results in or retrieve results from the query cache.

The one “1”

There are query results that starts with “SELECT S_NO_CACHE”. If we set the value for this directive as one, it represents that, to cache all the query results except for those that begin with SELECT S_NO_CACHE.

The two “2”

The other value we can set for this directive is “2”. If we set the value as “2”, it means that cache results only for queries that begin with SELECT SQL_CACHE.

Enabling Query Caching in MySQL

We can setup the caching in the following format. You need to enter into MySQL with the following command. This means that you are entering into MySQL as the root user after you enter the command, the system will ask for the password.

# mysql -u root -p

When you run the above command, you will get an output like the one below.

Enter password:

Now you need to enter the root password. If the entered password is correct system will log you in and you will get an output like this:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 16 to server version: 4.1.15-Debian_1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Assume that, we are setting up the query cache for 32 Mb. We need to do the following to acquire this.

mysql> SET GLOBAL query_cache_size = 33554432;

Query OK, 0 rows affected (0.00 sec)

To verify, we can run the following MySQL command:

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;

+——————+———-+

| Variable_name    | Value    |

+——————+———-+

| query_cache_size | 33554432 |

+——————+———-+

Now, we can append other configuration directives as follows:

query_cache_size = 268435456

query_cache_type=1

query_cache_limit=1048576

The above commands mean that the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. The memory size is set in Kb.

This is how we can set the query caching in the MySQL.

The MySQL Query Cache

I have been reading about MySQL's Query Caching features in the High Performance MySQL book, and on the web. I have also been playing around with it on my own. I have concluded that it is a pretty cool feature! You will need MySQL 4.0.1 or higher to play...

I think what I like best about it is that the cache expires automatically when the table is modified (inserts, updates, delete's, etc). So it may not be terribly harmful to just enable the cache, and see what happens.

The High Performance MySQL book states that the Query Cache identifies cacheable (is that a word?) queries by looking for SEL in the first three characters of the SQL statement. However in my testing I found that whitespace or comments before the SELECT statement did not have any effect on caching. Perhaps the JDBC driver trims whitespace and comments before sending the SQL to the server.

Enabling MySQL Query Cache

Edit your my.cnf and set query_cache_type equal to 1, and set the query_cache_size to some value (here we have set it to 25mb)

query_cache_type = 1 
query_cache_size = 26214400

If either query_cache_type or query_cache_size are set to zero caching will not be enabled. If you have lots of RAM on your server you may want to increase the size of the cache accordingly. There are some more settings you can tweak but these will get you going.

 Note you can also edit these settings using MySQL Administrator. They can be found under Health > System Variables > Memory > Cache 

Cache Hints

You can also set query_cache_type = 2 - with this setting queries are only cached if you pass the hint SQL_CACHE to them, for example:

SELECT SQL_CACHE something FROM table

Alternativly, if you have query_cache_type = 1, you can tell MySQL that you don't want a specific query to be cached. This is highly recommended because you don't want to fill up the cache with highly dynamic queries (such as a search form). This is done with the hint SQL_NO_CACHE.

SELECT SQL_NO_CACHE stuff FROM table

Making the hints database independent

If your like me, you cringe at the thought adding database server specific SQL code to your queries. The High Performance MySQL Book has a tip that will allow you to use the hints and not break compatibility:

SELECT /*! SQL_NO_CACHE */ stuff FROM table

This trick will also work with the SQL_CACHE hint. And if you are really like me you will miss that ! in there, don't forget that or it won't work.

MySQL Query Cache and Prepared Statements

Some very good news is that MySQL Query Cache does seam to work well with prepared statements. In ColdFusion if you use the CFQUERYPARAM tag your using prepared statements. ColdFusion's builtin query caching mechanism does not allow queries with CFQUERYPARAM to be cached. They can be cached with MySQL Query Cache however.

So if you have some code such as this:

SELECT stuff FROM table 
WHERE name = <cfqueryparam value="#url.name#">

The SQL statement looks like this:

SELECT stuff FROM table
WHERE name = ?

Buy MySQL is smart enough to cache this query:

SELECT stuff FROM table
WHERE name = 'bob'

MySQL Query Cache

MySQL has a query cache which caches the results of SELECT queries, if enabled. This means that frequently used database queries will run much faster, because the data resultset will be read from the cache instead of having to run the query again. The MySQL query cache is available from MySQL 4.0.1. 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.
You can tell if the query cache is enabled, and what parameters are set, by running the following query in MySQL:

SHOW VARIABLES LIKE '%query_cache%'

An example result for the above query is below, which 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.

+------------------------------+---------+
| 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     |
+------------------------------+---------+

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;

Running SHOW VARIABLES LIKE '%query_cache%' will now return the following:

+------------------------------+----------+
| 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      |
+------------------------------+----------+

This means a query cache will now be running, using 50MB of memory. However, the next time the MySQL server is restarted the setting will be lost and query cache will no longer be used. Next, we will look at how to make the change permanent.

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. These settings are stored in a file called my.cnf which is typically stored on a Linux system at /etc/my.cnf or sometimes at /etc/mysql/my.cnf. 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 command will take a while.
To enable the query cache with a 50MB query cache in MySQL, you would add the following line to the my.cnf file, under the [mysqld] section:

query-cache-size = 50M

The next time MySQL is restarted it will have the query cache enabled with the size specified.

MySQL Query Cache Speedup Example

Yesterday I posted an SQL query which works out the top selling categories in an ecommerce website. When I first ran this query on my local machine it was reasonably fast, but it took almost 4 seconds to run on the actual production server which is unacceptably slow. It had taken even longer but I optimized the query by adding appropriate indexes etc and only managed to get it down to 4 seconds.
After enabling the query cache, the first time the query was run it would still take almost 4 seconds, but subsequent queries for the same data took around 0.0001 seconds. This is much better and shows the power of the MySQL query cache.
The MySQL manual contains more information.