PCDE Monitor and optimize database performance Practice Test 10 — 15 Questions

Question 1

A Cloud SQL for MySQL instance is experiencing performance degradation. You run the 'SHOW ENGINE INNODB STATUS' command and notice a high number of 'History list length' and 'Pending reads' in the InnoDB status output. What is the most likely cause of the performance issue?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Long-running transactions causing excessive undo log growth.. The history list length in InnoDB represents the number of undo log records that are waiting to be purged. A high value typically indicates that there are long-running transactions that are preventing the purge process from cleaning up old versions. This can lead to increased I/O and performance degradation. Pending reads may further indicate I/O bottlenecks. The primary cause is long-running transactions.

Answer

Insufficient buffer pool size causing frequent disk reads.

Answer

Too many concurrent connections overwhelming the server.

Answer

Improper indexing causing full table scans.

Question 2

A Cloud Spanner instance is experiencing high write latency. The workload consists of many small transactions that insert rows into a table with a monotonically increasing primary key. You want to reduce write latency. What should you do?

Accepted Answer

Change the primary key to a UUID or a hash of the original key.. Monotonically increasing primary keys create hotspots because Spanner splits data by key range. All new inserts target the last split, causing high write latency. By hashing the key or using a UUID, writes are distributed across multiple splits, leveraging the parallel write capacity of the instance and reducing latency.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Add a secondary index on the primary key column.

Answer

Enable optimistic concurrency control for the transactions.

Question 3

You are monitoring a Cloud Bigtable instance that serves a read-heavy workload. You notice that read latency has increased. Which TWO actions should you take to diagnose the issue? (Choose two.)

Accepted Answer

Check the CPU utilization of the Bigtable cluster.. To diagnose increased read latency in Bigtable, you should monitor CPU utilization to assess cluster load and review row key design to identify hotspots. These steps help determine if the issue is due to insufficient resources or inefficient data distribution. Increasing nodes, enabling Data Boost, or checking replication lag are not diagnostic actions for this scenario.

Answer

Increase the number of nodes in the Bigtable cluster.

Answer

Enable Bigtable Data Boost for the instance.

Answer

Check the replication lag between clusters.

Question 4

A Cloud Bigtable cluster is experiencing high read latency. The workload consists of random point reads. You notice that the CPU utilization is high. Which action should you take to reduce read latency?

Accepted Answer

Increase the number of nodes in the cluster.. High CPU utilization in Bigtable indicates that the cluster's nodes are saturated. For random point reads, adding nodes increases the total CPU capacity and allows reads to be spread across more servers, directly reducing latency. This is the recommended scaling approach for CPU-bound read workloads.

Answer

Increase the size of the SSD storage per node.

Answer

Change the row key design to distribute reads more evenly.

Answer

Enable a replication policy for the cluster.

Question 5

You are responsible for a Cloud Bigtable cluster that handles a high volume of random reads and writes. Users report increased latency during peak hours. You observe that the cluster's CPU utilization is around 85%, and the disk utilization is low. You want to optimize performance without over-provisioning. What should you do first?

Accepted Answer

Review the row key design for hotspots.. Before scaling the cluster, you should analyze the row key design. Hotspots from sequential keys cause uneven load, driving up CPU on specific nodes. Redesigning the key to distribute reads evenly can alleviate CPU pressure and reduce latency without additional cost. This is a fundamental optimization step for Bigtable performance.

Answer

Increase the cluster's storage capacity.

Answer

Increase the number of nodes in the cluster.

Answer

Enable SSD storage for the cluster.

Question 6

Your team runs a Cloud SQL for PostgreSQL instance that serves an internal expense reporting application. Over the past week, users have complained about slow response times during the morning submission window. You notice that CPU utilization on the primary instance spikes to 95% during that window, and you want to determine which SQL statements are contributing most to that CPU load. What should you do first?

Accepted Answer

Enable the pg_stat_statements extension and query it to identify statements with the highest total execution time.. The goal is to pinpoint which SQL statements consume the most CPU during the morning window. pg_stat_statements aggregates execution time per statement, directly exposing the top consumers. Raising vCPUs or enabling slow query logging does not provide per-statement aggregation, and instance-level metrics lack statement granularity. Querying pg_stat_statements is the most direct and lowest-risk diagnostic step.

