MySQL Query Rewrite Plugin Performance Trade-Offs

This article examines the performance trade-offs of enabling the MySQL query rewrite plugin. While this tool is invaluable for modifying problematic SQL statements on the fly without changing application code, it introduces systemic overheads including increased query latency, double parsing, CPU utilization, and potential locking contention.

Query Parsing and Matching Overhead

The most consistent trade-off of enabling the query rewrite plugin is the CPU overhead added to the query ingestion phase. Once enabled, every incoming query must be evaluated against the active rewrite rules. Even if a query does not match any rules, it still incurs this evaluation cost. In high-throughput environments, this constant checking can slightly increase overall query latency and raise baseline CPU utilization.

Double Parsing for Matched Queries

When a query matches a rewrite rule, MySQL is forced to parse the SQL statement twice. First, the server parses the original query to identify the match. Second, it parses the newly generated, rewritten query before executing it. This double parsing significantly increases the execution latency of matched queries, making it a poor long-term solution for high-frequency transactions.

Memory Consumption

The plugin stores rewrite rules in the query_rewrite system database and loads them into memory for rapid lookup. While a small set of rules has a negligible footprint, maintaining a large, complex table of rewrite rules will consume valuable RAM. Additionally, reloading the rules cache can cause temporary performance blips under heavy database loads.

Thread Contention in High-Concurrency Environments

In highly concurrent databases where hundreds of threads execute queries simultaneously, accessing the shared memory structures of the query rewrite plugin can introduce mutex contention. When threads must wait for access to the rule matching engine, it can lead to thread pooling issues, increased query queue times, and reduced overall transaction throughput.

Debugging and Execution Plan Complexity

While not a direct hardware performance metric, the plugin introduces operational complexity that affects performance management. Database administrators analyzing slow query logs or running EXPLAIN plans may find it difficult to diagnose performance bottlenecks because the query executed by the database differs from the query sent by the application. This added complexity can delay the identification and resolution of genuine indexing or schema issues.