Query Cache in ProxySQL
ProxySQL has a built-in query cache that stores query results in memory and serves them directly without hitting the backend MySQL server. This can dramatically reduce database load for frequently executed read queries with relatively stable results.
⚠ Warning: The ProxySQL query cache works at the query level — it stores the raw resultset bytes. It does NOT understand table-level invalidation. You must set appropriate TTLs or manage cache invalidation manually.
How Cache TTL Works
Cache TTL (Time To Live) is set in milliseconds per query rule. When a matching query is executed:
- ProxySQL checks if a cached result exists and is within TTL
- If cache HIT — result returned immediately from memory, MySQL not contacted
- If cache MISS — query sent to MySQL, result stored in cache with TTL
- After TTL expires — next execution fetches fresh result from MySQL
Enabling Query Cache via Query Rules
SQL — Enable Query Cache
-- Cache all SELECTs from a specific table for 5 seconds
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, cache_ttl, destination_hostgroup, apply, comment)
VALUES
(100, 1, 'SELECT .* FROM products', 5000, 20, 1, 'Cache products SELECTs 5s');
-- Cache a specific query for 60 seconds
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, cache_ttl, destination_hostgroup, apply, comment)
VALUES
(101, 1, 'SELECT COUNT\(\*\) FROM orders', 60000, 20, 1, 'Cache order count 60s');
-- Cache lookup table queries for 5 minutes
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, cache_ttl, destination_hostgroup, apply, comment)
VALUES
(102, 1, 'SELECT .* FROM (countries|currencies|config)', 300000, 20, 1, 'Cache static lookup tables 5min');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Query Cache Variables
SQL — Cache Variables
-- Set max query cache size (bytes) — default 128MB
UPDATE global_variables SET variable_value='134217728'
WHERE variable_name='mysql-query_cache_size_MB';
-- Store results even when resultset is empty
UPDATE global_variables SET variable_value='1'
WHERE variable_name='mysql-query_cache_stores_empty_result';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
-- View cache variables
SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name LIKE '%query_cache%'; Monitoring Cache Performance
SQL — Cache Stats
-- Cache hit/miss stats
SELECT variable_name, variable_value
FROM stats.stats_mysql_global
WHERE variable_name IN (
'Query_Cache_Memory_bytes',
'Query_Cache_count_GET',
'Query_Cache_count_GET_OK',
'Query_Cache_count_SET',
'Query_Cache_bytes_IN',
'Query_Cache_bytes_OUT',
'Query_Cache_Purged',
'Query_Cache_Entries'
);
-- Calculate cache hit ratio
SELECT
variable_value AS total_gets
FROM stats.stats_mysql_global
WHERE variable_name = 'Query_Cache_count_GET';
SELECT
variable_value AS cache_hits
FROM stats.stats_mysql_global
WHERE variable_name = 'Query_Cache_count_GET_OK';
-- Which queries are being cached (check rule hits)
SELECT rule_id, hits, cache_ttl, match_pattern, comment
FROM stats.stats_mysql_query_rules
WHERE cache_ttl IS NOT NULL
ORDER BY hits DESC; What to Cache — Best Practices
| Good to Cache | Do NOT Cache |
|---|---|
| Country/currency/config lookup tables | User-specific queries |
| Aggregation counts refreshed periodically | Queries involving NOW(), RAND() |
| Product catalogue (slow-changing) | Inventory/balance queries |
| Public content (blog posts, pages) | Queries after writes (stale risk) |
| Frequently repeated identical queries | Queries with user-data in resultset |
Cache Invalidation
SQL — Cache Invalidation
-- Clear the entire query cache
SELECT variable_name, variable_value
FROM stats.stats_mysql_global
WHERE variable_name = 'Query_Cache_Entries';
-- To flush query cache, update the rule TTL or disable temporarily
UPDATE mysql_query_rules SET cache_ttl=0 WHERE rule_id=100;
LOAD MYSQL QUERY RULES TO RUNTIME;
-- Then re-enable
UPDATE mysql_query_rules SET cache_ttl=5000 WHERE rule_id=100;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;💡 Note: ProxySQL does not support automatic cache invalidation on write. Set TTL values carefully based on how often your data changes.
