The MySQL Query Cache is a utility that enhances performance by storing results from SELECT statements in memory. This enables rapid retrieval and removes the necessity of repeating the same queries. Although it is deprecated in MySQL 8.0, it is still applicable for earlier versions and can significantly enhance performance in environments where reading operations are predominant. This article provides guidance on setting up, optimising, and troubleshooting the MySQL Query Cache on Ubuntu 24, along with best practices for maximising its efficiency while steering clear of common pitfalls.

Understanding How MySQL Query Cache Functions

The Query Cache functions by retaining entire result sets from SELECT queries in a specific memory area. When a query runs, MySQL checks the cache first for a matching query. If it finds one, it instantly returns the cached result, skipping the parsing, optimisation, and execution steps.

The cache identifies entries using a hash-based system, with the cache key composed of the query string, database name, protocol version, and character set. Thus, even slight variations—like extra spaces or uppercase letters—will generate separate cache entries. The cache is automatically invalidated whenever a table involved in a cached query undergoes changes through INSERT, UPDATE, DELETE, or DDL operations.

The effectiveness of the Query Cache largely depends on how the workload is structured. Applications that are read-heavy and make frequent identical queries benefit significantly, whereas write-heavy systems might suffer from performance hits due to constant cache invalidations and locking issues.

Installing Query Cache on Ubuntu 24: A Detailed Guide

Start by checking your MySQL version and the current status of the Query Cache functionalities. Please note that the Query Cache is applicable only in MySQL 5.7 or prior:

mysql -u root -p -e "SELECT VERSION();"
mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"

Ensure MySQL 5.7 is installed or install it on Ubuntu 24:

sudo apt update
sudo apt install mysql-server-5.7
sudo systemctl start mysql
sudo systemctl enable mysql

Modify the MySQL configuration file to activate the Query Cache:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In the [mysqld] section, add or alter the following Query Cache parameters:

