Database Instance Sizing

In this lesson, you will learn how to align your MySQL or MariaDB instance size with your actual database workload to achieve maximum cost-efficiency.

The Balancing Act

Database sizing is rarely about "bigger is better." Instead, it is about mapping your specific load profile—the combination of throughput (transactions per second), concurrency, and data volume—to the underlying hardware. Over-provisioning leads to wasted cloud spend, while under-provisioning triggers latency spikes and resource contention.

Analyzing the Load Profile

Before adjusting instance sizes, use your performance baselines (Lesson 23) to categorize your workload:

1. CPU-Bound: If your queries involve heavy computation, complex joins (Lesson 3), or extensive aggregation, your instance requires high compute capacity. Monitor Threads_running and CPU utilization. If wait times are low but CPU is pegged, scale vertically.
2. I/O-Bound: If your queries are stalling while waiting for data from disk (common in large range scans or high-volume logging), increasing CPU won't help. You need higher IOPS (Input/Output Operations Per Second) or a shift to faster storage volumes (e.g., NVMe).
3. Memory-Bound (The "Golden Ratio"): For most transactional databases, the goal is to keep your "hot" data—the indexes and active records—entirely in the innodb_buffer_pool. If your buffer pool hit ratio drops below 95-99%, you are hitting the disk too often, signaling that you need an instance with more RAM.

Cost-Efficiency Illustration

Consider a database that grows periodically. Instead of choosing the largest instance "just in case," start with a baseline that accommodates your current working set:

-- Check your buffer pool hit ratio
SELECT 
  (1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) * 100 
  AS buffer_pool_hit_ratio 
FROM information_schema.innodb_metrics 
WHERE name = 'buffer_pool_reads';

If your hit ratio is consistently 99.9%, you are likely over-provisioned on RAM and can downsize to a smaller instance class without impacting performance. If it drops during peak hours, your load profile indicates a need for a larger instance tier.

Key Takeaway: Right-sizing is a continuous loop of monitoring your buffer pool efficiency and I/O wait times, then scaling compute and storage to match the specific bottleneck of your workload.