Diagnosing Slow Queries After Migrating to ApsaraDB RDS
An e-commerce team migrated its order database from self-managed MySQL on ECS to an ApsaraDB RDS for MySQL instance. Before migration, p99 read latency was 8 ms. After migration, p99 read latency is 60-90 ms during business hours, and the application team blames the managed service. Investigation notes: the RDS instance is a 4 vCPU / 16 GB general-purpose instance; the old ECS database host had 16 vCPU / 64 GB. Average CPU on the new instance is 45% but spikes to 95% during checkout peaks. The application connection pool was copied unchanged: 200 connections per app node across 6 nodes. Slow query logs show the same top queries as before migration, but each is executed far more often because a caching layer was disabled during cutover and never re-enabled. The team asks whether to scale up the RDS instance, add a read-only instance, or tune the application.
1. Evaluate the three proposed remedies. Which addresses the root cause, and which are premature?
Read the worked answer
The evidence points to a load/behavior change, not a raw capacity deficit, so tuning the application is the first and correct action.
- Scale up: CPU averages 45% and only spikes to 95% at peaks. A larger instance would mask the peak but the average headroom shows the instance is not chronically saturated. Scaling up before fixing the disabled cache and the connection count is premature and expensive.
- Add a read-only instance: read latency is the symptom, but the slow-query log shows identical queries executed far more often. The extra read volume is self-inflicted by the missing cache. Adding a read replica would divide the load but would not remove the redundant work; it is a valid later step once real read volume is understood.
- Tune the application: re-enable the caching layer, which removes the bulk of the extra query executions, and right-size the connection pool. 200 connections x 6 nodes = 1,200 connections against a 4 vCPU instance is excessive and causes context-switching and memory pressure; a pool sized to roughly the instance's concurrency (tens per node, not hundreds) is appropriate.
Root cause: redundant query volume from the disabled cache plus connection over-subscription. Fix those, re-measure, then consider read replicas if genuine read load remains high.
2. What measurement plan would you use to confirm the fix and decide whether further scaling is needed?
Read the worked answer
Establish a before/after baseline with the same metrics over comparable windows:
- Query volume: QPS and total executions of the top slow queries. Re-enabling the cache should sharply reduce executions; if it does not, the cache is misconfigured.
- Latency: p50/p95/p99 read latency during business hours, not just averages.
- Resource: CPU (average and peak), active connections, and connection churn. Watch whether the 95% peak persists after cache restoration.
- Contention indicators: slow-query count and lock waits.
Decision rule: if p99 returns near the pre-migration 8 ms and peak CPU drops well below the spike level, no scaling is needed. If p99 is acceptable but peak CPU remains near saturation, add a read-only instance to offload reads before scaling up the primary. Only scale up the primary if write-path CPU or memory is the binding constraint. Note the instance is smaller than the old host in vCPU and memory, so verify that buffer pool and working set still fit; if the working set exceeds memory, that is a genuine capacity reason to scale, distinct from the query-volume issue.