Getting MySQL sizing right is one of the most cost‑effective performance decisions you can make. Oversize the instance and you waste money; undersize it and you fight constant slowdowns and instability.

This article focuses on InnoDB and covers how to choose CPU, RAM, storage and network profiles, with practical rules of thumb and simple checks you can run.

Step 1: Understand your workload

Before thinking about hardware, classify how the database is used. The goal is not a perfect model, but a clear bias.

┌───────────────────────┬─────────────────────────┐
│ Workload type         │ Typical characteristics │
├───────────────────────┼─────────────────────────┤
│ Read-heavy OLTP       │ Many small SELECTs      │
│                       │ Few writes, low latency │
├───────────────────────┼─────────────────────────┤
│ Write-heavy OLTP      │ Many INSERT/UPDATE/DEL  │
│                       │ Hot primary keys        │
├───────────────────────┼─────────────────────────┤
│ Analytics / reporting │ Large scans, GROUP BY   │
│                       │ Occasional heavy joins  │
├───────────────────────┼─────────────────────────┤
│ Mixed                 │ Blend of the above      │
└───────────────────────┴─────────────────────────┘

Key questions:

• What is the peak QPS/TPS you need to sustain?
• Are queries mostly point lookups or range scans?
• Is the dataset growing quickly (e.g. >20% per year)?
• Do you have strict latency SLOs (e.g. 95th percentile < 10 ms)?

For many OLTP systems, the first capacity bottleneck is RAM, then storage IOPS/latency, then CPU.

Step 2: Size RAM for InnoDB

InnoDB performance is dominated by the buffer pool size. More buffer pool means fewer disk reads.

Rule of thumb for RAM

• Start with: RAM ≈ 2 × active dataset size, where active dataset is the part touched in a typical day.
• On single-tenant database servers, allocate 60–75% of RAM to innodb_buffer_pool_size.

Example: if your tables and indexes total 100 GB, but only 40 GB is hot:

• Target RAM ≈ 80–96 GB.
• innodb_buffer_pool_size ≈ 50–70 GB.

How to estimate the active dataset

On an existing server, use InnoDB buffer pool stats:

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Focus on:

• Innodb_buffer_pool_reads: physical reads from disk.
• Innodb_buffer_pool_read_requests: logical reads from memory or disk.

A healthy OLTP system aims for:

• Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 0.01 (1%) over peak hours.

If the ratio is higher, you are doing too many disk reads and likely need more RAM or better indexing.

Memory layout checklist

On a dedicated MySQL host, a reasonable starting layout:

• innodb_buffer_pool_size: 60–75% of RAM.
• innodb_log_buffer_size: 64–512 MB (write-heavy closer to the high end).
• query_cache_size: 0 (if still using versions where it exists, generally best disabled for OLTP).
• Leave 20–30% of RAM for OS cache, connections, and background tools.

On RHEL/Rocky Linux, verify memory pressure with:

free -m
vmstat 5

Step 3: Choose CPU profile and vCPU count

MySQL scales reasonably with cores for concurrent queries, but not linearly. Some operations (replication SQL thread, certain DDL, backups) can be bottlenecked on a single core.

CPU rules of thumb

• Fewer, faster cores are often better than many slow cores for OLTP.
• Avoid oversubscribed vCPUs where the hypervisor is heavily shared.
• Prefer instances with high single-thread performance (recent CPU generations, high clock speed).

As a starting point:

• Small OLTP (up to ~2k QPS): 4–8 vCPUs.
• Medium OLTP (2k–10k QPS): 8–16 vCPUs.
• Heavy OLTP / mixed analytics: 16–32 vCPUs, possibly more if queries are complex.

Monitor CPU utilisation:

mpstat -P ALL 5
pidstat -ut -p $(pidof mysqld) 5

If a single core is consistently near 100% while others are idle, you are hitting single-thread limits and may benefit from faster cores or query/index optimisation rather than more cores.

Step 4: Storage: IOPS, latency and durability

Storage is where many deployments go wrong. InnoDB is very sensitive to latency for writes and random reads.

Basic storage requirements

• Use SSDs (NVMe or high-quality SATA) for production OLTP. Avoid spinning disks for primary workloads.
• Prefer local SSDs or high-performance network volumes with guaranteed IOPS and low latency.
• Enable a battery-backed write cache on RAID controllers, or rely on the cloud provider’s durable cache.

