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

Question 1

Refer to the exhibit. What is the most effective optimization for this query?

Accepted Answer

Create a composite index on (status, order_date). The query filters on `status` and `order_date`, so a composite index on `(status, order_date)` allows the database to perform an index seek on the equality predicate (`status`) and then a range scan on the ordered column (`order_date`), avoiding a full table scan. This is the most effective optimization because it directly supports the WHERE clause with minimal I/O and no sorting overhead.

Answer

Increase the instance memory to 30 GB

Answer

Remove the WHERE clause and fetch all rows in application

Answer

Partition the orders table by month

Question 2

Refer to the exhibit. You receive an alert from this policy for a Cloud Spanner instance. Which action should you take first?

Accepted Answer

Add more nodes to the instance. The alert from the policy indicates that the Cloud Spanner instance is experiencing high CPU utilization, which is a sign of resource saturation. Adding more nodes increases the total compute and I/O capacity of the instance, directly alleviating the CPU bottleneck and improving throughput. This is the first and most immediate corrective action because Spanner's performance scales linearly with nodes, and other optimizations (like index or query tuning) are secondary to ensuring sufficient capacity.

Answer

Identify and remove unused indexes

Answer

Review the top queries by CPU usage in the Spanner console

Answer

Split large tables into smaller ones

Question 3

Refer to the exhibit. You notice replication latency is 15ms. What is the most likely cause of this latency?

Accepted Answer

High CPU utilization (85%) on the cluster. High CPU utilization (85%) on the cluster is the most likely cause of replication latency because the replication process (often using protocols like Raft or Paxos in distributed databases) is CPU-intensive. When CPU resources are saturated, the cluster cannot process replication requests in a timely manner, leading to increased latency. In many distributed database systems, replication involves serialization, checksumming, and network I/O, all of which compete for CPU cycles.

Answer

The table load is not uniform

Answer

Storage utilization is at 70%

Answer

The number of nodes is above the recommended count

Question 4

Which TWO metrics are most important to monitor for a Cloud SQL for PostgreSQL instance to detect performance degradation?

Accepted Answer

CPU utilization. CPU utilization (B) is a primary indicator of performance degradation because sustained high CPU usage (e.g., >80%) can lead to query queuing, reduced throughput, and increased latency. Disk IOPS (D) is equally critical because Cloud SQL for PostgreSQL relies on disk I/O for WAL writes, checkpointing, and query execution; hitting the IOPS limit of the underlying persistent disk (e.g., 3,000 IOPS for a pd-standard disk) causes throttling and severe performance drops.

Answer

Memory usage

Answer

Query latency

Answer

Network throughput (bytes sent/received)

Question 5

Which THREE actions can help reduce read latency in Cloud Spanner?

Accepted Answer

Use read-only transactions with strong consistency. Read-only transactions with strong consistency in Cloud Spanner use lock-free reads that return the most recent data without blocking writes. This reduces read latency because the system can serve the data directly from the current timestamp without waiting for write locks or replication delays, making it ideal for low-latency, strongly consistent reads.

Answer

Increase the staleness allowed for read queries

Answer

Batch multiple write operations into a single mutation

Question 6

Which TWO are best practices for optimizing write performance in Cloud Bigtable?

Accepted Answer

Group multiple mutations into a single request. Bigtable batches mutations into a single RPC request, reducing network round trips and improving throughput. Sending individual mutations incurs per-request overhead, so grouping them into a single atomic or non-atomic batch (via `MutateRows` or client-side batching) significantly increases write throughput.

Answer

Use short row keys to reduce storage size

Answer

Use the Dataflow Bulk Import API for real-time writes

Answer

Increase replication lag to allow more time for writes

Question 7

You manage a Cloud SQL for PostgreSQL instance that handles OLTP workloads. Users in a different region report slow query response times. You notice that the database CPU utilization is below 30%, but network latency is high. What is the most cost-effective solution to reduce query latency without migrating the database?

Accepted Answer

Create cross-region read replicas and route read queries to the nearest replica.. The issue is high network latency for users in a different region, not local resource contention. Creating cross-region read replicas allows read queries to be served from a replica closer to the users, reducing network round-trip time without migrating the database. This is the most cost-effective solution as it avoids expensive instance upgrades or a full migration to Cloud Spanner.

Answer

Add more memory to the instance to increase cache hit ratio.

Answer

Increase the instance's vCPUs to handle more concurrent connections.

Answer

Migrate the database to Cloud Spanner using a live migration service.

Question 8

You are using Cloud Monitoring to track performance of a Cloud Spanner instance. Which metric indicates that a database is approaching its throughput limits and may need a split or additional nodes?

Accepted Answer

High CPU utilization (percentage). High CPU utilization (percentage) is the correct metric because Cloud Spanner's CPU utilization directly reflects the processing load on the instance's nodes. When CPU utilization consistently exceeds 65-70%, it indicates the database is approaching its throughput limits, often requiring a split (to distribute load across more tablets) or additional nodes to maintain performance and avoid throttling.

Answer

Storage utilization (bytes used)

Answer

Query error rate (errors per second)

Answer

Average commit latency

Question 9

