One of ProxySQL most interesting features is query rewrite.

Query Rewrite with ProxySQL

This functionality is one of the main reasons to write a proxy that sits between the application and the database server: to rewrite queries on the fly, without the need to have a developer involved in rewriting said query ASAP (if ever possible!).

In this short post I will describe a use case scenario on how to use ProxySQL to troubleshoot performance and quickly rewrite queries if needed. The setup in this specific blog post is the Nylas platform. Nylas is a sharded environment, which has over 80 ProxySQL instances running.

The Issue

We noticed that an odd workload originated from Nylas’ open source sync engine. See the below graph on InnoDB rows read:

Initial read of InnoDB graph

Analysis

Therefore, we asked ProxySQL to get the list of the most time consuming queries:

Then we also asked for the query with the highest average execution time:

We could have gotten the same information from information_schema.events_statements_summary_by_digest. However, we already knew that the load was coming from a specific node so we queried stats.stats_mysql_query_digest inside ProxySQL itself.

From the output above, it was obvious that the problem was a specific query type running at that time.

Without copying the whole query again, it could be simplified as:

col2 and col3 are not indexed, because they do not provide enough cardinality. However, idx_col1 does, so it is indexed. Because of the WHERE and the ORDER BY clauses, mysqld performs a range scan on the primary key. The optimizer could optimize this query if it were smart enough. But it isn’t.

Therefore, the query can be rewritten as:

The Solution: Query Rewrite

How do you solve this with ProxySQL? Extremely easy!

The snippet below shows how to create a rule to rewrite the query, load the new rule into runtime, and persist the new rule on disk:

From the following graph of InnoDB rows read, the effect of the new query rewrite is immediately clear:

Effect of rewrite in a InnoDB rows read graph

Conclusion

Query rewrite is a feature that, depending from the queries running against the database server, quickly allows to isolate and correct problematic queries and improve performance.