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

Question 1

You are running a Cloud Bigtable instance for time-series data ingestion. Write throughput has dropped significantly, and you see an increase in 'resource exhausted' errors. The table has one column family and one rowkey format: `#orgId#deviceId#timestamp`. After analyzing cluster metrics, you see that one node is handling most of the traffic. What is the most likely cause?

Accepted Answer

The rowkey design is causing hotspotting on one tablet node due to a prefix (orgId) being written heavily.. The rowkey design `#orgId#deviceId#timestamp` causes hotspotting because all writes for a given `orgId` are directed to a single tablet node. Cloud Bigtable partitions data by rowkey range, and sequential or heavily skewed prefixes (like `orgId`) concentrate write traffic on one node, leading to 'resource exhausted' errors and throughput degradation.

Answer

The column family design is creating too many columns.

Answer

The cluster does not have enough nodes.

Answer

The timestamp is too granular, causing many rows with the same timestamp.

Question 2

You have a Memorystore for Redis instance used as a session store. You notice that the instance is experiencing high eviction rates. What is the best first step to take?

Accepted Answer

Increase the instance size or set a TTL policy on session keys.. High eviction rates in Memorystore for Redis indicate that the instance is running out of memory and the Redis `maxmemory-policy` is actively removing keys. The best first step is to either increase the instance size to provide more memory or set a TTL (Time-To-Live) policy on session keys so that expired sessions are cleaned up proactively, reducing memory pressure and evictions.

Answer

Monitor memory usage but take no action.

Answer

Add a read replica to offload read traffic.

Answer

Enable persistence (AOF or RDB) to reduce memory usage.

Question 3

Which TWO metrics should you monitor in Cloud Monitoring to evaluate the performance of a Cloud Spanner instance? (Choose two.)

Accepted Answer

Commit latency. Commit latency is a critical metric for Cloud Spanner because it directly measures the time taken to commit a transaction, which reflects the database's ability to handle write operations efficiently. High commit latency can indicate contention, node overload, or suboptimal schema design, making it essential for performance evaluation.

Answer

Row reads per second

Answer

Connection count

Answer

Disk IOPS

Question 4

Which THREE are best practices for designing a Cloud Spanner schema for high performance? (Choose three.)

Accepted Answer

Avoid using a monotonically increasing primary key as the first part of the key.. Option B is correct because a monotonically increasing primary key (e.g., an auto-increment integer or timestamp) creates a hot spot on the last tablet server, causing all writes to be serialized on a single split. Cloud Spanner distributes splits based on the primary key range; a sequential key forces all new inserts into the same split, leading to write contention and poor throughput. Using a hash prefix or a UUID-like key spreads writes evenly across splits, maximizing parallelism.

Answer

Spread data across multiple regions to reduce latency.

Answer

Denormalize frequently joined tables into a single table.

Question 5

Which TWO actions can help reduce connection overhead in a Cloud SQL for MySQL instance? (Choose two.)

Accepted Answer

Enable automatic connection management in the application driver.. Options B and D are correct. Using a connection pooler (B) like Cloud SQL Proxy reduces connection churn. Enabling automatic connection management (D) also helps. Option A is wrong because increasing max connections does not reduce overhead. Option C is wrong because SSL may increase overhead. Option E is wrong because read replicas do not reduce connection overhead on the primary.

Answer

Disable SSL encryption for database connections.

Answer

Increase the max_connections parameter.

Answer

Add a read replica to handle connect requests.

Question 6

You are using Cloud Memorystore for Redis as a caching layer. You notice that cache hit ratio is below 50%. What is the best action to improve it?

Accepted Answer

Increase the TTL (time-to-live) for cached data.. A low cache hit ratio indicates that a large proportion of requests are not finding their data in the cache, forcing the application to fetch from the primary database. Increasing the TTL (time-to-live) for cached data keeps valid entries in Redis longer, reducing the frequency of evictions and cache misses. This directly improves the hit ratio by ensuring that more requests can be served from the cache before the data expires.

Answer

