PCDE Monitor and optimize database performance — Timed 20 Questions

Question 1

You are managing a Cloud SQL for PostgreSQL instance that is experiencing high CPU usage and slow query performance. You notice that the database has a high number of idle-in-transaction connections. Which immediate action should you take to reduce CPU load without disrupting active transactions?

Accepted Answer

Set the cloudsql.enable_idle_in_transaction_session_timeout flag to true and configure idle_in_transaction_session_timeout.. Option C is correct because setting the `cloudsql.enable_idle_in_transaction_session_timeout` flag to true and configuring `idle_in_transaction_session_timeout` allows Cloud SQL to automatically terminate idle-in-transaction connections after a specified timeout, reducing CPU load without manually killing connections or disrupting active transactions. This is a built-in, non-disruptive mechanism that targets only connections that are holding resources while idle, freeing up CPU and memory for active queries.

Answer

Use VPC firewall rules to block new connections until the issue resolves.

Answer

Kill all idle-in-transaction connections using pg_terminate_backend.

Answer

Set a statement_timeout at the session level for new connections.

Question 2

A team is deploying a new application on Google Kubernetes Engine (GKE) that uses Cloud Spanner. They want to minimize latency for read operations. Which Spanner configuration should they use?

Accepted Answer

Use a regional instance with read replicas in the same region.. Option B is correct because a regional instance with read replicas in the same region provides the lowest read latency for applications running in that region. Cloud Spanner's regional configuration keeps all data and replicas within a single Google Cloud region, minimizing network round-trips. Read replicas in the same region can serve strongly consistent reads without cross-region hops, which is optimal for latency-sensitive workloads.

Answer

Use a multi-region configuration with default leader preference set to the region where the application runs.

Answer

Use a single-region instance and configure the leader preference to the application's zone.

Answer

Use a single-region instance and enable read-only replicas in multiple zones.

Question 3

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.. Option D is correct because 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 4

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 5

A developer has deployed a new version of an application that uses Cloud SQL. After the deployment, you notice a sharp increase in the number of slow queries. What should you do first to identify the problematic queries?

Accepted Answer

Use Cloud SQL Query Insights to identify the queries with the highest latency and examine their execution plans.. Cloud SQL Query Insights is the recommended first step for diagnosing slow queries because it provides built-in query monitoring, latency breakdowns, and execution plans without additional configuration. It directly surfaces the queries with the highest latency, allowing you to examine their execution plans to identify root causes such as missing indexes or inefficient joins.

Answer

Check the slow query log in Cloud Logging and look for queries with high rows_examined.

Answer

Increase the instance tier to reduce the impact of slow queries.

Answer

Enable the general query log and parse the log file to find slow queries.

Question 6

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.. Option A is correct because 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 7

Which THREE factors should you consider when configuring Cloud SQL for MySQL query caching to optimize performance?

Accepted Answer

High write workloads can lead to frequent cache invalidation, reducing its effectiveness.. Option A is correct because in Cloud SQL for MySQL, high write workloads cause frequent updates to tables, which invalidates the query cache entries for those tables. This means the cache must be repopulated often, reducing the hit rate and potentially adding overhead from cache maintenance, making it less effective for performance optimization.

Answer

Query cache uses disk storage for cached results.

Answer

Prepared statements always bypass the query cache.

Question 8

Your Cloud SQL for PostgreSQL instance is experiencing intermittent slowdowns during peak hours. You notice that the CPU utilization spikes to 80% and the number of connections increases. The application team confirms they are not running any new queries. What should you do first to diagnose the issue?

Accepted Answer

Use Cloud SQL Insights to analyze query performance and wait statistics.. Cloud SQL Insights provides built-in query performance monitoring and wait statistics that can pinpoint the root cause of intermittent slowdowns without making changes. Since CPU spikes and increased connections are symptoms, not the cause, analyzing wait events (e.g., CPU, IO, lock contention) directly reveals which queries or resources are bottlenecked. This is the first diagnostic step before any scaling or configuration changes.

