Shulou(Shulou.com)06/01 Report--

In this article, I will re-explore the Query Rewrite functionality in ProxySQL, because query rewriting is the most fundamental reason for creating ProxySQL.

Why do we need to rewrite the query?

You have identified a query that triggers a performance bottleneck or causes the system to slow down. You cannot (quickly) modify the application code for some special operations that require a "redirect query."

Here is an example where you find a "bad query" as a DBA, and you confirm that it causes the service to be slow and may cause the service to become unavailable. Well, this query must be optimized, and you communicate with the developer to correct the SQL, but the feedback from the developer can be changed, but due to technical and non-technical reasons, it is not so fast. What are you going to do then? wait? Obviously not, you can rewrite some queries through ProxySQL's Query Rewrite function to optimize and remain transparent to the application before the development completes the correction.

How do I rewrite a query? There are two ways to do this through ProxySQL.

Query rewrite is actually a match_pattern + replace_pattern process in the mysql_query_rules table, while match_digest (note the distinction between match_pattern and match_digest) is only used to match a query, not to rewrite it. Logically, fields such as match_digest and username,schemaname,proxy_addr serve the same purpose and are only used to match queries.

These two different mechanisms provide flexible and efficient matching methods for different query types (such as DML operations, SELECT, etc.). Note that if you want to rewrite the query, the match_pattern in the rule must match the original query. Query rules are processed in ascending order in the rule_id field and will only be processed if the active field is 1.

Here's how we demonstrate match_digest in our test environment

Mysql > SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 10 +-+-- + | hg | sum_time | count_star | digest_text | +-+-- -+-+ | 0 | 243549572 | 85710 | SELECT c FROM sbtest10 WHERE id=? | | 0 | 146324255 | 42856 | COMMIT | | 0 | 126643488 | 44310 | SELECT c FROM sbtest7 WHERE id=? | | 0 | 126517140 | 42927 | BEGIN | | 0 | 123797307 | 43820 | SELECT c FROM sbtest1 WHERE id=? | | 0 | 123345775 | 43460 | SELECT c FROM sbtest6 WHERE id=? | | 0 | 122121030 | 43010 | SELECT c FROM sbtest9 WHERE id=? | | 0 | 121245265 | 42400 | SELECT c FROM sbtest8 WHERE id=? | | 0 | 120554811 | 42520 | SELECT c FROM sbtest3 WHERE id=? | 0 | 119244143 | 42070 | SELECT c FROM sbtest5 WHERE id=? | +-+ -+-+ 10 rows in set (0.00 sec) mysql > INSERT INTO mysql_query_rules (rule_id Active,username,match_digest, match_pattern,replace_pattern,apply) VALUES (10, 10, 1, 10, 10, 1, 10, 10, 5, 5, 5, 5, 5, 5, 5, 2, 3, 4, 3, 4, 3, 4, 3, 4, 2, 3, 4, 4, 4, 4, 4, 4, 4, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 9, 6, 6, 6, 6, 3, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, Query OK, 1 row affected (0.00 sec) mysql > LOAD MYSQL QUERY RULES TO RUNTIME;Query OK, 0 rows affected (0.00 sec) mysql > SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +- -+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +-- -+ | 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT hits Mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +- -+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +-- -+ | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 | +- -+-+ 1 row in set (0.00 sec)

If you want to clear query rules statistics, use the following methods

Mysql > SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1 + | 1 | +-- + | 1 | +-- + | 1 | +-- + 1 row in set (0.01 sec) mysql > LOAD MYSQL QUERY RULES TO RUNTIME;Query OK, 0 rows affected (0.00 sec)

Here is an example of match_pattern:

Mysql > SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 5 +-+-- + | hg | sum_time | count_star | digest_text | +-+-- -- + | 0 | 98753983 | 16292 | BEGIN | | 0 | 84613532 | 16232 | COMMIT | | 1 | 49327292 | 16556 | SELECT c FROM sbtest3 WHERE id=? | | 1 | 49027118 | 16706 | SELECT c FROM sbtest2 WHERE id=? | | 1 | 48095847 | 16396 | SELECT C FROM sbtest4 WHERE id=? | +-+ 5 rows in set (0.01sec) mysql > INSERT INTO mysql_query_rules (rule_id) Active,username,match_pattern,replace_pattern,apply) VALUES (20 and 1) ORDER BY caster (. *) DISTINCT (.) Query OK, 1 row affected (0.00 sec) mysql > LOAD MYSQL QUERY RULES TO RUNTIME;Query OK, 0 rows affected (0.01 sec) mysql > SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +-+- -- +-+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +- -+ | 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 | | 0 | 20 | NULL | 1 | root | | NULL | DISTINCT (. *) ORDER BY c | DISTINCT1 | NULL | 1 | +-| -+ 2 rows in set (0.01sec) mysql > SELECT hits Mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +-+- -- +-+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +- -+ | 9994 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 | 6487 | 20 | NULL | 1 | root | | NULL | DISTINCT (. *) ORDER BY c | DISTINCT1 | NULL | 1 | +-- | -- + 2 rows in set (0.00 sec) mysql > SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1 +-- + | 1 | +-- + | 1 | +-- + 1 row in set (0.00 sec) mysql > LOAD MYSQL QUERY RULES TO RUNTIME;Query OK, 0 rows affected (0.00 sec)

