Evaluate performance and cost tradeoffs of different database configurations (e.g., machine types, storage types)

Compute and memory resource optimization is the practice of choosing a virtual machine size that matches the database bottleneck without paying for unused capacity. The Compute Engine (opens in a new tab) machine family determines the virtual CPUs (vCPUs) and RAM available to a database node, which controls query speed, caching capacity, and vertical scalability. Vertical scaling means moving to a larger machine shape on the same virtual machine (VM) instead of adding more nodes.

1.1Machine Families for Database VMs

Machine families for database VMs are groups of shapes divided by their ratio of CPU to memory and their performance guarantees. General-purpose shapes balance price and performance for mixed use, compute-optimized shapes provide the highest performance per core for CPU-bound work, and memory-optimized shapes provide large memory-to-vCPU ratios for memory-intensive work. Current guidance centers on the N4 series for general purpose, the C3 and C4 series for compute optimized, and the M3 and M4 series for memory optimized, including C4A and C3D variants built on newer processor platforms.

For performance-sensitive MySQL workloads, the latest C4 and C4A instances are recommended when available in the region, with C3 and C3D as similar alternatives. These performance-focused instances offer the lowest and most consistent latency for compute-bound operations plus additional controls:

• Control over host maintenance events with advance notification
• Control of single-core turbo boosting for greater performance consistency
• Tier_1 networking for higher network bandwidth

1.2Matching Machine Type to the Performance Bottleneck

Matching the machine type to the performance bottleneck means selecting the family that relieves the resource under the most pressure. If a SQL Server (opens in a new tab) database shows constantly high CPU usage, a compute-optimized type from the C3 or C4 series reduces query execution time. If the same database shows slow reads from disk, a memory-optimized type from the M3 or M4 series helps because SQL Server caches data and execution plans in memory faster than reading from disks.

The same split applies to OLTP versus online analytical processing (OLAP) patterns. High-transaction OLTP databases that handle thousands of small concurrent reads and writes need efficient per-core processing, so they map to compute-optimized types such as C4. Large data warehouses for OLAP that scan and aggregate terabytes for reporting need high throughput and large caches, so they map to memory-optimized types such as M4 that can hold more of the dataset in memory.

1.3Sizing Memory for Read Throughput and Vertical Scaling

Sizing memory for read throughput focuses on the working set, or the total data the database processes at once, and the RAM-based buffer pool that caches it. For high read queries per second (QPS) performance, the VM must be large enough that the working set fits into the buffer pool, and the buffer pool should use most of the RAM on the VM. When the buffer pool is larger than the working set, steady-state reads are served from memory without touching disk, which reduces disk accesses and raises QPS. For high write transactions per second (TPS) performance, block storage is the main factor rather than VM memory size.

To scale memory vertically without paying for unused vCPUs, high-memory shapes with a high RAM-to-vCPU ratio are recommended. For most MySQL workloads, the ideal balance is to measure the working set and choose the smallest highmem shape that fits that working set in RAM. Those highmem shapes provide about 8 GB of RAM per vCPU, which caches a large working set while retaining enough CPU for a high query load. For large working sets with low query rates on N4 instances, custom machine types with extended memory can further increase the RAM-to-vCPU ratio and lower total cost.

1.4Controlling Cost While Preserving Performance

Controlling cost while preserving performance means using lower-cost shapes where strong guarantees are not needed and rightsizing where they are. N4 instances use next-generation dynamic resource management to optimize total cost while maintaining solid performance, without the strong latency and consistency guarantees of C4, C4A, C3, and C3D. That tradeoff makes N4 the recommended choice for low- to medium-traffic MySQL databases and for development, staging, or test environments where cost-effectiveness matters more than peak performance.

Two additional levers adjust the cost-to-performance ratio without changing families. Custom machine types let teams provision CPU cores independently of memory so licensing and compute spend track only the cores actually needed, and Compute Engine machine-type recommendations help identify overprovisioned VMs. For licensed databases such as SQL Server on Compute Engine VMs, disabling simultaneous multithreading (SMT) reduces the core count reported for licensing by 50%, and overprovisioning CPUs by about 20% before disabling SMT can preserve performance while substantially lowering licensing cost.

Storage tiering and I/O performance analysis is the practice of matching database storage to workload needs by comparing input/output operations per second (IOPS), throughput, and latency against cost. In Google Cloud, disk performance directly controls database responsiveness, so the choice of SSD Persistent Disk, Hyperdisk Balanced, Hyperdisk Extreme, and Local SSD determines whether a workload meets its performance target within budget. Traffic to Local SSD flows from the VM to storage physically attached to the same host, which gives very low latency but without durability across host failures.

