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

Question 1

You are monitoring a Cloud Spanner instance and see that the average commit latency is high. The application performs many single-row inserts. Which metric would you check first to understand the root cause?

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

Accepted Answer

Lock conflicts (e.g., Spanner/API/Lock_wait).. High commit latency for single-row inserts in Cloud Spanner is often caused by lock conflicts, as concurrent transactions may contend for the same row or index. The metric Spanner/API/Lock_wait directly measures time spent waiting for locks, making it the first metric to check. High commit latency without high lock wait suggests other issues, but lock conflicts are the most common root cause for write-heavy single-row workloads.

Answer

Read latency.

Answer

Storage utilization.

Answer

CPU utilization per node.

Question 2

You are managing a Cloud SQL for MySQL instance that is experiencing high replication lag. The instance uses semi-synchronous replication. Which THREE actions could reduce the replication lag?

Accepted Answer

Change replication mode from semi-synchronous to asynchronous.. Switching from semi-synchronous to asynchronous replication removes the requirement for the primary to wait for at least one replica to acknowledge receipt of each transaction. In semi-synchronous mode, the primary waits for acknowledgment, which can introduce latency and contribute to replication lag under high write loads. Asynchronous replication allows the primary to commit transactions immediately without waiting, reducing the time the primary spends waiting for replica acknowledgment and thus lowering replication lag.

Answer

Reduce the binlog retention period on the primary.

Answer

Enable binary log compression on the primary.

Question 3

Refer to the exhibit. You restored a Spanner database from a backup and are checking the status of the optimize operation. The operation has been running for 15 minutes and is 45% complete. The database is already accessible but queries on it are slower than expected. What should you do?

Accepted Answer

Continue running queries; performance will improve once the optimize operation completes.. Spanner's optimize operation runs asynchronously and does not block database access. Queries are slower during optimization because the operation reorganizes data and rebuilds indexes, which consumes I/O and CPU resources. Once the optimize operation completes, query performance will improve as the data layout becomes more efficient.

Answer

Wait for the operation to finish before allowing any queries.

Answer

Drop and restore the database again to start fresh.

Answer

Cancel the optimize operation to reduce resource usage.

Question 4

You are managing a Spanner instance for a global financial application. The database has a table `transactions` with columns `transaction_id` (INT64), `user_id` (INT64), `amount` (FLOAT64), `timestamp` (TIMESTAMP), and `region` (STRING). The table is interleaved with a parent table `users`. Recently, you observed that point-read queries by `transaction_id` are taking over 100ms on average, whereas they used to take under 10ms. The instance CPU utilization is below 40%, and there are no contention issues. The `transactions` table has a primary key `(user_id, transaction_id)`. Queries filter on `transaction_id` only, without specifying `user_id`. Which optimization should you implement to improve point-read latency?

Accepted Answer

Create a secondary index on `transaction_id` to enable efficient key-based lookups.. Point-read queries by `transaction_id` are slow because the primary key is `(user_id, transaction_id)`, so without `user_id`, Spanner cannot directly locate the split (tablet) and must perform a full table scan or a less efficient lookup. Creating a secondary index on `transaction_id` allows Spanner to use that index for key-based lookups, reducing latency to under 10ms by enabling direct access to the specific split via the index's key.

Answer

Add a secondary index on `user_id` to help narrow down the search.

Answer

Use a Spanner query hint to force a specific index scan.

Answer

Change the primary key to `(transaction_id, user_id)` to enable direct access by transaction_id.

Question 5

Your company runs a critical application on Google Kubernetes Engine (GKE) with a StatefulSet using persistent volumes backed by Compute Engine persistent disks. The application performs frequent small random writes to a MySQL database stored on the persistent disks. You notice that the disk write latency has increased significantly, and the application's throughput has dropped. Monitoring shows that the disk queue depth is consistently high. The current disk type is pd-standard. What is the most cost-effective way to reduce write latency and improve throughput?

Accepted Answer

