Query Mirroring

Query Mirroring: Architectural Deep-Dive

The Problem

Performance testing a database backend against realistic workload is genuinely difficult. Synthetic benchmarks reproduce query shapes but not the distribution, concurrency patterns, session characteristics, or data access locality of real production traffic. Load testing tools approximate volume but cannot replicate the exact mix of reads, writes, and analytical queries that a live application generates across a full business day.

The consequence is that backend changes — new hardware, a different MySQL version, an index strategy change, a configuration tuning pass — are validated against synthetic load, deployed to production, and only then exposed to real traffic patterns. Problems that only surface under specific query sequences or concurrency conditions appear in production rather than in testing.

Getting real production traffic onto a test backend without affecting live users requires a facility that sits between the application and the database: something that can see every query, duplicate it, and send the copy somewhere else — without the originating client waiting for the duplicate to complete.

The ProxySQL Approach

ProxySQL’s mirroring capability duplicates queries matching specified rules to a secondary host group, asynchronously and independently of the primary query path. The original query executes normally against the production backend. The mirrored copy is dispatched to the mirror host group without the client waiting for its result. From the application’s perspective, nothing has changed — response time, error handling, and connection behaviour are identical to non-mirrored operation.

Mirroring is configured at the query rule level, which means it inherits the full expressiveness of ProxySQL’s routing rule engine. You can mirror all traffic, or only SELECT statements, or only queries from a specific user, or only queries targeting a particular schema. The mirror target is a host group, so it can be a single node, a cluster, or any backend topology ProxySQL manages.

What You Can Do With It

Shadow Load Testing sends a live copy of production queries to a candidate backend — a new database version, a different instance type, a tuned configuration — and allows that backend to be observed under real production load patterns without any risk to the live environment. Query latency, resource consumption, and error rates on the shadow backend reflect actual production behaviour, not synthetic approximation.

Capacity Planning and Load Analysis uses mirrored traffic to measure how a proposed backend handles the real query mix at current production volume, at projected future volume (by running multiple mirrors simultaneously or by throttling up mirror weight), and under peak load conditions that may not be reproducible synthetically.

Schema and Index Validation mirrors production queries to a backend running a candidate schema change, capturing the actual query execution plans and performance characteristics under real data access patterns before committing the migration to the production environment.

Parallel Correctness Testing sends mirrored queries to a new backend and captures results for comparison against production results offline, enabling compatibility validation for database engine upgrades or configuration changes where result correctness is as important as performance.

The Result

Query mirroring turns production traffic into a continuous, risk-free testing resource. Any backend change can be validated against real query patterns, real concurrency, and real data before it receives a single live user request. The gap between benchmark performance and production performance collapses — because the testing input is production. And because mirroring is asynchronous and scoped to query rules, the cost of running it is a controlled infrastructure choice, not an availability risk.