MySQL Query Cache

Introduction to MySQL Query Cache: 

Today morning, I was monitoring MySQL Production database.

I found out the list of running sessions with different SQL queries. I created some master and primary tables in MySQL which stored settings and configuration data for one of our Mobile App.

As these tables are for configuration purpose so not frequently update by the application.

But today, I was worry because of this configuration table was utilizing more CPU I/O. I created this all tables under InnoDB MySQL Storage engine.

I cannot store all this data under Memory engine because this all are primary data for our Application.

So I was finding a solution for faster retrieval and found that MySQL has a good option called a Query Cache.

In this post, I am sharing theory of MySQL Query Cache and I will share the second part of the practical configuration of MySQL Query Cache by tomorrow.

What is MySQL Query Cache ?
Sort & Simple, MySQL Query Cache stores some frequent SELECT statements with its corresponding results.

When table data are not changing frequently and the server receives many identical queries then only Query Cache returns data from the Cache area.

Whenever table modified with data, then Query cache will be flush.

I surprised by this feature of MySQL which improve your query performance in some circumstances.

When you trigger any select statement and if it is available in the cache, it will return from there and not require parsing and fetching from disk so it improves a query performance.

Please do not assume that this will work under all circumstances because this also depends on your load of memory and server.

You can also set the size for Query Cache but make sure that it should not be higher otherwise its create memory overhead for the whole server.

Few conditions in which Query Cache will not work:

• Not use when multiple server hitting to one query.
• Not use with Stored Procedure and UDF.
• Not use with any type of Variables.
• Not use with any table of default database like, MySQL, INFORMATION_SCHEMA.
• Not use with partition tables.
• Not use with Temporary tables.
• Not use with Prepared Statements.

In this post, I am sharing the details on Query Cache configuration and monitoring.

The first check, Query Cache is available or not.

Please execute below SHOW command:

1 2 3 4 5 6

mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | have_query_cache | YES   | +------------------+-------+

To disable the query cache at server startup, set the query_cache_size system variable to 0. By default, the query cache is disabled.

Using query_cache_size you can set the size of Query Cache which must be larger when you are planning to hold more data into Cache but again this is a task of analysis of your memory usage.

1 2 3 4 5 6 7 8 9 10

mysql> SET GLOBAL query_cache_size = 1000000; Query OK, 0 rows affected (0.04 sec) mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+--------+ | Variable_name    | Value  | +------------------+--------+ | query_cache_size | 999424 | +------------------+--------+ 1 row in set (0.00 sec)

Query Cache with SELECT :

1 2	SELECT SQL_CACHE * FROM tbl_users; SELECT SQL_NO_CACHE * FROM tbl_users;

In the above two select statements, you can find two identifiers.

SQL_CACHE

The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.

SQL_NO_CACHE

The server does not use the query cache. It neither checks the query cache to see whether the result is already cached nor does it cache the query result.

1	SHOW VARIABLES LIKE 'query_cache_type';

Using query_cache_type you can set the type of query cache. It should be ON, OFF or DEMAND.

• A value of 0 or OFF prevents caching or retrieval of cached results.
• A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.
• A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

To monitor query cache performance, use SHOW STATUS to view the cache status variables:

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+--------+ | Variable_name           | Value  | +-------------------------+--------+ | Qcache_free_blocks      | 36     | | Qcache_free_memory      | 138488 | | Qcache_hits             | 79570  | | Qcache_inserts          | 27087  | | Qcache_lowmem_prunes    | 3114   | | Qcache_not_cached       | 22989  | | Qcache_queries_in_cache | 415    | | Qcache_total_blocks     | 912    | +-------------------------+--------+