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

Question 1

What is the most likely cause of the high execution time?

Accepted Answer

Missing index on status column. A missing index on the status column is the most likely cause of high execution time because the query likely filters on status, and without an index, the database must perform a full table scan. This is a common performance bottleneck in relational databases. Adding an index on the status column would allow the database to quickly locate matching rows, drastically reducing execution time.

Answer

The WHERE clause uses a string comparison

Answer

The query selects two columns causing inefficiency

Answer

The instance has insufficient nodes

Answer

The query does not filter on primary key

Question 2

Which TWO actions would help optimize a Cloud SQL for PostgreSQL database experiencing high read latency?

Accepted Answer

Add indexes on frequently queried columns. Option B is correct because adding indexes on frequently queried columns lets PostgreSQL satisfy read queries via index scans instead of full table scans, directly reducing read latency for those workloads. Option C is correct because increasing the database tier machine type provides more vCPU and memory, which raises the instance's query-processing throughput and cache capacity, reducing read latency under load. Option A is not correct because read replicas primarily scale read throughput and offload the primary; they do not reduce the latency of an individual read query on a given instance. Option D is not correct because automatic storage increase only prevents out-of-space failures and does not improve read performance. Option E is not correct because pgBouncer connection pooling reduces connection overhead and contention, not the latency of read queries themselves.

Answer

Increase the number of read replicas

Answer

Configure automatic storage increase

Answer

Use pgBouncer connection pooling

Question 3

Your company runs a large e-commerce application on Google Cloud using Cloud SQL for MySQL (version 8.0) with 2 TB of data. The database experiences intermittent performance degradation during peak hours (10am-2pm). Cloud Monitoring shows a spike in CPU utilization to 90% and increased query latency. The database has been running for 6 months with default settings. You notice many slow queries like "SELECT * FROM orders WHERE customer_id=12345 ORDER BY order_date DESC LIMIT 10" that take 5-10 seconds. The orders table has 50 million rows, customer_id has a B-tree index, and order_date is not indexed. The query execution plan indicates a full table scan and a filesort. What is the most effective course of action to resolve the performance issue?

Accepted Answer

Add a composite index on (customer_id, order_date). The slow query uses a WHERE clause on customer_id (which is indexed) and an ORDER BY on order_date (not indexed). The index on customer_id alone is insufficient because the query still requires sorting, leading to a filesort. Adding a composite index on (customer_id, order_date) allows the database to retrieve rows for a specific customer in sorted order without a full scan or filesort. Option B (increasing memory) may help but does not address the root cause. Option C (read replicas) offloads read traffic but does not fix the query plan. Option D (partitioning) might help with data management but is not as direct or efficient as adding the appropriate index.

Answer

Create multiple read replicas to offload read traffic

Answer

Partition the orders table by month using range partitioning

Answer

Increase the memory size of the Cloud SQL instance to 30 GB

Question 4

A Cloud Bigtable cluster is used for a time-series workload. The cluster has 6 nodes and is experiencing high read latency during peak hours. You notice that the CPU utilization is consistently above 80%. You want to reduce read latency while maintaining cost efficiency. What should you do?

Accepted Answer

Increase the number of nodes in the Bigtable cluster.. For a Cloud Bigtable cluster, high CPU utilization directly impacts read latency. The most effective way to reduce latency is to add more nodes, which increases the cluster's processing capacity. Other options like enabling SSD (already default), changing row keys (not the issue here), or using instance types (not applicable) do not address the CPU bottleneck.

Answer

Enable SSD storage for the Bigtable cluster.

Answer

Use a larger instance type for the Bigtable cluster.

Answer

Change the row key design to distribute reads more evenly.

Question 5

A Cloud SQL for PostgreSQL instance is serving an OLTP application. During peak hours, the database experiences high CPU utilization and increased query latency. You observe that many queries are filtering on a column that has no index. You want to quickly identify the top SQL statements contributing to the high CPU. Which Cloud SQL feature should you use?

Accepted Answer

Enable and query the pg_stat_statements extension.. pg_stat_statements is the built-in PostgreSQL extension that aggregates execution statistics for all SQL statements. By querying it, you can sort by total execution time or CPU time to find the most resource-intensive queries. This directly addresses the need to identify top SQL statements causing high CPU, enabling targeted optimization such as adding indexes.

Answer

Use Cloud Monitoring to view the CPU utilization metric.

Answer

Run EXPLAIN ANALYZE on all queries in the pg_stat_activity view.

Answer

Enable the Cloud SQL Query Insights feature.

