PCDE Monitor and optimize database performance — Timed 100 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?

100 minute time limit — choose an answer to begin.

Accepted Answer

Set the cloudsql.enable_idle_in_transaction_session_timeout flag to true and configure idle_in_transaction_session_timeout.. 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 single-region instance and configure the leader preference to the application's zone.. Using a single-region Cloud Spanner instance with leader preference set to the zone where the application runs ensures that the leader replica is in the same zone as the application. Strongly consistent reads are served from the leader, so this configuration minimizes read latency by avoiding cross-zone network round trips. Option B is incorrect because a regional instance does not have separate 'read replicas'; all replicas in a regional instance are read-write, and the leader is already in the same region, but specifying 'read replicas' is misleading and not a valid Spanner configuration. Multi-region options (A and D) introduce cross-region latency for strongly consistent reads.

Answer

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

Answer

Use a regional instance with read replicas in the same region.

Answer

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

Question 3

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 4

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

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 6

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

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 8

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

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 scaling the instance to 8 vCPUs and 30 GB memory directly addresses resource saturation during peak hours, giving the mixed transactional and batch workload more CPU and memory to process queries and reduce latency. Option E is correct because enabling the Cloud SQL Auth Proxy provides secure connectivity while connection pooling reuses existing database connections, which reduces the overhead and timeouts caused by repeatedly opening new connections during peak load. Option A is not appropriate because disabling binary logging removes point-in-time recovery and replication capabilities, and it does not address the primary CPU, memory, or connection bottlenecks. Option B is incorrect because increasing storage to 200 GB may raise IOPS limits, but the instance is already on SSD and the described symptoms point more to compute and connection pressure than storage throughput. Option D is incorrect because lowering max_connections would restrict concurrency and worsen connection timeouts rather than reduce latency.

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.

Question 10

Refer to the exhibit. You are analyzing a slow query in Cloud SQL for PostgreSQL. The execution plan shows a sequential scan. Which index should you create to most effectively improve query performance?

Accepted Answer

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);. The query likely filters on `user_id` and then sorts or filters by `created_at`. A composite index on `(user_id, created_at)` allows PostgreSQL to first narrow down by `user_id` using index seek, then efficiently access rows in `created_at` order, avoiding a sequential scan. This matches the most common pattern for slow queries involving equality on `user_id` and range or ordering on `created_at`.

Answer

CREATE INDEX idx_orders_partial ON orders(created_at) WHERE user_id = 123;

Answer

CREATE INDEX idx_orders_created_at ON orders(created_at);

Answer

CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);

Question 11

Order the steps to set up a Cloud Spanner instance with a global database.

Accepted Answer

Step 1: Create instance, Step 2: Create database, Step 3: Define schema, Step 4: Insert data, Step 5: Configure IAM. The correct sequence for setting up Cloud Spanner starts with creating the instance to provide compute and storage resources, then creating the database within that instance, followed by defining the schema (tables and indexes), inserting data, and finally configuring IAM to manage access. This logical progression ensures that each step builds on the previous one, avoiding dependency errors.

Answer

Step 1: Create instance, Step 2: Create database, Step 3: Configure IAM, Step 4: Define schema, Step 5: Insert data

Answer

Step 1: Create instance, Step 2: Define schema, Step 3: Create database, Step 4: Insert data, Step 5: Configure IAM

Answer

Step 1: Configure IAM, Step 2: Create instance, Step 3: Create database, Step 4: Define schema, Step 5: Insert data

Question 12

Order the steps to troubleshoot a connection timeout from an application to Cloud SQL.

Accepted Answer

Step 1: Check logs, Step 2: Verify configuration, Step 3: Check authorization, Step 4: Test connectivity, Step 5: Check instance status. Start with logs, then verify configuration, authorization, test connectivity, and check instance status.

Answer

Step 1: Check instance status, Step 2: Check logs, Step 3: Verify configuration, Step 4: Check authorization, Step 5: Test connectivity

Answer

Step 1: Test connectivity, Step 2: Check logs, Step 3: Verify configuration, Step 4: Check authorization, Step 5: Check instance status

Answer

Step 1: Verify configuration, Step 2: Check authorization, Step 3: Check logs, Step 4: Test connectivity, Step 5: Check instance status

Question 13

Match each BigQuery feature to its purpose.

Accepted Answer

Partitioning: Divides table into segments based on a column (e.g., date) to reduce query cost and improve performance.. These features optimize cost, performance, and management in BigQuery. Partitioning reduces scan costs, Clustering improves query performance, Materialized Views accelerate complex queries, and Slots provide resource control.

Answer

Partitioning: Sorts data based on one or more columns to improve query performance.

Answer

Clustering: Divides table into segments based on a column to reduce cost.

Question 14

Match each Cloud SQL backup type to its retention policy.

Accepted Answer

