To maintain database health, database administrators must monitor the CPU capacity and query load of their instances. A database tool like Query Insights helps engineers find the source of performance issues by filtering data by database, user, or IP address. Analyzing the top contributors to database load helps determine if high resource use is driven by specific applications or inefficient queries. High average execution time paired with low row counts typically points to missing indexes that need to be created.
Engineers use several key metrics to diagnose these slow-running queries:
When designing an indexing strategy, engineers should exclude properties that are never used in query filters to save on storage and lower latency. While indexes speed up reads, having too many composite indexes can slow down write operations and increase overall costs. To prevent hotspotting, which occurs when many writes hit a narrow key range, databases should avoid using monotonically increasing values like sequential IDs or timestamps. Instead, using well-distributed IDs spreads the load evenly and prevents write bottlenecks.
Writing efficient queries also reduces database load and prevents slowdowns. Using projection queries or keys-only queries allows applications to retrieve only the specific fields needed rather than the entire entity. Replacing query offsets with cursors is another vital strategy to avoid unnecessary internal data processing. For exceptionally large, ad-hoc datasets, transferring the workload to a service like BigQuery may be more effective than managing numerous complex indexes.
Using Query Insights allows engineers to monitor database load in real-time and compare it against the maximum CPU capacity of the instance. The Top dimensions by database load table reveals which specific queries are consuming the most resources. Administrators can filter this performance data by database, user, or IP address to isolate high-latency operations. This visibility is crucial for maintaining high transaction throughput and finding queries that stall due to insufficient resources.
Evaluating slow queries requires analyzing metrics that show how resources are spent during execution:
Excluding unused properties from indexes reduces storage costs and avoids write delays, keeping transactions running quickly. Database performance can also suffer from hotspotting and locking issues when heavy workloads target a narrow range of keys. Tracking the lock wait time is essential for diagnosing contention, which measures how long a transaction waits for another process to release its lock.
Data contention occurs when two or more operations try to access or modify the same document at the same time. Database systems resolve these conflicts using specific rules known as concurrency controls. Understanding whether a database uses optimistic or pessimistic rules helps administrators diagnose performance issues and transaction failures.
Optimistic concurrency controls assume that data conflicts are rare, so they do not use database locks to block other operations. Instead, the system checks if the document changed during the transaction and only completes the write if the data is untouched, making it ideal for mobile apps with unreliable connections. In contrast, pessimistic concurrency controls assume conflicts are likely and use database locks to block other operations. When a pessimistic transaction fails due to high contention, the database returns an ABORTED error message, signaling that the operation must be retried.
Engineers can use monitoring tools to analyze wait events and identify blocking sessions where one operation is stuck waiting for another to release its lock. Pinpointing these blocking sessions allows administrators to resolve bottlenecks and optimize database throughput. The key metrics to monitor during diagnosis include:
Fascinated by the world of cloud databases? Explore the methods for structuring, scaling, and securing database solutions on Google Cloud as you gear up for the Professional Cloud Database Engineer exam!
Prepare and test your skills
Prepare and test your skills
Database administrators use Query Insights to monitor database load and filter by database, user, or IP address to find performance issues. Key metrics for diagnosing slow queries include average execution time, wait events, and times called, where high average execution time with low row counts typically indicates missing indexes.
Optimistic concurrency controls assume data conflicts are rare and do not use database locks, instead checking if a document changed during a transaction before completing the write. Pessimistic concurrency controls assume conflicts are likely and use database locks to block other operations, returning an ABORTED error when a transaction fails due to high contention.
To prevent hotspotting, which occurs when many writes hit a narrow key range, databases should avoid using monotonically increasing values like sequential IDs or timestamps for indexes. Instead, using well-distributed IDs spreads the load evenly and prevents write bottlenecks, while also avoiding too many composite indexes that can slow down writes.
Engineers can reduce load by using projection queries or keys-only queries to retrieve only specific fields instead of entire entities. Replacing query offsets with cursors avoids unnecessary internal data processing, and for large ad-hoc datasets, transferring the workload to BigQuery may be more effective than managing complex indexes.
StudyCerts
StudyCerts