🔍 Bottleneck Hunting · Days 19–24
Day 22: Slow Queries & DB Locks
Objective
Load a DB-heavy endpoint and find the slow query causing throughput collapse.
Scenario
Run a read-heavy or write-heavy transaction against a database. Use slow query logs, APM tracing, or DB monitoring to identify the worst offender.
Metrics to Watch
When It Clicks
One unindexed query can cap the throughput of the whole system.
Solution
Run a DB-heavy endpoint under load. Enable slow query log: in MySQL `SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.5;`. In PostgreSQL use `log_min_duration_statement = 500`. After the test, run `EXPLAIN ANALYZE` on the worst offenders. A full table scan (Seq Scan with high row count in Postgres, or 'type: ALL' in MySQL EXPLAIN) without an index is the most common culprit. Add the missing index and retest - throughput typically doubles or more for read-heavy workloads.
Reflection
Was the bottleneck the query itself, the connection pool, or the lock contention?
Deliverable
A script that reproduces a DB bottleneck, plus the identified query.
⚠️ Never run load tests against infrastructure you do not own or have explicit written permission to test. Always use a dedicated test environment. Unauthorized load injection can cause outages, trigger legal liability, and violate terms of service.