Alibaba Cloud Observability Lab: Finding a Production Problem Before Users Do

Part 9 of the Alibaba Cloud Engineering Lab Series.

Architecture

Application
     ↓
High CPU
     ↓
Slow responses
     ↓
Monitoring alert (Cloud Monitor)
     ↓
Log investigation (Log Service / SLS)
     ↓
Root-cause analysis
     ↓
Fix
     ↓
Verification

Observability stack: Cloud Monitor for metrics and alerting thresholds, Log Service (SLS) for structured application logs, and a deliberately introduced bug to make the investigation genuine rather than narrated.

Before the how, the what — three terms this walkthrough leans on:

• Observability — the ability to answer questions about a system's internal state you didn't know you'd need to ask in advance, by combining metrics (numbers over time), logs (discrete events), and traces (a request's path through multiple services). "Monitoring" tells you a known thing broke; observability lets you investigate an unknown thing that broke.
• Metric vs. log — a metric is a number sampled over time (CPU %, request count) — cheap to store, great for "is something wrong right now," but it can't tell you why. A log is a detailed record of a specific event (this request, this error, this query) — more expensive to store at volume, but it's where the actual root cause usually lives.
• Root-cause analysis — the practice of tracing a symptom (slow responses) back through each layer (application → database → specific query) until you find the actual cause, rather than fixing the first plausible-looking thing and hoping. The "detection chain" in this article — alert → logs → slow-query log → EXPLAIN — is root-cause analysis in a specific, repeatable form.

I seeded a real 1.24M-row table with the bug, watched the query genuinely take 2.9 seconds, and measured the fix drop it to 4ms — these aren't illustrative numbers, they're what actually ran. The companion repo reproduces the whole incident end to end against a real Postgres instance.

Problem

An API endpoint was seeded with an intentional performance bug (an unindexed database query added on purpose) and load was generated against it — the exercise: detect it the way you would in production, without knowing in advance what the bug is.

What Happened

At roughly 6 minutes into the load test, Cloud Monitor fired a CPU utilization alert on the RDS instance — sustained above 85% for 5 minutes. Application-side, p99 latency on the affected endpoint climbed from 120ms to 3.8 seconds.

Why It Happened

-- Log Service query: slow requests on the affected endpoint
* | select endpoint, avg(duration_ms) as avg_ms, count(*) as requests
  from log
  where endpoint = '/api/orders/search'
  group by endpoint

The logs isolated the slow endpoint immediately. The RDS slow-query log was the next stop:

SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
-- Query time: 2.9s, rows examined: 1,240,000

A full table scan on 1.2M rows, once per request, because customer_email had no index. Under low load this was invisible — a couple hundred milliseconds nobody noticed. Under concurrent load, it saturated RDS CPU and cascaded into API-wide latency.

How We Detected It

The detection chain, in order: Cloud Monitor alert → SLS log query isolating the slow endpoint → RDS slow-query log identifying the specific query → EXPLAIN confirming the missing index. Each step narrowed from "something is wrong" to "this exact query, this exact cause" — the standard incident-response funnel, regardless of which cloud's tools you're running it on.

How We Fixed It

CREATE INDEX idx_orders_customer_email ON orders(customer_email);

Re-ran EXPLAIN: query plan changed from a full table scan to an index seek, execution time dropped from 2.9s to 4ms.

Verification

Re-ran the identical load test. RDS CPU stayed under 30% throughout; p99 latency on the endpoint held at 95ms. Confirmed against the same load profile, not a lighter one — verification only counts if it reproduces the original stress condition.

How We Prevent It

• Added a slow-query alert (Cloud Monitor custom metric, threshold >500ms) so the next unindexed query surfaces in staging load testing, not production traffic.
• Added a migration-review checklist item: any new WHERE clause column on a table over 100k rows requires an index review before merge.
• Documented the detection chain itself (alert → logs → slow-query log → EXPLAIN) as the team's standard first-response runbook for latency incidents, so the next on-call engineer isn't starting from zero.

Cost / Performance

The entire fix was a single index — zero infrastructure cost, all engineering diagnosis. The most expensive part of this incident class isn't the fix; it's the time between symptom and root cause, which is exactly what a good observability stack compresses.

Lessons Learned

• A query that's fine at low load can be a production incident waiting for scale — load-test with realistic data volume, not a seed dataset of 50 rows.
• The detection chain (alert → logs → deep diagnostic → root cause) matters more than any single tool — Cloud Monitor and SLS are provider-specific, but the funnel is identical to Azure Monitor + Log Analytics or CloudWatch + X-Ray.
• Document the runbook while the incident is still fresh — the "how we prevent it" step is the one most teams skip under time pressure, and it's the step that actually compounds in value.

GitHub Repository: alibaba-cloud-observability-lab — the reproducible incident -- seed, diagnose, fix, verify, ready to run.

Observability · Alibaba Cloud · SRE · Incident Response · Log Service · Cloud Monitor

Originally published on my portfolio.