Website Architecture and Database Troubleshooting: From Symptoms to Root Cause

There are only a few types of symptoms, but over ten kinds of root causes. Following the "symptoms → layer → evidence" approach helps you avoid detours.

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:

  1. Check whether CPU and disk I/O are rising simultaneously.
  2. Check whether slow query logs surge during the same period.
  3. Check for uncaught external interface timeouts.
  4. 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:

  1. Access origin directly, bypassing CDN.
  2. Check whether page cache is being hit (inspect response headers).
  3. Check whether backend updates cleared the corresponding cache keys.
  4. 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

  1. Stabilize first, investigate later: Restore service before looking for the root cause.
  2. Change only one thing at a time: Changing both caching and queries simultaneously makes it impossible to attribute the effect.
  3. 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:

  • Page Cache & CDN Troubleshooting: From Symptoms to Root Cause