Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
A Cloud SQL for MySQL instance (with 16 GB of RAM) experiences severe query latency spikes during daily reporting windows. Reviewing telemetry in Google Cloud Monitoring and Query Insights reveals the following:
usage metric remains steady at 62%, while total_usage sits at 95%.tmp_disk_tables_created_count spike sharply; Storage usage is at 55% and innodb/history_list_length is flat.JOIN and SORT operations.Which resource constraint is the primary cause of the degraded database responsiveness?
In MySQL InnoDB, undo logs store copies of pre-modification data so that active transactions can maintain consistent read views (MVCC) and roll back changes if needed. When long-running transactions remain open, InnoDB cannot purge obsolete undo log records, causing the history list length to grow rapidly and potentially consume excessive disk space.
While undo log retention is a common source of hidden storage growth in MySQL, the observed Cloud Monitoring metrics explicitly rule it out here:
innodb/history_list_length metric remains flat during the reporting window, confirming that the InnoDB purge thread is not blocked by long-running uncommitted transactions.tmp_disk_tables_created_count points to transient on-disk temporary tables created by JOIN and SORT queries rather than persistent undo log accumulation.When long-running transactions cause undo log and storage capacity issues, different telemetry patterns emerge:
innodb/history_list_length: The history list length climbs continuously alongside elevated innodb/active_trx_longest_time and innodb/active_trx_total_time metrics.database/disk/utilization metric climbs steadily toward 80%–100%, risking an out-of-disk state that can make the instance unavailable.JOIN or SORT operations.This diagnosis would be accurate if Storage usage approached capacity alongside a sharp rise in innodb/history_list_length and innodb/active_trx_longest_time, indicating uncommitted transactions were blocking the undo purge.
Memory exhaustion occurs when active database processes consume nearly all available physical RAM, leaving insufficient memory for query buffers, connection threads, and internal caches. In severe cases, this triggers out-of-memory (OOM) conditions where the operating system kills the database process to protect system stability.
Although total_usage is at 95%, interpreting that metric alone as memory exhaustion reflects a common misunderstanding of how Cloud Monitoring reports memory in Cloud SQL:
usage vs. total_usage: The usage metric measures memory actively consumed by database container processes, whereas total_usage includes both database process memory and operating system page cache.usage metric is under 90%. At 62%, process memory consumption has substantial headroom.total_usage (95%) and usage (62%) represents OS cache that the kernel automatically repurposes whenever database processes need additional memory.Genuine memory exhaustion in a relational database presents a distinct telemetry pattern:
usage metric: The usage metric climbs steadily above 90% (or 95% on instances larger than 16 GB) and approaches total_usage.This diagnosis would be accurate if the usage metric itself exceeded 90%, leaving virtually no gap between usage and total_usage and indicating that database processes—not repurposable OS cache—had exhausted available RAM.
Monitoring both usage and total_usage together is essential for accurate capacity planning. Relying solely on total_usage often leads to unnecessary vertical scaling when the operating system is simply using idle RAM for beneficial file-system caching.
CPU saturation occurs when the computational demand of active database queries, background threads, and connection handling exceeds the processing capacity of the instance's allocated vCPUs. When CPU cores are fully utilized, runnable threads queue up waiting for processor time, causing sharp increases in query execution latency across all workloads.
The telemetry in Google Cloud Monitoring rules out CPU saturation as the primary bottleneck during the reporting window:
tmp_disk_tables_created_count directly tie the slowdown to storage operations rather than compute contention.When a database is genuinely constrained by CPU resources, monitoring dashboards show different indicators:
This diagnosis would be accurate if CPU utilization spiked near 95%–100% concurrently with the Query Insights latency spikes while disk I/O latency and tmp_disk_tables_created_count remained near baseline levels.
Disk I/O contention occurs when database queries generate more read and write operations to persistent storage than the underlying disk subsystem can serve within normal latency bounds. When working sets or intermediate query results cannot fit in memory buffers, the database engine must read pages from disk and write temporary structures to storage, causing query threads to stall while waiting for storage operations to complete.
Every signal observed in Google Cloud Monitoring and Query Insights points directly to a storage I/O bottleneck driven by query behavior rather than compute or memory exhaustion:
tmp_disk_tables_created_count confirms that the MySQL engine is spilling intermediate result sets to disk because they exceed in-memory temporary table thresholds (tmp_table_size and max_heap_table_size).JOIN and SORT operations during the exact latency window, which are classic triggers for creating large temporary disk files and heavy random I/O.usage at 62%, the database is not CPU-bound or out of memory; threads are simply waiting on slow disk I/O.To resolve disk I/O contention and restore responsiveness, take the following steps:
JOIN and SORT queries and identify full table scans or missing sort indexes.ORDER BY columns to eliminate unnecessary temporary table creation and disk sorts.Unlike memory exhaustion or CPU saturation, the combined spike in tmp_disk_tables_created_count, non-cached Number of Reads, and disk latency alongside moderate CPU and process memory usage isolates disk I/O contention from JOIN and SORT temporary tables as the true root cause.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated