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

Question 1

A company has a Cloud SQL for PostgreSQL instance that experiences high CPU usage during peak hours due to read-heavy queries. Which optimization is most effective for reducing CPU load?

Accepted Answer

Add read replicas. Adding read replicas offloads read-heavy queries from the primary Cloud SQL for PostgreSQL instance, distributing the query load and reducing CPU utilization on the primary. This is the most direct and effective optimization for read-heavy workloads because replicas handle SELECT traffic while the primary focuses on writes and critical operations.

Answer

Use connection pooling

Answer

Increase memory size

Answer

Enable automatic storage increase

Question 2

An e-commerce platform uses Cloud Bigtable for real-time user sessions. Write latency is high. On investigation, they find that rows are being written with monotonically increasing row keys (e.g., user_id + timestamp). What is the likely cause and solution?

Accepted Answer

Hotspotting on a single node; use salting or field promotion. Monotonically increasing row keys (e.g., user_id + timestamp) cause all writes to target a single tablet server, creating a hotspot. Cloud Bigtable distributes writes across nodes by row key range; sequential keys concentrate load on one node, degrading write latency. Salting (prepending a hash or random prefix) or field promotion (using a high-cardinality field as the first part of the key) spreads writes evenly across the cluster.

Answer

Too many column families; merge them

Answer

Inefficient reads; use reverse scan

Answer

Tablet splits are misconfigured; pre-split the table

Question 3

Which Cloud Monitoring metric indicates the number of queries waiting for locks in Cloud SQL?

Accepted Answer

Lock waits. The 'Lock waits' metric in Cloud SQL (for MySQL, PostgreSQL, or SQL Server) directly tracks the number of queries that are blocked because they are waiting for a lock held by another transaction. This is the correct indicator of query contention, as it measures the count of statements currently in a lock-wait state, not the total queries or connections.

Answer

Active connections

Answer

CPU utilization

Answer

Queries

Question 4

A company is experiencing slow query performance in Cloud SQL for PostgreSQL. Which TWO tools can help identify the root cause?

Accepted Answer

Cloud Monitoring. Query Insights (B) is the purpose-built tool for Cloud SQL for PostgreSQL that captures per-query latency, execution plans, and wait-event data, letting you pinpoint slow or high-frequency queries causing the slowdown. Cloud Monitoring (A) is also correct because it provides metrics such as CPU utilization, memory usage, disk I/O, and query latency for the Cloud SQL instance, helping correlate resource saturation with the slow query behavior. Together they cover both query-level and infrastructure-level diagnosis. Cloud Logging with error reporting (C) is for capturing and aggregating log/error events, not for profiling query execution or resource metrics. Cloud Profiler (D) analyzes CPU and heap usage of application code, not database query performance. Cloud Trace (E) traces request latency across distributed services, but it does not expose PostgreSQL query plans or instance-level database metrics.

Answer

Cloud Logging with error reporting

Answer

Cloud Profiler

Answer

Cloud Trace

Question 5

In Cloud Spanner, which TWO strategies can help reduce read latency for globally distributed applications?

Accepted Answer

Enable follower reads. Option A (Enable follower reads) is correct because Cloud Spanner follower reads let a read be served by a replica in the same region as the client rather than being routed to the leader region, eliminating the cross-region round trip and thereby reducing read latency for globally distributed applications. Option B (Use stale reads) is correct because reading at a bounded staleness timestamp (e.g., a timestamp at least 15 seconds in the past, or an exact staleness) allows Spanner to serve the read from the closest up-to-date replica without waiting for the leader, which lowers latency at the cost of slightly older data. Option C is not correct because a multi-region configuration with a default leader still routes strong reads to the leader region, so it does not by itself reduce read latency for clients far from that leader. Option D is not correct because database partitioning is not a Cloud Spanner latency-reduction feature; Spanner scales via splits and does not expose user-managed partitioning for read latency. Option E is not correct because increasing the number of nodes adds compute capacity and throughput, but it does not change the geographic routing of reads and therefore does not reduce cross-region read latency.

Answer

Use a multi-region configuration with a default leader

Answer

Use database partitioning

Answer

Increase the number of nodes

Question 6

Your Cloud Spanner instance has high latency for point reads. The workload is evenly distributed across all nodes. Which metric should you examine first to identify the bottleneck?

Accepted Answer