Flush the cache periodically to remove stale data.

Answer

Enable persistence to avoid data loss.

Answer

Increase the instance memory size.

Question 7

A team notices that queries on a Cloud Spanner database are slow. They want to identify which queries are consuming the most resources. What should they use?

Accepted Answer

Query Insights. Query Insights is the correct tool because it is specifically designed for Cloud Spanner to analyze query performance, providing detailed metrics such as execution latency, CPU usage, and rows scanned per query. It helps identify the most resource-intensive queries by breaking down performance by query fingerprint, allowing the team to pinpoint and optimize slow queries directly.

Answer

Cloud Logging

Answer

Performance Dashboard

Answer

Cloud Monitoring metrics

Question 8

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 9

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 10

A data warehouse in BigQuery is running slower due to large full-table scans. Which feature can reduce the amount of data processed for common queries?

Accepted Answer

All of the above. Option A is correct because partitioning, clustering, and materialized views all reduce the amount of data scanned in BigQuery. Partitioning limits scans to specific date ranges, clustering sorts data within partitions to skip irrelevant blocks, and materialized views precompute and store query results so subsequent queries read only the pre-aggregated output instead of scanning the base table. Together, these features minimize full-table scans and improve query performance.

Answer

Clustering

Answer

Materialized Views

Answer

Partitioning

Question 11

A Cloud SQL for MySQL database has frequent table locks causing contention and slow queries. Which diagnostic approach helps identify the blocking queries?

Accepted Answer

Use INFORMATION_SCHEMA.INNODB_TRX. Option D is correct because `INFORMATION_SCHEMA.INNODB_TRX` provides real-time data on all currently executing InnoDB transactions, including transaction IDs, state, and the waiting flag. By joining this with `INNODB_LOCK_WAITS` and `INNODB_LOCKS`, you can pinpoint which transaction is blocking others, directly addressing table lock contention in Cloud SQL for MySQL.

Answer

Set up a Cloud Monitoring alert on CPU

Answer

Use Query Insights

Answer

Enable slow query log

Question 12

A company runs a Cloud Spanner database with a multi-region configuration. They notice that write latency is higher than expected for clients in a region far from the leader region. What action should be taken to reduce write latency?

Accepted Answer

Change the default leader option to 'NEAREST'. Option C is correct because changing the default leader option to 'NEAREST' allows Cloud Spanner to dynamically assign the leader replica to the region closest to the majority of write requests, reducing the network round-trip time for clients far from the original leader region. This directly addresses the high write latency caused by geographic distance, as writes must be confirmed by the leader before committing.

Answer

Reduce the number of replicas

Answer

Use directed reads

Answer

Enable follower reads for writes

Question 13

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 14

A Cloud Memorystore for Redis instance used as a session store has a high eviction rate. Which configuration change can reduce evictions while maintaining performance?

Accepted Answer

Set maxmemory-policy to allkeys-lru. Option D is correct because setting `maxmemory-policy` to `allkeys-lru` allows Redis to evict the least recently used keys across all keys when memory is full, which directly reduces eviction rates by ensuring that only the least active session data is removed. This maintains performance by keeping frequently accessed session keys in memory, which is critical for a session store where active sessions are repeatedly read and written.

Answer

Enable persistence (RDB)

Answer

Increase number of replicas

Answer

Decrease timeout

Question 15

In Cloud Bigtable, a table has a high ratio of garbage collection (GC) that causes performance degradation during compaction. What is the best practice to monitor and optimize this?

Accepted Answer

Use Cloud Monitoring to track garbage collection count and adjust GC settings. Option C is correct because Cloud Monitoring provides the metrics (e.g., 'bigtable.googleapis.com/table/garbage_collection_count') needed to track GC activity, and adjusting GC settings (e.g., column family max versions or TTL) directly reduces the compaction overhead caused by excessive stale data. This aligns with best practices for proactive performance optimization in Cloud Bigtable.

Answer

Use the bigtableadmin API to view table stats

Answer

Compact the table manually

Answer

Increase node count