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

Question 1

Your Cloud Spanner instance is experiencing high write latency during peak hours. You have a table with a monotonically increasing primary key (e.g., a timestamp-based UUID). You want to reduce write latency without changing the application's write pattern. What should you do?

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

Accepted Answer

Use a UUID version 4 (random) as the primary key instead of a monotonically increasing key.. The issue is a hotspot caused by a monotonically increasing primary key, which causes all new writes to go to the last split. To mitigate this without changing the application's write pattern, you can use a key that is not monotonically increasing but still generated by the application. However, the scenario says 'without changing the application's write pattern' which likely means the application continues to write as before, but the key can be changed. In Cloud Spanner, the recommended approach is to use a random UUID or hash the key. But since the question asks 'what should you do?' and the options include increasing nodes, which doesn't help, and hashing, which changes the key, the best answer is to use a random UUID. However, this requires application changes. Let's check the options: A is invalid, B changes key and requires app changes, C doesn't solve hotspot, D changes key and requires app changes. The only viable is D. But the scenario says 'without changing the application's write pattern' - perhaps it means without changing the frequency or type of writes, but the key generation can be changed. I'll go with D.

Answer

Enable optimistic concurrency control for the table.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Use a hash of the primary key as the first part of the key.

Question 2

You are managing a Cloud SQL for MySQL instance that experiences performance degradation during nightly batch jobs. The batch jobs perform many small writes. You notice that the InnoDB buffer pool hit rate is low, and disk I/O is high. You want to improve performance without changing the application. Which action should you take?

Accepted Answer

Increase the size of the InnoDB buffer pool.. Increasing the InnoDB buffer pool size directly addresses the low hit rate by caching more data in memory, reducing disk I/O and improving performance for write-heavy batch jobs. This is a configuration change that requires no application modifications.

Answer

Change the storage type to SSD.

Answer

Enable the query cache.

Answer

Increase the number of connections.

Question 3

Your company uses Cloud Bigtable to store time-series sensor data. The table has a single column family and uses row keys that start with a sensor ID followed by a reversed timestamp. You notice that read latency for recent data has increased, and Cloud Monitoring shows high CPU utilization on the cluster. You want to reduce read latency for queries that fetch the latest readings for a specific sensor. What should you do?

Accepted Answer

Use a row key that stores the timestamp in reverse order so that the latest readings for a sensor are lexicographically first.. Bigtable rows are sorted lexicographically by row key. To fetch the latest readings for a sensor efficiently, the most recent timestamps must sort first within that sensor's prefix. Reversing the timestamp achieves this, allowing a small contiguous scan. Hashing scatters data, adding nodes does not fix access patterns, and garbage collection does not reorder rows, so none of those directly reduce read latency for the latest readings.

Answer

Enable a column family garbage collection policy to remove old data and reduce the amount of data scanned.

Answer

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

Answer

Change the row key design to prepend a hash of the sensor ID before the sensor ID and timestamp.

Question 4

Your Cloud Bigtable cluster is experiencing high read latency for a workload that performs many small random reads. You observe that the cluster has 6 nodes and CPU utilization is around 50%. You want to reduce read latency without increasing the number of nodes. Which action should you take?

Accepted Answer

Use a row key design that clusters related data together to improve read locality.. In Cloud Bigtable, row key design is critical for performance. Random reads that access scattered row keys cause many disk seeks and poor cache utilization. By redesigning the row key to cluster related data, reads become more sequential, allowing Bigtable to serve multiple reads from the same tablet and cache blocks more effectively. This reduces latency without scaling the cluster, making it the optimal solution for the given workload.

Answer

Increase the size of the row cache by modifying the table's app profile.

Answer

Change the app profile to use single-cluster routing instead of multi-cluster routing.

Answer

Enable a column family with a Bloom filter to reduce disk seeks for non-existent rows.

Question 5

You are managing a Cloud Bigtable instance that serves a workload with a mix of reads and writes. You notice that write latency has increased significantly. You suspect that the issue is due to large rows being written. Which action should you take to improve write latency?

Accepted Answer

Split large rows into multiple smaller rows with a common prefix.. Large rows can cause write latency because they require more resources and can create hotspots. Splitting them into smaller rows with a common prefix distributes the data and allows Bigtable to handle writes more efficiently. Increasing nodes, enabling Data Boost, or using sequential row keys do not address the root cause and may even worsen performance.

