Website Architecture and Database Tools & Commands: Ready to Reuse

Performance troubleshooting doesn't require complex tools; a handful of set commands can identify most issues. Below is organized in a 'discover — locate — veri…

Insights:Website Architecture and Database Tools & Commands: Ready to Reuse

Discovery: Identify Where It's Slow

  • Enable the slow query log with a threshold of 200ms and monitor for one week.
  • Record page TTFB baselines: take the average of 10 measurements each for the homepage, listing page, and detail page.
  • Check when server load peaks occur.

Interpretation: If slow queries are concentrated in a few SQL statements, the problem is query-related; if they're spread across many SQL statements, it indicates overall pressure.

Diagnostics: The Queries Themselves

  • Run EXPLAIN on slow SQL, focusing on:
  • Whether type is ALL (full table scan).
  • Whether key is empty (index not used).
  • Whether rows scanned is much larger than rows returned.
  • Whether Extra shows Using filesort or Using temporary.

If you see ALL combined with a large rows value, the issue is likely confirmed.

Diagnostics: Index Usage

  • Check the index list to confirm field names and order match expectations.
  • Review index statistics to identify unused indexes and consider removing them.
  • Compare query conditions against the leftmost column of each index.

Common Misdiagnosis: An index may exist but with incorrect column order, causing the execution plan to still fall back to a full table scan.

Diagnostics: Connections and Concurrency

  • Check current connections versus the maximum to determine if the limit is near.
  • Examine waiting connections to identify long-running transactions holding locks.
  • Verify whether the connection pool configuration aligns with the database's connection limits.

Diagnostics: Caching

  • Check cache hit rate and key count growth trends.
  • Anomalous growth in key count typically stems from random dimensions embedded in keys.
    • A low hit rate paired with high traffic indicates that hot data isn't staying in cache.

    Validation: Was It Effective?

    • Run the execution plan for the same SQL query both before and after the change, then compare rows and type.
    • Average the TTFB across 10 measurements for the same page, before and after the modification.
    • Monitor the weekly trend in slow query log entries.

    Without a baseline, there's no way to validate results. Always record numbers before making changes.

    A Fixed Diagnostic Sequence

    1. Slow query log → identify the top 10 entries.
    2. Execution plan → determine whether a full table scan is happening.
    3. Index statistics → assess whether indexes are effective.
    4. Connections and locks → check for resource contention.
    5. Cache → evaluate whether caching could be introduced.
    6. Retest → compare against the baseline.

    Boundaries

    These tools can only tell you "where the bottleneck is" — not "how the business should be designed." Adjustments to the data model and access patterns still require sound business judgment.

    Related Reading: