How to enable mysql server query cache

by · Published · Updated

MySql Server

The MySQL Server contains a query cache that stores the result of a query and can quick ly look up that result and pass it to a client without having to search the database.

The following tests demonstrates how to enable and use the query cache. The tests were run in MySQL Server 5.5.17, however the results apply to all versions of MySQL with the Query Cache with little or no modification.

Verify That the Query Cache is Enabled

In some installations of MySQL, the query cache is disabled by default so you will have to do a little configuration to set things up. The way to check is to log into MySQL and issue the following command:


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 | 0 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
7 rows in set (0.00 sec)

There are two variables to consider in order to verify whether the query cache is on:

query_cache_size: The amount of memory allocated to the query cache. If the query_cache_size is 0, the query cache is effectively switched off even though there will still be an overhead unless the query_cache_type is set to OFF.
query_cache_type: There are three valid values for the query_cache_type setting:
0 or OFF: The query cache is completely turned off. It is still necessary to set the query_cache_size to 0 to deallocate the buffer.
1 or ON: With this value the query cache will be used per default and it is necessary to use the SQL_NO_CACHE hint to disable the query cache for a query.
2 or DEMAND: With this value the query cache is still on, but will only be used when the SQL_CACHE hint is specified.

Query Cache Disabled

With the output above, the query cache is disabled. With these settings it is expected that each execution of the same query will take approximately the same amount of time (note that there will be other factors as well such as whether the data is in the InnoDB buffer pool or the the I/O cache maintained by the operating system):

mysql> SELECT * FROM t1 ORDER BY Value DESC LIMIT 5;
+--------+------------+
| id | Value |
+--------+------------+
| 689410 | 4294936334 |
| 689409 | 4294936333 |
| 689408 | 4294936332 |
| 689407 | 4294936331 |
| 689406 | 4294936330 |
+--------+------------+
5 rows in set (0.30 sec)

mysql> SELECT * FROM t1 ORDER BY Value DESC LIMIT 5;
+--------+------------+
| id | Value |
+--------+------------+
| 689410 | 4294936334 |
| 689409 | 4294936333 |
| 689408 | 4294936332 |
| 689407 | 4294936331 |
| 689406 | 4294936330 |
+--------+------------+
5 rows in set (0.29 sec)

Each result set is returned in roughly 0.3 seconds with only 0.01 difference between the two executions.

Enabling the Query Cache

The query cache size can be changed dynamically by setting the query_cache_size:

SET GLOBAL query_cache_size = 32 * 1024 * 1024;

In MySQL 5.5 and later, it is not possible to enable the query cache dynamically if it query_cache_type is OFF. If this is the case, update the MySQL configuration file and restart MySQL:

[mysqld]
query_cache_size = 32M
query_cache_type = 1

Query Cache Enabled

Running the test query with the query cache enabled gives:

mysql> SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33536856 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM t1 ORDER BY Value DESC LIMIT 5;
+--------+------------+
| id | Value |
+--------+------------+
| 689410 | 4294936334 |
| 689409 | 4294936333 |
| 689408 | 4294936332 |
| 689407 | 4294936331 |
| 689406 | 4294936330 |
+--------+------------+
5 rows in set (0.31 sec)

mysql> SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535320 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM t1 ORDER BY Value DESC LIMIT 5;
+--------+------------+
| id | Value |
+--------+------------+
| 689410 | 4294936334 |
| 689409 | 4294936333 |
| 689408 | 4294936332 |
| 689407 | 4294936331 |
| 689406 | 4294936330 |
+--------+------------+
5 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535320 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

This time the values of the QCache_% status variables are included for each step to show what happens with respect to the query cache.

The important status variables for this test are:

Qcache_hits: The number of times the query result set was fetched from the query cache
Qcache_inserts: The number of times the result set of a query was added to the query cache – this implies the query was not in the query cache already.
The five queries show:

Both the Qcache_hits and Qcache_inserts are 0 before we start the test.
The first execution of the query takes around the same time as before
Qcache_inserts has incremented by 1 – the result of the query was inserted into the query cache.
The second time the query is run, the result is returned immediately
Qcache_hits was incremented meaning the result set was taken from the query cache.

Queries Inside Stored Routine

There is no difference from the query cache point of view whether a query is executed directly or from within a stored routine. As an example consider the following stored procedure:

CREATE PROCEDURE test_query_cache()
SELECT * FROM t1 ORDER BY Value DESC LIMIT 5;

Reset the query cache and the Qcache_% status variables:

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.01 sec)

It is now possible to re-run the test, but running the query through the stored procedure:

mysql> SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33536856 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> CALL test_query_cache();
+--------+------------+
| id | Value |
+--------+------------+
| 689410 | 4294936334 |
| 689409 | 4294936333 |
| 689408 | 4294936332 |
| 689407 | 4294936331 |
| 689406 | 4294936330 |
+--------+------------+
5 rows in set (0.34 sec)

Query OK, 0 rows affected (0.34 sec)

mysql> SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535320 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> CALL test_query_cache();
+--------+------------+
| id | Value |
+--------+------------+
| 689410 | 4294936334 |
| 689409 | 4294936333 |
| 689408 | 4294936332 |
| 689407 | 4294936331 |
| 689406 | 4294936330 |
+--------+------------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535320 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

Which is the same result as when the query was executed directly.

Query Cache Hit Rate

The query cache hit rate is a measure for how often a result set for a SELECT statement can be returned from the query cache. There are several ways to calculate the query cache hit rate. The query cache hit rate as defined in MySQL Enterprise Monitor (MEM) is:

Qcache_hits
Query cache hit rate = ---------------------------- x 100%
Qcache_hits + Qcache_inserts

In the above examples with both Qcache_hits and Qcache_inserts equaling 1, this gives a hit rate of 50%.

Other commonly used definitions of the query cache hit rate include:

Qcache_hits
Query cache hit rate = ------------------------------------------------ x 100%
Qcache_hits + Qcache_inserts + Qcache_not_cached
Qcache_hits
Query cache hit rate = ------------------------ x 100%
Qcache_hits + Com_select

The hit rate dividing with Qcache_hits + Qcache_inserts + Qcache_not_cached is from Workbench, dividing with Qcache_hits + Com_selects is from High Performance MySQL v3 page 321.[This section is not visible to customers.]
The main difference is whether the not cached queries are included.

Limitations of the Query Cache

The query cache was designed for sizes of a few megabytes and use on single or dual core servers.

Locking means that query cache accesses are effectively a single-threaded gateway to the server. This is fine when there are few cores and usually only a few queries running. When a workload has many active cores and routinely has many queries running, the effectively single thread can become a significant performance bottleneck by restricting how quickly queries can enter the server proper. In such cases it is likely to be beneficial to set query_cache_size = 0 and query_cache_type = 0 to eliminate the overhead of the query cache. In MySQL 5.5 and later setting the query_cache_type to 0 before starting the server eliminates more mutex use and overhead than in earlier versions.

Sizes larger than a few megabytes can cause the Qcache_free_blocks count to become large, say over 10,000 entries. A linear search of the free block list is used to find a best fit block for an allocation. The overhead of this increases as the size of the free list increases and shows up as increased user CPU time. If executing the FLUSH QUERY CACHE statement produces a reduction in CPU use that is significant, consider a smaller query cache size or different block size.

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.

Tags:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.