Answer

Increase the number of nodes in the Bigtable cluster.

Answer

Enable Bigtable Data Boost for the instance.

Answer

Change the row key to use a sequential numeric value.

Question 6

You are optimizing a Cloud Bigtable instance that serves a time-series metrics workload. The table uses a row key of the form metric_id#reverse_timestamp, and reads target the most recent hour for a given metric. You observe that read latency is low for most metrics but very high for a few high-cardinality metrics, and the cluster's CPU is unevenly distributed across nodes. You want to reduce the tail latency for those hot metrics without changing the read consistency model. What should you do?

Accepted Answer

Use a row key that combines the metric_id with a shard suffix derived from a bucket of the timestamp, such as metric_id#shard#reverse_timestamp, and have the reader query the relevant shards.. Uneven CPU and tail latency on a few metrics indicate tablet-level hot spots: each metric's row range is served by one tablet. Introducing a shard component in the row key spreads a single metric across multiple tablets, letting reads fan out and merge, while keeping the reverse timestamp suffix for efficient recent-window scans. Node scaling, hashing the metric prefix, or in-memory families do not relieve the concentrated tablet.

Answer

Change the row key to prefix a hash of the metric_id before the reverse timestamp so that each metric's data is spread across multiple tablets.

Answer

Increase the cluster to at least three nodes and enable replication so reads can be served from multiple clusters.

Answer

Enable an in-memory column family for the hot metrics so that reads are served from memory instead of SSD.

Question 7

A Cloud Bigtable table stores time-series data and the workload is read-heavy with scans over recent time ranges. The database engineer notices that read latency is higher than expected and that the cluster's CPU utilization is around 40%. The engineer wants to reduce read latency without changing the row key schema. Which action is most appropriate?

Accepted Answer

Switch the table's storage type from HDD to SSD.. With CPU utilization at only 40%, the Bigtable cluster has spare compute capacity, so latency is not caused by node saturation. Bigtable's SSD storage option offers much lower read latency than HDD, making a storage type change the most effective action for a read-heavy scan workload without altering the row key design.

Answer

Increase the number of column families in the table.

Answer

Enable a replication cluster in a second region to serve reads.

Answer

Add more nodes to the cluster to increase read throughput.

Question 8

Your Cloud Bigtable cluster has 8 nodes and is experiencing high read latency for a workload that performs many small random reads. The CPU utilization is around 40%, and the disk utilization is low. You notice that the read requests are using a RowFilter with a regex filter. What is the most likely cause of the latency?

Accepted Answer

The regex filter forces Bigtable to scan all rows, increasing the amount of data read and latency.. In Cloud Bigtable, row keys are stored in lexicographic order, and filters that do not leverage the key order, such as regex filters, require a full table scan. This is inefficient and increases latency. For random reads, using a prefix scan or a get by exact row key is much faster. The regex filter causes Bigtable to read every row, which explains the high latency despite low CPU and disk utilization.

Answer

The cluster is using HDD disks instead of SSDs, causing higher read latency.

Answer

The cluster does not have enough nodes to handle the read throughput.

Answer

The read requests are not using a batch API, causing per-request overhead.

Question 9

You manage a BigQuery dataset that contains a large fact table partitioned by day and clustered by customer_id. Analysts frequently run queries that filter on a specific day and a specific customer_id, but they report that these queries scan much more data than expected. You want to reduce the bytes scanned by these queries. What should you do?

Accepted Answer

Instruct analysts to use the _PARTITIONDATE pseudo-column in the WHERE clause instead of a timestamp column.. For a table partitioned by day, partition pruning occurs only when the query filter matches the partitioning column. The _PARTITIONDATE pseudo-column represents the partition boundary and allows BigQuery to skip irrelevant partitions. Clustering on another column, materialized views, or changing the partitioning type do not ensure that the analysts' day filter prunes partitions, so bytes scanned may remain high.

Answer

Create a materialized view that pre-aggregates the data by day and customer_id.

Answer

Change the table to use ingestion-time partitioning instead of a date column partition.

Answer

Add a cluster on the timestamp column in addition to the existing cluster on customer_id.

Question 10

You are optimizing a Cloud Spanner database for a workload that includes frequent reads of a large table. You want to reduce read latency and improve performance. Which two actions should you take? (Choose two.)

Accepted Answer

