Loading…
ProxySQL
2 posts about ProxySQL. Every summary links to the original.
Shopify ·
Dynamic ProxySQL Query Rules
Shopify uses ProxySQL to manage application connections to MySQL databases, but applying query rules across thousands of ProxySQL instances is difficult, especially during incidents. ProxySQL rules can reroute, rewrite, or reject regex-matched queries, yet incorrect patterns may cause unintended rejection, rewriting, data corruption, or downtime. To make rule changes safer, Shopify added dry-run support to a ProxySQL fork, using the mysql_query_rules table’s active and log fields; dry-run rules log matched queries without executing their actions. A sidecar polls a global key-value store, synchronizes dynamic rules to each ProxySQL instance, caches rules, supports optional TTLs, and uses exponential backoff for broad logging patterns. The system favors consistency during synchronization failures, while static startup rules cover requirements for full availability.
2023-10-18Shopify ·
Read Consistency with Database Replicas
Database replicas can improve read-heavy application performance and reduce pressure on primary servers, but differing replication lag can make multi-query reads produce incoherent datasets. Shopify’s Database Connection Management team compared tight consistency and GTID-based causal consistency before choosing opt-in monotonic read consistency, in which related reads are routed to one replica and therefore follow a consistent timeline rather than necessarily seeing the latest data. Their ProxySQL fork accepts a UUID in the consistent_read_id query comment, hashes it into a pseudorandom index over a weighted hostgroup server list, and disables later rebalancing that could override the selection. The implementation indexes configured servers before handling outages, though a selected server failure can still break consistency. The approach offers low overhead but tolerates occasional inconsistencies.
2023-10-18
