Replace the Removed MySQL Query Cache with ProxySQL Caching (2026)

MySQL’s query cache, deprecated in 5.7 and removed in 8.0, was a simple but often problematic performance feature.

Let’s see ProxySQL, a high-performance, high-availability proxy for MySQL, tackle the caching problem with a more robust and configurable approach.

Imagine you have a busy web application backed by MySQL. Many read queries are identical and repetitive. The query cache tried to store the result of these queries and serve them back directly if the exact same query arrived again. Simple, right? But it had issues:

Invalidation Overhead: Any write operation (INSERT, UPDATE, DELETE) to a table would invalidate all cached queries referencing that table. On busy write-heavy systems, this meant the cache was constantly being invalidated, leading to more overhead than benefit.
Contention: Locks were often needed to check the cache and to invalidate it, leading to contention on busy systems.
Memory Bloat: The cache could consume significant memory, and its effectiveness often diminished as the dataset grew.

ProxySQL offers a sophisticated alternative. Instead of caching query results directly in the MySQL server, ProxySQL intercepts queries before they reach your MySQL instances. It then intelligently decides whether to serve the query from its own cache, forward it to a MySQL server, or even route it to a different replica.

Here’s a simplified setup:

# Install ProxySQL (example for Ubuntu/Debian)
sudo apt update
sudo apt install proxysql

# Configure ProxySQL (edit /etc/proxysql.cnf)
[mysql_servers]
# Add your MySQL backend servers here
192.168.1.10:3306 {
    hostgroup_id: 10
    weight: 1
    max_connections: 2000
}
192.168.1.11:3306 {
    hostgroup_id: 10
    weight: 1
    max_connections: 2000
}

[mysql_users]
# Define a user for ProxySQL to connect to MySQL as
admin:admin:1
proxysql_user:proxysql_user:1:10,20 # User for application to connect through ProxySQL

[mysql_replication_hostgroups]
10:
    master: 192.168.1.10:3306
    # If you have read replicas, add them here
    # read_secondary: 192.168.1.11:3306

[query_cache]
# Enable query caching
enabled: true
# Define which queries to cache. 'all' means all queries.
# You can also use regex for more fine-grained control.
query_cache_level: 2
query_cache_size: 1024M # 1GB cache size
query_cache_prune_interval: 300 # Prune cache every 5 minutes
query_cache_ttl: 3600 # Cache entries live for 1 hour

# Load ProxySQL configuration
sudo systemctl restart proxysql

Once ProxySQL is running, your application connects to localhost:3306 (or wherever ProxySQL is listening) instead of directly to your MySQL servers.

The Magic of query_cache_level and query_cache_ttl

In the query_cache section of proxysql.cnf, enabled: true is the switch. But query_cache_level is where it gets interesting:

0: Query cache is disabled.
1: Cache queries that are not already in the cache. This is the default.
2: Cache all queries, regardless of whether they are already in the cache. This is more aggressive and can be beneficial if you have many identical queries.

And query_cache_ttl (Time To Live) is crucial. It defines how long a cached query result remains valid. A longer TTL means results are served from cache more often, but increases the risk of stale data if your writes are frequent. A shorter TTL reduces stale data risk but means more queries hit the actual database.

How ProxySQL Avoids the Original Query Cache’s Pitfalls

ProxySQL operates outside the MySQL server. This separation is key.

Decoupled Invalidation: ProxySQL doesn’t automatically invalidate its cache on every write. Instead, it uses a more sophisticated mechanism. When a write query arrives, ProxySQL can be configured to identify the affected tables. It then selectively invalidates cache entries related only to those specific tables. This is a massive improvement over the blanket invalidation of MySQL’s old query cache. You can even use query_rules to explicitly tell ProxySQL to never cache certain queries, or to invalidate specific cache entries based on patterns.

For example, a query_rule might look like this:
```
{
    "rule_id": 1,
    "active": true,
    "match_digest": "^SELECT",
    "destination_hostgroup": 10,
    "cache_ttl_ms": 3600000
}
```
This rule caches all SELECT queries for 1 hour (3600 seconds). If you have a DELETE statement that affects users table, you’d create another rule that targets that specific DELETE and can be configured to invalidate cache entries related to users.
Reduced Contention: Since caching and invalidation logic is in ProxySQL, it doesn’t contend with MySQL’s internal locking mechanisms for query caching. ProxySQL itself uses efficient data structures and threading models to handle this.
Configurable Cache Size: query_cache_size in proxysql.cnf allows you to precisely control memory usage. If it gets too large, ProxySQL will start evicting older entries using an LRU (Least Recently Used) algorithm, preventing memory bloat.
Intelligent Routing: Beyond just caching, ProxySQL can route read queries to specific read replicas, further offloading your primary MySQL server. This is configured via mysql_replication_hostgroups.

The most surprising thing is that ProxySQL’s caching is often more effective than the original MySQL query cache because it’s designed to be smarter about invalidation. Instead of a sledgehammer, it uses a scalpel. For instance, you can define rules that cache only SELECT statements that don’t contain FOR UPDATE or LOCK IN SHARE MODE, ensuring consistency.

The next thing you’ll want to explore is ProxySQL’s query_rules for fine-grained control over query routing and caching based on query patterns, users, or source IPs.