PCDOE Manage Database Solutions Practice Test 11 — 15 Questions

Question 1

A company runs an e-commerce platform on Cloud SQL for PostgreSQL. They need to perform point-in-time recovery (PITR) to recover from a user error that occurred 30 minutes ago. Which configuration is required to enable PITR?

Accepted Answer

Enable automated backups and configure a retention of 1-7 days; PITR uses WAL archiving automatically.

Answer

Enable binary logging and set a backup retention of 1-7 days.

Answer

Set up cross-region backup replicas and configure a retention of 1-7 days.

Answer

Enable PITR by setting the 'pitr_enabled' flag to true in the database flags.

Question 2

A Cloud Spanner database is experiencing increased read traffic. You need to add an index to improve query performance without downtime. What is the correct approach?

Accepted Answer

Use the CREATE INDEX statement. Spanner creates indexes online without blocking writes or reads.

Answer

Export the database, create the index in a new instance, then import.

Answer

Use the ALTER TABLE statement to add the index as a constraint.

Answer

Create a secondary index using the 'CREATE INDEX WITH OFFLINE' option to avoid blocking.

Question 3

You are monitoring Cloud Bigtable replication lag. Which metric should you use to determine if replicas are up to date, and what consistency level is typical for Bigtable replication?

Accepted Answer

Use 'replication_lag' metric; consistency is eventually consistent.

Answer

Use 'cluster_lag' metric; consistency is read-your-writes consistent.

Answer

Use 'replica_lag' metric; consistency is strongly consistent.

Answer

Use 'replication_lag' metric; consistency is strongly consistent.

Question 4

A Memorystore for Redis instance is running out of memory. The application uses Redis as a cache with key expiration. You want to prevent data loss for keys that have not reached their TTL. Which eviction policy should you configure?

Accepted Answer

volatile-lru

Answer

noeviction

Answer

volatile-ttl

Answer

allkeys-lru

Question 5

A company has a Firestore database in Native mode. They need to run a query that filters on two fields (status and date) and orders by date. The query is slow and returns an error that a matching index is missing. What must the engineer do to resolve this?

Accepted Answer

Create a composite index on status and date in the Firebase Console or using gcloud.. Option C is correct because Firestore requires a composite index on both the equality filter field (status) and the order field (date) when a query uses equality filters on one field and an order on another. Without this composite index, the query cannot be executed efficiently and returns an error. Creating the composite index via the Firebase Console or gcloud CLI resolves the issue.

Answer

Enable single-field indexes for both fields; Firestore will automatically use them.

Answer

Rewrite the query using 'IN' clauses to avoid the need for a composite index.

Answer

Change the database to Datastore mode, which does not require indexes.

Question 6

Your Cloud SQL for MySQL instance is running low on storage. You need to increase the storage size without downtime. What should you do?

Accepted Answer

Edit the instance and increase the storage size; it will be applied online.. Option D is correct because Cloud SQL for MySQL supports online storage increases. When you edit the instance and increase the storage allocation, the operation is applied without requiring a restart or causing downtime. The underlying infrastructure uses live migration and dynamic volume expansion to make the additional capacity available immediately while the instance continues serving traffic.

Answer

You cannot increase storage online; you must create a new instance.

Answer

Create a clone of the instance with larger storage and switch traffic.

Answer

Enable automatic storage increase and it will happen automatically.

Question 7

A team is designing a disaster recovery strategy for Cloud SQL. They need to be able to recover the database in a different region with a Recovery Point Objective (RPO) of less than 30 minutes. What should they configure?

Accepted Answer

Create a cross-region read replica and promote it to standalone during a disaster.

Answer

Use on-demand backups every 30 minutes and store them in a multi-regional bucket.

Answer

Enable binary logging and configure a cross-region replica for PITR.

Answer

Create automated backups with a retention of 7 days and restore to a new instance in the desired region.

Question 8

A Bigtable instance is experiencing high latency and uneven load distribution. The operations team suspects a hot spot. Which tool should they use to identify the hot spot, and what action can they take to mitigate it?

Accepted Answer

