TechPrufer
Get started
Shopify
Shopify logoShopify · Feature trail

Database Replica Read Consistency

2 posts · Feb 2021 – Sep 2021

What this trail covers

Shopify has evolved its approach to managing database replica read consistency. Initially, replication lag was a concern for read-heavy applications. The team explored tight consistency and causal consistency (GTID-based) but found them too complex or expensive. The chosen solution is a form of monotonic read consistency, achieved by modifying ProxySQL to route related read requests to the same replica using a consistent read ID (UUID) embedded in query comments. This ensures a co

All Shopify trails
  1. Shard Balancing: Moving Shops Confidently with Zero-Downtime at Terabyte-scale - ShopifyThis post details Shopify's strategy for shard balancing in their MySQL database infrastructure. It explains the architecture of isolated pods, each containing a MySQL shard, and the need to rebalance shops to ensure even utilization and mitigate failure risks. The post outlines two key problems: determining shop distribution and executing zero-downtime moves. It describes Ghostferry, an in-house tool for batch copying and binlog tailing to migrate data, using `SELECT...FOR UPDATE` for data integrity and binlog streaming for changes. The migration process is broken down into three phases: batch copying/binlog tailing, cutover, and control plane updates.
  2. Read Consistency with Database Replicas - ShopifyThis post details the implementation of a consistent read algorithm for database replicas. The core contribution is the modification of ProxySQL to use a `consistent_read_id` embedded in query comments. This ID is hashed to deterministically select a database replica for a series of related read requests, ensuring monotonic read consistency. The post also describes solutions for handling server unavailability by indexing into the full list of configured servers and disabling secondary load balancing for consistent read requests to prevent inconsistencies.