Note that:

  • specifications can change at any time
  • It doesn’t support prepared statements

Extensions to mysql_query_rules

Table mysql_query_rules was modified to add 2 more columns:

  • mirror_flagOUT
  • mirror_hostgroup

Therefore, the new table definition of mysql_query_rules becomes:

Implementation overview

When either mirror_flagOUT or mirror_hostgroup is set for a matching query, real time query mirroring is automatically enabled.
Note that mirroring is enabled for the the final query if the original was rewritten: if the query was rewritten along the way, the mirroring logic applies to the rewritten query. Although, the mirrored query can be rewritten and modified again. Details below.
If a source query is matched against multiple query rules, it is possible that mirror_flagOUT or mirror_hostgroup is changed multiple times.
The mirroring logic is the following:

  • if mirror_flagOUT or mirror_hostgroup is set while processing the source query, a new MySQL session is created
  • the new MySQL session will get all the same properties of the original MySQL session : same credentials, schemaname, default hostgroup, etc (note: charset is currently not copied)
  • if mirror_hostgroup was set in the original session, the new session will change its default hostgroup to mirror_hostgroup
  • if mirror_flagOUT is not set, the new session will execute the original query against the defined mirror_hostgroup
  • if mirror_flagOUT was set in the original session, the new MySQL session will try to match the query from the original session against mysql_query_rules starting from a value of FlagIN=mirror_flagOUT : in this way it is possible to modify the query, like rewriting it, or changing the hostgroup again


Mirror selects to the same hostgroup

In this very simple example we will just send all SELECT statements to hostgroup2, both the original and the mirror ones.

From a MySQL session we will run some queries:

Back to the admin interface, we can see that the SELECT statement was executed twice:

As an additional test we re-run the same query:

On the admin interface:

count_star is twice the number of times we executed the queries, because it is mirrored.
It is important to note that ProxySQL collects metrics both for the original query and the mirrors.

Mirror SELECT to a different hostgroup

In this example, we will re-configure ProxySQL to send all the SELECT statements to hostgroup1, but to mirror them on hostgroup2 :

We also empty stats_mysql_query_digest to have fresh stats:

From the MySQL client we can now run some queries (for simplicity’s sake, we will run the same):

In Admin we can now verify what happened:

The same identical query was sent to both hostgroup1 and hostgroup2!

Rewrite both the source query and the mirror

In this example, we will rewrite the original query, and then mirror it:
For simplicity’s sake, we will rewrite sbtest[0-9]+ to sbtest3 :

Again, we reset stats_mysql_query_digest :

From the MySQL client we can now run the usual query:

As expected, the output is different from the previous one because now the original query was rewritten.
Let’s check stats_mysql_query_digest :

As expected, the modified query was executed on both hostgroups.

Rewrite the mirror query only

In this example we will rewrite only the mirrored query.
This is very useful, if for example, we want to understand the performance of the rewritten query, or if a new index will improve performance.

In this example we will compare the performance of the same queries with and without the use of indexes.
We will also send the queries to the same hostgroups.

The following creates a rule (rule_id=5) that:

  • matches FROM sbtest1
  • sets destination hostgroup=2
  • sets mirror_flagOUT=100
  • does NOT set a mirror_hostgroup

Because mirror_flagOUT is set, a new session will be created to run the same query. However, mirror_hostgroup was not set, so the query will be sent to the default hostgroup for the specific user, according to mysql_users. Instead of this, we want to send the mirror query to the same hostgroup as the original. We could do this either by setting the mirror_hostgroup in the rule with rule_id=5 , or by creating a new rule. We will also create a new rule to rewrite the query:

It is important to note that in the rule with rule_id=10 , the one against which the mirrored query will match, we need to set destination_hostgroup and not mirror_hostgroup : mirror_hostgroup should be set only for the original query in order to immediately specify where the mirror query should be sent, without the need of extra rules in mysql_query_rules .

Let’s reset stats_mysql_query_digest:

And run some tests from the MySQL client:

Let’s check stats_mysql_query_digest :

Table stats_mysql_query_digest confirms that:

  • queries were mirrored
  • the original query was not rewritten
  • the mirrored query was rewritten
  • the mirrored query was much slower because it ignored the index

Advanced example: use mirroring to test query rewrite

While working on mirroring I was asked a completely different question, related to query rewrite: how one could know if the given regex matches a given query, and verify that the rewrite pattern is correct?
To be more specific, the problem is to understand if the rewrite is correct without affecting live traffic.
Although mirroring wasn’t initially designed for that, it can answer this question.

In this example, we will write a rule to match all the SELECT statements, “mirror” them , and try to rewrite them .

The regexes above are quite complex, and this is why mirroring helps instead of rewriting live traffic.

Let’s reset stats_mysql_query_digest:

And run some tests from the MySQL client:

The query ran successfully. As said, we didn’t modify the original traffic.

What about in stats_mysql_query_digest ?

The original query was executed twice, so something didn’t run correctly.
We can note that both queries were sent to hostgroup2 : we should assume that rule_id=10 was a match, but didn’t rewrite the query.
Let’s verify if it was a match:

Rule with rule_id=10 was a match according to hits in stats_mysql_query_rules.
Then why wasn’t the query rewritten? The error log has this information:

Indeed, it is invalid syntax , let’s fix this:

Let’s re-execute the query from the MySQL client:

And now let’s verify if the query was rewritten correctly:

Well, yes, the query was rewritten correctly, and was also executed. :-)

Advanced example: use mirroring and firewall to test query rewrite

Taking the previous example/exercise a bit forward: is it possible to know how a query will be rewritten without executing it? YES!
To achieve this, we will set error_msg for the mirrored query: in this way ProxySQL will process the query, but it will filter it without sending it to any MySQL servers. As stated in the very beginning, the mirrored query can be modified, and the firewall is an example of modifying the mirrored query.


Let’s rerun the query in the MySQL client:

And now let’s check statistics:

Great! We know that the query was rewritten, but was actually not sent anywhere:

  • sum_time=0 because the response was immediate
  • hostgroup=-1 has the special meaning of “not sent anywhere”