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_ttl is a positive cache lifetime in milliseconds.
• cache_empty_result controls whether an empty result set is cached: 1 caches it and 0 does not. When the field is NULL, the protocol’s *-query_cache_stores_empty_result setting 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;

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

Statistics available in stats_mysql_query_cache include:

Known Limitations

• No invalidation mechanism beyond TTL expiration; stale data can persist until the hard TTL expires.
• Incompatible with prepared statements.
• Memory limits are not strictly enforced.
• No LRU-based eviction when limits are reached.