MySQL Query Cache Optimization Techniques

MySQL Query Cache Optimization Techniques

The MySQL Query Cache was a feature that stored the text of a SELECT statement along with its result set. When an identical query was executed, MySQL could return the cached result immediately without re-executing the query. However, understanding its behavior, limitations, and modern alternatives is crucial for database optimization.

⚠️ Important Notice: Query Cache in MySQL 8.0+

Warning: The Query Cache was deprecated in MySQL 5.7.20 and removed entirely in MySQL 8.0. If you're running MySQL 8.0+, skip to the Modern Alternatives section.

This guide covers:

1. Query Cache configuration for MySQL 5.7 and earlier
2. Why Query Cache was removed
3. Modern caching alternatives for MySQL 8.0+

🎯 Understanding the Query Cache

How It Worked

┌─────────────────────────────────────────────────────────────────────┐
│                    QUERY CACHE FLOW                                  │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│     Client Query                                                     │
│         │                                                            │
│         ▼                                                            │
│   ┌─────────────┐                                                    │
│   │ Query Cache │──── Cache HIT ────►  Return Cached Result         │
│   │   Lookup    │                                                    │
│   └──────┬──────┘                                                    │
│          │                                                           │
│     Cache MISS                                                       │
│          │                                                           │
│          ▼                                                           │
│   ┌─────────────┐                                                    │
│   │   Parser    │                                                    │
│   └──────┬──────┘                                                    │
│          ▼                                                           │
│   ┌─────────────┐                                                    │
│   │  Optimizer  │                                                    │
│   └──────┬──────┘                                                    │
│          ▼                                                           │
│   ┌─────────────┐                                                    │
│   │  Executor   │                                                    │
│   └──────┬──────┘                                                    │
│          ▼                                                           │
│   Store in Cache ◄─── If cacheable                                  │
│          │                                                           │
│          ▼                                                           │
│   Return Result                                                      │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Query Cache Behavior

🔧 Configuration (MySQL 5.7 and Earlier)

Basic Configuration

# /etc/mysql/mysql.conf.d/query_cache.cnf

[mysqld]
# Enable query cache
query_cache_type = 1

# Cache size (start with 64MB for most workloads)
query_cache_size = 67108864

# Minimum result size to cache
query_cache_min_res_unit = 4096

# Maximum single query result size
query_cache_limit = 1048576

# Don't cache results larger than this
query_cache_wlock_invalidate = OFF

Configuration Options Explained

-- Check current configuration
SHOW VARIABLES LIKE 'query_cache%';

Dynamic Configuration

-- Enable query cache at runtime
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 67108864;

-- Force a query to use cache (when type=2/DEMAND)
SELECT SQL_CACHE * FROM products WHERE category_id = 5;

-- Force a query to NOT use cache
SELECT SQL_NO_CACHE * FROM products WHERE category_id = 5;

📊 Monitoring Query Cache Performance

Essential Monitoring Queries

-- Query cache status overview
SHOW STATUS LIKE 'Qcache%';

-- Calculate hit ratio
SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_hits') AS hits,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_inserts') AS inserts,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_not_cached') AS not_cached,
    ROUND(
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Qcache_hits') /
        ((SELECT VARIABLE_VALUE FROM performance_schema.global_status
          WHERE VARIABLE_NAME = 'Qcache_hits') +
         (SELECT VARIABLE_VALUE FROM performance_schema.global_status
          WHERE VARIABLE_NAME = 'Com_select')) * 100, 2
    ) AS hit_ratio_percent;

-- Check fragmentation
SELECT
    VARIABLE_VALUE AS free_blocks
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Qcache_free_blocks';

-- If free_blocks is high relative to total_blocks, defragment:
FLUSH QUERY CACHE;

-- Completely clear the cache
RESET QUERY CACHE;

Interpreting Query Cache Status

-- Detailed query cache analysis
SELECT
    'Total Queries' AS metric,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_hits') +
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Com_select') AS value
UNION ALL
SELECT 'Cache Hits',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_hits')
UNION ALL
SELECT 'Cache Inserts',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_inserts')
UNION ALL
SELECT 'Queries in Cache',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_queries_in_cache')
UNION ALL
SELECT 'Cache Size (MB)',
    ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_variables
           WHERE VARIABLE_NAME = 'query_cache_size') / 1024 / 1024, 2)
