Problem Statement:
Many PHP developers using MySQL have unclear understanding of the MySQL query cache. So we decided to write a series of introductory articles to get everyone on the same page. This article is the first installment of the series and here we will introduce the basics of query cache in MySQL. Note that unlike a typical book chapter, this article will be of low-fat flavor — less theory and more actionables — of an introduction to query caching for MySQL.
What is a MySQL query cache?
It turns out that MySQL has a built-in query cache that can cache a specific type of queries — SELECT statements — to speed up delivery of the result sets. The cache can increase performance for many instances but can also hurt performance if not used wisely.
What can be cached in the MySQL query cache?
Only SELECT statements can be cached. This does not include prepared SELECT statements. Query caching only works for SELECT statements that are fully qualified and returns same result every time. This means you cannot use non deterministic functions that return data depending on situation. For example:
// Following SELECT query can be cached
$stmt = "SELECT * FROM user WHERE active = 1";
// Following SELECT query cannot be cached
$stmt = "SELECT * FROM user where signup_date >= NOW()";
// Following SELECT query cannot be cached
$stmt = "SELECT count(*) FROM user";
Here are the requirements a query must meet to take advantage of the query cache:
- Only exact queries are serviced from the cache — must match the stored query in exact detail.
- Queries with placeholders — such as the ones for prepared statements — are not cached in query cache
- Queries with user defined functions or non-deterministic functions cannot be cached
- Any table changes (such as issuing of an ALTER statement) will remove the queries from the cache for that table
Introduction to query cache parameters
The more you understand the query caching parameters, the better you are going to be at tuning the query cache to your advantage. First find out what are the global query caching parameters that you can fiddle with using the following query at the mysql command-line prompt.
mysql> show global variables like '%query_cache%';
A sample output is shown below:
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 536870912 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
6 rows in set (0.00 sec)
The purpose of these parameters are described briefly as:
- have_query_cache - size of query cache in bytes
- query_cache_limit - the maximum size of result set (default: 1048576 bytes or 1 MB). If your query returns result set that is greater than the limit set here, it will NOT BE CACHED
- query_cache_min_res_unit - the smallest block size allocated by query cache. Default is 4KB
- query_cache_size - the total memory available to query cache
- query_cache_type - when set to ON or 1, query caching is on for all applicable queries, when set to OFF (0) query caching is turned off and when set to DEMAND or 2, caching is on for queries with SQL_CACHE directive in the query
- query_cache_wlock_invalidate-causes the query cache to invalidate any query in the cache if a write lock is executed against the table(s) it uses
Whats your query cache status right now?
To find out whats going on with your query cache, run the following command from the MySQL command-line prompt:
mysql> show status like '%qc%';
Here is a sample result:
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 978 |
| Qcache_free_memory | 527371984 |
| Qcache_hits | 645545 |
| Qcache_inserts | 130796 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 417579 |
| Qcache_queries_in_cache | 4973 |
| Qcache_total_blocks | 11167 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
Here are some brief explanations of these status metrics:
- Qcache_free_blocks - number of memory blocks marked as free, which indicates memory fragmentation
- Qcache_free_memory - total amount of memory free for query cache
- Qcache_hits - number of times query result was found in the query cache
- Qcache_inserts - number of times queries were written to the query cache
- Qcache_not_cached - number of queries removed from cache due to low cache memory
- Qcache_queries_in_cache - number of queries that could not be cached
- Qcache_total_block - total number of blocsk in query cache
Calculating query cache hits vs misses
Here is the formula for calculating hit ratio for query cache:
$totalSelectQueryCount = $comSelect + $qcacheHits
$percentHits = ($qcacheHits * 100)/ $totalSelectQueryCount
What the above formula does is adds up all the SELECT queries in the system using two MySQL global variables: com_select and qcache_hits.
To set $comSelect, run show global status like '%com_select%' query. For example:
mysql> show global status like '%com_select%';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_select | 1739663 |
+---------------+---------+
To set $qcacheHits, run show status like '%qcache_hit%'. For example:
mysql> show status like '%qcache_hit%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Qcache_hits | 20786961 |
+---------------+----------+
With the above sample number, the percent hit is 92.28% which is great.
Managing query cache
To manipulate your query cache, you can use the following MySQL statements from the MySQL command-line:
To remove all the queries from your query cache, run:
RESET QUERY CACHE;
To defragment the query cache memory, run:
FLUSH QUERY CACHE;
