A database problem rarely announces itself as a database problem. The report is "the site is slow after lunch", "the invoice page times out", or "the admin panel takes ten seconds to save an order". By the time someone opens the database, three other theories have already been tried.
This guide is a diagnostic sequence for teams running MySQL, MariaDB or PostgreSQL behind a web application, which is the common shape of a Bangladeshi SME stack whether it sits on shared hosting, a local VPS or a cloud instance. The order matters: measure first, explain second, change third.
Symptoms worth investigating
- Page latency that rises with concurrent users and falls overnight.
- Timeouts on one page while the rest of the site is fine.
- CPU pinned near maximum with no obvious traffic spike.
- High disk queue length while the processor sits idle.
- Lock wait timeouts during order or stock updates.
- Connection errors at peak even though the application pool looks correctly sized.
- Replication lag on a read replica used for reporting.
- A query that was fast last month and is slow this month without a code change.
Diagnostic sequence
- Confirm the database is actually the cause. Time the request end to end, then time the query in isolation. If the query returns in 40 milliseconds and the page takes four seconds, the database is a symptom. Evidence: request timing broken down by layer.
- Enable slow query logging. Set the threshold from user patience rather than the default. Collect a full business day, including the peak hour. Evidence: slow query log with timestamps.
- Rank queries by total time, not average. A 300-millisecond query called twenty thousand times costs more than a three-second report run once. On PostgreSQL,
pg_stat_statementsprovides the ranking; on MySQL,performance_schemaor a digested slow log does the same job. Evidence: top ten queries by total execution time. - Read the execution plan. Use
EXPLAINand, where supported,EXPLAIN ANALYZE. Look for sequential scans on large tables, nested loops over thousands of rows, sorts that spill to disk and estimated row counts that bear no relation to actual counts. Evidence: saved plans for each candidate query. - Audit the indexes that exist. Missing indexes cause scans; duplicate and unused indexes slow every write. A composite index must match the query's filter and sort order to help. Evidence: index list per table with columns and usage counts.
- Check statistics and data volume. A table that grew from thousands to millions of rows changes the planner's choices, and stale statistics produce bad plans. Evidence: table sizes and last-analyze timestamps.
- Check locks and long transactions. An idle transaction holding a row lock blocks every order update behind it. Look for sessions sitting idle inside a transaction. Evidence: lock wait graph and the blocking session.
- Check connections and pooling. Too many connections cause context switching and memory pressure; too few cause queueing. Compare maximum connections with observed concurrent sessions. Evidence: connection count over 24 hours.
- Check the storage layer. Cloud volumes have IOPS and throughput ceilings, and a burst balance that runs out at midday produces exactly the slow-after-lunch pattern. Evidence: disk latency and IOPS against the provisioned limit.
- Fix in the application where possible. Remove N+1 queries, paginate lists, cache expensive reads and move reporting to a replica. Query changes are faster to reverse than schema or configuration changes. Evidence: change log with before-and-after timings.
Cause and fix reference
- Missing index on a filter column. Add a composite index covering the filter and sort, then verify that write latency did not regress.
- Function wrapped around an indexed column.
WHERE DATE(created_at) = ?cannot use a plain index. Rewrite it as a range:created_at >= ? AND created_at < ?. - Leading wildcard search.
LIKE '%term%'forces a scan. Use a full-text index or a search service for that feature. - N+1 queries. A list of fifty orders issuing fifty customer lookups turns one page into fifty-one round trips. Join or batch-load instead.
- Unbounded report. A monthly report that scans every order since launch gets slower every month. Aggregate into a summary table on a schedule.
- Lock contention. Shorten transactions, update rows in a consistent order, and move payment or courier calls outside the transaction.
- Connection storm. Add pooling in the application or a proxy, and cap workers at what the database can actually serve.
- Plan regression after an upgrade. Compare plans before and after, then rewrite or pin the query rather than disabling the planner.
Common mistakes
- Adding an index to every column and wondering why writes became slower.
- Increasing the server size before reading a single execution plan.
- Applying database parameters copied from a post written for a different workload.
- Putting a cache in front of a query that returns wrong results, which hides the bug instead of fixing it.
- Testing against a development database with two thousand rows while production holds two million.
- Ignoring the reporting workload, which is often the largest single consumer of database time.
Evidence checklist
- Slow query log for a full business day, including peak.
- Top ten queries by total time, with call counts.
- Execution plans before and after each change.
- Index inventory with usage counts and duplicate detection.
- Connection and lock graphs for the peak hour.
- Storage latency and IOPS against the provisioned ceiling.
- Before-and-after response times for the affected pages.
Metrics
- p95 query latency for the ten most important queries.
- Number of slow queries per hour, not per day.
- Buffer cache hit ratio, with the caveat that a high ratio can still hide a bad plan.
- Lock wait time and count of blocked sessions.
- Connections in use against the configured maximum.
- Replication lag in seconds.
Local conditions
Shared hosting hides most of the evidence. Slow query logs and EXPLAIN output may be unavailable, connection limits are fixed, and the database is shared with other tenants. If the site runs on a VPS with 2 GB of memory, the database, PHP workers and a control panel compete for the same RAM, so an extra connection can be more expensive than it looks. Month-end reconciliation and campaign peaks are the two periods when a marginal configuration turns into an outage, so test at those volumes rather than at today's average.
Where this fits in our work
We handle this kind of diagnosis as part of Managed Technology & Cybersecurity, alongside server monitoring and backup verification. The pricing page explains how an engagement is scoped. For the cost side of the same decision, read the FinOps guide for small teams.
Sources
- PostgreSQL Documentation, Using EXPLAIN, accessed 17 November 2026.
- PostgreSQL Documentation, pg_stat_statements, accessed 17 November 2026.
- MySQL Reference Manual, Using EXPLAIN, accessed 17 November 2026.
- Markus Winand, Use The Index, Luke, accessed 17 November 2026.
- PostgreSQL Documentation, Monitoring Database Activity, accessed 17 November 2026.
Responses (0)
No comments yet. Add the first one below.
Leave a response
Your email address will not be published. Required fields are marked *.