PCDOE Manage Database Solutions — 10 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.