Change the persistent disk type from pd-standard to pd-ssd.. The application is experiencing high write latency due to insufficient IOPS from pd-standard disks, which are HDD-based and optimized for sequential reads, not small random writes. Changing to pd-ssd (SSD-based) provides significantly higher IOPS and lower latency for random write workloads, directly addressing the high queue depth and throughput drop. This is the most cost-effective solution because pd-ssd offers the necessary performance improvement without requiring architectural changes or over-provisioning capacity.

Answer

Use a regional persistent disk for higher availability and performance.

Answer

Add more replicas of the StatefulSet to distribute writes across multiple disks.

Answer

Increase the size of the persistent disks to improve IOPS limits.

Question 6

You are a database engineer for an e-commerce platform running on Cloud SQL for PostgreSQL. The application team reports that a critical report query taking 5 seconds last week now takes over 30 seconds. The database CPU usage has increased from 40% to 85%. The query plan shows a sequential scan on the orders table. Which action should you take first to diagnose the problem?

Accepted Answer

Enable pg_stat_statements and check the query's execution statistics.. Enabling pg_stat_statements provides detailed execution statistics (e.g., total time, calls, rows, I/O) for each query, allowing you to identify the root cause of the performance regression without making speculative changes. Since the query plan shows a sequential scan and CPU is high, pg_stat_statements can reveal if the query is now reading more rows due to data growth or plan changes, guiding the next action (e.g., adding an index or tuning parameters).

Answer

Migrate the database to Cloud Spanner for better scaling.

Answer

Add a composite index on the orders table based on the query's WHERE clause.

Answer

Increase the machine type to add more vCPUs.

Question 7

A Cloud SQL for PostgreSQL instance is experiencing high CPU usage during peak hours. Query Insights shows that a complex reporting query is causing full table scans on a large table. The query filters on a column used in JOINs. Which optimization should be applied first?

Accepted Answer

Add a composite index on the filtered column and join columns.. Adding a composite index on the filtered column and the join columns directly addresses the root cause of the full table scans. Query Insights indicates the query filters on a column used in JOINs; a composite index covering both the filter and join columns allows PostgreSQL to perform an Index Scan instead of a sequential scan, reducing CPU usage without requiring additional infrastructure or data duplication.

Answer

Increase the instance size.

Answer

Create a read replica for reporting.

Answer

Use query rewriting with materialized views.

Question 8

Which tool is best for identifying hot spots in a Cloud Spanner database?

Accepted Answer

Key Visualizer. Key Visualizer is the correct tool because it is specifically designed to visualize access patterns in Cloud Spanner and identify hot spots—keys or ranges that receive a disproportionate share of reads or writes. Unlike generic monitoring tools, Key Visualizer provides a heatmap of key-space activity, enabling you to pinpoint and mitigate performance bottlenecks caused by uneven distribution of workload across splits.

Answer

Query Insights

Answer

Cloud Trace

Answer

Cloud Monitoring

Question 9

A Cloud SQL instance is using InnoDB and has a large buffer pool. The query performance is slower after a failover. What is the most likely cause?

Accepted Answer

Buffer pool warm-up time. After a failover in Cloud SQL, the new primary instance starts with a cold buffer pool. InnoDB relies on the buffer pool to cache data and index pages in memory for fast queries. Since the buffer pool is empty after the failover, queries must read from disk until the cache warms up, causing significantly slower performance.

Answer

Read replica lag

Answer

Binary log not enabled

Answer

Data corruption

Question 10

Your organization uses Cloud SQL for MySQL as the backend for a content management system. The Operations team reports that the database performance degrades every weekday morning at 9 AM, coinciding with a batch job that updates thousands of rows. You need to minimize the impact on end users. What is the best approach?

Accepted Answer

Break the batch job into smaller transactions with a delay between batches.. Breaking the batch job into smaller transactions with a delay between batches reduces lock contention and transaction log pressure on the primary Cloud SQL instance. This prevents a single large transaction from blocking concurrent user queries, thereby minimizing performance degradation for end users during the batch window.

Answer

Increase the Cloud SQL instance memory and CPU before the job starts.

Answer

Disable binary logging during the batch window.

Answer

Move batch reads to a read replica.

Question 11

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 12

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 13

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 14

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 15

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