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

Question 1

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?

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

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 2

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 3

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 4

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 5

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.. Increasing the number of nodes distributes the load and reduces CPU per node, directly addressing the high utilization. Adding SSDs or compaction may help marginally but not as effectively as adding nodes.

Answer

Add SSDs instead of HDDs.

Answer

Enable replication for read offloading.

Answer

Compact the table to reduce SSTable count.

Question 6

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.. If the composite index has timestamp in ascending order, the order by desc cannot use the index efficiently, leading to empty results due to scanning in wrong order.

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.

Question 7

A Cloud Spanner database contains the Orders table as defined above. The query `SELECT * FROM Orders WHERE CustomerID=123` takes a long time. What is the most likely reason?

Accepted Answer

The table needs a secondary index on CustomerID.. The query `SELECT * FROM Orders WHERE CustomerID=123` filters on the `CustomerID` column, but the primary key of the Orders table is likely defined on a different column (e.g., `OrderID`). Without a secondary index on `CustomerID`, Cloud Spanner must perform a full table scan to find matching rows, which is slow for large tables. Creating a secondary index on `CustomerID` allows Cloud Spanner to directly locate the relevant splits and rows, dramatically reducing latency.

Answer

The ORDER BY clause is missing.

Answer

Interleaving causes extra I/O.

Answer

The primary key is not optimized for this query.

Question 8

A slow query log entry shows the above for a Cloud SQL for MySQL instance. Which index would most improve performance?

Accepted Answer

Composite index on orders(product_id, order_date). The query likely filters or joins on product_id and then sorts or filters by order_date, so a composite index on orders(product_id, order_date) allows the database to satisfy both conditions with a single index scan, avoiding a filesort or extra lookups. In MySQL, a composite index with the most selective column first (product_id) followed by the range/order column (order_date) is optimal for queries that filter on product_id and then order or filter by order_date.

Answer

Index on products(product_id)

Answer

Index on orders(order_date)

Answer

Composite index on orders(order_date, product_id)

Question 9

A Cloud SQL for PostgreSQL instance is experiencing high replication lag between primary and read replica. Which TWO actions would reduce the lag?

Accepted Answer

Increase the replica's machine size.. Increasing the replica's machine size (Option B) reduces replication lag by providing more CPU and memory resources to apply WAL changes faster. Placing the replica in the same zone as the primary (Option C) minimizes network latency between the instances, which directly reduces the time for WAL data to travel from primary to replica, thus lowering lag.

Answer

Reduce the value of `max_wal_size`.

Answer

Use a higher number of read replicas.

Answer

Enable synchronous replication.

Question 10

A Cloud Spanner database has a table with a primary key (UserId, Timestamp). Queries that filter by Timestamp range for a specific UserId are fast, but queries that filter only by Timestamp range across all users are slow. Which TWO improvements would help?

Accepted Answer

Use a leading column of the primary key that supports range scans.. In Cloud Spanner, the primary key order determines how data is physically sorted and stored. By making Timestamp the leading column of the primary key (e.g., (Timestamp, UserId)), range scans on Timestamp become efficient as Spanner can perform a contiguous scan of the sorted data. This directly addresses the slow queries that filter only by Timestamp range across all users.

Answer

Use a hash prefix on UserId.

Answer

Create an interleaved table structure.

Answer

Partition the table by Timestamp.

Question 11

A Bigtable instance is running out of storage and performance is degraded. The schema design is known to be efficient. Which THREE actions can help?

Accepted Answer

Delete unused column families.. Deleting unused column families is correct because each column family in Bigtable stores data in separate SSTable files, and unused families consume storage and memory resources without providing value. Removing them frees up space and reduces the amount of data that must be scanned during reads and compactions, directly improving performance.

Answer

Reduce the number of tablets.

Answer

Add SSDs.

Question 12

Your application uses Cloud SQL for PostgreSQL. You notice that the database CPU utilization has been consistently above 90% during peak hours, causing increased query latency. You have already tuned the most expensive queries. What is the most cost-effective next step?

Accepted Answer

Implement connection pooling via Cloud SQL Proxy or a dedicated pooler like PgBouncer.. The question states that expensive queries have already been tuned, yet CPU remains high. This indicates that the database is spending excessive CPU on handling connection overhead (forking processes, parsing sessions) rather than query execution. Connection pooling with PgBouncer reduces the number of active connections, allowing PostgreSQL to reuse backend processes and significantly lower CPU usage without scaling hardware.

Answer

Enable high availability with a failover replica.

Answer

Increase the machine type to a higher CPU tier.

Answer

Add a read replica to offload read queries.

Question 13

You are managing a Cloud Spanner instance that supports a global e-commerce application. Queries that join two large tables (Orders and OrderItems) have high latency. The tables use the CustomerID as the primary key prefix. The join condition is on OrderID, which is the second part of the primary key in both tables. What should you do to improve performance?

Accepted Answer

Change the primary key of both tables to start with OrderID, then CustomerID.. By making OrderID the first part of the primary key, the join becomes a local join within the same split, reducing cross-node communication. Option A is wrong because increasing nodes may not fix the join strategy and costs more. Option B is wrong because secondary indexes do not help with joins across tables. Option D is wrong because interleaving tables requires a parent-child relationship by primary key, which is not the case here (OrderID is not a prefix of the parent's primary key).

Answer

Increase the number of nodes to handle the load.

Answer

Create a secondary index on OrderID in both tables.

Answer

Recreate the tables as interleaved tables with Orders as parent.

Question 14

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 15

You are designing a Cloud Spanner schema for a global social media application. The application reads the most recent 100 posts for a user's timeline. The Posts table has a primary key of (UserId, PostTimestamp DESC). You observe that queries for the timeline are hitting high read latency and the transaction abort rate is increasing. What is the most likely cause?

Accepted Answer

The descending timestamp in the primary key is causing hotspotting on the tablet leader for the most recent data.. The descending timestamp in the primary key causes all writes for the most recent posts to be concentrated on the same tablet leader, because Cloud Spanner uses the first primary key column for splitting and distributing data. This hotspotting leads to high read latency and increased transaction abort rates as the single leader becomes overloaded with both writes and reads for the latest data.

Answer

The instance has too few nodes to handle the write volume.

Answer

The lack of a secondary index on UserId is forcing full table scans.

Answer

The table should be interleaved under a Users table.