Dynamic ProxySQL Query Rules

ProxySQL comes with a powerful feature called query rules. The main use of these rules at Shopify is to reroute, rewrite, or reject queries matching a specified regex. However, with great power comes great responsibility. These rules are powerful and can have unexpected consequences if used incorrectly. At Shopify’s scale, we’re running thousands of ProxySQL instances, so applying query rules to each one is a painful and time consuming process, especially during an incident. We’ve built a tool to help us address these challenges and make deploying new rules safe and scalable.

Shopify logo
Shopify
•
Rahul Rangith
•9 min read•intermediate•
--
•View Original
MySQLSQLSQLite

Overview

The article discusses the implementation and management of dynamic ProxySQL query rules at Shopify, focusing on their importance for database resiliency and performance. It covers the types of query rules, their applications, potential dangers, and the development of a tool to manage these rules across thousands of ProxySQL instances.

What You'll Learn

1

How to implement reroute, rewrite, and reject rules in ProxySQL

2

Why dry running query rules can prevent database issues

3

How to manage query rules across multiple ProxySQL instances dynamically

Prerequisites & Requirements

  • Understanding of ProxySQL and MySQL database management
  • Familiarity with SQL for managing query rules

Key Questions Answered

What are the types of query rules available in ProxySQL?
ProxySQL supports three types of query rules: reroute rules, which send matched queries to specific hostgroups; rewrite rules, which modify queries for compatibility; and reject rules, which block harmful queries. Each type addresses different database performance and security issues.
How does Shopify manage query rules across thousands of ProxySQL instances?
Shopify developed a tool that allows developers to create, modify, and delete query rules dynamically. This tool updates a global key-value store, which a sidecar container polls to apply changes to ProxySQL instances, ensuring scalability and efficiency.
What are the dangers of using query rules in ProxySQL?
The dangers include the potential for unintended consequences, such as rejecting more queries than intended or causing data corruption through incorrect rewrites. These issues can lead to significant downtime and performance degradation if not managed carefully.

Technologies & Tools

Some links below are affiliate links. We may earn a commission if you make a purchase.

Database
Proxysql
Used to manage connections between applications and MySQL databases, improving performance.
Database
Mysql
The primary database system managed by ProxySQL.

Key Actionable Insights

1
Implementing dry run modes for query rules can significantly reduce the risk of errors when deploying changes.
By previewing the effects of query rules before activating them, developers can ensure that their rules perform as expected without causing disruptions to database operations.
2
Utilizing a sidecar container for managing ProxySQL rules enhances scalability and reduces manual overhead.
This approach allows for centralized management of query rules across numerous instances, making it easier to adapt to changing requirements and maintain performance.
3
Regularly reviewing and updating query rules can prevent performance bottlenecks and ensure optimal database operation.
As application demands evolve, query patterns may change, necessitating adjustments to rules to maintain efficiency and responsiveness.

Common Pitfalls

1
Creating overly broad query rules can lead to performance degradation and unintended query rejections.
This often occurs when regex patterns are not carefully defined, resulting in excessive logging and processing overhead. To avoid this, ensure regex patterns are specific and tested before deployment.

Related Concepts

Database Resiliency
Dynamic Rule Management
Proxysql Configuration