Answer

Use Cloud Monitoring to view the CPU utilization metric for the instance and correlate it with the number of active connections.

Answer

Increase the number of vCPUs on the instance to reduce CPU utilization during the spike.

Answer

Enable slow query logging with a threshold of 100 ms and wait for the next morning window.

Question 7

You manage a Cloud Spanner instance with a regional configuration and a database that handles a global retail catalog. A reporting service issues a large number of single-row, strongly consistent reads against the primary key of the Products table, and p99 read latency has climbed from 8 ms to 45 ms over two weeks. Spanner's CPU utilization is at 55%, and no schema changes were made. You want to reduce read latency without weakening consistency for the catalog service. Which action should you take?

Accepted Answer

Investigate whether a small number of primary key values are receiving disproportionate read traffic, and if so, reshard the key space or add a key prefix to spread the load.. The latency pattern with moderate CPU and stable schema points to a hot spot: a narrow key range or a few popular keys concentrated on one split. Spanner scales by splitting key ranges, so redistributing the hot keys reduces per-split load and latency while keeping strong consistency. Adding nodes, indexes, or staleness does not fix a concentrated access pattern and can add cost or weaken guarantees.

Answer

Use a stale read with an exact staleness bound of 15 seconds for the reporting service while keeping strong reads for the catalog service.

Answer

Add a secondary index on the Products table primary key columns and force the reporting service to use it.

Answer

Increase the instance's node count to add more serving capacity for the read workload.

Question 8

A Cloud Bigtable cluster is used to store IoT sensor readings keyed by device ID with a timestamp suffix. The table uses a row key of the form deviceID#timestamp, and the workload is a mix of writes from devices and reads of the most recent reading for a given device. The database engineer observes that CPU utilization is high and that reads are slow, even though the cluster has plenty of nodes. What is the most likely cause?

Accepted Answer

The row key design causes hotspotting because device IDs are sequential.. Bigtable distributes rows across tablets by lexicographic row key order. Sequential device IDs cause consecutive rows to concentrate on a few tablets, so a small number of nodes handle most of the traffic while others sit idle. This produces high CPU and slow reads even with many nodes. Salting or hashing the row key prefix spreads the load.

Answer

The table has too many column families, which increases read amplification.

Answer

The cluster's replication configuration is set to a single cluster.

Answer

The cluster is using SSD storage instead of HDD storage.

Question 9

A Cloud SQL for PostgreSQL 14 instance is used by a SaaS application and shows a steady increase in active connections over several weeks, reaching the max_connections limit during business hours. Users see 'FATAL: sorry, too many clients already' errors. The application uses a connection pool per pod, and there are 40 pods. You want to eliminate the connection errors without reducing application availability. What should you do?

Accepted Answer

Deploy a connection pooler such as PgBouncer in transaction pooling mode between the application and the instance, and point the pods at it.. Connection exhaustion from many application pods is best solved by inserting a transaction-mode pooler that multiplexes client sessions onto a small backend pool. This keeps PostgreSQL below max_connections and preserves throughput, whereas raising the limit risks resource exhaustion and shrinking pool sizes degrades concurrency. Monitoring extensions do not reduce connection demand.

Answer

Enable the pg_stat_statements extension and use it to identify and terminate long-running queries that are holding connections.

Answer

Increase max_connections on the instance to a value that accommodates all pods plus administrative headroom.

Answer

Lower the application's connection pool maximum size per pod so the total stays under max_connections.

Question 10

You are monitoring a Cloud SQL for MySQL instance that supports an online transaction processing application. Users report intermittent slowness, and you want to identify the root cause using available observability data. (Choose two.)

Accepted Answer

Review the instance's CPU utilization, memory, and disk I/O metrics in Cloud Monitoring.. Query Insights provides per-digest latency and frequency data that pinpoints which statements contribute to load, while Cloud Monitoring resource metrics reveal whether CPU, memory, or disk I/O is saturated. Together they let you correlate user-reported slowness with a specific resource or query pattern. Capturing all queries, resizing the buffer pool, or restarting the instance do not diagnose the problem.

Answer

Enable the slow query log and set long_query_time to 0 to capture every statement.

Answer

