Managing ProxySQL Query Rules at Scale

ProxySQL sits between applications and MySQL databases at Shopify, managing connections and distributing traffic across database pools. One of its most useful features is query rules, which let operators reroute, rewrite, or reject queries that match a specified regex. But applying rules to thousands of ProxySQL instances is slow and error-prone, especially during an incident. Shopify built a tool to make this process safe and scalable.

Rule Types and Use Cases

Query rules serve three main purposes: rerouting traffic to different hostgroups, rewriting queries before they reach the database, and rejecting queries that should never execute.

Reroute rules send matched queries to a specific hostgroup, such as a pool of read replicas. Shopify used these rules to test which queries could perform better on read replicas without deploying code changes. After measuring the impact, teams could permanently route the queries that showed improvement.

Rewrite rules modify queries in transit. If an application references a renamed database column while waiting for a code deployment, a rewrite rule can map the old column name to the new one temporarily. A rule is inserted into the mysql_query_rules table with an ID, active flag, match pattern, and replacement pattern.

Reject rules block queries that could harm the database, whether from malicious traffic or a faulty code deployment.

The Danger of Powerful Rules

Query rules are flexible, but that flexibility carries risk. A regex that is broader than intended could reject or rewrite far more queries than expected, potentially causing downtime or data corruption.

ProxySQL has no built-in dry run mode, so Shopify added one to its fork. With dry running, a rule can log matched queries and the action it would take without actually performing it. Once an author confirms the rule behaves correctly, they switch it from dry run to active mode. If a rule misbehaves in production, it can be fixed without having caused any damage. Shopify is working to contribute this feature upstream via a pull request to ProxySQL's repository.

The dry run feature relies on the active and log fields in the mysql_query_rules table. Rules in dry run mode log matched queries; rules in active mode apply their actions.

Heavy logging is a potential concern with dry run mode. A rule with an overly broad regex could match thousands of queries per second and flood the logs. Shopify's fork mitigates this with an exponential backoff: only the 1st, 2nd, 4th, 8th, and subsequent matched queries are logged.

Applying Rules Dynamically

Query rules are managed with standard SQL—INSERT, UPDATE, and DELETE statements against mysql_query_rules. With thousands of ProxySQL replicas, manually applying changes to each one is not practical.

Shopify's solution is a tool with three components:

  • A web app where developers enter parameters for a rule.
  • A global key-value store that acts as the source of truth for the ruleset.
  • A sidecar container that runs alongside each ProxySQL instance, polling the key-value store and applying changes.

The sidecar caches a list of rules locally. On each polling cycle, it compares its cached list against the key-value store's list and makes whatever changes are necessary to stay in sync, modifying ProxySQL's mysql_query_rules table with SQL statements.

Failure Handling

The system is designed with distributed systems failure modes in mind.

If the key-value store goes down, the sidecar continues operating with its cached rules. It simply won't pick up new changes until the store is available again.

If communication between the sidecar and ProxySQL fails, or the two fall out of sync, both the ProxySQL rules list and the sidecar cache are wiped. On the next polling loop, the sidecar fetches the ruleset from the key-value store and repopulates ProxySQL. This means rules may be absent for a brief moment, but Shopify prefers consistency across all ProxySQL instances over availability of every rule at all times. The alternative is split-brain scenarios where different instances enforce different rulesets.

Rules can also have a time-to-live (TTL) that developers set when creating them. Even if the key-value store is unreachable, the sidecar will still remove a rule once its TTL expires.

Some rules must be available 100 percent of the time. These are defined statically in a proxysql.cnf file and loaded at startup. Rules created through the dynamic tool are separate from these static, startup rules.

The dynamic rule system is most often a last-resort tool, but it becomes essential when a database-level fix is needed quickly. It also enables experimentation that would otherwise require lengthy code deployment cycles.