2.1IOPS and Throughput as Performance Measures

IOPS and throughput are the two standard measures of disk performance for databases. IOPS is the number of read and write requests a disk can handle per second, and throughput is the total amount of data moved to or from the disk per second. IOPS is critical for OLTP workloads with many small, random reads and writes, such as updating customer records or processing orders. Throughput is essential for OLAP workloads that scan large amounts of data, such as running reports, data warehousing, or performing backups.

2.2Comparing Google Cloud Disk Types for Databases

Google Cloud disk types for databases differ in how performance scales, how latency behaves, and whether data survives host failure. Performance of SSD Persistent Disk depends on both the disk size and the vCPUs of the connected Compute Engine (opens in a new tab) VM. Performance of Hyperdisk Balanced and Hyperdisk Extreme depends on provisioned settings rather than size, so IOPS and throughput can be raised or lowered based on demand.

2.3Matching Storage to Workload Requirements

Matching storage to workload requirements means selecting disk types and layouts based on whether the workload needs IOPS, throughput, or low cost. The three common patterns separate durable data and logs from temporary operations to avoid contention. Using separate disks for data and logs isolates sequential log writes from random data reads in OLTP systems.

Offloading temporary operations from durable disks to Local SSD lets tempdb absorb sorts, spills, and rebuilds at the lowest latency. For OLAP systems, high provisioned throughput accelerates large table scans, while a large buffer pool lets steady-state reads be served from RAM without touching disk.

2.4Controlling Storage Cost Without Losing Performance

Controlling storage cost means paying only for the I/O resources the write workload actually needs. Hyperdisk Balanced provides SSD latency at low cost with better than 99.999% durability and encryption of data at rest with Google-managed or customer-managed keys, and its IOPS and throughput scale independently so spend matches demand. This independence contrasts with SSD Persistent Disk, where higher performance requires a larger disk or larger VM.

For MySQL with the InnoDB storage engine, Hyperdisk performance is sized around the write path, specifically access to InnoDB redo logs and subsequent updates to data files and indexes. When the buffer pool is larger than the working set, steady-state reads flow from RAM to the database without disk access, so disk cost follows writes and maintenance events. Maintenance events such as post-crash recovery using redo logs or backups that read all changes since the last backup create spikier disk demand that scales with write volume, so provision headroom for those peaks rather than only for average throughput.

Scaling strategies and managed service pricing models describe how database capacity is increased and how that capacity is billed. Vertical scaling makes a single instance larger with more vCPUs, memory, and disk performance, while horizontal scaling adds more instances such as read replicas or application VMs to spread load. Provisioned capacity charges for reserved size even when idle, autoscaling adjusts capacity with demand, and serverless consumption charges only for what is used.

3.1Vertical Scaling by Resizing Database Capacity

Vertical scaling by resizing database capacity increases the power of one database instance by choosing a larger machine type with more vCPUs and memory. In Compute Engine (opens in a new tab), machine type recommendations help match the VM to the workload, and custom machine types let a predictable workload provision CPU cores independently of memory to control cost and licensing. In Cloud SQL, editing an instance to add vCPUs or memory increases performance but also increases cost and triggers a restart that causes downtime, so choosing a size that fits expected growth matters more than exact rightsizing.

Vertical scaling also changes storage performance because Cloud SQL disk performance is based on disk size and the number of vCPUs. For performance-intensive workloads, an instance should not be constrained on CPU or memory, with at least 60 GB of memory as a practical guide. Spanner (opens in a new tab) follows the same vertical idea at the instance level because compute capacity in nodes or processing units determines storage capacity, and read and write throughput scale linearly with that compute capacity.

3.2Horizontal Scaling by Adding Instances

Horizontal scaling by adding instances adds more copies of capacity instead of making one instance larger, which improves throughput and availability but adds management and connection load. Stateless managed instance groups (MIGs) can scale out automatically by creating VMs when traffic rises and scale in when demand falls, while stateful MIGs cannot be autoscaled because their disks must be preserved with each VM. Cloud SQL supports horizontal read scaling through read replicas that offload reads from the primary, but creating replicas increases cost and does not replace the need to size the primary correctly.

Horizontal application scaling creates direct pressure on the database because connection load flows from each application instance to the database. An application service often keeps an internal connection pool so requests reuse existing connections without waiting for new connections. When the application tier scales out, each new application instance creates its own pool with a minimum set of connections, so total connections to the database grow with the number of instances and must be managed since databases cannot support unlimited connections.

3.3Provisioned Capacity and Predictable Workload Pricing

