Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

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;

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:

MetricDescription
Query_Cache_Memory_bytesCurrent memory consumption
Query_Cache_count_GETTotal GET operations
Query_Cache_count_GET_OKSuccessful cache hits
Query_Cache_count_SETTotal SET operations
Query_Cache_bytes_INData written to cache
Query_Cache_bytes_OUTData read from cache
Query_Cache_PurgedEntries purged
Query_Cache_EntriesCurrent 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.