Use Key Visualiser to identify the hot row/key; then redesign the row key to distribute load.. Key Visualizer is the correct tool for identifying hot spots in Cloud Bigtable because it provides a heatmap of row key access patterns, allowing you to pinpoint specific rows or key ranges causing uneven load. Once identified, redesigning the row key (e.g., by salting or reversing keys) distributes writes and reads across nodes, mitigating the hot spot without requiring manual table splits or storage changes.

Answer

Use 'gcloud bigtable hot-spots list' command; then split the hot table into multiple tables.

Answer

Use Cloud Monitoring to check CPU utilisation; then add more nodes to the cluster.

Answer

Use the Bigtable admin console to view table statistics; then change the storage type from HDD to SSD.

Question 9

Your Memorystore for Redis instance needs to survive a zonal failure with minimal data loss. Which feature should you use?

Accepted Answer

Use a Standard tier instance with replication across zones within the same region.. Option B is correct because a Standard tier Memorystore for Redis instance with replication across zones provides automatic failover to a replica in a different zone, ensuring high availability and minimal data loss during a zonal failure. The Standard tier uses synchronous replication within the region, so data written to the primary is replicated to the cross-zone replica before acknowledging the write, which minimizes data loss to only in-flight transactions that were not yet committed.

Answer

Use a Basic tier instance and schedule periodic exports to Cloud Storage.

Answer

Enable persistence with RDB snapshots stored in Cloud Storage.

Answer

Create a cross-region replica (read replica in another region).

Question 10

A Cloud SQL for PostgreSQL read replica is experiencing replication lag of several minutes. You need to reduce the lag. What is the most effective solution?

Accepted Answer

Increase the CPU and memory of the read replica to speed up applying changes.. Option C is correct because replication lag in Cloud SQL for PostgreSQL read replicas is often caused by the replica being unable to keep up with the rate of write-ahead log (WAL) apply from the primary. Increasing the CPU and memory of the read replica directly improves its ability to replay WAL records faster, reducing the lag. This is the most effective solution as it addresses the bottleneck at the replica side without impacting the primary instance.

Answer

Disable automated backups on the primary to free up resources.

Answer

Promote the replica to a standalone instance and recreate the replica.

Answer

Increase the CPU and memory of the primary instance.

Question 11

You are building a global application that requires strong consistency across regions. The application needs to support SQL queries and horizontal scaling. Which database service should you choose?

Accepted Answer

Cloud Spanner

Answer

Cloud SQL with cross-region replicas

Answer

Cloud Bigtable

Answer

Firestore

Question 12

Which TWO actions are required to set up point-in-time recovery (PITR) for Cloud SQL for MySQL? (Choose 2)

Accepted Answer

Enable binary logging.

Answer

Set the 'log_bin' flag to ON in the database flags.

Answer

Create a read replica in a different region.

Answer

Configure a cross-region backup replica.

Question 13

Which TWO statements about Cloud Spanner schema changes are correct? (Choose 2)

Accepted Answer

Schema changes are applied asynchronously and may take minutes to hours to complete.

Answer

Schema changes require the instance to be restarted.

Answer

You can only add columns, not drop them.

Answer

Creating a secondary index requires taking the table offline.

Question 14

Which THREE metrics should you set up alerts for to proactively monitor the health of a Cloud SQL for MySQL instance? (Choose 3)

Accepted Answer

Replication lag (for instances with replicas). Replication lag is a critical metric for Cloud SQL for MySQL instances with replicas because it measures the delay between the primary instance and its read replicas. High replication lag can lead to stale reads and data inconsistency, and if it exceeds certain thresholds, it may indicate network issues, high write load, or insufficient replica resources. Proactive alerts on replication lag help ensure data freshness and application reliability.

Answer

Number of queries per second

Answer

Network throughput

Question 15

A Memorystore for Redis instance needs to be scaled to handle increased traffic. Which TWO methods can you use? (Choose 2)

Accepted Answer

Vertically scale by changing the tier to a larger machine type.

Answer

Increase the maxmemory parameter.

Answer

Change the eviction policy to allkeys-lru.

Answer

Add read replicas in the same zone.