Automated backups: Retained for a user-specified number of days (default 7). Cloud SQL backup types: Automated backups have configurable retention (default 7 days). On-demand backups are retained until deleted (max 365 days). Point-in-time recovery logs are kept for the same period as automated backup retention. Common confusions involve swapping these policies.

Answer

Automated backups: Retained until deleted

Answer

On-demand backups: Retained for 7 days by default

Answer

Point-in-time recovery logs: Retained for 365 days regardless of backup retention

Question 15

A Cloud Spanner database is experiencing high latency for point reads. The table has a primary key of (CustomerID, OrderDate). Most reads are by CustomerID only. What should the engineer do?

Accepted Answer

Add a secondary index on CustomerID.. Point reads by CustomerID only are inefficient on the primary key (CustomerID, OrderDate) because Cloud Spanner requires the full primary key for direct lookup. Adding a secondary index on CustomerID allows Spanner to perform an index scan followed by a point read, drastically reducing latency for these queries.

Answer

Use interleaved tables.

Answer

Reorder primary key to (OrderDate, CustomerID).

Answer

Increase the number of nodes.

Question 16

A Bigtable instance stores time-series data with row keys formatted as `#deviceID#timestamp`. The application often queries recent data for a specific device. Monitoring shows high read latency when scanning multiple devices. The row key design is causing hotspotting. What is the best redesign?

Accepted Answer

Use a hash prefix before deviceID.. Prepending a hash prefix (e.g., a cryptographic hash of the deviceID) to the row key distributes writes and reads evenly across Bigtable tablets, eliminating hotspotting caused by sequential deviceID-based keys. This ensures that queries for recent data (which would otherwise concentrate on a single tablet) are spread across multiple nodes, reducing read latency.

Answer

Use separate tables per device.

Answer

Store timestamps in column qualifiers.

Answer

Prefix the row key with the deviceID and reverse the timestamp.

Question 17

A Firestore database is used for a social app. A collection of posts has indexes on fields `author` and `timestamp`. The query `where author == 'user1' order by timestamp desc limit 10` is performing a large number of document reads. What is the likely cause?

Accepted Answer

Missing composite index on (author, timestamp).. The query filters on `author` and orders by `timestamp`, which requires a composite index on `(author, timestamp)` to avoid a full scan. Without this composite index, Firestore must scan all documents matching `author == 'user1'` (or all posts if no single-field index on `author` is used) and then sort them in memory, leading to excessive document reads. The existing single-field indexes on `author` and `timestamp` are insufficient for this combined filter and sort operation.

Answer

The limit is too high.

Answer

The query is scanning all posts.

Answer

Index on timestamp is not descending.

Question 18

A Cloud SQL for MySQL instance is configured with 8 vCPUs and 30GB RAM. The Database Advisor suggests adding an index on a table, but after adding, write performance degrades. The table has 10 million rows and receives 500 writes per second. What is the most likely reason?

Accepted Answer

The index is on a highly selective column causing write amplification.. Adding an index on a highly selective column (e.g., a column with many unique values) forces MySQL to update the B-tree index structure on every write operation. With 500 writes per second and 10 million rows, each INSERT or UPDATE must maintain the index, causing write amplification that degrades write throughput. The 8 vCPUs and 30GB RAM are sufficient for the workload, but the index maintenance overhead becomes the bottleneck.

Answer

The instance needs more CPU.

Answer

The index is too large for memory.

Answer

The index is not used for reads.

Question 19

A Bigtable cluster has 10 nodes and is experiencing 90% CPU utilization, causing increased latency. The workload is mostly random reads (70%) and writes (30%). The table has 50TB of data, and the row key design is efficient. What is the best way to reduce CPU utilization?

Accepted Answer

Increase the number of nodes to 20.. Bigtable CPU utilization scales roughly linearly with the number of nodes for a given workload, so doubling the node count from 10 to 20 should roughly halve CPU utilization, bringing it well below the 90% threshold and reducing latency. Since the row key design is already efficient and the workload is mostly random reads, adding nodes is the most direct and effective way to add CPU capacity. This is the recommended first response to high CPU in Bigtable.

Answer

Add SSDs instead of HDDs.

Answer

Enable replication for read offloading.

Answer

Compact the table to reduce SSTable count.

Question 20

A Firestore database has a collection with a composite index on (status, timestamp desc). The query `where status == 'active' order by timestamp desc limit 50` is returning empty results even though there are active documents. What could be the issue?

Accepted Answer

The composite index exists but the order of timestamp is asc instead of desc.. The composite index on (status, timestamp desc) must match the query's order by direction. If the index was created with timestamp ascending instead of descending, the query `where status == 'active' order by timestamp desc` will not be served by that index and may return empty results because Firestore requires an index that exactly matches the query's filters and sort order. The correct index should have timestamp in descending order to match the query.

Answer

The status field is not indexed.

Answer

The documents have 'active' status but timestamp field is missing.

Answer

The query requires an index on timestamp only.