Provisioned capacity pricing pays for a fixed amount of compute, memory, and storage whether it is fully used or not, which fits steady and predictable workloads. Compute Engine reservations provide assured VM capacity in a specific zone for a chosen machine type and can be specific to one project or shared across projects. Cloud SQL committed use discounts (CUDs) lower the price for predictable resource needs in exchange for a commitment, and Spanner makes cost predictable because payment follows the provisioned nodes or processing units that are selected.

Provisioned database choices also carry licensing tradeoffs that affect total cost. A self-managed third-party database on Compute Engine requires separate planning for software license costs, with options such as bringing existing licenses through bring your own licenses (BYOL), limiting licensed cores with custom machine types, and disabling simultaneous multithreading (SMT) to reduce vCPUs per core. When a managed service such as Cloud SQL is used, database license costs are included in the service charges, so the choice is between managing infrastructure plus licenses separately or paying one managed rate with less operational work.

3.4Autoscaling and Automatic Growth for Variable Demand

Autoscaling and automatic growth keep costs aligned with actual demand by adding capacity during peaks and removing it when demand falls. The autoscaler for stateless MIGs lets traffic flow from the load balancer to a larger set of VMs during increases and then reduces the set when need is low, which handles growth gracefully while saving money during quiet periods. For databases, enabling automatic storage increases lets Cloud SQL storage capacity grow automatically as data grows, although instances should still keep buffer for peak workloads because database workloads tend to increase over time.

Rightsizing turns monitoring data into lower cost without adding capacity risk. Cloud Monitoring provides predefined dashboards that show health and capacity use for components including Cloud SQL, and Active Assist provides cost insights such as idle-instance and overprovisioned-instance recommendations for Cloud SQL and storage-quota guidance for AlloyDB for PostgreSQL. Removing high availability or disaster recovery configurations where they are not required, deleting unneeded tables and objects to archival Cloud Storage, and using the columnar engine in AlloyDB for aggregation-heavy analytical queries all reduce the provisioned footprint that must be paid for.

3.5Serverless and Flexible Consumption Choices

Flexible consumption models trade configuration control for less management and pay for use rather than for reserved size. VMs and containers on Google Kubernetes Engine (GKE) provide more configuration flexibility, but the team remains responsible for managing the resources. In a serverless architecture with Cloud Run, workloads deploy to a preconfigured platform that requires minimal management effort, so effort shifts from operating infrastructure to working with data and applications.

Spot VMs are a discounted flexible option for fault-tolerant work that can tolerate preemption, with significantly lower cost than regular VMs and the same machine types, options, and performance. Compute Engine may preemptively stop or delete Spot VMs to reclaim capacity, and MIGs may be unable to scale out to the target size when zonal capacity is limited. Choose Spot VMs for batch jobs without high availability requirements, and choose regular provisioned VMs or managed database capacity when availability during capacity shortages matters more than the discount.

3.6Storage and Machine-Type Tradeoffs for Performance and Cost

Storage and machine-type tradeoffs link performance directly to cost, so the lowest price is not always the most cost-effective option. For most Cloud SQL uses, SSD is the most efficient and cost-effective choice because it delivers higher performance for general workloads. HDD becomes appropriate when datasets are large, latency-insensitive, or infrequently accessed, where lower storage cost outweighs slower performance.

Beyond SSD versus HDD, durability and performance features change both resilience and price. Zonal Persistent Disk volumes keep data in one zone, while regional Persistent Disk and Hyperdisk Balanced High Availability replicate data synchronously between two zones in the same region to survive a single-zone outage. Hyperdisk Storage Pools share block capacity across VMs in a zone and attempt to keep utilization at or below 80% of provisioned capacity, with advanced capacity pools using thin provisioning and data reduction to improve utilization and reduce cost.

Key Facts

• Compute-optimized C3 and C4 machine types reduce query time for CPU-bound and high-volume OLTP work, while memory-optimized M3 and M4 types improve performance by caching data in memory instead of reading from disk.
• High-memory shapes with about 8 GB of RAM per vCPU let a MySQL working set fit in the buffer pool so steady-state reads are served from memory and disk cost follows writes.
• Hyperdisk Balanced and Hyperdisk Extreme allow IOPS and throughput to be provisioned independently of disk size, while SSD Persistent Disk performance depends on disk size and VM vCPUs.
• Local SSD provides the highest IOPS and throughput but has no durability, so it is used only for temporary data such as tempdb and the Windows page file.
• Cloud SQL with SSD is the efficient default for active workloads, while Cloud SQL with HDD fits datasets of 10 TB or more that are latency-insensitive or infrequently accessed.