Question 6

You manage a Cloud SQL for PostgreSQL instance. Users report that a specific report query is slow. The query filters on a column with a highly skewed distribution: one value appears in 90% of rows. The query uses that common value. The column has a B-tree index. The query plan shows a sequential scan. What is the most likely reason for the sequential scan, and what should you do?

Accepted Answer

The index is not being used because the query planner estimates that a sequential scan is cheaper due to the high selectivity of the filter. You should update the statistics and consider using a partial index or a different index type.. The query planner chooses a sequential scan because the filter on a common value is not selective; reading most of the table via index lookups would be slower. Updating statistics and considering a partial index for rare values can improve performance for queries that filter on selective values, but for the common value, a sequential scan is appropriate.

Answer

The query planner is not using the index because the table is too small. You should add more rows to make the index beneficial.

Answer

The index is corrupted and needs to be rebuilt with REINDEX.

Answer

The index is not being used because the column has a high number of NULL values. You should add a partial index that excludes NULLs.

Question 7

You manage a Cloud Spanner instance that supports a globally distributed application. Users in Europe report that read queries on a small set of rows take 200-300 ms, while users in the US see 20-30 ms for the same queries. The database schema uses a monotonically increasing primary key (e.g., a timestamp-based UUID) for the main table. You want to reduce read latency for European users without changing the application code. What should you do?

Accepted Answer

Add a read-only replica in the Europe region and configure the application to use a read-only transaction with a staleness of 15 seconds.. The high latency for European users stems from the data being stored in the US region, where the leader replica resides. By adding a read-only replica in Europe and using read-only transactions with bounded staleness, reads can be served locally, reducing latency. This approach leverages Spanner's built-in replication without altering the schema or application logic significantly.

Answer

Use a SQL query hint to force the read to be served from a replica in the Europe region.

Answer

Create a secondary index on the primary key column and query using that index.

Answer

Change the primary key to a randomly distributed value, such as a UUID, to spread data across all regions.

Question 8

You manage a Cloud Bigtable instance used for time-series IoT sensor data. The table's row key is the sensor ID followed by a timestamp. You notice that reads for the most recent data are fast, but range scans over the last 30 days are slow and CPU utilization is high. What should you do to improve read performance?

Accepted Answer

Use a composite row key with a coarse time bucket followed by sensor ID and timestamp.. The current row key design stores all data for a single sensor contiguously, so a range scan over 30 days for one sensor reads a large contiguous block from one node, causing hotspots and slow performance. Reordering the row key to lead with a time bucket distributes the scan across multiple nodes, enabling parallel reads and reducing per-node CPU load. This is a standard Bigtable schema design best practice for time-series data.

Answer

Pre-split the table into multiple regions based on the sensor ID prefix.

Answer

Change the row key to a salted hash of the sensor ID concatenated with the timestamp.

Answer

Reverse the timestamp portion of the row key so that the most recent data is written first.

Question 9

You are managing a Cloud Spanner instance that serves a global e-commerce application. Users in multiple regions report increased read latency during peak shopping hours. You have already optimized schema design and secondary indexes. You want to reduce read latency further. Which TWO actions should you take? (Choose two.)

Accepted Answer

Set the instance to a multi-region configuration with a read-write leader in the primary region and read-only replicas in other regions.. Staleness reads and a multi-region configuration with read-only replicas both reduce read latency by enabling reads to be served from nearby replicas. Staleness reads avoid contacting the leader for every read, while multi-region configurations place replicas closer to users. Together, they significantly cut down network round-trip time, which is crucial for global applications.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Enable Data Boost for the instance.

Answer

Configure a read-only replica in each region.

Question 10

Your team runs a Cloud SQL for MySQL 8.0 instance (db-n1-standard-8) that supports an online order management application. During the daily peak window, the instance's CPU utilization stays near 95% and the application reports slow INSERT and UPDATE statements. You examine Query Insights and see the top wait event is 'io/table/sql/handler' with high average latency on writes, while the buffer pool hit ratio remains above 99%. You must reduce write latency without changing the application schema. What should you do first?

Accepted Answer

Increase the number of vCPUs on the instance by changing the machine type to db-n1-highmem-16 or a larger vCPU tier.. The instance is CPU-bound: utilization is near saturation and the top wait event is on the write path, while the buffer pool is already effective. Adding vCPUs expands execution capacity for write threads and reduces queueing, which lowers write latency. Durability relaxation, memory increases, and zone moves do not address the actual bottleneck and carry risk or downtime without benefit.

