What does this guide solve?
Database server sizing should start from measured workload, not product datasheets. CPU, memory and storage cannot indefinitely compensate for each other.
CPU sizing
OLTP often values high clock speed and low query latency, while analytical workloads can benefit more from core count and parallelism.
Monitor run queue, query CPU time, context switching, NUMA and virtualization steal/ready time—not CPU percentage alone.
Memory sizing
The goal is to keep as much active data/index working set in memory/cache as practical. PostgreSQL, InnoDB, SQL Server and MongoDB use memory differently.
Swap/paging can create severe performance problems for production databases.
Storage latency and IOPS
Database storage is about random IOPS, p95/p99 latency, queue depth and durable writes—not only MB/s throughput.
NVMe is especially valuable for logs/journals/temp space and heavy random I/O.
RAID and redundancy
RAID10 is commonly evaluated for transaction workloads because of performance and redundancy balance; RAID5/6 write penalty and rebuild impact need workload-specific analysis.
RAID is not backup, and controller write cache should be protected against power loss.
Virtualization
Databases can run very successfully in VMs, but control CPU overcommit, NUMA alignment, ballooning, shared-storage contention and noisy neighbours.
Critical database VMs may justify resource reservations and storage QoS.
Frequently Asked Questions
How many CPU cores does a database need?
There is no fixed formula. Size from query concurrency, CPU time and licensing model.
Is RAID10 always required?
No. Workload, storage technology, cloud design and redundancy model differ; it is often considered for write-heavy systems.
Are databases slow in virtual machines?
Not inherently. Well-designed virtual infrastructure can perform very well; overcommit and shared contention must be controlled.
Evaluate Your Database Infrastructure
We can review workload, security, hardware, HA and backup requirements together.
Architecture and operating model
Choose database hardware from working sets, queries and commit paths, not data size alone. Assess CPU single-thread/parallelism, memory caches/workspaces and log/data I/O. Average IOPS or capacity may not explain peak transaction latency.
- 1Transaction profile
- 2CPU/memory working set
- 3Log/data storage
- 4Peak load and growth
Design parameters
- Working set
- Separate total data from active data/indexes; cache hits alone do not prove query performance.
- I/O and commit
- Test random reads, checkpoints and log flush separately; record p99 latency alongside IOPS.
- Workload and query mix
- Measure read/write ratio, connection count, transaction duration and expensive queries. Database size alone does not explain hardware needs.
- Memory and storage
- Examine working set, log writes, temporary space and checkpoint behavior separately. Reliable write guarantees matter alongside speed.
- Recovery chain
- Test recovery of the starting backup, required logs, configuration and keys together.
- Access and change
- Separate application, administrator and schema-change permissions. Control production index and parameter changes.
Worked example
A 1 TB database might have an 80 GB active working set; this does not imply that all data must fit in RAM. Measure log writes and random reads together but with separate metrics. Apply a 30% illustrative growth assumption separately to data, queries and connections.
Troubleshooting
| Observation | Likely cause / distinction | Verification |
|---|---|---|
| Transactions are slow despite NVMe | Commit flush, locks or a single thread may constrain performance. | Correlate wait types with log/data I/O and execution plans. |
| CPU is low but queries are slow | Locks, I/O or client waits may dominate. | Inspect the execution plan and wait type for the same query. |
| Replica is connected but stale | Sending, replay or transaction waits can create lag. | Compare the last applied operation with source commit time. |
| Storage keeps growing | Log retention, open transactions, replication slots or maintenance may be involved. | Inspect supported product statistics; do not delete logs manually. |
Acceptance checks
- Report commit latency, working set and growth separately from storage capacity.
- Test representative transactions and queries using realistic data distributions.
- Restore to a separate target and validate application records.
- Reject unauthorized reads and schema changes using a controlled identity.
- Verify one authorized writer and the correct connection target after failover.
- Measure locks, log volume and latency during maintenance.
Related concepts
IOPS and block size
IOPS measures operations per second, whereas MB/s measures transferred data. Identical IOPS values produce very different bandwidth at different block sizes. For example, 10,000 operations/s at 4 KiB is approximately 39.1 MiB/s. Read/write mix, random versus sequential access and protection mechanisms affect the result. Measure actual block distributions and concurrency rather than transferring a benchmark result directly to production. High disk IOPS does not prove good application response; latency must be examined alongside it.
Latency distribution
Latency is the time between starting an operation and receiving its result. An average can hide a small number of very slow operations; medians and p95/p99 percentiles answer different questions. Network RTT, storage waits, processor queues and application processing contribute to end-to-end time. State whether measurements come from the client or server. Check whether increases coincide with traffic growth, maintenance or capacity limits. Record normal and peak-hour baselines before selecting an alert threshold.
Queues and concurrency
A queue holds work arriving faster than a resource can process it. More concurrency can improve utilization up to a point, then increase waiting time. In a stable system Little’s law relates L = λ × W: average work in the system equals throughput multiplied by average total time. Units must agree. At 2,000 operations/s and 5 ms total time, roughly 10 operations are present concurrently. This is a planning relationship; queue limits, bursts and highly variable service times still require measurement.
NUMA locality
On NUMA systems a processor may access nearby and remote-node memory at different costs. VM vCPU and memory sizes must be assessed against physical node capacity and hypervisor placement. More vCPUs do not always improve performance: reduced locality and scheduling waits can make it worse. Record physical topology, topology exposed to the guest and the workload’s memory behavior together. Compare changes under the same load, using application latency and remote-memory effects rather than CPU percentage alone.
Write endurance and wear
SSD selection requires more than capacity and initial performance. Consider TBW, DWPD, warranty duration, power-loss protection and workload write patterns together. DWPD calculations must use the capacity and conditions defined by the manufacturer. Database logs, small random writes and temporary work files affect wear differently. Monitor SMART or vendor health data, considering temperature, media errors and remaining endurance together. Write amplification means application-written bytes may differ from the amount written to NAND.
Durability and power loss
Survival of committed data depends on write guarantees across the database, OS, filesystem, controller and disks. Do not disable safety settings for speed without understanding caches and flush behavior. Power-loss-protected storage helps but does not prove correctness of the entire chain. Run failure and recovery tests in a controlled lab, not production. Validate application records and supported consistency checks rather than merely opening a sample file.