Answer

Increase the machine type of the Cloud SQL instance to add more CPU.

Answer

Enable connection pooling to reduce the number of connections.

Answer

Set a maximum connection limit and reduce the connection lifetime.

Question 9

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 10

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.

Question 11

You are running a Memorystore for Redis instance with a high write volume. You notice that the eviction rate is high and the cache hit ratio has dropped significantly. Which configuration change would most directly reduce the eviction rate?

Accepted Answer

Change maxmemory-policy to allkeys-lru.. Option D is correct because setting `maxmemory-policy` to `allkeys-lru` tells Redis to evict the least recently used keys from the entire keyspace when memory is full. This directly reduces the eviction rate by ensuring that only the least accessed data is removed, preserving the most frequently accessed data and improving the cache hit ratio. In a high-write-volume scenario, this policy adapts to access patterns and minimizes unnecessary evictions of hot data.

Answer

Use many small keys instead of a few large keys.

Answer

Decrease the maxmemory setting to force earlier eviction.

Answer

Set maxmemory-policy to noeviction.

Question 12

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?

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 13

You are troubleshooting a slow-performing query on Cloud Spanner. The query scans a large table with a secondary index. Which TWO metrics from the Query Insights dashboard would most directly indicate the source of the performance issue?

Accepted Answer

CPU time. CPU time (A) is correct because high CPU usage indicates that the query is performing expensive operations like sorting, joining, or complex filtering, which can slow performance even if the index is used. Rows scanned (B) is correct because scanning a large number of rows, even with a secondary index, suggests the index is not selective enough or the query is retrieving many rows, leading to excessive I/O and latency. Both metrics directly point to query execution inefficiency.

Answer

Storage utilization

Answer

Commit latency

Answer

Lock wait time

Question 14

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.. Option A is correct because 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 15

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.. Option A is correct because 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 16

Your Cloud SQL for PostgreSQL instance is experiencing high CPU utilization during peak hours. You notice that the query `SELECT * FROM orders WHERE order_date >= '2024-01-01'` is frequently run against a table with 10 million rows. The table has a B-tree index on `order_date`. What is the most likely cause of the high CPU usage, and how should you address it?

Accepted Answer

The query retrieves all columns, causing significant heap lookup overhead; rewrite the query to select only required columns.. Option C is correct because the query uses `SELECT *`, which forces PostgreSQL to fetch all columns from the heap (the main table storage) even though the index on `order_date` can efficiently locate the matching rows. This results in significant heap lookup (also known as bitmap heap scan or index scan with tuple retrieval) overhead, consuming CPU cycles for each row fetched. Reducing the selected columns to only those needed minimizes I/O and CPU usage by avoiding unnecessary data retrieval from the heap.

Answer

The index is fragmented; rebuild the index to improve performance.

Answer

Increase the instance machine type to provide more CPU capacity.

Answer

The index on `order_date` is not being used; add a hint to force index usage.

Question 17

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 18

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 19

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.. Option D is correct because 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 20

You are managing a Cloud SQL for MySQL instance that is experiencing high latency and connection timeouts during peak hours. The current configuration uses 4 vCPUs, 15 GB memory, and 100 GB SSD storage. The database workload is a mix of transactional queries and batch inserts. Which TWO actions would most effectively reduce latency and improve performance?

Accepted Answer

Increase the instance to 8 vCPUs and 30 GB memory.. Option C is correct because increasing vCPUs and memory directly addresses the resource bottleneck causing high latency and connection timeouts during peak hours. Cloud SQL for MySQL performance is heavily dependent on CPU for query processing and memory for buffer pool caching; doubling these resources reduces query execution time and improves concurrency handling.

Answer

Disable binary logging to reduce write I/O.

Answer

Increase the storage size to 200 GB to improve IOPS.

Answer

Decrease the max_connections parameter to reduce overhead.