Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Distributed resource hotspots and connection exhaustion are two types of contention that slow horizontally scalable databases even when total capacity looks large enough. A hotspot is a data split or shard that receives a disproportionate share of reads or writes, so one server task becomes hot while others stay idle. Connection exhaustion is the state where too many concurrent client connections consume CPU and memory, causing timeouts, missing metrics, and rejected work.
Cloud Spanner distributes an instance's compute capacity across zones in its instance configuration to provide high performance and high availability. Each zone holds server resources that serve the data replica in that zone, so loss of one server does not constitute a node failure. For instances of 1000 processing units (PUs) (1 node) and smaller, Spanner allocates a single server task per zone, while instances larger than 1000 PUs allocate multiple server tasks per zone with one task for each 1000 PUs. Using multiple server tasks enables Spanner to create more database splits and spread load more evenly.
Storage limits in Cloud Spanner are tied directly to compute capacity. For instances smaller than 1 node, Spanner allots 1024.0 GiB of data for every 100 PUs, and for instances of 1 node and larger, Spanner allots 10 TiB of data for each node. For example, a 300 GB database can run on 100 PUs until it grows past 1024.0 GiB, after which another 100 PUs must be added or Spanner might reject writes. Spanner bills only for storage actually utilized, not for the total allotment.
A distributed hotspot in a sharded or split database is an unbalanced partition that outweighs the benefit of horizontal scaling. Sharding splits one database instance into multiple smaller instances by a partitioning criteria such as customer or subject, which lets both writes and reads scale horizontally. When the criteria sends too much traffic to one shard, that unbalanced shard becomes a hotspot with high latency while other shards remain underused.
The same pattern appears in Cloud Spanner as an overloaded split. Peak read and write throughput for a given amount of compute depends on instance configuration, schema design, and dataset characteristics, and instances with fewer than 1000 PUs can show non-linear scaling with intermittent latency increases under larger workloads. Poor schema choices, such as monotonically increasing keys or a single heavily updated table, concentrate work on a few splits. Poor data model design can be partially compensated by vertical scale-up, but partitioning, archiving unneeded data, and fixing the model are the long-term fixes.
Hotspot detection in Cloud Spanner starts with the Query insights dashboard, which measures query load as total CPU utilization for all queries in the selected database and time range. The dashboard shows an aggregated average rate of CPU usage in CPU seconds per minute for all queries, a second graph broken out per query or request tag, and a TopN queries and tags table sorted from highest to lowest CPU utilization and limited to 100 entries. If the total CPU graph spikes or stays elevated in the same window as overall instance CPU usage, one or more expensive queries are the likely cause.
Deeper investigation uses the SPANNER_SYS introspection tables that rank individual splits by CPU usage. A query against SPANNER_SYS.SPLIT_STATS_TOP_MINUTE ordered by CPU_USAGE_SCORE descending shows the splits with the highest CPU usage over a window such as the past 5 hours. In Spanner Omni (opens in a new tab), the same diagnosis uses the system insights dashboard in Grafana and the Spanner Omni command-line interface to query SPANNER_SYS tables directly. Use query parameters or request tags to keep the TopN table usable, because untagged and unparameterized queries can return too many distinct results for the table to load.
Exam tip: Use the Query insights TopN table to find which query fingerprint is hot, then use SPANNER_SYS.SPLIT_STATS_TOP_MINUTE ordered by CPU_USAGE_SCORE to find which data split is hot.
Scaling strategies relieve contention by adding the right type of capacity in the right order. Vertical scale-up that adds disk throughput, vCPU, and RAM resolves most urgent performance issues quickly, with most Cloud Spanner scale-ups completing in a few minutes and rarely up to an hour. When vertical scaling, tuning, and maintenance such as index defragmentation, statistics updates, and vacuum operations are not enough, horizontal options spread load across more servers. Compute capacity in Spanner cannot be reduced if data exceeds 10 TiB per node, so growth must be planned before storage fills.
The three documented strategies compare as follows:
| Strategy | What it adds | Choose when |
|---|---|---|
| Scale up infrastructure | More vCPU, memory, and disk throughput and IOPS on the existing instance | Urgent CPU, memory, or I/O pressure needs fast relief |
| Scale out reads | Read replicas that serve read queries routed from the application | Reads dominate and vertical scaling no longer helps, with application changes to connect to the replica |
| Shard or add Spanner capacity | More shards or Spanner nodes and server tasks that split writes and reads | Writes dominate or a single split or shard stays hot despite tuning |
Routing reads to a replica improves overall workload performance but requires additional effort to change applications to connect to the replica. Sharding requires re-architecting the database and application, performing data migration, and accepting higher operational complexity. In cache systems, scaling shards during low-write periods avoids extra memory pressure from replication or slot migration, and scaling down safely requires provisioning about 1.5 times the memory used by data.
Connection exhaustion is the condition where the number of client connections itself becomes the bottleneck that exhausts CPU and memory. When a cluster experiences high CPU utilization or its resources become exhausted by having too many connections, the cluster might misbehave and external metrics might be missing. Applications therefore depend on the database connection path, and the database depends on bounded connection counts and replica capacity to stay observable and responsive. Effective connection pooling and limits keep concurrent connections bounded so CPU and memory remain available for actual query work.
Resource constraints that cause the same class of outage must be avoided through sizing and monitoring. The three constraints are storage full without automatic storage increase enabled, consistently high CPU utilization, and consistently high memory usage, each of which means the instance is not properly sized for its workload. Best practice is to keep at least 20% available space for maintenance, keep table counts under 10,000 per Cloud SQL (opens in a new tab) instance where possible, and set up alerts in Cloud Monitoring for CPU, memory, and disk. Only highly available instances with at least one dedicated CPU remain covered by the service level agreement, while shared-core and single-zone instances and overloaded instances fall outside that coverage.
Locking mechanisms control how concurrent transactions share the same rows, tables, and resources without corrupting data. A transaction holds locks on the records it touches until the transaction finishes, so other transactions that need the same records must wait. In high-concurrency relational workloads, this waiting appears as blocking sessions, higher latency, lower throughput, and timeouts when contention grows.
Transaction locks create a direct dependency between concurrent sessions when many writers target the same record. The Cloud Healthcare API FHIR server (opens in a new tab) shows this pattern clearly when parallel transaction bundles update the same resource, such as a Patient resource. The first bundle locks the Patient resource until its transaction finishes, and every other bundle that needs the same Patient resource waits behind that lock.
The contention follows an ordered sequence:
diagnostics field with the text Resource type: PATIENT.429 Resource Exhausted operation_too_costly errors, which throttle all FHIR create, read, update, and delete (CRUD) operations in the Google Cloud project.Long lock contention periods lead to timeouts, reduced throughput, and increased resource usage even when clients retry with exponential backoff. Because throttling applies at the project level, contention from one workload affects all applications connected to the same project.
Exam tip: A 429 Too Many Requests error with operation_too_costly means lock contention or cumulative heavy load, not billing cost or necessarily quota exhaustion. In this context, cost refers to resource usage and data throughput.
Database lock contention limits throughput scaling in AlloyDB Omni and other relational systems. As the number of clients increases, workload and throughput increase until resources are exhausted, then throughput flattens or decreases instead of doubling with load. Identifying that flattening point shows whether contention or exhaustion is the bottleneck and where to tune the application or database system.
Typical reasons throughput levels off or drops include:
Lock contention is distinct because adding clients or compute does not help when sessions are waiting on each other. Throughput stays flat while wait time grows, which points to blocking rather than a lack of CPU, memory, or storage performance.
Diagnosing blocking sessions means finding which queries hold locks, which sessions wait, and whether errors signal contention. Search Cloud Logging for errors containing operation_too_costly to confirm lock contention, and check separately for quota issues if resource exhaustion is suspected. The error diagnostics distinguish transactional-bundle contention from cumulative heavy load in the project.
Slow queries and execution plans reveal the database side of blocking. Investigate slow queries and their execution plans to check whether critical queries use available indexes and to check for index scans, locks, and waits on other resources. A large number of active sessions can also signal transaction locks, which can be checked with pg_stat_activity grouped by state and user to find the root cause of session buildup.
Lock capacity settings also affect stability under concurrency. The max_locks_per_transaction setting controls how many database objects can be locked at the same time, with a default of 64 that is sufficient in most cases. The required capacity follows max_locks_per_transaction multiplied by max_connections plus max_prepared_transactions, so 300,000 objects with 200 connections needs a value of 1500. Clients that touch many tables in one serializable transaction may also need a higher max_pred_locks_per_transaction, but both settings use shared memory and should not be set unreasonably high.
Reducing contention shortens how long locks are held and how many locks each transaction needs. Smaller transaction bundles avoid contention because they hold only a few locks at a time and finish quickly, which prevents a backlog of stacked transactions. Large bundles hold locks longer, are more likely to hit throughput bottlenecks, and show diminishing returns when retried because failures affect more work at once.
Bundle choices differ in lock behavior and failure risk:
| Bundle type | Lock behavior | Contention and error risk |
|---|---|---|
| Small transaction bundle | Holds a few locks across dependent updates and finishes quickly | Lowest contention; supports consistency where resources depend on each other |
| Large transaction bundle | Holds many locks until the whole bundle completes | Higher 429 Too Many Requests risk; retries can multiply failures |
| Batch bundle | Does not hold locks across resource updates | Less contention at similar size, but large size or high queries per second can still cause 429 and 413 Request Entity Too Large errors |
Choose small transaction bundles when updates must stay consistent, separate independent resources into their own requests, and use moderate batching instead of single requests or very large batches. Clients with large bundles should add logic to manage only the subset of FHIR resources that failed, rather than retrying thousands of transactions together.
Database tuning and scaling further lower lock pressure when queries or the data model keep transactions open too long:
A system-level resource bottleneck occurs when CPU, memory, or disk I/O reaches its limit and slows database responsiveness. Google Cloud Monitoring exposes the underlying resource metrics for an instance or cluster, while Query Insights shows how query load consumes those resources over time. Diagnosing the bottleneck means comparing load against capacity, separating CPU, I/O, and memory signals, and then tracing the load to specific databases, users, or queries.
CPU pressure in Query Insights is shown as database load relative to CPU capacity. The dark blue line represents CPU capacity, or the number of CPUs available on the instance, and colored lines show load from CPU and CPU Wait, IO Wait, and Lock Wait. CPU and CPU Wait is the ratio of time taken by queries in an active state to wall-clock time, and it covers both time using the CPU and time waiting for the Linux scheduler to schedule the server process while other processes use the CPU. Because scheduler wait is included, CPU load can go beyond the maximum core line, which is a strong signal that queries are queued for CPU rather than just running efficiently.
Total CPU utilization is also tracked as an average rate in CPU seconds per minute for all queries and per query or request tag. In Spanner, the Total CPU Utilization graphs display aggregated usage and usage broken out by each query or request tag, with a TopN table limited to 100 queries sorted from highest to lowest CPU. If CPU utilization is consistently high, the instance is considered undersized for its workload and can lose Service Level Agreement coverage for CPU overload. For Memorystore clusters, the Main Thread CPU metric /cluster/cpu/maximum_utilization is used with targets of 0.5 seconds for primary and replica with one replica per node, and 0.9 seconds for the primary and 0.5 seconds for each replica with two or more replicas.
Disk I/O pressure appears as IO Wait, which is the ratio of time taken by queries waiting for I/O to wall-clock time. IO Wait includes Read IO Wait and Write IO Wait, with a detailed breakdown available in Google Cloud Monitoring under Cloud SQL metrics. When IO Wait dominates database load while CPU stays below capacity, the constraint is the storage subsystem rather than compute, and the next step is to check IOPS, throughput, and disk latency. The SQL Server workload on persistent disk SSD is throttled when it reaches its IOPS limit or throughput limit, so the size of I/O issued to storage directly affects observed IOPS and throughput.
Storage capacity and configuration set the ceiling for I/O performance. A disk size of 4 TB or greater provides more throughput and IOPS, higher vCPU provides more IOPS and throughput, and issuing I/O in parallel achieves a higher I/O queue depth. If disk utilization stays consistently high without automatic storage increase enabled, the instance is treated as improperly sized and can lose coverage for storage full. To leave room for maintenance operations performed by Cloud SQL, maintain at least 20% available space and enable automatic storage increases where supported.
Memory pressure in Cloud SQL is tracked by comparing process memory against total system memory. The usage metric tracks memory used by database processes, while the total_usage metric tracks the percentage of available memory used by both the database container and operating system cache. Observing the difference between the two metrics shows how much memory is held by processes versus how much sits in operating system cache that can be repurposed. To predict out-of-memory issues, both metrics must be checked and interpreted together, since high values can reflect custom configuration, an undersized instance, or both.
For Cloud SQL for PostgreSQL, the database/memory/components.usage metric in Metrics Explorer is the primary signal, with database/memory/components.cache and database/memory/components.free showing reclaimable and free memory. If cache plus free memory falls below 10% combined, the risk of an out-of-memory event is high, and an alerting policy with a 90% threshold on database/memory/components.usage is recommended. The most common out-of-memory pattern is a high work_mem value combined with a high number of active connections, because each connection uses memory and sort or hash operations can exhaust it. Changing memory size requires an instance restart, and if the instance is already at maximum memory the database must be sharded across multiple instances.
Exam tip: Keep usage below 90% for process memory, but judge out-of-memory risk from database/memory/components.usage at 90% and from cache plus free falling below 10% combined.
Query Insights database load separates why queries are slow by execution time across CPU, I/O, and lock categories. The workflow for pinpointing a system bottleneck follows an explicit order:
A normalized query removes sensitive values and returns a digest, so select * from test_table where id=1; and select * from test_table where id=2; share the digest select * from test_table where id=?;. The Queries tab in Database Center (opens in a new tab) extends this view across a fleet for AlloyDB and Cloud SQL, showing resources used, database product and engine, Google Cloud project, rows returned, total and average CPU time, and run count. Selecting a resource opens per-resource average and total runtime, run count, and rows returned, with a link to that resource's query insights page. For memory-heavy queries, the explain plan highlights high-memory scan methods including bitmap heap scan, quick sort, and hash join or hash, which hold memory for longer durations.
Different bottlenecks share the symptom of slow responsiveness but differ in their metric signature and fix. The following comparison preserves the documented signals and the first response for each constraint.
| Bottleneck | Key signal in Monitoring and Query Insights | Response when signal persists |
|---|---|---|
| CPU constraint | CPU and CPU Wait exceeds CPU capacity; total CPU seconds per minute stays elevated | Scale up vCPU and investigate top CPU queries; add replicas or shards if vertical scaling is insufficient |
| I/O constraint | IO Wait dominates load; workload hits IOPS or throughput limit | Increase disk to 4 TB or greater, increase vCPU for IOPS, parallelize I/O, and enable automatic storage increase |
| Memory constraint | usage or components.usage near 90%; cache plus free below 10%; low cache hit ratio | Tune work_mem and shared_buffers, reduce active sessions, then scale memory or shard |
Immediate relief usually comes from scaling up infrastructure by increasing disk throughput, vCPU, and RAM, since vertical scaling resolves most performance issues quickly. Longer-term options depend on the pattern and tradeoff: schedule maintenance such as index defragmentation, statistics updates, vacuum analyze, and reindexing; tune queries, indexes, and data types; scale out reads by routing read queries to a read replica; re-architect through partitioning, archiving, and normalization; or shard writes across instances by customer or subject. Sharding scales both writes and reads but increases application complexity and can create unbalanced hotspots that outweigh the benefit.
SPANNER_SYS.SPLIT_STATS_TOP_MINUTE ordered by CPU_USAGE_SCORE identifies the hottest data split.429 Resource Exhausted operation_too_costly error from the FHIR server signals lock contention or cumulative heavy load that throttles all CRUD operations in the project.database/memory/components.usage reaches 90% or when cache plus free memory falls below 10% combined.Last updated