---
title: "Shard Balancing: Moving Shops Confidently with Zero-Downtime at Terabyte-scale"
description: "Shopify’s MySQL shards can become unevenly utilized as merchants grow after being assigned to arbitrary shards, increasing over-utilization risk while leaving other capacity underused. The post describes a strategy for selecting shop placements using historical utilization and traffic data, simulations, and potentially other metrics such as shop size, GMV, and move time. It then explains online shop migration with Ghostferry, which batch-copies records and tails MySQL’s binary log before a brief cutover updates the routing table and directs new writes to the target pod. Verification checks data completeness, integrity, and stale traffic before old data is pruned; the migration system’s central algorithm is also represented in a TLA+ formal specification. The stated outcome is a more balanced infrastructure that can isolate high-traffic merchants across pods with virtually zero consumer-facing downtime."
---

# Shard Balancing: Moving Shops Confidently with Zero-Downtime at Terabyte-scale

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · Sep 24, 2021

**Type:** Explainer

## Summary

Shopify’s MySQL shards can become unevenly utilized as merchants grow after being assigned to arbitrary shards, increasing over-utilization risk while leaving other capacity underused. The post describes a strategy for selecting shop placements using historical utilization and traffic data, simulations, and potentially other metrics such as shop size, GMV, and move time. It then explains online shop migration with Ghostferry, which batch-copies records and tails MySQL’s binary log before a brief cutover updates the routing table and directs new writes to the target pod. Verification checks data completeness, integrity, and stale traffic before old data is pruned; the migration system’s central algorithm is also represented in a TLA+ formal specification. The stated outcome is a more balanced infrastructure that can isolate high-traffic merchants across pods with virtually zero consumer-facing downtime.

## Context

Merchants are assigned to arbitrary shards, but their sizes and traffic patterns change over time. Resource-intensive shops can consequently share a shard, producing large differences in database utilization, increasing failure risk on heavily used shards, and leaving lightly used capacity underused. The migration must preserve availability, data integrity, and reasonable throughput.

## Approach / What changed

Shopify models desired shop placement using historical shard utilization and traffic data, simulates proposed moves, and can prioritize metrics including shop size, GMV, and time to move. Ghostferry performs the migration through batch copying and MySQL binlog tailing, followed by a cutover that updates the unsharded routing table. The system then verifies the move, serves traffic from the target pod, and prunes stale source data.

## Takeaways

- Shop placement can be evaluated with simulated move lists so the expected effect on shard-utilization deviation and under- or over-utilized shards is assessed before migration.
- Ghostferry combines batch copying with MySQL binary-log tailing to transfer existing shop data and capture writes made during the migration.
- After cutover, verification checks data transfer correctness and that traffic and writes no longer reach the source shard before stale data is removed.

**Tags:** [Architecture](https://yomu.fyi/topic/architecture), [Migrations](https://yomu.fyi/topic/migration), [MySQL](https://yomu.fyi/topic/mysql), [Reliability](https://yomu.fyi/topic/reliability), [Scalability](https://yomu.fyi/topic/scalability)

- Source: [Shopify](https://shopify.engineering/mysql-database-shard-balancing-terabyte-scale)
- Source URL: https://shopify.engineering/mysql-database-shard-balancing-terabyte-scale
- Ingested by Yomu: 2026-08-30T15:29:12.005Z

[Read original post](https://shopify.engineering/mysql-database-shard-balancing-terabyte-scale)