PCDOE Manage Database Solutions — Timed 100 Questions

Question 1

Your team manages a Cloud SQL for MySQL instance used by a critical application. You need to ensure the instance is recoverable to any point within the last 4 days, with a Recovery Point Objective (RPO) of under 5 minutes. What configuration steps are required?

Accepted Answer

Enable automated backups and binary logging. Set the transaction log retention period to 4 days.. Point-in-time recovery (PITR) in Cloud SQL for MySQL requires binary logging to be enabled, which automatically turns on automated backups. The retention period for binary logs is set via the transactionLogRetentionDays setting, which can be 1-7 days. Enabling automated backups alone does not provide PITR; binary logging is necessary. The retention period of 4 days satisfies the 4-day recovery window. Automated backups are already required for PITR, so option B is the complete answer.

Answer

Enable automated backups and set the backup retention to 4 days. Binary logging is not required because automated backups already capture all changes.

Answer

Enable binary logging and set the binary log retention to 4 days. Automated backups are optional and not needed for PITR.

Answer

Create an on-demand backup daily and set binary log retention to 4 days. This provides the same RPO as automated backups with binary logging.

Question 2

You are responsible for a Cloud Spanner instance that serves a global user base. A new feature requires adding an index on a column of an existing table that contains millions of rows. The table is actively used by production traffic. What is the recommended approach to add the index with minimal impact?

Accepted Answer

Use the gcloud command 'gcloud spanner databases ddl update' with a CREATE INDEX statement. The operation will be non-blocking and will not impact production traffic.. Cloud Spanner supports online, non-blocking index creation. The CREATE INDEX statement processes the index in the background and does not block reads or writes on the table. This is the most efficient and least impactful method. Using a backup/restore or export/import is unnecessarily complex and disruptive.

Answer

Create a second table with the index already defined, then copy the data using batch writes and switch traffic. This avoids any impact to the original table.

Answer

Take a full database backup to Cloud Storage, restore it to a new instance, create the index on the restored instance, and then fail over to the new instance.

Answer

Use the gcloud command 'gcloud spanner databases ddl update' with an ALTER TABLE statement to add the index. This will lock the table briefly but is acceptable.

Question 3

Your Cloud Bigtable instance is experiencing high latency for certain row key ranges. You suspect a hotspot is developing. Which Google Cloud tool should you use to visualize and diagnose the hotspot?

Accepted Answer

Key Visualiser. Key Visualiser is a tool specifically designed for Bigtable to identify hotspots and access patterns by visualizing row key distribution and traffic. It helps pinpoint uneven load distribution.

Answer

Cloud Monitoring dashboard to check CPU utilization per node

Answer

Bigtable's built-in 'hotspot detection' alert in Cloud Monitoring

Answer

Cloud Logging to review request logs and identify slow queries

Question 4

You manage a Memorystore for Redis instance that is regularly hitting its maximum memory limit. The application can tolerate some data loss and prefers to keep the most recently used data. Which eviction policy should you configure?

Accepted Answer

volatile-lru. volatile-lru evicts keys with an expiry set (volatile keys) using an LRU (least recently used) algorithm. This fits the requirement: the application can tolerate loss of expired data, and it prefers to keep recently used data. allkeys-lru would evict any key, which might include important unexpired keys.

Answer

noeviction

Answer

volatile-ttl

Answer

allkeys-lru

Question 5

Your Firestore database in Native mode is used by a mobile app. You need to query a collection where documents are filtered by two fields: 'status' (string) and 'createdAt' (timestamp). The query is not performing as expected. What action is required?

Accepted Answer

Create a composite index on the 'status' and 'createdAt' fields.. Firestore in Native mode requires a composite index to efficiently query documents filtered by multiple fields, such as 'status' and 'createdAt'. Without this index, the query may fail or perform poorly, as Firestore cannot combine separate single-field indexes for equality and range filters. Option C is correct because creating a composite index on both fields enables the query to run as expected.

Answer

Create an index exemption for the collection to allow multi-field queries.

Answer

