Buy Percona ServicesBuy Now!

ProxySQL Query Rewrite Use Case

 | May 2, 2018 |  Posted In: Insight for DBAs, Insight for Developers, MySQL, ProxySQL

PREVIOUS POST
NEXT POST

ProxySQL Query RewriteIn this blog post, I’m going to revisit the ProxySQL Query Rewrite feature. You may have seen me talking about possible use case scenarios in the past few conferences, but the reason I’m starting with this is that query rewriting was the original intention for building ProxySQL.

Why would you need to rewrite a query?

  • You’ve identified a query that’s causing bottleneck or slowness
  • A special operation requires query routing
  • You cannot modify application code

So here we have a case of a bad query hitting the backend database. You as a DBA have identified the query as causing severe slowdown, which could lead to a site-wide outage. This query needs to be optimized, and you have asked the developer to correct this bad query. Their answer isn’t really what you expected. You can rewrite some queries to have the same data result by choosing a different optimizer path. In cases where an application was written in ORM – such as Hibernate or similar – it is not easy to quickly make a code change.

The query rewrite feature of ProxySQL makes this possible (until the application can be modified).

How do we rewrite a query? There are two ways to accomplish this with ProxySQL.

Query rewrite is just a match_pattern + replace_pattern activity, whereas match_digest is only used for matching a query, not rewriting it. Logically, match_digest serves the same purpose of username, schemaname, proxy_addr, etc. It only matches the query.

These two different mechanisms offers ways to optimize query matching operation efficiently depending on the query type (such as DML operation versus SELECT query). Please note that if your intention is to rewrite queries, the rule must match the original query by using match_pattern. Query rules are processed by using rule_id field and only applied if active = 1.

Here’s how we can demonstrate match_digest in our test lab:

We can also monitor Query Rules activity live using the ProxyTop utility:

To reset ProxySQL’s statistics for query rules, use following steps:

Here’s a match_pattern example:

The key in query ruling for a rewrite is the order of the apply field:

  • apply = 1 means don’t evaluate any other rules if there’s a match already.
  • apply = 0 means evaluate the next rules in the chain.

As we can see in the test below, all queries matching with rule_id = 10 or rule_id = 20 have hits. In reality, all rules in runtime_mysql_query_rules are active. If we want to disable a rule that is in the mysql_query_rules table, set active = 0:

Additionally, ProxySQL can help to identify bad queries. Login to the admin module and follow these steps:

Find the most time-consuming queries:

Find highest average execution time:

The above information can also be gathered from information_schema.events_statements_summary_by_digest, but I prefer the ProxySQL admin interface. Also, you can run the slow query log analysis by running a detailed pt-query-digest on your system to identify slow queries. You can also use PMM’s QAN.

Conclusion

I’ve found the best documentation on ProxySQL query rewrite is at IBM’s site, where they explain query rewrite fundamentals with examples. It’s worth a read. I’m not going to get into the details of these techniques here, but if you find more relevant resources, please post them in the comments section.

A few of the possible query optimization techniques:

  • Operation merging
  • Operation movement
  • Predicate translation

At the time of this blog post, ProxySQL has also announced a new fast schema routing algorithm to support thousands of shards.

There may be other cases where you want to divert traffic to another table. Think of a table hitting the maximum integer value, and you want to keep inserts going into a new table while you alter the old one to correct the issue. In the mean time, all selects can still point to the old table to continue operation.

As of MySQL 5.7.6, Oracle also offers query rewrite as a plugin, and you can find the documentation here. The biggest disadvantage of using Oracle’s built-in solution is the rewrite rule sits with the server it is implemented on. That’s where ProxySQL has a bigger advantage: it sits between the application and database server, so the rule applies to the entire topology, not just for a single host.

As you can see, ProxySQL query rewrite is a great way to solve some real operational issues and make you a hero to the team and project. To become a rock star, you might want to consider Percona Training on ProxySQL. The training will provide the knowledge to set up a ProxySQL environment with best practices, understand when and how to change the configuration, and maintain it to ensure increasing your uptime SLAs. Contact us for more details at info@percona.com.

References:

https://www.percona.com/blog/2017/04/10/proxysql-rules-do-i-have-too-many/

http://www.proxysql.com/blog/query-rewrite-with-proxysql-use-case-scenario

https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#query-rewrite

https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005293.html

PREVIOUS POST
NEXT POST
Alkin Tezuysal

Alkin has extensive experience in enterprise relational databases working in various sectors for large corporations. With more then 20 years of industry experience he has acquired skills for managing large projects from ground up to production. For the past eight years he's been focusing on e-commerce, SaaS and MySQL technologies. He managed and architected database topologies for high volume site at eBay Intl. He has several years of experience on 24X7 support and operational tasks as well as improving database systems for major companies. He has led MySQL global operations team on Tier 1/2/3 support for MySQL customers. In July 2016 he has joined Percona's expert technical management team.

One Comment

  • I’ve never had the proper time to play around with/implement proxysql since I first heard of it, but I always thought the following would be a good way to deal with query rewrites. Wonder what you think.

    – Have application insert in-line comment with unique identifier into all queries sent from application, preferably at or near the front of the query. A decent example, and potentially automated would be an identifier of the filename and line #.
    – When it is found that query needs rewriting, base your match pattern on the unique identifier.

    This allows you to simply match on that unique identifier and then rewrite the rest of the query without hassle. You wouldn’t have to worry about similar queries that you do not want to rewrite, or that may match your non unique identifier. I haven’t tested it, but I do think it would work. I know there is a –comments option for the mysql command line tool that allows you to see such comments. I assume proxy sql can see the comments as well.

Leave a Reply