CPU utilization per node. High latency for point reads in Cloud Spanner, even with evenly distributed workload, often points to CPU saturation on individual nodes. Spanner uses a shared-nothing architecture where each node handles a portion of the data and queries; if CPU utilization per node is high, it indicates that the node is overloaded, causing queuing and increased latency. This metric directly reflects processing capacity and is the first place to look for a bottleneck in point-read performance.

Answer

Storage utilization

Answer

Rows returned per second

Answer

Number of committed nodes

Question 7

You are tuning a Cloud SQL for PostgreSQL instance that runs reporting queries. The slowest query performs a full table scan on a 100 GB table. Which action is most likely to improve performance?

Accepted Answer

Create an index on the columns used in the WHERE clause. A full table scan on a 100 GB table indicates that PostgreSQL has no efficient access path to retrieve the required rows. Creating an index on the columns used in the WHERE clause allows the query planner to use an index scan instead of a sequential scan, drastically reducing the number of disk pages read and improving query performance. This is the most direct and effective optimization for a query that filters rows without an index.

Answer

Increase shared_buffers to 50% of instance memory

Answer

Set statement_timeout to 30 seconds

Answer

Move the query to a read replica

Question 8

Your Cloud Spanner instance has several tables with interleaved parent-child relationships. You notice that queries that join parent and child tables are slow. What is the best practice to optimize these joins?

Accepted Answer

Ensure the tables are defined as interleaved with the parent key as the first part of the child primary key. Cloud Spanner optimizes interleaved table joins by physically co-locating parent and child rows on the same split, based on the parent key as the prefix of the child's primary key. This eliminates the need for distributed cross-split joins, dramatically reducing latency. Queries that join on the interleaved key benefit from local data access, making them fast and efficient.

Answer

Create secondary indexes on the join columns

Answer

Use batch update operations to reduce round trips

Answer

Remove interleaving and use a separate JOIN statement

Question 9

You are managing a Memorystore for Redis cluster with standard tier (persistence disabled). The application experiences occasional latency spikes while performing SET operations. You observe that the 'evicted_keys' metric spikes during the spikes. What is the most effective solution?

Accepted Answer

Increase the maximum memory size of the instance. The evicted_keys metric spikes during SET operations indicate that the Redis instance has reached its maxmemory limit and is evicting keys to accommodate new writes. Increasing the maximum memory size directly addresses the root cause by providing more headroom for data, reducing the need for eviction and the associated latency spikes.

Answer

Enable AOF persistence with fsync every second

Answer

Change the maxmemory-policy to 'volatile-lru'

Answer

Configure a read replica to offload read traffic

Question 10

Your Cloud SQL for SQL Server instance has a query that uses a non-clustered index to filter rows but then performs key lookups to retrieve additional columns. The query is slow. Which database tuning option would most likely reduce I/O?

Accepted Answer

Create a covering index that includes all columns referenced in the query. The query is slow because key lookups require random I/O to retrieve additional columns not included in the non-clustered index. Creating a covering index that includes all columns referenced in the query eliminates the need for key lookups entirely, converting the operation into a single index seek or scan with minimal I/O. This directly reduces the number of page reads and improves query performance.

Answer

Increase the buffer pool size

Answer

Rebuild the non-clustered index with FILLFACTOR=80

Answer

Use a FORCESEEK query hint

Question 11

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

Accepted Answer

Group multiple mutations into a single request. Option B is correct because batching multiple mutations (SetCell, DeleteFromRow, etc.) into a single MutateRow or MutateRows request amortizes RPC overhead and reduces round trips, which is a documented Cloud Bigtable best practice for write throughput. Option C is correct because Cloud Bigtable shards data by row-key ranges into tablets, so a well-distributed, high-cardinality row key (e.g., avoiding sequential timestamps or monotonic prefixes) prevents hotspots and spreads writes evenly across nodes for higher aggregate throughput. Option A is not a write-performance best practice; row-key length affects storage and read efficiency, not write throughput, and short keys alone do not distribute load. Option D is wrong because the Dataflow Bulk Import API is designed for large offline/batch imports, not real-time writes, which should use the Cloud Bigtable client libraries or the Write API. Option E is wrong because replication lag is a consequence of replication, not a tunable write-optimization knob, and increasing lag does not improve write performance.

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 12

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 13

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 14

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 15

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%