PCDE Practice Test 36 — 15 Questions

Question 1

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 2

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 3

A Cloud Spanner database contains the Orders table as defined above. The query `SELECT * FROM Orders WHERE CustomerID=123` takes a long time. What is the most likely reason?

Accepted Answer

The table needs a secondary index on CustomerID.. The query `SELECT * FROM Orders WHERE CustomerID=123` filters on the `CustomerID` column, but the primary key of the Orders table is likely defined on a different column (e.g., `OrderID`). Without a secondary index on `CustomerID`, Cloud Spanner must perform a full table scan to find matching rows, which is slow for large tables. Creating a secondary index on `CustomerID` allows Cloud Spanner to directly locate the relevant splits and rows, dramatically reducing latency.

Answer

The ORDER BY clause is missing.

Answer

Interleaving causes extra I/O.

Answer

The primary key is not optimized for this query.

Question 4

Your Cloud SQL for MySQL instance is experiencing unusually high disk usage. You need to identify the cause. Which metric should you monitor in Cloud Monitoring?

Accepted Answer

Binary log disk usage. Binary logs in MySQL store all data changes (e.g., INSERT, UPDATE, DELETE) and can consume significant disk space, especially under heavy write workloads or if retention is misconfigured. Monitoring 'Binary log disk usage' directly reveals whether accumulated binary logs are the primary cause of high disk usage, which is a common issue in Cloud SQL for MySQL.

Answer

InnoDB row reads

Answer

CPU utilization

Answer

Query latency

Question 5

A DBA notices high query cache lock contention on this Cloud SQL for MySQL instance. Which configuration change should be recommended?

Accepted Answer

Set query_cache_type=0. Query cache lock contention occurs when multiple threads try to access the query cache simultaneously, causing serialization. Setting `query_cache_type=0` disables the query cache entirely, eliminating the lock contention. This is the recommended fix because MySQL 8.0 deprecated the query cache due to scalability issues, and Cloud SQL for MySQL instances benefit from removing this bottleneck.

Answer

Decrease max_connections

Answer

Increase innodb_buffer_pool_size

Answer

Increase query_cache_size

Question 6

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

Question 7

Your Bigtable cluster is showing high read latency for row key lookups. The application accesses rows with keys in the format 'user_id#timestamp'. You notice that most reads are for recent timestamps. Which optimization should you implement?

Accepted Answer

Reverse the row key order to start with the timestamp. High read latency for recent timestamps occurs because Bigtable stores rows in lexicographic order by row key. With the format 'user_id#timestamp', older timestamps appear first, causing recent data to be scattered across tablets and requiring more seeks. Reversing the row key to start with the timestamp (e.g., 'timestamp#user_id') groups recent data together in contiguous tablets, enabling faster point lookups and reducing latency.

Answer

Increase the number of nodes in the cluster

Answer

Configure single cluster routing to reduce cross-cluster latency

Answer

Use a scan with a prefix filter instead of point reads

Question 8

What is this alert likely monitoring?

Accepted Answer

High number of slow queries. The alert is likely monitoring a high number of slow queries because slow query logs are a primary metric for identifying database performance bottlenecks. In PostgreSQL, the `log_min_duration_statement` parameter controls which queries are logged, and a sudden spike in slow queries indicates inefficient SQL, missing indexes, or lock contention. This directly impacts database throughput and user experience, making it a critical monitoring target.

Answer

High disk I/O

Answer

High number of database connections

Answer

High CPU usage

Question 9

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.. The correct answer is C because 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 10

A retail company uses Cloud SQL for PostgreSQL for inventory management. The schema has a table 'inventory' with columns: product_id, warehouse_id, quantity, last_updated. The table contains over 100 million rows. The application frequently runs aggregate queries to compute total quantity of a product across all warehouses (e.g., SELECT SUM(quantity) FROM inventory WHERE product_id = ?). These queries are slow, taking tens of seconds. The team tries a covering index on (product_id, quantity) but sees little improvement because they still need to scan many rows. They need to redesign the schema to improve aggregation performance. What is the best approach?

Accepted Answer

Create a summary table 'product_totals' with columns product_id and total_quantity, and use triggers to keep it updated on INSERT/UPDATE/DELETE in inventory.. Option A is correct. Creating a summary table that pre-aggregates totals per product, updated via triggers on the inventory table, dramatically speeds up the aggregate queries. Option B (covering index) helps but still requires scanning many rows. Option C (Spanner) is a migration to a different database. Option D (BigQuery) is external and not a schema change.

Answer

Add a covering index on (product_id, quantity).

Answer

Migrate the inventory table to Cloud Spanner and use interleaved indexes.

Answer

Use BigQuery as a read replica and query there.

Question 11