Create a secondary index on columns used in the WHERE clause of frequent queries.. Secondary indexes allow Spanner to quickly locate rows matching query filters, avoiding full table scans. Interleaved tables co-locate related rows, reducing the need for network hops when reading hierarchical data. Both actions directly target read latency by improving data access efficiency. Increasing nodes or using Data Boost addresses capacity and workload isolation, not single-query latency, and stale reads trade consistency for latency.

Answer

Increase the number of nodes in the Spanner instance to add more CPU resources.

Answer

Use a read-only transaction with a staleness of 15 seconds to read from the nearest replica.

Answer

Enable Data Boost to offload read workloads to a separate compute layer.

Question 11

Your Cloud SQL for MySQL instance is experiencing intermittent performance degradation. You suspect that the issue is due to a sudden spike in connections from a specific application. Which metric and monitoring approach would best help you correlate the connection spike with performance degradation?

Accepted Answer

Monitor 'cloudsql.googleapis.com/database/mysql/threads/threads_connected' and correlate with CPU utilization and query latency.. The 'threads_connected' metric directly measures the number of active connections to the MySQL instance. Correlating this with CPU utilization and query latency allows you to pinpoint whether a sudden spike in connections is causing resource contention and degraded query performance, which is the exact scenario described.

Answer

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Answer

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Answer

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Question 12

You are running a production workload on Cloud Bigtable and notice that read latency has increased. Upon reviewing the monitoring dashboard, you see that CPU utilization is below 50% but the number of active tablets is high. What is the most likely cause of the increased read latency?

Accepted Answer

There are too many tablets, causing increased metadata operations and slower reads.. In Cloud Bigtable, each tablet is a contiguous range of rows managed by a tablet server. When the number of active tablets is high, the tablet server must perform more metadata operations (e.g., splitting, merging, and serving multiple tablets) which increases per-request overhead and can degrade read latency. This is true even when CPU utilization is below 50%, because the overhead is not purely CPU-bound but involves increased I/O and coordination.

Answer

Read requests are being throttled due to exceeding IOPS limits.

Answer

A hot node is throttling read requests.

Answer

The cluster is underprovisioned, causing resource contention.

Question 13

Which TWO actions can help reduce the number of read replicas needed for a Cloud SQL for PostgreSQL instance that serves a read-heavy workload?

Accepted Answer

Implement connection pooling to reuse database connections.. Connection pooling reduces the overhead of establishing new database connections, which can consume significant CPU and memory resources on the primary instance. By reusing existing connections, the primary instance can handle more read requests without needing additional read replicas to offload the connection management load. This directly reduces the number of replicas required for a read-heavy workload.

Answer

Enable synchronous replication on all read replicas.

Answer

Use smaller machine types for read replicas.

Answer

Increase the max_connections parameter on the primary instance.

Question 14

Your Spanner instance is running a workload with high read throughput. You notice that read latency has increased significantly. Upon investigating, you find that the instance is experiencing high CPU utilization on the Spanner nodes. The workload consists of many small point lookups (reads by primary key). Which action is most likely to reduce read latency?

Accepted Answer

Use stale reads with a timestamp bound to allow reads from replicas.. B is correct because stale reads allow Cloud Spanner to serve read requests from read-only replicas, which offloads CPU-intensive processing from the leading replica nodes. By using a timestamp bound (e.g., exact_staleness or max_staleness), the workload can tolerate slightly outdated data, reducing the load on the nodes and lowering read latency for point lookups.

Answer

Add secondary indexes on the primary key columns.

Answer

Redesign the schema to use interleaved tables.

Answer

Reduce the number of Spanner nodes to lower CPU overhead.

Question 15

A bigquery job is running slower than expected. Checking the job information, you see that the slot usage is at 100% for the entire duration of the query. You are using on-demand pricing. What is the most effective way to improve query performance?

Accepted Answer

Purchase a slot reservation and assign the project to it.. With on-demand pricing, your query is limited to the default per-project slot capacity (typically 2,000 slots in BigQuery). If slot usage is at 100% for the entire duration, the query is resource-constrained and cannot be sped up without additional slots. Purchasing a slot reservation and assigning the project to it provides dedicated slots, eliminating the contention and allowing the query to run faster.

Answer

Create materialized views for common aggregations.

Answer

Cluster the tables on frequently filtered columns.

Answer

Partition the tables by date.