UNION ALL
SELECT 'Free Memory (MB)',
    ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'Qcache_free_memory') / 1024 / 1024, 2)
UNION ALL
SELECT 'Lowmem Prunes',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Qcache_lowmem_prunes');

Health Check Thresholds

⚠️ Why Query Cache Was Removed

The Scalability Problem

┌─────────────────────────────────────────────────────────────────────┐
│                    QUERY CACHE MUTEX CONTENTION                      │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  Thread 1 ─────┐                                                     │
│                │                                                     │
│  Thread 2 ─────┼────► GLOBAL MUTEX ────► Query Cache                │
│                │      (Serialized!)                                  │
│  Thread 3 ─────┘                                                     │
│                                                                      │
│  Problem: Only ONE thread can access the cache at a time            │
│  Result: Severe bottleneck on multi-core systems                    │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Key Problems

When Query Cache Hurts Performance

-- Example: Write-heavy table
-- Every INSERT invalidates all cached queries for this table
INSERT INTO orders (customer_id, total) VALUES (123, 99.99);
-- All cached SELECT queries on 'orders' are now invalidated

-- Example: High-concurrency reads
-- Multiple threads competing for cache mutex
-- Can be slower than no cache at all!

🚀 Modern Alternatives for MySQL 8.0+

Alternative 1: Application-Level Caching with Redis

# Python example with Redis caching
import redis
import mysql.connector
import json
import hashlib

class MySQLRedisCache:
    def __init__(self):
        self.redis = redis.Redis(host='localhost', port=6379, db=0)
        self.mysql = mysql.connector.connect(
            host='localhost',
            user='app_user',
            password='password',
            database='production'
        )
        self.default_ttl = 300  # 5 minutes

    def _get_cache_key(self, query, params):
        """Generate unique cache key for query"""
        key_string = f"{query}:{json.dumps(params, sort_keys=True)}"
        return f"mysql_cache:{hashlib.md5(key_string.encode()).hexdigest()}"

    def query(self, sql, params=None, ttl=None):
        """Execute query with Redis caching"""
        cache_key = self._get_cache_key(sql, params or [])

        # Try cache first
        cached = self.redis.get(cache_key)
        if cached:
            return json.loads(cached)

        # Execute query
        cursor = self.mysql.cursor(dictionary=True)
        cursor.execute(sql, params)
        result = cursor.fetchall()
        cursor.close()

        # Store in cache
        self.redis.setex(
            cache_key,
            ttl or self.default_ttl,
            json.dumps(result, default=str)
        )

        return result

    def invalidate_table(self, table_name):
        """Invalidate all cached queries for a table"""
        # Use Redis patterns or maintain a table->keys mapping
        pattern = f"mysql_cache:*{table_name}*"
        for key in self.redis.scan_iter(match=pattern):
            self.redis.delete(key)

# Usage
cache = MySQLRedisCache()
products = cache.query(
    "SELECT * FROM products WHERE category_id = %s",
    [5],
    ttl=600  # Cache for 10 minutes
)

Alternative 2: ProxySQL Query Cache

-- ProxySQL provides a more intelligent query cache
-- Connect to ProxySQL admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Enable query caching for specific patterns
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    cache_ttl,      -- TTL in milliseconds
    destination_hostgroup,
    apply
) VALUES (
    100,
    1,
    '^SELECT.*FROM products WHERE category_id',
    60000,  -- 60 seconds
    20,
    1
);

-- Cache product catalog (rarely changes)
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    cache_ttl,
    destination_hostgroup,
    apply
) VALUES (
    101,
    1,
    '^SELECT.*FROM product_categories',
    300000,  -- 5 minutes
    20,
    1
);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- Monitor cache performance
SELECT * FROM stats_mysql_query_digest
ORDER BY sum_time DESC;

Alternative 3: MySQL InnoDB Buffer Pool Optimization

# /etc/mysql/mysql.conf.d/innodb_optimization.cnf

[mysqld]
# Size buffer pool to 70-80% of available RAM
innodb_buffer_pool_size = 12G

# Multiple buffer pool instances for concurrency
innodb_buffer_pool_instances = 8

