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

Question 1

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. 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 2

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. Cloud Monitoring provides metrics and dashboards to track database performance indicators like CPU utilization, memory usage, disk I/O, and query latency, helping identify resource bottlenecks. Query Insights offers detailed query-level diagnostics, including execution plans, lock contention, and slow query analysis, directly pinpointing problematic SQL statements in Cloud SQL for PostgreSQL.

Answer

Cloud Logging with error reporting

Answer

Cloud Profiler

Answer

Cloud Trace

Question 3

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

Accepted Answer

Enable follower reads. Follower reads allow read requests to be served by any replica in a Cloud Spanner multi-region configuration, not just the leader replica. This reduces read latency by enabling reads to be processed from a replica that is geographically closer to the client, without incurring the round-trip time to the leader. Follower reads are particularly effective for globally distributed applications where read consistency can be slightly relaxed.

Answer

Use a multi-region configuration with a default leader

Answer

Use database partitioning

Answer

Increase the number of nodes

Question 4

Which THREE metrics from Cloud Monitoring are important for monitoring Cloud Bigtable performance?

Accepted Answer

CPU utilization. CPU utilization (option B) is a critical metric for Cloud Bigtable because it directly reflects the processing load on the cluster's nodes. High CPU utilization indicates that the cluster is approaching its throughput limits, which can lead to increased latency and throttling. Monitoring this metric helps in scaling decisions, such as adding nodes or optimizing queries, to maintain performance.

Answer

Storage utilization

Answer

Disk usage

Question 5

Based on the exhibit from Cloud Spanner Query Insights, what is the most likely performance issue?

Accepted Answer

No index on customer_id. The exhibit shows a query with a filter on `customer_id` that is not indexed, forcing Cloud Spanner to perform a full table scan to find matching rows. This is the most likely performance issue, as indicated by high latency and high row scan counts in Query Insights, which directly points to a missing index on the filtered column.

Answer

High network latency

Answer

Full table scan

Answer

Inefficient join

Question 6

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 7

You are monitoring a Cloud SQL for PostgreSQL instance and notice that the CPU utilization is consistently above 90% during peak hours. What is the most cost-effective initial action to reduce CPU load?

Accepted Answer

Add a read replica to offload read traffic. Adding a read replica is the most cost-effective initial action because it offloads read queries from the primary instance, reducing CPU contention without incurring the cost of upgrading the entire instance. Cloud SQL for PostgreSQL replicas use native streaming replication, so they handle read traffic while the primary focuses on writes, directly addressing high CPU utilization from read-heavy workloads.

Answer

Remove unnecessary indexes to reduce write overhead

Answer

Reduce the max_connections parameter to limit concurrent users

Answer

Increase the machine type (e.g., go to a higher vCPU count)

Question 8

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 9

A Cloud SQL for MySQL instance is experiencing increased replica lag. The write workload is constant. What is the most likely cause?

Accepted Answer

A long-running query is executing on the replica. A long-running query on the replica can block the SQL thread from applying relay log events, causing replica lag to increase even if the primary's write workload is constant. This is because replication is single-threaded by default in MySQL, so one slow query stalls all subsequent events until it completes.

Answer

Binary logs are being deleted too frequently on the primary

Answer

The query cache is enabled on the primary

Answer

The replica is on a higher machine tier than the primary

Question 10

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 11

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 12

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 13

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 14

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 15

Your Bigtable cluster is experiencing high CPU utilization and write latency during a daily batch job that writes 500 GB of data. The job uses bulk mutations with 1000 rows per request. What is the most effective way to reduce CPU usage?

Accepted Answer

Increase the number of rows per mutation to 10,000. Larger mutations reduce overhead per row. Option A is wrong because disabling write-ahead logs does not significantly reduce CPU and risks data integrity. Option C is wrong because increasing nodes does not reduce CPU per request. Option D is wrong because creating a new cluster in a different zone does not directly reduce CPU usage for writes.

Answer

Disable write-ahead logs

Answer

Increase the number of nodes temporarily

Answer

Create a new cluster in a different zone