Mysql Query Cache Optimization Review

Query Cache:

my.cnf:
query_cache_type = 1
query_cache_size = 20M
query_cache_limit = 1M

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 537      |
| Qcache_free_memory      | 14197176 |
| Qcache_hits             | 24170    |
| Qcache_inserts          | 4697     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 68       |
| Qcache_queries_in_cache | 2895     |
| Qcache_total_blocks     | 6481     |
+-------------------------+----------+
8 rows in set (0.00 sec)

Query Cache Formulas:

Query cache hit rate = ((Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached))*100)

Query cache use percentage = ((query_cache_size – Qcache_free_memory) / query_cache_size )*100

Average size of the queries = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

Threads:
my.cnf:
thread_cache_size = 30

mysql> SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 91082 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 12    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 12    |
+----------------------+-------+
1 row in set (0.00 sec)

Attention:
Threads_created / Connections should be < 0.01 (if needed increase thread_cache_size)
thread_cache_size should be > Max_used_connections

Thread Formulas:
Thread cache hit rate = 100 – ((Threads_created / Connections) * 100)

Connection Formulas:

Percentage of used connections: processlist / MAX_CONNECTIONS

mysql> select (( pl.connections / gv.max_connections ) * 100) as percentage_used_connections from ( select count(*) as connections from information_schema.processlist ) as pl, ( select VARIABLE_VALUE as max_connections from information_schema.global_variables where variable_name = 'MAX_CONNECTIONS' ) as gv;

+-----------------------------+
| percentage_used_connections |
+-----------------------------+
|          0.6622516556291391 |
+-----------------------------+
1 row in set (0.00 sec)

Maintenance

mysql> RESET QUERY CACHE; 

will clear out the query cache

if Qcache_free_blocks approaches Qcache_total_blocks / 2, your query cache is severely fragmented.

mysql> FLUSH QUERY CACHE; 

does NOT clear out the query cache but rather defrags the cache and leaving the cached query results in place

Problem:
Qcache_lowmem_prunes value is increasing and you have a lot of Qcache_free_blocks
Reason 1:
Fragmentation is causing queries to be deleted from the cache.
In this case, increase query_cache_limit or query_cache_size, or both of them.
Reason 2:
The query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable (default value 4096 bytes). When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit:

If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit (minimum value 512 bytes). The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

To change in runtime

mysql> Set global query_cache_min_res_unit = 1024;

To change in my.cnf

query_cache_min_res_unit = 1024

mysql> show variables like "query%";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_alloc_block_size       | 8192     |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 1024     |
| query_cache_size             | 20971520 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
| query_prealloc_size          | 8192     |
+------------------------------+----------+
7 rows in set (0.00 sec)