Before ordering a larger database machine, how do you determine which resource — CPU, memory, or storage IOPS and latency — is actually limiting the server, and which of those a hardware upgrade will genuinely fix?

## Why utilisation graphs mislead "CPU is at 80%" tells you a resource is busy; it does not tell you the workload is limited by it. A query doing a needless full scan burns CPU and IO happily — the machine is busy doing the wrong work. The diagnostic discipline is **wait analysis**: for the sessions you care about, where is wall-clock time going? Every mainstream engine exposes some form of wait events or time breakdown, and the shape of that breakdown maps to a bottleneck class. ## The four classes **1. CPU-bound.** Cores near saturation, the working set fits in the buffer pool, waits are dominated by execution rather than IO. Typical causes: expensive predicates, large sorts and hash joins in memory, heavy row-by-row processing, high statement rate. Buying more or faster cores helps *if* the work is parallel — many small concurrent statements parallelize perfectly across cores; one huge single-threaded query does not, unless the engine can parallelize that operator. **2. Memory-bound.** The buffer cache hit ratio is low, physical read volume is high and random, and sort/hash operations spill to temporary storage. This is the most common and most fixable case, because RAM is comparatively cheap and the payoff is nonlinear: once the hot working set fits, random reads collapse to memory accesses and latency drops by orders of magnitude. Before buying RAM, check whether the working set is inflated by bad plans — a scan reads pages an index lookup would not. **3. Storage-bound.** Two distinct sub-cases, and conflating them is a classic mistake: - *Throughput/IOPS* — the device is at its queue-depth or bandwidth limit; scans, backups, and large writes suffer. More IOPS or more parallel devices help. - *Latency* — commit rate is capped by fsync latency. This is a **serial** constraint: a device with huge IOPS but mediocre latency will not raise single-stream commit rate. Lower-latency media or a battery-backed cache does. **4. Not a hardware bottleneck at all.** Resources look unremarkable but latency is bad. Look for: lock and latch waits (hot rows, hot index pages, a single sequence or counter), single-threaded serialization points (single-threaded replication apply, one background cleanup worker, a nightly job that cannot parallelize), and version/garbage accumulation from long-running transactions blocking cleanup. Adding cores to a workload queued on one row lock changes nothing. This class is exactly why "just buy a bigger instance" so often disappoints. ## The order of investigation 1. **Is the work necessary?** Find the top statements by total time (not by average). A single bad plan usually explains more than any hardware dimension. Fixing one missing index has repeatedly bought teams a year of headroom. 2. **Is the schema hostile?** Excessive indexes on write-heavy tables, oversized rows, unnecessary wide selects, overflow-storage churn, and update patterns that rewrite unchanged data all multiply resource use. 3. **Is it contention?** Check lock waits and the hottest objects. Contention is fixed by design (sharded counters, shorter transactions, ordered access to reduce deadlocks), not by hardware. 4. **Is it configuration?** Undersized cache, per-operation memory too small (constant spilling) or too large (memory exhaustion under concurrency), checkpoint settings causing IO storms, background cleanup starved. 5. **Only then, hardware.** Map the dominant wait to the dimension: cache misses → RAM; commit latency → low-latency storage; scan/sort throughput with a cached working set → cores; large sequential IO → bandwidth. ## Validating the purchase before making it - Model the effect. If reads are 40% cache hits and doubling RAM would fit 90% of the working set, estimate the change in physical reads. If the answer is "20% fewer reads", the upgrade is not the fix. - Test on a replica or a restored copy at the candidate size with a replayed or synthetic workload. Sizing decisions made on a graph alone routinely miss the real constraint. - Check the **cost curve**: near the top of an instance family, price per unit of capacity rises steeply. The last doubling may cost three times the previous step and buy a smaller relative improvement. - Check **operational** consequences: a resize usually means a failover or restart; one very large machine is one failure domain; backup and restore times grow with data volume, which affects recovery objectives. ## When the honest answer is "stop scaling up" Hardware stops being the answer when: the dominant wait is contention or a serial phase; the workload is already on near-top-of-range hardware and each further step is disproportionately expensive; the write ceiling of a single primary is the constraint; or maintenance windows (backup, restore, index rebuild, upgrade) have grown beyond the recovery objectives the business needs. Those are the signals that push toward offloading reads, splitting workloads by function, partitioning very large tables, or moving to multiple primaries. ## The interview-ready summary Don't buy hardware from a utilisation chart. Establish the dominant wait class, confirm the work is necessary and the schema/config sane, then map wait class to hardware dimension — and be explicit that contention and single-threaded phases are immune to bigger machines, which is precisely when the scaling conversation becomes architectural rather than a purchase order.