8 TB MySQL primary with analytical queries on the only replica
A ticket-selling platform runs MySQL 8.0 on a single 8 TB primary with one 8 TB read replica. Seat-map reads and per-event reports both go to that replica. During on-sales, replication lag reaches 40 minutes, so customers see stale seat availability and occasionally double-book. The reports scan three years of order history and hold the replica for up to 20 minutes at a time. Peak write volume is around 60,000 writes per minute, the orders table alone is 4.5 TB, and finance wants reporting refreshed every 15 minutes. The platform team has no MySQL DBA experienced in manual sharding.
1. Diagnose the lag and propose a target architecture for read and reporting traffic.
Read the worked answer
The lag is a capacity problem on the replica, not a MySQL bug.
Both the seat-map reads and the 20-minute reporting scans consume the replica's CPU and I/O; when the apply thread cannot get resources, it falls behind the primary. Adding an index will not fix a reporting query that scans three years of 4.5 TB of orders - it will still hold the replica for minutes.
Separate the three workloads:
- Latency-critical seat reads → DCS for Redis in front of the database, with the database as the authority for the final booking write. A cache miss must never be the reason a seat is sold twice; the sale itself needs a conditional update on the primary.
- Read scaling → one or more dedicated read replicas used only by the application's read path, with read/write splitting, so reporting can never starve them again.
- Reporting → replicate with DRS into a DWS (or big-data) layer and run the scans there. Columnar MPP storage is built for three-year aggregates and does not compete with the OLTP replica. If finance's 15-minute freshness matters more than cost, near-real-time DRS keeps the gap small; if not, hourly batch is cheaper.
The judgement is that a single replica cannot serve both a millisecond read path and a multi-minute scan; the fix is physical separation, not tuning.
2. The orders table is 4.5 TB and growing. Compare DDM, GaussDB and hand-built sharding for this team.
Read the worked answer
The team's skill profile is a hard constraint, so it eliminates one option immediately.
- Hand-built sharding (application-level routing plus multiple RDS instances) gives complete control and makes every cross-shard query and distributed transaction the application team's problem. With no experienced sharding DBA and six-figure order volume per minute, this is how migrations stall halfway.
- DDM is MySQL-compatible middleware that presents logical tables and shards them transparently behind a shard key, so the application keeps writing MySQL SQL. It fits when the workload is MySQL, write-heavy on one large table, and the team cannot run sharding themselves. The cost is that cross-shard JOINs and distributed transactions are limited, and shard-key choice becomes the single most important design decision.
- GaussDB means moving off MySQL semantics to a different engine - the right long-term answer if you also want to consolidate analytics and OLTP, the wrong answer on an on-sale deadline.
For this case DDM is the pragmatic fit, with the 4.5 TB orders table sharded and reporting already offloaded to DWS. If a future re-platform is on the roadmap, that decision should be taken deliberately later, not under on-sale pressure.
3. Which shard key would you choose for orders, and what breaks either way?
Read the worked answer
There is no free shard key here; choose the one whose failure mode you can absorb.
- customer_id makes "my orders" single-shard and gives near-uniform distribution, because customers are many and roughly balanced. It breaks on the dominant on-sale query: "seats remaining for this event", which then scatters across every shard. But that query should be served from the Redis seat map, not from orders at all, so the scattering falls on the rarer path.
- event_id makes per-event reporting single-shard, but celebrity or stadium events create extreme skew - one shard takes the hottest event and the cluster's balance assumption collapses exactly when load peaks. That is the worse failure mode for a ticketing platform.
Recommendation: shard orders by customer_id, keep per-event aggregates in DWS where they are pre-computed, and keep the live seat inventory in DCS with atomic operations. Also verify the order-id strategy - a globally unique ID must not be a database sequence that becomes a cross-shard bottleneck, so use a distributed ID generator.
Write the decision down with the queries it optimises and the queries it penalises; the shard key is effectively permanent once 4.5 TB of data is distributed.