Restart the instance during off-peak hours to clear any accumulated locks.

Answer

Increase the innodb_buffer_pool_size to the maximum value supported by the instance.

Question 11

You are optimizing a Cloud SQL for MySQL instance that supports an online transaction processing workload. The instance has 16 vCPUs and 64 GB of RAM. During peak hours, you observe high CPU utilization and many queries waiting on the InnoDB buffer pool. You want to reduce the number of disk reads caused by buffer pool misses. Which action should you take?

Accepted Answer

Increase the innodb_buffer_pool_size to a larger value within the available memory.. Buffer pool misses force InnoDB to read pages from disk, which increases latency and CPU wait. The buffer pool size determines how much of the working set can be cached. Increasing it within available memory reduces disk reads. The query cache is deprecated and does not help non-repeating queries, more vCPUs do not add memory, and changing log flush behavior affects writes rather than read caching.

Answer

Enable the query cache to cache the results of frequent SELECT statements.

Answer

Set the innodb_flush_log_at_trx_commit parameter to 2 to reduce disk I/O.

Answer

Increase the number of vCPUs on the instance to handle more concurrent queries.

Question 12

You have a Cloud BigQuery dataset that is queried frequently. Users complain that queries scanning large tables are slow and expensive. You want to reduce the amount of data processed by common queries. What should you do?

Accepted Answer

Partition the table by a date column and cluster by commonly filtered columns.. Partitioning and clustering are the primary BigQuery features for reducing data scanned. Partitioning divides the table into segments based on a column (e.g., date), and clustering sorts data within partitions by specified columns. When queries filter on these columns, BigQuery can skip irrelevant partitions and blocks, minimizing bytes processed and improving performance.

Answer

Set a maximum bytes billed limit on the queries.

Answer

Use the cached results feature by enabling query caching.

Answer

Create a materialized view that pre-aggregates the data.

Question 13

You manage a Cloud Spanner instance with a multi-region configuration (nam3). A read-write transaction that updates a single row in a table with a monotonically increasing primary key (e.g., timestamp) experiences high commit latency. What is the most likely cause?

Accepted Answer

The monotonically increasing primary key causes hotspots, as all writes go to the same split, overwhelming a single server.. Cloud Spanner partitions data by key ranges, and each partition is served by a specific server. When a table uses a monotonically increasing primary key, such as a timestamp, new inserts and updates concentrate on the last partition, causing a hotspot. This hotspot leads to increased latency and resource contention. To avoid this, keys should be designed to distribute writes evenly, for example by using a hash of the timestamp or a UUID.

Answer

The transaction is using a read-only lock mode, causing it to wait for a global snapshot.

Answer

The multi-region configuration replicates data synchronously across regions, and the commit must wait for quorum acknowledgment from all regions.

Answer

The transaction is not using an index, forcing a full table scan before the update.

Question 14

You are analyzing a Cloud BigQuery dataset that contains billions of rows. A daily report query scans the entire table, resulting in high cost and long execution time. You want to reduce the amount of data processed by the query. Which action should you take?

Accepted Answer

Partition the table by date and filter on the date column.. Partitioning the table by date and filtering on that column allows BigQuery to prune partitions, scanning only the necessary data. This reduces both cost and execution time, directly addressing the full-table scan problem.

Answer

Increase the number of slots in the project.

Answer

Create a clustered table on a high-cardinality column.

Answer

Use a materialized view to precompute the report.

Question 15

You are managing a Cloud SQL for MySQL instance that supports an e-commerce application. During peak hours, you notice that the number of active connections spikes, and the instance occasionally reaches the maximum connections limit, causing errors. You want to reduce the likelihood of hitting this limit and improve performance. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure a connection pooler such as ProxySQL or the Cloud SQL Auth Proxy with connection pooling.. To reduce connection spikes, implementing a connection pooler reduces the number of actual database connections by multiplexing client requests. Additionally, lowering idle timeouts ensures that unused connections are closed promptly, freeing up slots for active connections. Together, these actions directly mitigate the risk of hitting max_connections without over-provisioning resources.

Answer

Enable the slow query log and analyze queries to optimize them.

Answer

Increase the max_connections database flag to a higher value.

Answer

Increase the instance's vCPU count to handle more connections.