Understanding MySQL Query Cache Configuration
MySQL's query cache stores SELECT statement results in memory. This cuts execution time dramatically for repeated queries. You'll see immediate performance gains on read-heavy applications where the same queries run over and over.
The cache works by storing complete result sets alongside their SQL statements. When MySQL sees an identical query, it returns cached results instead of re-executing the statement.
This proves especially valuable for WordPress sites, e-commerce platforms, and content management systems. Modern MySQL installations often disable query cache by default due to concurrency issues.
Still, proper MySQL query cache configuration delivers substantial performance improvements for the right workloads.
Prerequisites and System Requirements
Your Linux VPS needs MySQL 5.7 or earlier for query cache functionality. MySQL 8.0 removed query cache entirely, so this tutorial focuses on compatible versions.
Check your current version:
mysql --version
You'll need root access to modify MySQL configuration files. Most HostMyCode VPS plans include full root access for complete system control.
Make sure your VPS has adequate memory for query cache allocation. A typical setup requires 32-256 MB depending on your application's query patterns and traffic volume.
Enabling Query Cache in my.cnf
Open your MySQL configuration file, typically at /etc/mysql/my.cnf or /etc/my.cnf:
sudo nano /etc/mysql/my.cnf
Add these query cache settings under the [mysqld] section:
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
query_cache_min_res_unit = 2048
The query_cache_type = 1 enables caching for all SELECT statements except those using SQL_NO_CACHE. Set query_cache_size between 32M and 256M based on available memory.
The query_cache_limit prevents oversized results from consuming cache space.
Restart MySQL to apply changes:
sudo systemctl restart mysql
Optimal Memory Allocation Strategy
Query cache memory allocation requires careful balance. Too little provides minimal benefit. Too much can actually hurt performance through overhead.
Start with 64MB for low-traffic sites handling under 1000 daily visitors. Medium-traffic applications benefit from 128MB allocation. High-traffic sites may require 256MB or more.
Check your current memory usage before adjusting cache size:
free -h
Reserve at least 25% of total system memory for other MySQL operations and system processes. A 2GB VPS shouldn't allocate more than 256MB to query cache.
Configuration Parameter Tuning
Fine-tune these additional parameters for optimal performance:
query_cache_wlock_invalidate = 0
query_cache_strip_comments = 1
The query_cache_min_res_unit setting controls memory block allocation. Default 4KB blocks work well for most queries. Reduce to 2KB if your application generates many small result sets.
Set query_cache_strip_comments = 1 to treat queries with different comments as identical. This increases cache hit rates when applications add timestamp comments to queries.
Verify your configuration loaded correctly:
mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"
Monitoring Cache Performance
Track query cache effectiveness using MySQL status variables. Connect to MySQL and examine cache statistics:
SHOW STATUS LIKE 'Qcache%';
Key metrics include:
Qcache_hits- Queries served from cacheQcache_inserts- New queries added to cacheQcache_not_cached- Queries that bypassed cacheQcache_free_memory- Available cache space
Calculate hit ratio with this formula: Qcache_hits / (Qcache_hits + Com_select) * 100
A healthy hit ratio exceeds 25% for most applications. Ratios below 15% suggest query cache provides minimal benefit.
Identifying Cache-Friendly Queries
Not all queries benefit from caching. SELECT statements with frequently changing data create cache invalidation overhead.
Examine your slow query log to identify candidates:
sudo tail -f /var/log/mysql/mysql-slow.log
Ideal queries for caching include:
- Configuration lookups
- Category listings
- User permission checks
- Static content queries
Avoid caching queries that include NOW(), RAND(), or user-specific WHERE clauses. These rarely produce cache hits due to their dynamic nature.
Cache Invalidation Behavior
MySQL invalidates cached results whenever related tables change. Any INSERT, UPDATE, or DELETE operation clears all cached queries for affected tables.
This aggressive invalidation prevents stale data but can reduce cache effectiveness on write-heavy applications.
Monitor Qcache_lowmem_prunes to detect frequent cache clearing:
SHOW STATUS LIKE 'Qcache_lowmem_prunes';
High prune rates indicate insufficient cache memory or excessive table modifications. Consider increasing cache size or implementing application-level caching for frequently updated tables.
Troubleshooting Common Issues
Query cache problems often show up as poor hit rates or memory exhaustion. First, verify cache activation:
SHOW VARIABLES LIKE 'have_query_cache';
If this returns 'NO', your MySQL build lacks query cache support. Reinstall MySQL with query cache enabled or upgrade your managed VPS hosting plan for better MySQL support.
Zero cache hits despite enabled cache usually indicates:
- Queries contain non-deterministic functions
- Result sets exceed query_cache_limit
- Tables receive frequent updates
- Binary logging conflicts with cache settings
Check for binary logging conflicts:
SHOW VARIABLES LIKE 'log_bin';
Performance Testing and Validation
Measure query cache impact using MySQL's built-in profiling. Enable profiling for your session:
SET profiling = 1;
Execute identical queries multiple times, then examine execution profiles:
SHOW PROFILES;
The first execution shows normal query time. Subsequent executions should show significantly reduced duration when served from cache.
Use tools like mysqltuner for comprehensive performance analysis:
wget mysqltuner.pl
perl mysqltuner.pl
This script provides recommendations for query cache sizing and identifies potential configuration improvements.
Integration with Application Frameworks
Popular PHP frameworks require specific considerations for query cache optimization. WordPress benefits from query cache when using standard database queries, but custom post queries may bypass cache entirely.
Laravel's Eloquent ORM sometimes generates queries with unique identifiers that prevent cache hits. Consider using raw queries for frequently repeated database operations.
For optimal results, review your application's database query patterns. Look for opportunities to standardize query formats and eliminate unnecessary query variations.
Our database query caching strategies guide covers application-level optimization techniques.
Frequently Asked Questions
Can I use MySQL query cache with MySQL 8.0?
No, MySQL 8.0 completely removed query cache functionality. Use application-level caching solutions like Redis or Memcached instead.
How much memory should I allocate to query cache?
Start with 64MB for small sites, 128MB for medium traffic, and up to 256MB for high-traffic applications. Never exceed 25% of total system memory.
Why is my query cache hit rate still low after configuration?
Low hit rates often result from frequent table updates, non-deterministic queries, or result sets exceeding query_cache_limit. Review your query patterns and table modification frequency.
Should I disable query cache for write-heavy applications?
Yes, applications with frequent INSERT, UPDATE, or DELETE operations may perform better without query cache due to constant cache invalidation overhead.
Can query cache improve WordPress performance?
Query cache helps WordPress sites with standard queries, but many WordPress queries include dynamic elements that prevent caching. Combine with Redis for better results.