Historically, there are 2 ways of using caching in a MySQL environment:

  • enable MySQL Query Cache : embedded in MySQL server itself, no external dependencies. Although it is a bottleneck for any write-intensive workload because cache entries are invalidated when the relevant table receives a write.
  • use external caching : allows a lot of flexibility, but also requires a lot of application changes and logic, because the application must connect to both the database and the caching system, and be responsible for keeping it updated

Although external caching is very efficient, it requires development efforts and DBAs have no control over data flow.

Caching on the wire

ProxySQL introduced a new paradigm to query caching. According to configuration (details below) resultsets are cached on the wire, while queries are executed and the resultset is returned to the application.
If the application will re-execute the same query, the resultset will be returned by the embedded Query Cache.

It is a quite common scenario to identify a database load caused by non-optimal SELECT statements that generate a resultset that should be cached for a few seconds.
Implementing a code change can be a long process (developers should write new code, build it, test it in staging, then deploy it in production), and this is often not a suitable option during an emergency.
As the configuration of the database proxy layer (ProxySQL in this case) falls under the responsibility of DBAs, to enable caching DBAs won’t require developers to make changes to the application.
Therefore this is a feature that empowers the DBAs.

Define traffic that needs to be cached

To define what traffic needs to be cached we need to define query rules that match incoming traffic, and define a cache_ttl for it.

As described in the documentation, there are many ways to define matches for incoming traffic.
All we need to do to cache a resultset is to define matching criteria and timeout.

Caching example

The best way to illustrate how to configure caching is with an example.
Assume we run sysbench against ProxySQL, with a very small table:

And the result is:

In ProxySQL we can see the following results:

Without a doubt, most of the execution time comes from a single type of SELECT, executed many times.
Let’s cache it, creating a matching rule. In this example we will use digest as a matching criteria, and a cache_ttl of 2000ms.

Let’s rerun the testing benchmark:

And the result is:

We can immediately see that the throughput has increased drastically, as some queries were cached by ProxySQL.

In ProxySQL we can see the following results from stats_mysql_query_digest:

Note: queries with hostgroup=-1 represent traffic served directly from the query cache, without hitting any backends.


Some of the metrics currently avaiable are the ones reported in stats_mysql_query_digest with hostgroup=-1, like in the example below.

Other metrics related to Query Cache are available through the stats table stats_mysql_global :

They represent:

  • Query_Cache_Memory_bytes : total size of the resultset stored in the Query Cache. This doesn’t include metadata;
  • Query_Cache_count_GET : total number of GET requests executed against the Query Cache;
  • Query_Cache_count_GET_OK : total number of successful GET requests executed against the Query Cache: resultset was present and not expired;
  • Query_Cache_count_SET : total number of resultset inserted into Query Cache;
  • Query_Cache_bytes_IN : amount of data written into the Query Cache;
  • Query_Cache_bytes_OUT : amount of data read from the Query Cache;
  • Query_Cache_Purged : number of entries purged;
  • Query_Cache_Entries : number of entries currently in the Query Cache.

Query Cache tuning

At the moment, it is only possible to tune the total amount of memory used by the Query Cache, using the variable mysql-query_cache_size_MB :

Important note: the current implementation of mysql-query_cache_size_MB doesn’t impose a hard limit . Instead, it is used as an argument by the purging thread.

To change the total amount of memory used by the Query Cache, it is possible to use commands like the following:

A variable not strictly related to Query Cache but one that influences its behavior is mysql-threshold_resultset_size.
mysql-threshold_resultset_size defines the maximum resultset size that ProxySQL will buffer before starting to send it to the client.
Setting this variable too low will prevent the retry of queries failed while retrieving the resultset from the backend.
Setting this variable too high will potentially increase the memory footprint, as ProxySQL will try to buffer more data.
Because mysql-threshold_resultset_size defines the maximum resultset size that can be buffered, it also defines the maximum resultset size that can be stored in Query Cache.

Implementation details

Every element in the Query Cache has several metadata associated with it:

  • key : uniquely identify a Query Cache entry: it is a hash derived from username, schemaname and the query itself. Combining these, it ensures that users access only their resultsets and they access it for the correct schema;
  • value : the resultset;
  • length : length of the resultset;
  • expire_ms : defines when the entry will expire;
  • access_ms : records the last time an entry was accessed;
  • ref_count : a reference count to identify the resultset currently in use.
GET calls

Every time a GET call succeeds, to improve performance, a copy of the data is made after increasing a reference pointer and releasing any locks. When the copy is completed, the ref_count is decreased. This ensures that entries are not deleted from the Query Cache while they are still in use.
When a GET call finds an entry that is expired, the entry will be moved to a purging queue.

SET calls

A SET call never fails. If mysql-query_cache_size_MB is reached, the SET call will still not fail.
If there is an entry with the same key, it is moved into the purging queue.

Purging thread

The purging of entries in the Query Cache is performed by a Purging thread.
This ensures that any maintenance of the Query Cache is not performed by the MySQL Thread accessing it, but by a background thread, thus improving performance.
This is the reason why SET calls never fail even if mysql-query_cache_size_MB is reached: it is not the responsibility of the MySQL Thread accessing the Query Cache to free some space; the Purging thread will take care of it instead.

The Purging thread is not only responsible for purging entries from the purging queue. It is also responsible for periodically scanning the whole Query Cache looking for expired entries.
As an optimization, thd Purging thread doesn’t perform any purging if the current memory usage is less than 3% of mysql-query_cache_size_MB .


There are currently multiple known limitations in Query Cache.
Some are simple to implement, others are less so.
There is no defined priority list: priorities will be defined based on user requests.

Current known limitations:

  • it is not possible to define query cache invalidation other than by cache_ttl;
  • there is no command to immediately purge the whole content of the query cache
  • mysql-query_cache_size_MB is not strictly enforced, but only used as a metric to trigger the automatic purging of expired entries;
  • although access_ms is recorded, it is not used as a metric to expire unused metric when mysql-query_cache_size_MB is achieved;
  • Query Cache does not support Prepared Statements.