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

Question 1

Your Cloud SQL for MySQL instance is experiencing unusually high disk usage. You need to identify the cause. Which metric should you monitor in Cloud Monitoring?

Accepted Answer

Binary log disk usage. Binary logs in MySQL store all data changes (e.g., INSERT, UPDATE, DELETE) and can consume significant disk space, especially under heavy write workloads or if retention is misconfigured. Monitoring 'Binary log disk usage' directly reveals whether accumulated binary logs are the primary cause of high disk usage, which is a common issue in Cloud SQL for MySQL.

Answer

InnoDB row reads

Answer

CPU utilization

Answer

Query latency

Question 2

You are running a Cloud Bigtable instance for time-series data ingestion. Write throughput has dropped significantly, and you see an increase in 'resource exhausted' errors. The table has one column family and one rowkey format: `#orgId#deviceId#timestamp`. After analyzing cluster metrics, you see that one node is handling most of the traffic. What is the most likely cause?

Accepted Answer

The rowkey design is causing hotspotting on one tablet node due to a prefix (orgId) being written heavily.. The rowkey design `#orgId#deviceId#timestamp` causes hotspotting because all writes for a given `orgId` are directed to a single tablet node. Cloud Bigtable partitions data by rowkey range, and sequential or heavily skewed prefixes (like `orgId`) concentrate write traffic on one node, leading to 'resource exhausted' errors and throughput degradation.

Answer

The column family design is creating too many columns.

Answer

The cluster does not have enough nodes.

Answer

The timestamp is too granular, causing many rows with the same timestamp.

Question 3

You have a Memorystore for Redis instance used as a session store. You notice that the instance is experiencing high eviction rates. What is the best first step to take?

Accepted Answer

Increase the instance size or set a TTL policy on session keys.. High eviction rates in Memorystore for Redis indicate that the instance is running out of memory and the Redis `maxmemory-policy` is actively removing keys. The best first step is to either increase the instance size to provide more memory or set a TTL (Time-To-Live) policy on session keys so that expired sessions are cleaned up proactively, reducing memory pressure and evictions.

Answer

Monitor memory usage but take no action.

Answer

Add a read replica to offload read traffic.

Answer

Enable persistence (AOF or RDB) to reduce memory usage.

Question 4

Which TWO metrics should you monitor in Cloud Monitoring to evaluate the performance of a Cloud Spanner instance? (Choose two.)

Accepted Answer

Commit latency. Commit latency is a critical metric for Cloud Spanner because it directly measures the time taken to commit a transaction, which reflects the database's ability to handle write operations efficiently. High commit latency can indicate contention, node overload, or suboptimal schema design, making it essential for performance evaluation.

Answer

Row reads per second

Answer

Connection count

Answer

Disk IOPS

Question 5

Which THREE are best practices for designing a Cloud Spanner schema for high performance? (Choose three.)

Accepted Answer

Avoid using a monotonically increasing primary key as the first part of the key.. A monotonically increasing primary key (e.g., an auto-increment integer or timestamp) creates a hot spot on the last tablet server, causing all writes to be serialized on a single split. Cloud Spanner distributes splits based on the primary key range; a sequential key forces all new inserts into the same split, leading to write contention and poor throughput. Using a hash prefix or a UUID-like key spreads writes evenly across splits, maximizing parallelism.

Option A is wrong because spreading data across multiple regions does not inherently reduce latency; it may increase latency due to geographic distance. Cloud Spanner automatically replicates data across regions for high availability, and best practices recommend placing instances close to users for lower latency.

Option C is wrong because denormalization is generally not recommended in Cloud Spanner. Instead, use interleaved tables to efficiently join parent-child tables without data redundancy. Denormalization can lead to increased storage and update anomalies.

Option D is correct because interleaved tables allow parent and child rows to be stored together, providing efficient access when querying by the parent key. This reduces the need for joins and improves performance.

Option E is correct because secondary indexes are necessary for efficient queries on non-key columns. Without an index, Cloud Spanner would need to perform a full table scan, which is slow for large tables.

Answer

Spread data across multiple regions to reduce latency.

Answer

Denormalize frequently joined tables into a single table.

Question 6

Which TWO actions can help reduce connection overhead in a Cloud SQL for MySQL instance? (Choose two.)

Accepted Answer

Enable automatic connection management in the application driver.. Enabling automatic connection management in the application driver (e.g., MySQL Connector/J with autoReconnect=true or HikariCP) allows the driver to reuse idle connections and transparently recover from broken connections, reducing the overhead of repeatedly establishing new TCP connections. Option C is correct because Cloud SQL Proxy or a connection pooler (like pgBouncer or ProxySQL) maintains a persistent pool of connections to the database, amortizing the cost of connection setup across multiple client requests and reducing the total number of concurrent connections to the instance.

Answer

Disable SSL encryption for database connections.

Answer

Increase the max_connections parameter.

Answer

Add a read replica to handle connect requests.

Question 7

You are using Cloud Memorystore for Redis as a caching layer. You notice that cache hit ratio is below 50%. What is the best action to improve it?

Accepted Answer

Increase the TTL (time-to-live) for cached data.. A low cache hit ratio indicates that a large proportion of requests are not finding their data in the cache, forcing the application to fetch from the primary database. Increasing the TTL (time-to-live) for cached data keeps valid entries in Redis longer, reducing the frequency of evictions and cache misses. This directly improves the hit ratio by ensuring that more requests can be served from the cache before the data expires.

