How do you determine whether a database instance is right-sized? What indicators suggest over-provisioning or under-provisioning, and how do you adjust without impacting performance?

Question

Accepted Answer

Right-sizing is the practice of matching database instance resources to actual workload requirements. Both over-provisioning and under-provisioning are problematic. Over-provisioning wastes money on unused capacity, which at scale can represent thousands of dollars monthly per instance. Under-provisioning causes performance degradation, user-facing latency, and potential outages.

Indicators of over-provisioning include consistently low CPU utilization during peak hours, typically below 20 percent averaged over the peak period across several weeks. Memory utilization that never approaches the available amount, with the buffer cache holding the entire working set with significant room to spare. IO throughput and IOPS that are a fraction of the provisioned capacity. Connection counts that rarely exceed a small percentage of the maximum. These patterns suggest the instance could be downsized without performance impact.

Indicators of under-provisioning include CPU consistently above 80 percent during normal operations with spikes to 100 percent. Buffer cache hit ratio declining or page life expectancy falling, indicating memory pressure. IO wait times increasing as the storage subsystem is saturated. Query response times degrading during peak periods. Connection timeouts or connection pool exhaustion errors in application logs. These require immediate attention to prevent user impact.

The right-sizing process begins with data collection over a representative period. I collect resource utilization metrics at five-minute intervals for at least two to four weeks, ensuring the data captures weekly patterns, month-end processing, and any batch processing cycles. Looking at only one week might miss a pattern that occurs biweekly or monthly.

For cloud instances, I use the cloud provider's recommendation tools as a starting point. AWS Compute Optimizer, Azure Advisor, and Google Cloud Recommender analyze utilization patterns and suggest instance types. However, these tools consider only infrastructure metrics and do not understand the database workload context. A database that is currently idle during off-hours but might need capacity for a disaster recovery failover should not be downsized based purely on average utilization.

When downsizing, I follow a conservative approach. I reduce by one instance size rather than jumping directly to the smallest possible instance. I make the change during a low-traffic period and monitor closely for the first several days, paying attention to peak-period performance. In cloud environments, most instance type changes can be made with minimal downtime using Multi-AZ failover, so the risk is primarily performance rather than availability.

I also consider the memory-to-storage ratio. A database with a 500 GB working set on an instance with 64 GB of memory will never achieve high cache hit ratios. Increasing memory might eliminate IO bottlenecks more cost-effectively than upgrading storage. Conversely, a database with a small working set on a high-memory instance is paying for memory it does not use.

Right-sizing is not a one-time exercise. I schedule quarterly reviews of all database instances, comparing current utilization to the last review. Workloads change as applications evolve, and instances that were right-sized six months ago may need adjustment. Automated alerting for both sustained high utilization and sustained low utilization ensures that right-sizing opportunities are identified between scheduled reviews.