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

Question 1

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

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

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 4

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 5

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 6

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

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?

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

Accepted Answer

Increase the instance to 8 vCPUs and 30 GB memory.. 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. Option E is correct because using Cloud SQL proxy with connection pooling reduces the overhead of establishing new connections, which is a common cause of latency and timeouts under high concurrency. Connection pooling reuses database connections, minimizing connection setup time and improving throughput. The other options are incorrect: A (disabling binary logging is not recommended for production as it compromises point-in-time recovery), B (increasing storage does not linearly improve IOPS beyond a baseline and does not address compute/memory bottleneck), and D (decreasing max_connections may cause more timeouts under peak load rather than reduce them).

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 8

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 9

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 10

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 11

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 12

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 13

A Cloud SQL for PostgreSQL instance is experiencing high CPU usage during peak hours. Query Insights shows that a complex reporting query is causing full table scans on a large table. The query filters on a column used in JOINs. Which optimization should be applied first?

Accepted Answer

Add a composite index on the filtered column and join columns.. Adding a composite index on the filtered column and the join columns directly addresses the root cause of the full table scans. Query Insights indicates the query filters on a column used in JOINs; a composite index covering both the filter and join columns allows PostgreSQL to perform an Index Scan instead of a sequential scan, reducing CPU usage without requiring additional infrastructure or data duplication.

Answer

Increase the instance size.

Answer

Create a read replica for reporting.

Answer

Use query rewriting with materialized views.

Question 14

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 15

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.