Query Cache
Overview
ProxySQL implements query caching on the wire, storing result sets during query execution. When an application repeats an identical query, the cached result can be returned without accessing the backend.
Configuration
Both mysql_query_rules and pgsql_query_rules support the same cache-rule fields:
cache_ttlis a positive cache lifetime in milliseconds.cache_empty_resultcontrols whether an empty result set is cached:1caches it and0does not. When the field isNULL, the protocol’s*-query_cache_stores_empty_resultsetting determines the behavior.
-- MySQL: cache matching queries for five seconds, including empty result sets.
INSERT INTO mysql_query_rules (rule_id, active, match_digest, cache_ttl, cache_empty_result, apply)
VALUES (1, 1, '^SELECT', 5000, 1, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- PostgreSQL: cache matching queries for five seconds, excluding empty result sets.
INSERT INTO pgsql_query_rules (rule_id, active, match_digest, cache_ttl, cache_empty_result, apply)
VALUES (1, 1, '^SELECT', 5000, 0, 1);
LOAD PGSQL QUERY RULES TO RUNTIME;
SAVE PGSQL QUERY RULES TO DISK;
Prepared-query results
ProxySQL 3.1.12 and 4.0.12 add experimental result caching for eligible MySQL binary prepared executions and PostgreSQL extended-query executions. There is no separate enable switch: existing positive-TTL rules can select eligible prepared requests after upgrading. Stable 3.0.12 does not include these paths.
Use the protocol-specific guides for supported client exchanges, bypass conditions, scoped rules, and observation:
Result caching saves returned rows. The separate prepared-statement metadata caches reduce preparation work; using a prepared statement does not itself make its result safe to cache.
Soft TTL
Both protocols support a soft-TTL percentage: mysql-query_cache_soft_ttl_pct and
pgsql-query_cache_soft_ttl_pct. Set either value to a percentage strictly between 0 and 100 to create a
soft threshold within the rule’s cache_ttl; the released default is 0, which disables soft-TTL refresh.
After an entry reaches its soft threshold but before its hard cache_ttl expires, the first requester marks
the entry for refresh and fetches a new result from the backend. While that refresh is in progress, concurrent
requesters can continue to receive the still-valid cached result. The hard cache_ttl remains the expiration
boundary: after it expires, the entry is no longer returned from cache.
UPDATE global_variables
SET variable_value='80'
WHERE variable_name='mysql-query_cache_soft_ttl_pct';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
UPDATE global_variables
SET variable_value='80'
WHERE variable_name='pgsql-query_cache_soft_ttl_pct';
LOAD PGSQL VARIABLES TO RUNTIME;
SAVE PGSQL VARIABLES TO DISK;
Memory Configuration
Administrators adjust cache memory allocation with mysql-query_cache_size_MB and
pgsql-query_cache_size_MB. The implementation uses soft limits rather than strict hard enforcement, with a
background purging thread managing eviction.
SET mysql-query_cache_size_MB=512;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
Cache Metrics
Query these counters through ProxySQL Admin in stats_mysql_global or
stats_pgsql_global:
SELECT Variable_Name, Variable_Value
FROM stats_mysql_global
WHERE Variable_Name LIKE 'Query_Cache%';
The counters include:
| Metric | Description |
|---|---|
Query_Cache_Memory_bytes | Current memory consumption |
Query_Cache_count_GET | Total GET operations |
Query_Cache_count_GET_OK | Successful cache hits |
Query_Cache_count_SET | Total SET operations |
Query_Cache_bytes_IN | Data written to cache |
Query_Cache_bytes_OUT | Data read from cache |
Query_Cache_Purged | Entries purged |
Query_Cache_Entries | Current entries |
Known Limitations
- Writes do not invalidate stored results. A cached answer can remain visible until its TTL expires or the cache is flushed. Keep reads that must confirm a write immediately on an uncached path.
- Prepared-result caching requires 3.1.12 or 4.0.12 and supports only the eligible requests described in the protocol-specific guides above.
- Memory limits are not strictly enforced.
- No LRU-based eviction when limits are reached.