Reporting query starves a MySQL read replica
A SaaS team runs Cloud DB for MySQL with an 8 vCPU, 32 GB primary and one read replica. The web tier sends 1,200 reads/s and 250 writes/s. Every hour a reporting job runs a five-minute aggregation on the replica. During that job, replica lag rises from under one second to 45 seconds, and p95 API latency triples because some read traffic still goes to the replica. The primary stays at 55% CPU, while the replica reaches 100% CPU and its disk queue grows. The database holds 2 TB, uses seven-day automatic backups, and has a 03:00 maintenance window. The application's read/write split cannot change this quarter, but the team can change DB specs, add replicas, or move reporting. The API target is 300 ms p95; reporting must finish within 15 minutes. They want a tested failover before the next quarter.
1. Diagnose the lag. Which evidence distinguishes a reporting workload problem from a replication or primary problem?
Read the worked answer
- The primary is at 55% CPU while the replica is at 100% CPU with a growing disk queue. That points to work executing on the replica, not to primary write saturation or network breakage.
- The hourly correlation is the strongest signal: lag rises only when the five-minute aggregation runs. Check the replica slow query log and execution plan for full scans, temporary tables, and large sorts. A reporting query that scans 2 TB of history can saturate the replica's CPU and disk even if it returns few rows.
- Replication evidence: measure the relay log apply rate and the lag timeline. If lag starts after the reporting job begins and recovers when it ends, the apply thread is being starved by read competition. If lag rises without reporting, investigate binlog size, network throughput, and primary write bursts.
- Application evidence: confirm which read queries are routed to the replica. If p95 triples while replica lag is 45 seconds, the application is reading stale or blocked data from the replica. That is a routing problem layered on the lag, not proof that the primary is slow.
- Do not fix this by adding CPU to the primary. The primary is not the bottleneck.
2. Recommend a target architecture and query changes that meet 300 ms p95 and a 15-minute reporting finish.
Read the worked answer
- Add a second read replica dedicated to reporting. Route all reporting traffic to it and keep the existing replica for API reads. Give the reporting replica more CPU and faster storage, for example 16 vCPU and 64 GB, because the aggregation is CPU and disk bound.
- Remove reporting from the API read replica entirely. Even with a dedicated replica, scheduled reporting should not share a host with user-facing reads.
- Tune the query: add a composite index on the time and tenant columns used by the aggregation, avoid SELECT *, and pre-aggregate hourly rollups if the five-minute job is scanning raw events. Set a statement timeout so a runaway report cannot occupy the replica indefinitely.
- If 15 minutes is still missed, move reporting to an analytics store or build a summary table on the primary during the 03:00 window. The API target is protected by isolating reporting; the reporting target is met by reducing scanned data.
- Monitor per-replica lag, CPU, disk queue, and slow query count in Cloud Insight. Alarm on API replica lag above five seconds, not on the reporting replica, because reporting lag is expected during its job.
3. How do you test failover without risking the production API, and what happens to reporting during failover?
Read the worked answer
- If Cloud DB for MySQL high availability is not enabled, enable it and understand the managed failover behavior. If it is enabled, schedule a controlled failover in the 03:00 maintenance window, not during business hours.
- Before the test, confirm the application uses the service endpoint and retries connections with backoff. A hard-coded primary IP will fail over badly. Run the test in staging first with the same connection strings and batch jobs.
- During the test, stop reporting jobs, record the failover duration, verify that writes resume, and check that the API error rate returns to zero within the retry budget. Validate that monitoring alarms fire and clear.
- Reporting should be paused during failover. After promotion, the reporting replica may need to reattach or be rebuilt; do not point reporting at the new primary. Confirm lag recovery and rerun the hourly job manually if the window was missed.
- Rollback is a second controlled failover or a restore from backup, so the runbook must state the decision point and the maximum acceptable data loss, not just the command to promote.