# Dump and reload buffer pool on restart
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# Optimize for SSDs
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Reduce disk I/O
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

-- Monitor buffer pool efficiency
SELECT
    POOL_ID,
    POOL_SIZE,
    FREE_BUFFERS,
    DATABASE_PAGES,
    PAGES_MADE_YOUNG,
    PAGES_NOT_MADE_YOUNG,
    PAGES_READ,
    ROUND(DATABASE_PAGES / POOL_SIZE * 100, 2) AS pool_utilization_pct
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- Check buffer pool hit ratio
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- Calculate hit ratio
SELECT
    (1 - (
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )) * 100 AS buffer_pool_hit_ratio;

Alternative 4: Query Result Caching with Memcached

-- MySQL InnoDB Memcached Plugin (still available in MySQL 8.0)

-- Install the plugin
INSTALL PLUGIN daemon_memcached SONAME "libmemcached.so";

-- Create cache container table
CREATE TABLE cache_table (
    c1 VARCHAR(255) NOT NULL PRIMARY KEY,
    c2 MEDIUMBLOB,
    c3 INT,
    c4 BIGINT UNSIGNED,
    c5 INT,
    INDEX idx_c3(c3)
) ENGINE=InnoDB;

-- Register with memcached
INSERT INTO innodb_memcache.containers (
    name, db_schema, db_table, key_columns, value_columns,
    flags, cas_column, expire_time_column, unique_idx_name_on_key
) VALUES (
    'default', 'mydb', 'cache_table', 'c1', 'c2',
    'c3', 'c4', 'c5', 'PRIMARY'
);

# Access cached data via Memcached protocol
import pymemcache

client = pymemcache.Client('localhost:11211')

# Store data
client.set('user:123', '{"name": "John", "email": "[email protected]"}')

# Retrieve data (backed by InnoDB!)
user = client.get('user:123')

📈 Performance Comparison

Benchmark Results

*With low-write workload; degrades rapidly with writes

✅ Best Practices for Caching

When to Use Each Approach

Caching Strategy Checklist

• [ ] Identify cacheable queries (repeated, stable results)
• [ ] Determine appropriate TTL for each data type
• [ ] Implement cache invalidation strategy
• [ ] Monitor cache hit ratios
• [ ] Set up cache warming for critical data
• [ ] Plan for cache failures (graceful degradation)

Cache Invalidation Patterns

# Pattern 1: Time-based expiration
cache.set('products:category:5', products, ttl=300)

# Pattern 2: Event-based invalidation
def on_product_update(product_id):
    cache.delete(f'product:{product_id}')
    cache.delete(f'products:category:{product.category_id}')

# Pattern 3: Version-based cache keys
def get_products(category_id):
    version = cache.get(f'products:version') or 1
    cache_key = f'products:category:{category_id}:v{version}'
    # Increment version on any product change to invalidate all

# Pattern 4: Tag-based invalidation (Redis)
import redis
r = redis.Redis()

def cache_with_tags(key, value, tags, ttl):
    r.setex(key, ttl, value)
    for tag in tags:
        r.sadd(f'tag:{tag}', key)

def invalidate_tag(tag):
    keys = r.smembers(f'tag:{tag}')
    if keys:
        r.delete(*keys)
    r.delete(f'tag:{tag}')

🎓 Conclusion

While the MySQL Query Cache served its purpose in earlier versions, modern MySQL deployments benefit from more scalable caching solutions. Whether you choose ProxySQL, Redis, or optimize your InnoDB Buffer Pool, the key is matching your caching strategy to your workload characteristics.

💡 Pro Tip: For most modern applications, a combination of well-tuned InnoDB Buffer Pool and application-level caching (Redis/Memcached) provides the best performance and flexibility.

Key Takeaways

1. Query Cache is removed in MySQL 8.0 - don't rely on it for new deployments
2. InnoDB Buffer Pool is often sufficient for many workloads
3. ProxySQL provides intelligent query caching at the proxy layer
4. Redis/Memcached offer the most flexibility for application-level caching
5. Monitor cache effectiveness - not all caching improves performance

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Related Articles

• MySQL Performance Schema Monitoring
• MySQL Index Optimization Strategies
• MySQL Connection Pool Configuration

Need help with MySQL performance optimization? Contact us for expert database consulting.