Answer

Flush the cache periodically to remove stale data.

Answer

Enable persistence to avoid data loss.

Answer

Increase the instance memory size.

Question 8

A team notices that queries on a Cloud Spanner database are slow. They want to identify which queries are consuming the most resources. What should they use?

Accepted Answer

Query Insights. Query Insights is the correct tool because it is specifically designed for Cloud Spanner to analyze query performance, providing detailed metrics such as execution latency, CPU usage, and rows scanned per query. It helps identify the most resource-intensive queries by breaking down performance by query fingerprint, allowing the team to pinpoint and optimize slow queries directly.

Answer

Cloud Logging

Answer

Performance Dashboard

Answer

Cloud Monitoring metrics

Question 9

A company has a Cloud SQL for PostgreSQL instance that experiences high CPU usage during peak hours due to read-heavy queries. Which optimization is most effective for reducing CPU load?

Accepted Answer

Add read replicas. Adding read replicas offloads read-heavy queries from the primary Cloud SQL for PostgreSQL instance, distributing the query load and reducing CPU utilization on the primary. This is the most direct and effective optimization for read-heavy workloads because replicas handle SELECT traffic while the primary focuses on writes and critical operations.

Answer

Use connection pooling

Answer

Increase memory size

Answer

Enable automatic storage increase

Question 10

An e-commerce platform uses Cloud Bigtable for real-time user sessions. Write latency is high. On investigation, they find that rows are being written with monotonically increasing row keys (e.g., user_id + timestamp). What is the likely cause and solution?

Accepted Answer

Hotspotting on a single node; use salting or field promotion. Monotonically increasing row keys (e.g., user_id + timestamp) cause all writes to target a single tablet server, creating a hotspot. Cloud Bigtable distributes writes across nodes by row key range; sequential keys concentrate load on one node, degrading write latency. Salting (prepending a hash or random prefix) or field promotion (using a high-cardinality field as the first part of the key) spreads writes evenly across the cluster.

Answer

Too many column families; merge them

Answer

Inefficient reads; use reverse scan

Answer

Tablet splits are misconfigured; pre-split the table

Question 11

A data warehouse in BigQuery is running slower due to large full-table scans. Which feature can reduce the amount of data processed for common queries?

Accepted Answer

All of the above. Partitioning, clustering, and materialized views all reduce the amount of data scanned in BigQuery. Partitioning limits scans to specific date ranges, clustering sorts data within partitions to skip irrelevant blocks, and materialized views precompute and store query results so subsequent queries read only the pre-aggregated output instead of scanning the base table. Together, these features minimize full-table scans and improve query performance.

Answer

Clustering

Answer

Materialized Views

Answer

Partitioning

Question 12

A Cloud SQL for MySQL database has frequent table locks causing contention and slow queries. Which diagnostic approach helps identify the blocking queries?

Accepted Answer

Use INFORMATION_SCHEMA.INNODB_TRX. `INFORMATION_SCHEMA.INNODB_TRX` provides real-time data on all currently executing InnoDB transactions, including transaction IDs, state, and the waiting flag. By joining this with `INNODB_LOCK_WAITS` and `INNODB_LOCKS`, you can pinpoint which transaction is blocking others, directly addressing table lock contention in Cloud SQL for MySQL.

Answer

Set up a Cloud Monitoring alert on CPU

Answer

Use Query Insights

Answer

Enable slow query log

Question 13

A company runs a Cloud Spanner database with a multi-region configuration. They notice that write latency is higher than expected for clients in a region far from the leader region. What action should be taken to reduce write latency?

Accepted Answer

Change the default leader option to 'NEAREST'. Changing the default leader option to 'NEAREST' allows Cloud Spanner to dynamically assign the leader replica to the region closest to the majority of write requests, reducing the network round-trip time for clients far from the original leader region. This directly addresses the high write latency caused by geographic distance, as writes must be confirmed by the leader before committing.

Answer

Reduce the number of replicas

Answer

Use directed reads

Answer

Enable follower reads for writes

Question 14

Which Cloud Monitoring metric indicates the number of queries waiting for locks in Cloud SQL?

Accepted Answer

Lock waits. The 'Lock waits' metric in Cloud SQL (for MySQL, PostgreSQL, or SQL Server) directly tracks the number of queries that are blocked because they are waiting for a lock held by another transaction. This is the correct indicator of query contention, as it measures the count of statements currently in a lock-wait state, not the total queries or connections.

Answer

Active connections

Answer

CPU utilization

Answer

Queries

Question 15

A Cloud Memorystore for Redis instance used as a session store has a high eviction rate. Which configuration change can reduce evictions while maintaining performance?

Accepted Answer

Set maxmemory-policy to allkeys-lru. Setting `maxmemory-policy` to `allkeys-lru` allows Redis to evict the least recently used keys across all keys when memory is full, which directly reduces eviction rates by ensuring that only the least active session data is removed. This maintains performance by keeping frequently accessed session keys in memory, which is critical for a session store where active sessions are repeatedly read and written.

Answer

Enable persistence (RDB)

Answer

Increase number of replicas

Answer

Decrease timeout