Answer

Move the instance to a different zone within the same region to reduce write latency caused by zone-level storage contention.

Answer

Enable innodb_flush_log_at_trx_commit=2 to relax durability of the redo log during peak hours.

Answer

Increase the size of the InnoDB buffer pool by setting innodb_buffer_pool_size to 70-80% of the instance memory.

Question 11

A Cloud Spanner instance is experiencing high read latency for queries that perform large scans. You want to optimize these queries to reduce latency. Which two strategies should you use? (Choose two.)

Accepted Answer

Add a secondary index to cover the query's filter and projection columns.. To optimize large scan queries in Cloud Spanner, you can add a secondary index that covers the query's filter and projection columns, allowing the query to be satisfied entirely from the index. Additionally, partitioning the table by a frequently filtered column enables partition pruning, reducing the data scanned. These two strategies directly address the volume of data processed and can significantly reduce read latency.

Answer

Use a covering index that includes all columns referenced in the query.

Answer

Use a query hint to force the use of a specific index.

Answer

Increase the number of nodes in the Spanner instance.

Question 12

You are monitoring a Cloud SQL for PostgreSQL instance that hosts a web application. Users report slow response times during peak hours. You check the Cloud SQL metrics and notice that CPU utilization is consistently above 90%. You want to quickly identify the top SQL statements contributing to this high CPU usage. What should you do?

Accepted Answer

Enable the pg_stat_statements extension and query it to find the most time-consuming statements.. The pg_stat_statements extension is designed to track SQL execution statistics, making it the ideal tool to identify top CPU-consuming statements. It provides cumulative metrics such as total execution time and call count, allowing you to pinpoint queries that need optimization. This is a built-in PostgreSQL feature that can be enabled in Cloud SQL, and it directly addresses the need to find problematic SQL.

Answer

Use Cloud Monitoring to view the CPU utilization metric and correlate it with the number of connections.

Answer

Increase the instance size to add more vCPUs to handle the load.

Answer

Enable query insights in Cloud SQL and view the query execution plans.

Question 13

A Cloud Bigtable table is experiencing high read latency for scans that retrieve a range of rows. The table has a row key that starts with a timestamp, causing sequential writes and reads to be concentrated on a single node. You want to improve read performance for these range scans. What should you do?

Accepted Answer

Change the row key to include a hash prefix before the timestamp.. A timestamp-prefixed row key causes all new data to be written to the last split, creating a hotspot. By adding a hash prefix, writes are distributed across many splits, and range scans can be parallelized across those splits. This reduces the load on any single node and improves read performance for scans that cover a time range.

Answer

Enable a replication policy for the Bigtable cluster.

Answer

Increase the number of nodes in the Bigtable cluster.

Answer

Pre-split the table at regular intervals based on the timestamp.

Question 14

You have a Cloud SQL for MySQL instance. You want to monitor the performance of a specific query that is executed frequently. Which feature should you use to identify the query's execution plan and performance characteristics?

Accepted Answer

MySQL Performance Schema. The MySQL Performance Schema provides detailed, low-level instrumentation of query execution, including wait events and stage information. It allows you to analyze a specific query's performance characteristics and execution plan. While other tools like Query Insights, Slow Query Log, and Cloud Monitoring dashboards are useful for monitoring, they do not offer the same depth for a single query's execution plan.

Answer

Cloud SQL Query Insights

Answer

MySQL Slow Query Log

Answer

Cloud Monitoring custom dashboards

Question 15

Your Cloud SQL for PostgreSQL instance experiences periodic spikes in commit latency every 5 minutes, each lasting about 10 seconds. You have enabled Query Insights with a 1-second sampling interval. The spikes correlate with an increase in the 'checkpoint write time' metric. What is the most likely cause?

Accepted Answer

The checkpoint interval is set too low, causing frequent checkpoints that flush dirty buffers and increase write latency.. PostgreSQL triggers checkpoints at intervals set by checkpoint_timeout. When the interval is short, each checkpoint flushes many dirty pages, causing a burst of write I/O that delays commits. The 5-minute periodicity aligns with the default checkpoint interval, and the checkpoint write time metric confirms the cause. Adjusting checkpoint_timeout or increasing checkpoint_completion_target can smooth out the I/O.

Answer

The Cloud SQL instance is experiencing a network partition, causing delayed acknowledgments from replicas.

Answer

The PostgreSQL autovacuum process is running too frequently, causing I/O contention.

Answer

The database is running out of disk space, causing the write-ahead log (WAL) to be flushed more aggressively.