Database Performance Basics: CPU, RAM, Storage and Connections

Database performance is closely tied to server resources. Understanding how CPU, RAM, storage, and connections affect your database helps you size servers correctly and optimize performance.

CPU

Databases use CPU for: - Query parsing and planning - Sorting and aggregation - Join operations - Connection management

CPU-bound database operations: - Complex queries with multiple joins - Large sort operations - Hash aggregations - Query planning for complex schemas

Most database workloads are I/O-bound rather than CPU-bound. If CPU is consistently high, look for inefficient queries (missing indexes, full table scans) before adding more CPU.

RAM

RAM is the most critical resource for database performance. Databases use memory for:

Buffer Cache

The buffer cache (shared_buffers in PostgreSQL, InnoDB buffer pool in MySQL) holds frequently accessed data pages in memory. When data is in the buffer cache, reads are served from memory instead of disk (microseconds vs milliseconds).

Size the buffer cache to hold your working dataset: - PostgreSQL: set shared_buffers to 25% of system RAM - MySQL: set innodb_buffer_pool_size to 60-70% of system RAM

Sort and Hash Memory

Queries that sort data or perform hash joins use memory for these operations. Insufficient sort memory causes the database to use temporary disk files, which is dramatically slower.

Connection Memory

Each database connection consumes memory for connection state, query buffers, and sort space. Hundreds of connections can consume significant RAM.

Storage

Database performance is heavily influenced by storage characteristics.

IOPS

Random I/O (index lookups, row reads) depends on IOPS. NVMe storage provides 500,000+ IOPS compared to 100,000 for SATA SSD. For production databases, NVMe storage is strongly recommended.

Write Performance

Write-heavy workloads (INSERT, UPDATE, DELETE) generate write-ahead log (WAL) writes and page flushes. NVMe storage handles concurrent writes more efficiently.

Sequential Scan Performance

Large sequential scans (full table scans, large range queries) benefit from high throughput. This is less common in well-indexed databases.

Connections

Each database connection consumes memory and processing resources. Too many connections can exhaust memory and increase context switching overhead.

Connection Pooling

Use a connection pooler (PgBouncer for PostgreSQL, ProxySQL for MySQL) to: - Limit the number of active database connections - Reuse connections instead of creating new ones for each request - Queue requests when all connections are busy

A typical configuration: 20 to 50 active database connections behind a connection pooler serving hundreds of application requests.

Monitoring Key Metrics

• Cache hit ratio: percentage of reads served from memory (should be > 99%)
• Active connections: current number of running queries
• Query latency: average time for queries to complete
• Lock waits: queries waiting for locks
• Temporary file usage: queries using disk for sorting (indicates insufficient memory)
• Replication lag: delay between primary and replica

Optimization Priority

1. Optimize queries (add indexes, rewrite inefficient queries) 2. Configure memory settings appropriately 3. Use connection pooling 4. Upgrade storage to NVMe 5. Add read replicas for read-heavy workloads 6. Scale vertically (more RAM, more CPU) as a last resort

Starting with query optimization is almost always more effective than throwing hardware at the problem.

Key Takeaways

• **CPU bottlenecks** manifest as high `iowait` and slow query execution — fix them by optimizing queries and adding indexes before upgrading hardware.
• **Memory tuning** (buffer cache, sort/hash memory) has the highest ROI of any database optimization; misconfigured memory settings cause more performance issues than slow disks.
• **Storage IOPS and sequential scan performance** drive real-world database speed — use NVMe or SSD storage and ensure your working set fits in memory.
• **Connection pooling** prevents connection exhaustion and reduces per-query overhead; configure pool sizes based on actual workload, not guesses.
• **Monitor the four key signals** — query execution time, buffer hit ratio, lock waits, and replication lag — to catch problems before users notice.