Your Cloud Spanner database is experiencing a high volume of read-write conflicts, causing many aborts and high latency. You have already increased the compute capacity. Upon analyzing the schema, you find that most conflicts occur on a single table with frequent updates to the same row. What index or schema change would most effectively reduce contention?

Accepted Answer

Change the primary key to include a hash prefix of the original key.. Adding a hash prefix to the primary key distributes writes across multiple splits, reducing hot-spotting on a single row. Cloud Spanner uses range-based splits; without a hash prefix, monotonically increasing keys cause all writes to target the same split, leading to read-write conflicts and aborts. A hash prefix randomizes the key distribution, spreading load across nodes and minimizing contention.

Answer

Enable batch writes in the client library to combine updates.

Answer

Use a monotonically increasing timestamp as a prefix to the primary key.

Answer

Add a secondary index on the most frequently updated column.

Question 10

A data analytics team runs ad-hoc queries on BigQuery that often exceed their slot capacity, causing queuing. They want to ensure predictable performance for their critical dashboard while still allowing ad-hoc queries. What is the most cost-effective solution?

Accepted Answer

Use a single reservation with a baseline of slots for the dashboard (top priority), and allow ad-hoc queries to use idle slots.. Setting a baseline number of slots for the dashboard guarantees resources, while allowing idle slots to be used by ad-hoc queries. Adding a reservation for only the dashboard with a separate project would waste slots; converting to on-demand is unpredictable; changing the BQ location does not affect slots.

Answer

Create a separate BigQuery reservation for the dashboard with a fixed number of slots, and let ad-hoc queries use on-demand pricing.

Answer

Switch all queries to on-demand pricing; the dashboard will automatically get priority.

Answer

Move the data to a different BigQuery region with more slot availability.

Question 11

You are monitoring a Memorystore for Redis instance serving as a cache for an e-commerce application. The cache hit ratio has dropped from 95% to 70%. Which action is most likely to restore the hit ratio?

Accepted Answer

Increase the maxmemory setting to accommodate more cache entries.. A drop in cache hit ratio from 95% to 70% indicates that the cache is evicting frequently accessed keys to make room for new ones. Increasing the maxmemory setting allows Redis to store more entries, reducing evictions and restoring the hit ratio. This is the most direct way to address the capacity issue without changing application behavior.

Answer

Reduce the network latency between the application and Redis by moving them to the same zone.

Answer

Change the eviction policy to 'noeviction' to prevent key removal.

Answer

Enable AOF persistence to ensure data survives restarts.

Question 12

You are optimizing a Cloud SQL for MySQL instance for an OLTP application. You observe frequent buffer pool contention and high disk reads per second. The instance has 16 vCPUs and 120 GB memory. What is the most effective initial tuning action?

Accepted Answer

Increase innodb_buffer_pool_size to 80% of available memory.. Buffer pool contention and high disk reads per second are classic symptoms of an undersized InnoDB buffer pool. For a dedicated Cloud SQL for MySQL OLTP instance with 120 GB memory, increasing innodb_buffer_pool_size to 80% (96 GB) maximizes cached data and index pages in memory, reducing physical disk I/O and contention. This directly addresses the root cause—insufficient memory for the working set—without introducing side effects like connection overhead or query cache invalidation.

Answer

Increase the max_connections parameter to handle more concurrent sessions.

Answer

Reduce max_heap_table_size to avoid in-memory temp tables spilling to disk.

Answer

Enable query cache to cache SELECT results.

Question 13

Your application uses Firestore for real-time updates. You notice increasing read latency during peak hours. The database is in Native mode with a single-location (us-central1). After reviewing metrics, you see that the number of document reads has not changed significantly, but the database size has grown. What is the most likely cause and solution?

Accepted Answer

Review and create composite indexes for common query patterns.. As the database size grows, Firestore's query performance can degrade if queries rely on automatic index scanning without composite indexes. Composite indexes allow Firestore to serve queries without scanning all documents, reducing read latency. The unchanged read count but increased latency indicates that queries are scanning more data due to missing indexes.

Answer

Enable multi-region replication to distribute read traffic.

Answer

The database needs to be defragmented periodically; run a compaction command.

Answer

Migrate the database to Datastore mode for better performance.

Question 14

You need to set up a Cloud Monitoring alert for a Cloud Spanner instance to notify when the CPU utilization exceeds a threshold that could indicate performance degradation. What is the recommended CPU utilization threshold for Cloud Spanner?

Accepted Answer

65%. The recommended CPU utilization threshold for Cloud Spanner is 65%. This value is based on Google's best practices, as sustained CPU usage above 65% can lead to increased latency and performance degradation due to queuing and contention. Setting the alert at 65% provides a proactive warning before the instance reaches a critical state, allowing time for scaling or optimization.

Answer

90%

Answer

40%

Answer

80%

Question 15

You are monitoring a Cloud Spanner instance that is experiencing high CPU utilization (consistently above 70%). You want to identify the root cause. Which TWO metrics should you examine first? (Choose two.)

Accepted Answer

Average commit latency. Examining read and write throughput helps identify if the workload is pushing the instance. Analyzing commit latency and lock wait time reveals contention. Stale reads show replica lag but are not primary indicators of high CPU. Node count is configuration, not utilization.

Answer

Lock wait time

Answer

Stale read rate

Answer

Number of nodes