[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M
query_cache_min_res_unit = 4096

To apply these changes, restart MySQL:

sudo systemctl restart mysql

Check that the Query Cache is successfully enabled:

mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"
mysql -u root -p -e "SHOW STATUS LIKE 'Qcache%';"

Configuration Parameters of the Query Cache

Grasping each configuration parameter is essential for fine-tuning the Query Cache suited to your workload:

Parameter	Function	Suggested Values	Effect
query_cache_type	Regulates cache functionality	0 (OFF), 1 (ON), 2 (DEMAND)	0 disables it, 1 enables caching for all SELECT queries, 2 allows caching only for those with the SQL_CACHE option
query_cache_size	Memory space assigned to cache	64M – 512M	Too little diminishes effectiveness, while an excessive amount wastes memory and escalates management complexity
query_cache_limit	Maximum size for a cached result	1M – 4M	Prevents oversized results from monopolising cache space
query_cache_min_res_unit	Smallest memory block size	2048 – 8192	Smaller settings lower memory waste for minor results, while larger ones minimise fragmentation

Adjust settings dynamically without a restart:

mysql -u root -p
SET GLOBAL query_cache_size = 25610241024;
SET GLOBAL query_cache_limit = 410241024;
SET GLOBAL query_cache_type = 1;

Monitoring and Assessing Performance

Keep tabs on the Query Cache performance using the internal status variables:

mysql -u root -p -e "SHOW STATUS LIKE 'Qcache%';"

Key metrics to observe include:

• Qcache_hits: Count of queries served from the cache
• Qcache_inserts: Count of queries added to the cache
• Qcache_t_cached: Number of queries not cached (non-SELECT, too large, etc.)
• Qcache_lowmem_prunes: Cache entries removed due to memory constraints
• Qcache_free_memory: Available memory in the cache
• Qcache_free_blocks: Free memory blocks (indicator of fragmentation)

To compute the Query Cache hit ratio:

mysql -u root -p -e "
SELECT 
  ROUND(Qcache_hits / (Qcache_hits + Com_select) * 100, 2) AS 'Query Cache Hit Ratio %',
  Qcache_hits AS 'Cache Hits',
  Com_select AS 'Total SELECT Queries'
FROM 
  (SELECT VARIABLE_VALUE AS Qcache_hits FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits') AS hits,
  (SELECT VARIABLE_VALUE AS Com_select FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_select') AS selects;
"

For ongoing tracking, create a monitoring script:

#!/bin/bash
# query_cache_monitor.sh
while true; do
echo "=== Query Cache Stats $(date) ==="
mysql -u root -p[password] -e "
SELECT 
VARIABLE_NAME, 
VARIABLE_VALUE 
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME IN ('Qcache_hits', 'Qcache_inserts', 'Qcache_t_cached', 'Qcache_lowmem_prunes');"
sleep 60
done

Practical Examples and Use Cases

The Query Cache is particularly beneficial in certain situations. Here are some practical instances:

E-commerce Product Listings:

-- Often cached queries
SELECT name, price, description FROM products WHERE category_id = 5 AND status="active";
SELECT COUNT(*) FROM products WHERE category_id = 5;
-- Cache invalidation triggers
UPDATE products SET price = 29.99 WHERE id = 1001;  -- Invalidates all cached queries associated with the products table

Content Management System:

-- Repetitive queries that benefit from caching
SELECT title, content, author FROM articles WHERE published = 1 ORDER BY created_at DESC LIMIT 10;
SELECT COUNT(*) FROM comments WHERE article_id = 123 AND approved = 1;
-- Selective caching with SQL_CACHE hint (when query_cache_type = 2)
SELECT SQL_CACHE * FROM static_pages WHERE slug = 'about-us';

Performance comparisons illustrating the impact of Query Cache:

Scenario	Without Query Cache	With Query Cache	Improvement
Basic SELECT (10,000 executions)	2.5 seconds	0.3 seconds	8.3x faster
Complex JOIN (1,000 executions)	15.2 seconds	1.8 seconds	8.4x faster
COUNT query (5,000 executions)	4.1 seconds	0.2 seconds	20.5x faster

Best Practices and Optimisation Suggestions

Enhance the effectiveness of the Query Cache using these well-tested strategies:

• Adjust cache size wisely: Begin with 64-128MB and modify according to hit ratio and memory load.
• Watch for fragmentation: A high Qcache_free_blocks count signals fragmentation issues; consider a smaller query_cache_min_res_unit.
• Maintain consistent query formats: Identical queries should be formatted identically, with attention to whitespace and case.
• Utilise query standardisation: Implement prepared statements or query builders for consistency.
• Explore selective caching options: Apply query_cache_type = 2 with SQL_CACHE hints for precise control over cached queries.

Query Cache performs optimally when:

• The read-to-write ratio exceeds 3:1.
• Queries are frequently repeated with the same parameters.
• Result sets are small to medium-sized (below query_cache_limit).
• Tables are stable and experience infrequent updates.

To disable Query Cache for specific queries, use SQL_NO_CACHE:

SELECT SQL_NO_CACHE * FROM real_time_data WHERE timestamp > NOW() - INTERVAL 1 MINUTE;

Common Challenges and Solutions

Low Hit Ratio: If your hit ratio is consistently below 20%, investigate these potential issues:

• Queries utilise non-deterministic functions such as NOW(), RAND(), or USER()
• Frequent updates to tables lead to constant cache invalidation.
• Variation in queries prevents cache reuse.
• Result sizes exceed the query_cache_limit.

Investigate cache misses through query analysis:

-- Activate general logging to examine query patterns
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file="/var/log/mysql/query_analysis.log";
-- Review queries that have been excluded from caching
SHOW STATUS LIKE 'Qcache_t_cached';

Memory Pressure Issues: A high rate of Qcache_lowmem_prunes suggests the cache size might be insufficient:

-- Examine current memory usage
SHOW STATUS WHERE Variable_name IN ('Qcache_free_memory', 'Qcache_queries_in_cache', 'Qcache_total_blocks');
-- Increase cache size if system memory permits
SET GLOBAL query_cache_size = 51210241024;

Fragmentation Issues: An excessive number of free blocks can lead to fragmentation:

-- Clear and reset the cache to reduce fragmentation
FLUSH QUERY CACHE;
RESET QUERY CACHE;

Performance Drop: The Query Cache may negatively affect performance in write-heavy environments:

-- Monitor the frequency of invalidation
SHOW STATUS LIKE 'Qcache_invalidations';
-- Consider deactivating for applications with heavy write operations
SET GLOBAL query_cache_type = 0;

Modern Alternatives and Strategies

Given that MySQL 8.0 has removed the Query Cache, here are a few alternatives to consider:

Solution	Type	Best Suited For	Complexity Level
Redis	External Cache	Distributed applications requiring complex caching rules	Medium
Memcached	External Cache	High-performance simple key-value caching	Low
Application-Level Caching	Code-based	Custom control with integration of business logic	High
ProxySQL	Database Proxy	Routing queries, pooling connections, and saving query results	Medium

For example, here’s a Redis implementation to cache MySQL query results:

# Install Redis
sudo apt install redis-server
Python example with Redis caching
import redis
import mysql.connector
import json
import hashlib
r = redis.Redis(host="localhost", port=6379, db=0)
def cached_query(query, params=None):
Generate cache key using the query and parameters
cache_key = hashlib.md5(f"{query}{params}".encode()).hexdigest()

# Check the cache first
cached_result = r.get(cache_key)
if cached_result:
    return json.loads(cached_result)

# Execute the query if not cached
conn = mysql.connector.connect(host="localhost", user="root", password='password', database="mydb")
cursor = conn.cursor()
cursor.execute(query, params)
result = cursor.fetchall()

# Cache the result with a TTL of 300 seconds
r.setex(cache_key, 300, json.dumps(result, default=str))

return result

For comprehensive MySQL performance enhancement, refer to the official MySQL Query Cache documentation and contemplate the integration of contemporary caching methodologies alongside optimisations at the database level.

    

This article draws on information and resources from a variety of online sources. We gratefully acknowledge the contributions of all original authors, publishers, and websites. While we endeavour to give appropriate credit to the source material, any accidental oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. Should you believe that any content included in this article infringes on your copyright, please reach out to us immediately for review and corrective action.
This article serves informational and educational purposes and does not infringe on the rights of copyright owners. If any copyrighted material has been utilised without proper attribution or in violation of copyright laws, this is unintentional and will be rectified swiftly upon notification. For permissions or further inquiries, please do contact us.