One of the key points in the routing rule is the apply field of mysql_query_rules

Apply = 1 (default) indicates that once the query matches a rule, it no longer matches the remaining rules apply = 0 indicates that it continues to try to match subsequent rules

(translator's note: similar to the break parameter in the nginx rewrite instruction)

As shown in the following test, all queries that match rule_id = 10 or rule_id = 20 match exactly. In fact, all the rules are now active in the runtime_mysql_query_rules table. If we want to disable a rule in the mysql_query_rules table, set active = 0

Mysql > update mysql_query_rules set apply = 1 where rule_id in (10); Query OK, 1 row affected (0.00 sec) mysql > update mysql_query_rules set apply = 0 where rule_id in (20); Query OK, 1 row affected (0.00 sec) mysql > LOAD MYSQL QUERY RULES TO RUNTIME Query OK, 0 rows affected (0.00 sec) mysql > SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +-+- -- +-+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +- -+ | 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 | | 0 | 20 | NULL | 1 | root | | NULL | DISTINCT (. *) ORDER BY c | DISTINCT1 | NULL | 0 | +-| -+ 2 rows in set (0.00 sec) mysql > SELECT hits Mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, flagIN, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +-+- +-+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | flagIN | apply | +- -+ | 10195 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | 0 | 1 | 6599 | 20 | NULL | 1 | root | NULL | | DISTINCT (. *) ORDER BY c | DISTINCT1 | 0 | 0 | + -+ 2 rows in set (0.00 sec) mysql > SELECT hits Mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, flagIN, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +-+- +-+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | flagIN | apply | +- -+ | 20217 | 5 | NULL | 1 | root | NULL | DISTINCT (. *) ORDER BY c | DISTINCT1 | 0 | 1 | 27020 | 10 | NULL | 1 | root | SELECT.* WHERE id=? | sbtest2 | sbtest10 | 0 | 0 | +-+- -+ 2 rows in set (0.00 sec) mysql > update mysql_query_rules set active = 0 where rule_id = 5 Query OK, 1 row affected (0.00 sec) mysql > LOAD MYSQL QUERY RULES TO RUNTIME;Query OK, 0 rows affected (0.02 sec) mysql > SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +- -+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +-- -+ | 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 0 | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT hits Mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id +- -+ | hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply | +-- -+ | 4224 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 0 | +- -+-+ 1 row in set (0.01 sec)

In addition, ProxySQL can also help identify "inefficient queries". The login management interface is as follows

Find the query that takes the most time

Mysql > SELECT SUM (sum_time), SUM (count_star) Digest_text FROM stats_mysql_query_digest GROUP BY digest ORDER BY SUM (sum_time) DESC LIMIT 3Gmail * 1. Row * * SUM (sum_time): 95053795SUM (count_star): 13164 digest_text: BEGIN* * 2. Row * * SUM (sum_time): 85094367SUM (count_star): 13130 digest_text: COMMIT** 3.row * * SUM (sum_time): 52110099SUM (count_star): 13806 digest_text: SELECT c FROM sbtest3 WHERE id=?3 rows in set (0.00 sec)

Find the query that takes the most time on average

Mysql > SELECT SUM (sum_time), SUM (count_star), SUM (sum_time) / SUM (count_star) avg, digest_text FROM stats_mysql_query_digest GROUP BY digest ORDER BY SUM (sum_time) / SUM (count_star) DESC limit 1 +-+ | SUM (sum_time) | SUM (count_star) | avg | digest_text | +- -- +-+ | 972162 | 1 | 972162 | CREATE INDEX Kong5 ON sbtest5 (k) | +-+- -+-- +-- + 1 row in set (0.00 sec)

I found that the "best" document on ProxySQL query rewrite is in IBM, which introduces the principles and examples of query rewriting, which is worth reading.

There are other scenarios you may need to rewrite the query, imagine that the self-increasing ID column of a table has reached the maximum value of the int type, you can redirect the newly inserted data to another table while you correct the problem of the original table through the alter command, during this period all queries will also access the original table, and after the completion of the original alter table, the data of the new table will be imported into the original table, which can achieve the effect of non-stop machine repair DDL.

Since MySQL 5.7.6, MySQL has provided query rewrite functionality as a plug-in, and you can find relevant documentation here. One of the biggest disadvantages of MySQL's built-in query rewriting feature is that the rewriting rules only work on the current MySQL instance, which is the advantage of ProxySQL: it is between the application and the database, so its rewriting rules are global.

Original text link

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.