Insights:Website Architecture and Database Troubleshooting: From Symptoms to Root Cause
Symptom 1: The entire site suddenly slows down
Possible layer: Database pressure, disk I/O, external dependency timeouts.
Troubleshooting path:
- Check whether CPU and disk I/O are rising simultaneously.
- Check whether slow query logs surge during the same period.
- Check for uncaught external interface timeouts.
- Check whether someone is running batch jobs or full exports.
Root cause judgment: High database CPU + concentrated slow queries → query issue; high disk I/O with normal queries → backup or log writes.
Symptom 2: Occasional timeouts that resolve on retry
Possible layer: Connection pool exhaustion, lock waits, cache stampede.
Troubleshooting path:
- Check whether the connection count curve shows a sawtooth pattern.
- Check for long-running transactions holding locks.
- Check for request spikes when cache expires.
Root cause judgment: Sawtooth connections + no obvious slow SQL → connection pool too small or requests queuing; spike at cache expiry moment → cache stampede.
Symptom 3: Backend changed but frontend unchanged
Possible layer: Page cache, CDN cache, object cache, master-slave replication delay.
Troubleshooting path:
- Access origin directly, bypassing CDN.
- Check whether page cache is being hit (inspect response headers).
- Check whether backend updates cleared the corresponding cache keys.
- Check whether reads are going to the replica.
Root cause judgment: Origin correct but public network stale → CDN; origin also stale → page or object cache; reading immediately after update → master-slave delay.
Symptom 4:大量 errors during peak hours
Possible layers: Connection exhaustion, queue backlog, file handles, memory.
Troubleshooting path:
- Check the error type: connection failure, timeout, or out of memory.
- Check the process count and queue length.
- Check whether any slow request is holding up a process for too long.
Root cause judgment: Errors concentrated on connection failure → connection pool or database limit; errors concentrated on timeouts → slow-request backlog.
Three Principles During Troubleshooting
- Stabilize first, investigate later: Restore service before looking for the root cause.
- Change only one thing at a time: Changing both caching and queries simultaneously makes it impossible to attribute the effect.
- Preserve evidence: Screenshot and archive logs, monitoring graphs, and execution plans.
Common Misjudgments
- Treating a "slow database" as an "inadequate server" and upgrading specs — the problem persists.
- Blaming a broken cache when the real issue is poor key design causing low hit rates.
- Assuming master-slave replication lag is a code bug and changing code with no effect.
Scope
The troubleshooting path covers common symptoms but not code-level defects (e.g., infinite loops, memory leaks). The latter require log analysis combined with code review.
Related reading:
- Website Architecture, Database Tools & Commands: Ready to Reuse
- Common Website Architecture & Database Mistakes: These 6 Settings Hurt Performance the Most