ProxySQL Query Rules: Notes From Production

by Pythian Marketing on Sep 3, 2020 12:00:00 AM

After spending four years working with ProxySQL in production, I've learned a few interesting lessons about interpreting and processing query rules. I hope to save you some time (and avoid wrong turns) with this summary of ProxySQL query rules.

ProxySQL query engine is very powerful and supports the building of complex rule sets. These can be used to route traffic to backend MySQL instances, rewrite queries, and for traffic mirroring, among other use cases. When the rule set is short and simple, you can easily understand what the outcome for a certain query would be. However, for complex sets or combined scenarios, you need a deeper understanding of the engine logic.

Note 1: Rules are processed in rule_id order

It might seem like a trivial start, but during testing or implementation, it's not uncommon to delete or insert the rules without taking the id into consideration. Furthermore, in more mature environments, it's also common to have disabled rules allocating ids. This can cause the runtime set to be less linear than with newer environments. Unless different chains were implemented, rules are processed based on the id, from lowest to highest.

Note 2: Things to consider when using flagIN / flagOUT to create rule chains

Sometimes you need the query engine to take different paths depending on certain conditions, and you need to create rule chains for that purpose. The documentation says that you can set a flagOUT value for a rule so that only those with a matching flagIN are evaluated next. What is not always clear is that a query is only marked with flagOUT if the query matches the rule in question. Also:

Remember that only rules with flagIN equal to 0 are considered at the beginning. [caption id="attachment_108415" align="alignnone" width="528"] If default values are used for flagIN, flagOUT and apply, all rules are processed, from min to max rule_id[/caption]
Any rules above the one currently being processed are not visited again, regardless of the flagIN value.
flagOUT equals NULL means stay on the current chain id. [caption id="attachment_108417" align="alignnone" width="625"] flagOUT is only enforced when the rule matches[/caption]

Note 3: "apply" will end rules processing

Regardless of how many chains we created, and how many rules exist after the one currently being processed, if the apply flag is set to 1, no further rules will be checked. A very important detail is that apply is only honored if there is a match. Effect of apply in rules processing

Note 4: negate_match_pattern combined with replace_pattern won't work as you might expect

This scenario is more specific, but I noticed this behavior while working on a production solution. You might think if you have a pattern and want to return a generic message for any queries *NOT* matching that pattern, setting the message in replace_pattern and enabling negate_match_pattern will do. As this github issue explains, replace_pattern only works when the query matches the pattern. If you use negate_match_pattern equals 1, the query won't match the pattern, and no replacement will happen.

Note 5: IP/Port-based rules

If you are looking to create rules based on specific combinations of ports and IPs, you need to specify every combination in mysql-interfaces parameter . For example, if I want to create different rules for queries coming on different IPs, for port 6098, it is not enough to include 0.0.0.0:6098 in mysql-interfaces. I need to specify every combination of IP and port that I'm expecting to use in rules, even when the port is the same. See the example below: Rules:

a) IF proxy_addr = 10.0.0.10 AND proxy_port = 6098 THEN hostgroup = 0

b) IF proxy_addr = 10.0.0.11 AND proxy_port = 6098 THEN hostgroup = 1

c) IF proxy_addr = 10.0.0.12 AND proxy_port = 6098 THEN hostgroup = 2

Incorrect configuration

mysql-interfaces = 0.0.0.0:6033;0.0.0.0:6034;0.0.0.0:6098

Correct configuration

mysql-interfaces = 0.0.0.0:6033;0.0.0.0:6034;10.0.0.10:6098;10.0.0.11:6098;10.0.0.12:6098

Conclusion

When you need to use ProxySQL in scenarios combining different features and handling different rule branches, it is critical to understand how flagIN, flagOUT and apply attributes interact with each other and affect query handling. Using replace_pattern with negate_matching_pattern won't work. Once you think about this, it does make sense. Finally, remember to specify each endpoint (ip-port combination) independently if you are planning to build rules based on proxy_addr and proxy_port. Because mysql-interfaces is not dynamic, you will need to restart ProxySQL by using service / systemctl or issuing "proxysql restart" on the admin interface.

No Comments Yet

Let us know what you think

See all

Share this