Latency targets

For comfortable OLTP performance:

• Random read latency: < 2 ms (ideally < 1 ms).
• fsync / write latency: < 5 ms for log flushes.

On RHEL/Rocky Linux, you can run a simple fio test (on a non-production or idle system) to understand the device profile:

yum install -y fio
fio --name=randrw --filename=/var/lib/mysql/fio.test \
    --size=1G --bs=16k --rw=randrw --iodepth=32 --direct=1 \
    --numjobs=4 --time_based --runtime=60

Warning: Do not run synthetic I/O tests against a busy production MySQL data directory without careful planning. They can severely impact latency.

Layout considerations

• Place data, redo logs, and binary logs on fast storage.
• If you must separate, prioritise ib_logfile* and binlog directories on the lowest-latency devices.
• Use RAID10 or equivalent for on-prem deployments where you manage disks directly.

Ensure the filesystem and mount options are appropriate:

• Use a modern filesystem such as XFS or ext4.
• Disable atime (noatime) to reduce unnecessary writes.

Step 5: Network considerations

For standalone databases, network is rarely the primary bottleneck, but it matters for replication and application latency.

• Keep application servers and MySQL in the same availability zone or data centre when possible.
• Aim for < 1 ms RTT between app and DB for latency-sensitive OLTP.
• Use at least 1 Gbps NICs; 10 Gbps is recommended for heavy replication or backup traffic.

Check network utilisation and errors:

sar -n DEV 5
ss -t state established '( sport = :mysql )'

Step 6: Right-sizing instance families

Once you know your approximate CPU, RAM, and I/O needs, you can choose an instance family or physical profile.

General guidance

• Memory-optimised instances are usually the right default for MySQL OLTP, because buffer pool size dominates.
• Compute-optimised instances are useful for CPU-heavy analytics or complex stored procedures, but ensure you still have enough RAM.
• Storage-optimised instances can help for extremely write-heavy or log-heavy workloads.

Do not chase the largest possible instance first. A better approach is:

1. Choose a modestly over-provisioned size based on your estimates.
2. Load-test with realistic traffic and datasets.
3. Scale up or out based on measured bottlenecks.

Step 7: Plan for growth and high availability

Your initial instance size should consider growth and HA strategy.

Capacity headroom

• Plan for at least 30–50% headroom in CPU and IOPS at peak.
• Keep disk utilisation under ~70% to allow for growth, temporary files, and online DDL.

Replication and failover

• Size replicas at least as large as the primary, unless they serve only light read traffic.
• Ensure replicas have comparable storage performance; a slow replica can lag significantly.
• Test failover with real workloads to ensure the chosen size can handle promoted-primary traffic.

Step 8: Validate with metrics and adjust

After deployment, confirm that the chosen hardware profile works under real traffic.

Key MySQL metrics

• Buffer pool hit ratio (from Innodb_buffer_pool_read% counters).
• Query latency (95th/99th percentile) from application metrics or performance_schema.
• InnoDB log waits (e.g. Innodb_log_waits): high values suggest log I/O bottlenecks.
• Replication delay (if applicable).

Key system metrics

• CPU utilisation per core (avoid sustained > 80% on many cores).
• Disk latency and queue depth.
• Memory utilisation and swap (swap should be effectively 0 under normal load).

Use these observations to decide whether to:

• Add RAM (high disk reads, low CPU).
• Move to faster storage (high I/O wait, high log waits).
• Increase CPU or move to faster cores (high CPU, low I/O wait).

Best-practice checklist

• Classify workload: read-heavy, write-heavy, or mixed.
• Size RAM to at least 2 × active dataset where feasible.
• Allocate 60–75% of RAM to innodb_buffer_pool_size on dedicated servers.
• Prefer SSD/NVMe with low latency; verify with I/O tests in non-production.
• Choose memory-optimised, high single-thread performance instances for OLTP.
• Keep app and DB close in network terms; avoid cross-region latency.
• Maintain 30–50% headroom for CPU and IOPS at peak.
• Continuously monitor and adjust rather than relying on one-time sizing.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

Right-sizing MySQL is an iterative process, not a one-off decision. Start from a clear understanding of your workload, apply conservative rules of thumb for RAM, CPU, and storage, and then refine based on real metrics. With a disciplined approach, you can achieve predictable performance and availability without overspending on hardware.