A healthcare analytics company uses Cloud Bigtable to store time-series data from medical devices. The table has a row key of 'device_id#timestamp' where timestamp is stored in reverse order (max - timestamp) so that recent data is at the top. Queries that fetch data for a specific device over a date range are very fast. However, analysts also need to run queries that aggregate data across all devices for a specific hour (e.g., count of readings between 2023-01-01 10:00 and 11:00). These queries are extremely slow because they require scanning all rows. The team must redesign the schema to support both access patterns without duplicating data unnecessarily. What is the best approach?

Accepted Answer

Create a second table with row key 'timestamp#device_id' (with timestamp in natural order) to support time-range queries.. Option A is correct. Creating a separate table with a row key of 'timestamp#device_id' allows efficient range scans for a given time period across all devices. This is a common pattern in Bigtable to support multiple access patterns. Option B is not possible (no secondary indexes). Option C is external and not a schema change. Option D (adding nodes) helps throughput but not query efficiency.

Answer

Use BigQuery to query Bigtable via an external table and run the aggregation there.

Answer

Increase the number of Bigtable nodes to improve scan throughput.

Answer

Add a secondary index on the timestamp column.

Question 12

A marketing team uses a BigQuery BI dashboard to analyze campaign performance. The table campaign_performance is 5 TB, partitioned by date, clustered by campaign_id. Queries filter on date range and campaign_id, and are fast. However, one query that joins this table with a user_dimensions table (10 GB, not partitioned) takes too long. The join is on user_id. What is the best improvement?

Accepted Answer

Denormalize user_dimensions into campaign_performance.. Option C is correct because user_dimensions is small (10 GB) relative to campaign_performance, denormalizing eliminates the join entirely. Option A (partition user_dimensions) helps but the join still occurs. Option B (cluster by user_id) reduces shuffle but not elimination. Option D (broadcast join hint) forces a broadcast but join still occurs.

Answer

Cluster user_dimensions by user_id.

Answer

Partition user_dimensions by date.

Answer

Use a broadcast join hint.

Question 13

A global gaming company uses Cloud Spanner for user profiles and game state. They have a single-region instance in us-central1. Recently, they launched in Europe and notice high latency for European users. They also need to ensure data locality compliance (GDPR). The database is heavily written with throughput spikes. They want to minimize latency without sacrificing write throughput. They consider adding a secondary index on region column. What is the best course of action?

Accepted Answer

Create a multi-region configuration spanning US and Europe, and modify application to read from closest region.. Option D is correct because a multi-region Spanner configuration with regional endpoints allows the application to read from the closest region, reducing latency for European users while maintaining strong consistency and write throughput. Spanner's multi-region configurations use synchronous replication across regions, ensuring GDPR compliance by keeping European user data within Europe for reads, and the write throughput is not sacrificed because all writes are committed to the primary region and replicated asynchronously to other regions.

Answer

Shard the database by region into separate Spanner instances.

Answer

Use Cloud CDN to cache read data.

Answer

Create a read-only replica in Europe using Cloud Spanner's read replica feature.

Question 14

You administer a Cloud SQL for PostgreSQL instance running with 4 vCPUs and 15 GB of memory. The application frequently runs complex read-only reporting queries during business hours. Recently, the database started throwing 'out of memory' errors, and the instance's memory usage is consistently above 90%. You have enabled the pgtune recommendations and applied them. The workload is read-heavy and does not require a high write throughput. Which change would most effectively reduce memory pressure while maintaining query performance?

Accepted Answer

Scale up the instance to a higher memory tier, such as 8 vCPUs and 30 GB of memory.. Option B is correct: Increasing the instance memory directly addresses the memory shortage. Option A (reducing shared_buffers) may free memory but hurt performance. Option C (using connection pooling) reduces memory per connection but not overall memory needs. Option D (enabling compression) might help with I/O but not memory very much; also, pgzstd is a tricky flag.

Answer

Enable the pgzstd extension to compress data in memory.

Answer

Set up connection pooling using PgBouncer to limit the number of concurrent connections.

Answer

Decrease the shared_buffers configuration parameter to free memory for other uses.

Question 15

Your team uses Cloud Bigtable for a time-series data analytics platform. You observe that the write throughput has dropped significantly, and Cloud Monitoring shows that most of the CPU usage is concentrated on a few nodes. The remaining nodes have low CPU usage. The data model uses sequential timestamps as row keys, and the application writes data for many different sensors. Each sensor ID is part of the row key. What is the most effective action to resolve this hot spotting?

Accepted Answer

Prepend a hash of the sensor ID to the row key to distribute writes evenly.. Option A is correct: Adding a hash prefix to row keys will distribute writes across nodes, preventing hot spotting. Option B (increasing nodes) may spread load but without fixing row key design, hot spots may persist. Option C (using a different cluster) doesn't address the design issue. Option D (reducing write batch size) may reduce latency but not the uneven distribution.

Answer

Reduce the batch size of writes to decrease the load on each node.

Answer

Use a different Bigtable cluster and migrate data.

Answer

Increase the number of nodes in the cluster to provide more CPU capacity.