Database Monitoring, Troubleshooting, and Diagnostics Questions
Observing and fixing databases in production: health checks, metrics and alerting, and diagnosing common failures like slow queries, lock contention, replication lag, resource exhaustion, and data-integrity incidents such as duplicate keys or lost updates after a crash or migration. Covers a systematic troubleshooting method under incident pressure. Tests operational instincts distinct from design knowledge.
You've just inherited on-call ownership of a production relational database that's never had real monitoring set up. What would you actually put on a dashboard and watch, and how would you go about setting alert thresholds so the on-call rotation gets paged for real problems without drowning in noise?
Sample Answer
Direct answer
I'd put four categories on the dashboard: request-level health (queries per second, latency at the p50 and p99 percentiles, error rate), resource saturation (connections in use, cache hit ratio, lock wait time), replication and durability (replication lag, write-ahead log queue depth, backup freshness), and the specific failure modes that have actually paged people before. For alerting, I set thresholds using rolling windows and a warn/page split, not single static numbers, and I map every page to a first remediation step so the on-call engineer isn't starting from zero at 3am.
Structured elaboration
What goes on the dashboard, and why each one.
| Metric | What it tells you | Why it matters here |
|---|---|---|
| Queries per second (QPS) / transactions per second (TPS) | Overall load level | Baseline for everything else; a latency spike at 2x normal load means something different than the same spike at flat load |
| Latency, p50 and p99 | Typical vs. tail experience | The average hides the pages: a p50 of 5ms with a p99 of 4s tells you a subset of queries or users are badly served even while the average looks fine |
| Active connections vs. max_connections | Headroom before the pool is exhausted | This is a leading indicator: connection exhaustion is usually visible minutes before it causes outright rejected connections |
| Cache (buffer) hit ratio | Whether the working set fits in memory | A drop here often precedes a latency spike, because the database starts going to disk for data it used to serve from memory |
| Lock wait time / count of blocked sessions | Contention, not raw load | High QPS with low lock wait is a scaling problem; low QPS with high lock wait is a code or transaction-design problem |
| Replication lag (time and bytes) | Read-replica freshness, failover readiness (failover: promoting a replica to take over as primary if the current one fails) | Determines whether reads from a replica are stale and whether a failover right now would lose data |
| Write-ahead log (WAL) queue / replication send lag | Whether the primary is generating writes faster than a replica or backup process can consume them | Rising WAL queue depth with flat replication lag on other metrics is often a network or replica-I/O problem, not a primary-side one |
| Backup completion and validation freshness | Whether your last known-good restore point is real | A backup job that "succeeds" but was never test-restored is not actually a safety net; track age since last validated restore, not just last backup run |
If the cluster runs on a storage engine with background compaction (an LSM-tree, log-structured merge-tree: a storage design that buffers writes in memory and periodically merges, or compacts, them into new sorted files on disk instead of updating in place; or a columnar engine, one that stores each column's values together instead of each row, common for BI, business intelligence, and analytics workloads) sitting next to the OLTP (online transaction processing: the primary's regular small, latency-sensitive reads and writes) primary, such as a Citus columnar table (Citus is primarily PostgreSQL's distributed sharding extension; Citus columnar is one table access method within it, added for compressed, append-mostly storage of historical partitions, not the extension's main purpose) or a ClickHouse/TimescaleDB continuous-aggregate sidecar (a separate analytics-oriented database kept in sync with the primary specifically to serve rollup queries without loading the OLTP primary), add compaction backlog and table bloat (wasted, dead space that accumulates in a table or index and isn't automatically shrunk back down) to the list; a growing compaction backlog degrades read latency the same way index bloat does on a classic B-tree engine, just through a different mechanism.
Setting thresholds without drowning in noise.
Three techniques matter more than picking the "right" number:
- Use rolling windows, not instantaneous values. A single 5-second latency spike from garbage collection or a checkpoint is normal; the same value sustained for 5 minutes is not. Alert on "p99 latency over 500ms for 5 consecutive minutes," not on any single sample crossing 500ms.
- Split warn from page. Warn (visible on a dashboard, maybe a low-priority ticket) catches trend problems early; page (wakes a human) is reserved for things that are both urgent and actionable right now. Example: cache hit ratio dropping below 95% is a warn (the working set is starting to outgrow memory, worth planning around); dropping below 85% is a page (you're now taking a meaningfully worse I/O hit on a large fraction of reads).
- Prefer relative and anomaly-aware thresholds over hardcoded absolutes where the baseline varies by time of day or by deployment size. A fixed "QPS > 10,000" alert is wrong for a service whose normal peak is 50,000; a threshold expressed as a deviation from the same hour last week, or a simple rolling z-score, survives normal traffic-shape changes without needing to be hand-tuned every time the service grows.
Concrete threshold examples, with the reasoning attached (not just the numbers):
- Cache hit ratio: warn under 95%, page under 85%. Reasoning: below 95% you're starting to lose the benefit of memory caching on a meaningful slice of reads; below 85% the marginal cost of each additional point drops is large, because you've moved from "occasional disk read" to "disk read is now common."
- Replication lag: warn over 30 seconds, page over 300 seconds (5 minutes). Reasoning: 30 seconds of staleness is tolerable for most read-replica use cases and often self-heals; 5 minutes means a failover right now risks losing up to 5 minutes of committed writes on an asynchronous replica, and reads from that replica are stale enough to be user-visible.
- Long-running transactions: page anything open longer than 10 minutes holding row or table locks. Reasoning: a transaction open that long is either a bug (forgotten
COMMIT), a batch job that should be broken into smaller pieces, or actively blocking other work; none of those are things you want to discover only when something else pages. - I/O operations per second (IOPS) on the storage volume, if the cluster runs on cloud block storage (a virtual disk volume attached to the database server, such as AWS EBS or GCP Persistent Disk, as opposed to object storage): page at 90% of the provisioned IOPS ceiling, sustained for 5 minutes. Reasoning: cloud volumes throttle hard at the ceiling rather than degrading gracefully, so you want the page before you hit the wall, not after latency has already spiked.
Map every page to a first action, not just a threshold. An alert without a next step trains people to acknowledge and go back to sleep. Two of the most common:
- High I/O wait alert fires: first action is to check whether it's a specific query (via the active-session view) versus a broad pattern across all queries (pointing at the underlying storage), and whether a recent deploy or backfill job correlates.
- Replication lag alert fires: first action is to check whether the primary's write volume actually increased (a load-driven cause you may just need to ride out) versus the replica itself being resource-starved (a replica-side problem you can act on directly, like resizing or restarting a stuck apply process).
Worked example
Say the team's baseline is 2,000 QPS with p99 latency normally around 40ms. I'd set: p99 latency warn at 150ms sustained 5 minutes, page at 400ms sustained 5 minutes (roughly 4x and 10x baseline, chosen because a load test showed the application's own request timeout is 500ms, so 400ms is the last point where paging still gives someone time to act before users see timeouts, not after). Connections in use: warn at 70% of max_connections, page at 90%, because pool exhaustion is a hard cliff, not a gradual degradation, so you want the warning well before the cliff. If, after a week of this configuration, the warn-level connection alert has fired forty times with no corresponding incident, that's a signal the threshold is miscalibrated for this workload's normal variance, not that the metric is useless; the fix is widening the window or raising the threshold, not deleting the alert.
Trade-offs and pitfalls
The most common failure mode isn't missing an important metric, it's alerting on too many of them with static thresholds tuned for a different traffic level than the service has grown into, which trains the on-call rotation to ignore pages. The opposite failure, cutting alerts down to "just the essentials," risks the exact regression this question is trying to avoid: rediscovering a real incident an hour late because nothing caught it. Anomaly-aware and relative thresholds reduce false positives but are harder to reason about at 3am ("why did this page, the number doesn't look that bad") than a simple static threshold, so pair them with a runbook line that explains, in plain language, what the alert is actually detecting. A backup-freshness check that only verifies "the job ran" rather than "the resulting backup restores cleanly" is a common blind spot: it will show green right up until the day you actually need it and discover the backup was silently corrupt.
List and explain five key metrics you would monitor to assess the health and performance of a production relational database. For each metric, describe one alert condition you might set and why.
Sample Answer
Direct answer
Five metrics cover the core questions a responder needs answered first: is the database slow (a high-percentile query or write latency), is it actually serving load correctly (throughput and error rate), is it running out of room (connection and CPU or memory saturation), is its working set still fitting in memory (buffer or cache hit rate), and, if replicas exist, is it falling behind (replication lag). Pick alert thresholds relative to the workload's own historical baseline and its SLO (service-level objective, an internal target for how the system should perform), not a generic industry number, and expect an OLTP workload (online transaction processing, many small, fast operations) and an OLAP workload (online analytical processing, fewer, larger, longer-running queries) to need different baselines for the same metric.
Structured elaboration
| Metric | What it tells you | Example alert condition | Why that condition |
|---|---|---|---|
| p99 query or write latency | The tail experience, the slowest 1% of operations, catches problems an average hides | p99 latency more than 3x its trailing 7-day baseline for 5 consecutive minutes | Relative-to-baseline avoids one fixed number being wrong for every workload; 5 minutes filters transient blips from real degradation |
| Throughput and error rate (queries or transactions per second, and error or rollback rate) | Whether the database is serving load, and serving it correctly | Error rate above 1% of transactions over a rolling 5-minute window | A latency-only view can miss a database that's fast because it's failing fast, for example rejecting connections outright |
| Connections in use (percent of max) and CPU or memory saturation | Whether the instance is running out of room to accept more work | Connections in use above 80% of the configured maximum for 5 minutes, or CPU above 85% sustained for 10 minutes | Headroom-based thresholds catch the problem before the hard ceiling (connection refusals, out-of-memory) rather than after |
| Buffer or cache hit rate (percent of reads served from memory rather than disk) | Whether the working set still fits in memory; a falling hit rate predicts rising I/O-driven latency before it fully arrives | Hit rate drops more than 10 percentage points below its 30-day baseline | A sudden drop usually means a growing working set or a large one-off query evicting the cache, both worth knowing early |
| Replication lag (if replicas exist) | How far behind a replica is, and how much recent data a failover would risk losing | Lag exceeds the defined RPO (recovery point objective, the maximum acceptable amount of recently written data you could afford to lose), or a fixed threshold like 30 seconds absent a stated RPO | Lag is meaningless without a target; the condition should be "will this violate our stated data-loss tolerance," not an arbitrary number |
On a managed database
A managed relational database service typically removes OS shell access, but the same five categories are still visible, just organized through the provider's own console or API instead of OS commands: host-level metrics (CPU, memory, disk, network, exposed as instance-level monitoring), database-level metrics (connections, buffer hit ratio, via the engine's own views proxied through the console), query-level metrics (top SQL by load, via a performance-insight feature layered on top rather than raw direct access to the engine's own statistics views), and replication metrics (lag, as a first-class console metric since the provider manages the replica topology). Losing shell access doesn't mean losing observability; it means the same five things live in a different namespace. Know where each one lives before an incident, not during one.
OLTP versus OLAP baselines
The same five metrics need structurally different thresholds depending on workload shape. An OLTP system, many small, fast, latency-sensitive operations, should alert tight on p99 latency, since milliseconds matter, and treat connection saturation as urgent, since a starved OLTP system fails user-facing requests immediately; its throughput baseline is usually fairly steady and predictable. An OLAP system, fewer, larger, longer-running analytical queries, should alert loose on p99 latency, since a single query legitimately taking minutes is normal, but tight on sustained resource saturation, CPU, memory, or I/O held near capacity for an extended period is the actual OLAP health signal, since a single slow query is expected but the whole system staying pinned means queuing is building, and on queue depth (how many queries are waiting to run) rather than on individual query latency. Applying an OLTP-shaped p99-latency alert to a warehouse would page constantly on completely normal behavior, and applying an OLAP-shaped hourly-average view to an OLTP system would miss a real user-facing latency spike entirely.
Worked example
Turning the p99-latency alert condition into concrete numbers, showing why a baseline-relative threshold rather than a fixed one is the right shape:
p99_baseline_ms = 42 # trailing 7-day baseline
multiple = 3
threshold_ms = p99_baseline_ms * multiple
print(threshold_ms)
126
At a 42ms trailing baseline, the alert fires above 126ms. If legitimate growth naturally moves the baseline to, say, 55ms next quarter, the threshold recalculates to 165ms automatically, whereas a fixed ">100ms" rule set today would already be uncomfortably close to normal, healthy behavior by then and would need someone to remember to revisit it.
Trade-offs and pitfalls
- A fixed threshold copied from a blog post or another team's dashboard, rather than derived from this workload's own baseline, is the most common reason alerts are either constantly noisy (too tight for this workload's normal variance) or dangerously quiet (too loose, so real incidents don't page). Baseline-relative thresholds avoid this, but need enough historical data to compute a meaningful baseline; don't set them from one week of a system still finding its steady state right after launch.
- Alerting only on averages instead of a high percentile hides exactly the tail-latency problems users actually feel; a p99 spike can coexist with a perfectly healthy-looking average if it affects a small fraction of requests, which is often the fraction most likely to matter most.
- Five metrics is a floor, not a ceiling. This is the minimum viable health picture, not a complete observability strategy; deeper investigation, specific slow-query identification, lock waits, log growth, still needs its own dedicated tooling once one of these five flags a problem.
That is every published Database Monitoring, Troubleshooting, and Diagnostics question for Software Engineer so far. Browse the other topics in this category, or practice this one interactively.