Add a third field to the query to make it more specific.

Answer

Ensure that single-field indexes exist for both 'status' and 'createdAt'.

Question 6

You need to set up disaster recovery for a Cloud SQL for PostgreSQL instance. The primary instance is in us-central1, and you want a standby in us-west1 that can be promoted to a standalone instance during a regional outage. The solution must minimize data loss and recovery time. Which approach should you take?

Accepted Answer

Create a cross-region read replica in us-west1. During a disaster, promote the replica to a standalone instance.. Cross-region read replicas in Cloud SQL for PostgreSQL use synchronous replication (for regional replicas, it's asynchronous across regions, but still the best option for DR). Creating a cross-region read replica in us-west1 allows it to be promoted to a standalone instance in DR scenarios. Cross-region backups are point-in-time backups, not a live standby. External replication is not managed by Cloud SQL.

Answer

Configure cross-region automated backups with a retention of 7 days. In the event of a disaster, restore the latest backup to a new instance in us-west1.

Answer

Set up an on-premise PostgreSQL instance and configure streaming replication from Cloud SQL to the on-premise instance.

Answer

Use gcloud sql instances create with a backup configuration pointing to us-west1, but this does not create a live standby.

Question 7

A Cloud SQL for MySQL instance is running low on disk space. You have enabled automatic storage increase, but you also want to be proactively alerted when disk usage exceeds 80%. Which steps should you take?

Accepted Answer

Create a Cloud Monitoring alert on the metric 'disk/utilization' with a threshold of 80%.. Cloud Monitoring allows you to create alerting policies based on metrics. The disk usage metric for Cloud SQL is available, and you can set a threshold of 80%. Automatic storage increase is a separate setting that automatically increases storage when needed.

Answer

Set the storage size to a fixed large value to avoid alerts.

Answer

Create a Cloud Logging sink for disk usage logs and set up a Pub/Sub notification.

Answer

Use Cloud SQL's built-in email notifications for disk usage.

Question 8

Your Memorystore for Redis instance is used as a session store for a web application. You need to ensure that session data is not lost during a node failure. What should you do?

Accepted Answer

Configure the instance as Standard Tier (with replication) and schedule periodic exports to Cloud Storage.. Memorystore for Redis does not provide native persistence. To achieve high availability and durability, you can use a cross-region replica (Standard Tier with replication) or take regular snapshots to Cloud Storage. The Standard Tier with replication provides in-memory replication across zones; in case of a node failure, the replica can take over. However, data is still not persisted to disk. For durability, you need to export data to Cloud Storage periodically.

Answer

Use a Basic Tier instance with a large maxmemory setting.

Answer

Enable persistence by setting the 'persistence' parameter to 'rdb' in the instance configuration.

Answer

Enable AOF persistence in the Memorystore instance.

Question 9

You have a Cloud Spanner database that needs to be migrated from one region to another. You want to ensure no data loss and minimal downtime. Which approach should you use?

Accepted Answer

Create a backup of the database and restore it to a new instance in the target region.. Option B is correct because Cloud Spanner does not support in-place region changes or read replicas in different regions. The only supported method to migrate a Cloud Spanner database between regions with no data loss and minimal downtime is to create a backup of the database and restore it to a new instance in the target region. This approach ensures a consistent snapshot of the data and allows you to plan the cutover window to minimize downtime.

Answer

Use gcloud spanner instances move to change the region of the existing instance.

Answer

Create a read replica in the target region and promote it after replication catches up.

Answer

Use gcloud command 'gcloud spanner databases export' to export the database to CSV files, then import into the new instance.

Question 10

Your Cloud Bigtable instance uses HDD storage. You need to change to SSD to improve read performance. What is the correct procedure?

Accepted Answer

Create a new cluster with SSD storage in the same instance, replicate data, then delete the old cluster.. Bigtable does not support converting storage type in place. To change from HDD to SSD, you must create a new cluster with SSD storage, then replicate or re-ingest data. The simplest method is to use a backup and restore, or use Dataflow to copy data. Directly creating a new cluster and enabling replication can also work.

Answer

Use Cloud Bigtable's online storage migration feature to switch to SSD without downtime.

Answer

Update the instance's storage type using 'gcloud bigtable instances update' with the --storage-type flag.

Answer

Export the HDD cluster's data to Cloud Storage, then import into a new SSD cluster.

Question 11

You need to monitor the replication lag between a Cloud SQL for MySQL primary instance and its read replica. Which metric should you use to set up an alert?

Accepted Answer

cloudsql.googleapis.com/database/replication/replication_lag. Cloud SQL exposes a metric 'replication/replication_lag' (or 'cloudsql.googleapis.com/database/replication/replication_lag') that measures the lag in seconds between the primary and replica. This is the correct metric for alerting.

Answer

cloudsql.googleapis.com/database/replication/replica_count

Answer

cloudsql.googleapis.com/database/cpu/utilization

Answer

cloudsql.googleapis.com/database/memory/utilization

Question 12

Your Firestore database in Native mode contains a collection with millions of documents. You need to query documents where the 'tags' field (an array) contains the string 'urgent'. What must you ensure in your index configuration?

Accepted Answer

No additional index configuration is required; Firestore automatically creates the necessary index for array fields.. Firestore automatically creates a single-field index for array fields. However, to query array-contains, you need an index on the field with the array-contains filter. Firestore automatically creates a single-field index for the 'tags' field, so no additional index is needed. If the query includes additional equality clauses, a composite index may be required, but for a simple array-contains, it's automatic.

Answer

Ensure the 'tags' field is not indexed, and then create a composite index with the array-contains filter.

Answer

Create an index exemption for the 'tags' field to enable array queries.

Answer

Create a composite index on 'tags' and '__name__' for the collection.

Question 13

Your company runs a global e-commerce platform on Cloud Spanner. You need to capture real-time changes to a table (e.g., orders) and stream them to a downstream analytics pipeline in near real-time. Which TWO Google Cloud features or services should you combine to achieve this? (Choose two.)

Accepted Answer

Dataflow streaming pipeline. Option B is correct because a Dataflow streaming pipeline can consume Cloud Spanner change streams in near real-time, process the data (e.g., transform, filter, aggregate), and write it to a downstream analytics system. Dataflow provides exactly-once processing semantics and auto-scaling, making it ideal for continuous change data capture (CDC) pipelines.

Answer

Cloud Functions

Answer

Cloud Pub/Sub

Answer

Cloud SQL for PostgreSQL

Question 14

You have a Memorystore for Redis instance used as a cache. You need to scale it to handle increased load. The current instance is Basic Tier with 2 GB memory. Which TWO actions can you take to scale the instance? (Choose two.)

Accepted Answer

Vertically scale the instance by increasing its memory size or changing to a higher-tier (e.g., Standard Tier).. Memorystore supports vertical scaling by changing the tier (e.g., from Basic to Standard, or increasing memory size) and horizontal scaling using Redis Cluster (which shards data across multiple nodes). Scaling up within the same tier by increasing memory is also possible, but option A (vertical scaling) and option B (Redis Cluster) are the two general scaling methods.

Answer

Create additional instances and implement client-side sharding.

Answer

Change the eviction policy to noeviction to allow more data.

Answer

Add read replicas to offload read traffic.

Question 15

You are designing a disaster recovery plan for a Cloud Bigtable instance. The instance has a single cluster in us-east1. You need to ensure that if the cluster becomes unavailable, the database can still serve read and write requests with minimal downtime. Which THREE steps should you take? (Choose three.)

Accepted Answer

Ensure the application can handle eventual consistency between clusters.. Option A is correct because Cloud Bigtable uses eventual consistency for replication across clusters. When you add a second cluster and enable replication, data is replicated asynchronously, meaning writes to one cluster are not immediately visible in the other. The application must be designed to handle eventual consistency to avoid reading stale data after a failover.

Answer

Enable synchronous replication between clusters.

Answer

Take a full backup of the table to Cloud Storage daily.

Question 16

A company uses Cloud SQL for MySQL to run an e-commerce application. They need to ensure that they can recover the database to any point within the last 4 days. They also want to minimize storage costs. Which configuration should they use?

Accepted Answer

Enable binary logging with the 'log_bin' flag and set the transaction log retention to 4 days in the backup configuration.. Point-in-time recovery (PITR) in Cloud SQL for MySQL requires binary logging, which can be enabled with the 'log_bin' flag. The transaction log retention period can be set from 1 to 7 days. For 4-day recovery, set the retention to 4 days. Option C is correct: enable binary logging and set retention to 4 days. Option A is wrong because automated backups alone only restore to backup time, not any point. Option B is wrong because disabling binary logs prevents PITR. Option D is wrong because the retention is set via the backup configuration, not a separate flag.

Answer

Enable automated backups with a 4-day retention schedule.

Answer

Enable binary logging and set the transaction log retention to 4 days. Disable automated backups.

Answer

Enable point-in-time recovery by setting the 'point_in_time_recovery' flag to true with a 4-day window.

Question 17

A Cloud SQL for PostgreSQL instance is experiencing high read traffic. You need to offload read queries and ensure the solution can survive a regional outage. What should you do?

Accepted Answer

Create a cross-region read replica and direct read traffic to it.. Cross-region read replicas serve read traffic and provide disaster recovery if the primary fails. Creating a cross-region read replica reduces read load and can be promoted if the primary region fails. Option D is correct. Option A (same-region replica) does not survive a regional outage. Option B (increasing tier) helps performance but not regional failover. Option C (cache) reduces latency but not a full recovery solution.

Answer

Set up Cloud Memorystore as a cache in front of the database.

Answer

Increase the machine type of the primary instance to handle the load.

Answer

Create a read replica in the same region and use it for read queries.

Question 18

A development team uses Cloud Spanner and needs to add a new index to a table with 10 TB of data. They require that the operation does not block writes or cause downtime. Which approach should they take?

Accepted Answer

Execute 'CREATE INDEX' statement; Spanner will build the index in the background without blocking.. In Cloud Spanner, CREATE INDEX is a non-blocking operation. It can be executed online while the database remains available for reads and writes. Option A is correct. Option B (batch updates) is unnecessary. Option C (maintenance window) is incorrect because Spanner handles online schema changes. Option D (drop and recreate) causes downtime.

Answer

Create a new table with the index and batch copy data from the original table.

Answer

Schedule a maintenance window, then create the index during the window.

Answer

Drop the table and recreate it with the index, then restore from backup.

Question 19

A company is using Cloud Firestore to store user profiles. They need to query users by both their 'age' and 'city' fields. What must they configure to perform this query efficiently?

Accepted Answer

Create a composite index on age and city.. Firestore automatically creates single-field indexes for simple queries. However, for queries filtering on multiple fields like 'age' and 'city', a composite index must be created manually. Option B is correct. Option A (single-field index) is insufficient. Option C (index exemptions) is for arrays and maps, not for multi-field queries. Option D (Datastore mode) does not change this requirement.

Answer

Use Firestore in Datastore mode for automatic composite indexes.

Answer

Configure index exemptions for the fields.

Answer

No configuration needed; single-field indexes are sufficient.

Question 20

A Cloud SQL for MySQL instance is running out of storage. The team wants to increase storage automatically without manual intervention. However, they also want to control costs and avoid a sudden increase in storage beyond 1 TB. What should they do?

Accepted Answer

Enable auto-storage increase and set the maximum storage size to 1 TB.. Cloud SQL offers 'auto-storage increase' feature that automatically adds storage in increments when usage is high. To cap the growth, set the maximum storage size. Option C is correct. Option A (automatic increase without cap) could exceed 1 TB. Option B (manual resize) is not automatic. Option D (increase disk size) is manual and not automatic.

Answer

Set up a Cloud Function to monitor disk usage and resize the disk when needed.

Answer

Manually increase disk size by 10% each time usage reaches 80%.

Answer

Enable auto-storage increase; it automatically stops at 1 TB.