Courseiva

CCNA Db Performance Questions

27 of 102 questions · Page 2/2 · Db Performance topic · Answers revealed

Practice these questionsExam hubAll questions
76
MCQeasy

A team is deploying a new application on Google Kubernetes Engine (GKE) that uses Cloud Spanner. They want to minimize latency for read operations. Which Spanner configuration should they use?

A.Use a multi-region configuration with default leader preference set to the region where the application runs.
B.Use a regional instance with read replicas in the same region.
C.Use a single-region instance and configure the leader preference to the application's zone.
D.Use a single-region instance and enable read-only replicas in multiple zones.
AnswerC

A single-region instance with leader preference set to the application's zone ensures the leader is local, minimizing read latency.

Why this answer

Using a single-region Cloud Spanner instance with leader preference set to the zone where the application runs ensures that the leader replica is in the same zone as the application. Strongly consistent reads are served from the leader, so this configuration minimizes read latency by avoiding cross-zone network round trips. Option B is incorrect because a regional instance does not have separate 'read replicas'; all replicas in a regional instance are read-write, and the leader is already in the same region, but specifying 'read replicas' is misleading and not a valid Spanner configuration.

Multi-region options (A and D) introduce cross-region latency for strongly consistent reads.

Exam trap

Google Cloud often tests the misconception that multi-region configurations with leader preference reduce read latency, when in fact leader preference only affects write commit latency, not read latency, and multi-region setups inherently add cross-region latency for reads.

How to eliminate wrong answers

Option A is wrong because multi-region configurations introduce cross-region replication and quorum overhead, which increases read latency compared to a regional setup, even with leader preference set to the application's region. Option C is wrong because a single-region instance with leader preference set to a zone does not add read replicas; leader preference only affects write latency and transaction commit, not read latency. Option D is wrong because read-only replicas in multiple zones within a single-region instance do not reduce read latency for the application; they are used for failover and disaster recovery, not for serving reads with lower latency.

Practice this question →
77
MCQeasy

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?

A.Increase the instance size or set a TTL policy on session keys.
B.Monitor memory usage but take no action.
C.Add a read replica to offload read traffic.
D.Enable persistence (AOF or RDB) to reduce memory usage.
AnswerA

More memory or key expiration reduces evictions.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that persistence (AOF/RDB) frees memory, but persistence only affects durability, not memory usage, and candidates may confuse read replicas as a solution for memory pressure rather than read throughput.

How to eliminate wrong answers

Option B is wrong because monitoring without action does not resolve the high eviction rate, which can degrade session store performance and cause data loss. Option C is wrong because adding a read replica does not increase the primary instance's memory capacity or reduce evictions; replicas are for read scaling and high availability, not for alleviating memory pressure on the primary. Option D is wrong because enabling persistence (AOF or RDB) does not reduce memory usage; it writes data to disk but the dataset still resides in memory, so evictions will continue at the same rate.

Practice this question →
78
MCQmedium

A Cloud SQL instance is using InnoDB and has a large buffer pool. The query performance is slower after a failover. What is the most likely cause?

A.Read replica lag
B.Buffer pool warm-up time
C.Binary log not enabled
D.Data corruption
AnswerB

The new instance starts with an empty buffer pool, so queries initially incur higher I/O.

Why this answer

After a failover in Cloud SQL, the new primary instance starts with a cold buffer pool. InnoDB relies on the buffer pool to cache data and index pages in memory for fast queries. Since the buffer pool is empty after the failover, queries must read from disk until the cache warms up, causing significantly slower performance.

Exam trap

Google Cloud often tests the misconception that failover performance issues are due to replication lag or binary log settings, when the real cause is the cold buffer pool requiring disk reads until it warms up.

How to eliminate wrong answers

Option A is wrong because read replica lag affects replicas, not the primary instance after a failover; the failover promotes a replica to primary, and lag would have been caught before promotion. Option C is wrong because binary log is used for replication and point-in-time recovery, not for query performance; disabling it would not cause post-failover slowdown. Option D is wrong because data corruption would cause errors or crashes, not a gradual performance degradation; Cloud SQL automatically checks for corruption during failover.

Practice this question →
79
MCQeasy

Refer to the exhibit. What is the most effective optimization for this query?

A.Increase the instance memory to 30 GB
B.Create a composite index on (status, order_date)
C.Remove the WHERE clause and fetch all rows in application
D.Partition the orders table by month
AnswerB

Index allows efficient range scan and filter.

Why this answer

The query filters on `status` and `order_date`, so a composite index on `(status, order_date)` allows the database to perform an index seek on the equality predicate (`status`) and then a range scan on the ordered column (`order_date`), avoiding a full table scan. This is the most effective optimization because it directly supports the WHERE clause with minimal I/O and no sorting overhead.

Exam trap

Google Cloud often tests the misconception that partitioning alone improves query performance, but without a supporting index, partitioning only reduces the scan scope to a subset of partitions and does not eliminate the need for a full scan within those partitions.

How to eliminate wrong answers

Option A is wrong because increasing instance memory to 30 GB does not address the lack of an appropriate index; it may reduce buffer pool misses but cannot eliminate the need for a full table scan on a large table. Option C is wrong because removing the WHERE clause and fetching all rows in the application would transfer massive amounts of data over the network and force client-side filtering, which is far less efficient than letting the database engine use an index. Option D is wrong because partitioning the table by month does not automatically create an index on `status` and `order_date`; while partition pruning might help, without a proper index the query would still scan all rows in the relevant partitions.

Practice this question →
80
MCQmedium

A data analytics team runs ad-hoc queries on BigQuery that often exceed their slot capacity, causing queuing. They want to ensure predictable performance for their critical dashboard while still allowing ad-hoc queries. What is the most cost-effective solution?

A.Create a separate BigQuery reservation for the dashboard with a fixed number of slots, and let ad-hoc queries use on-demand pricing.
B.Switch all queries to on-demand pricing; the dashboard will automatically get priority.
C.Use a single reservation with a baseline of slots for the dashboard (top priority), and allow ad-hoc queries to use idle slots.
D.Move the data to a different BigQuery region with more slot availability.
AnswerC

A baseline guarantees slots for the dashboard, and idle slots are available for ad-hoc queries.

Why this answer

Setting a baseline number of slots for the dashboard guarantees resources, while allowing idle slots to be used by ad-hoc queries. Adding a reservation for only the dashboard with a separate project would waste slots; converting to on-demand is unpredictable; changing the BQ location does not affect slots.

Practice this question →
81
MCQmedium

You are responsible for a Cloud SQL for MySQL instance that supports a content management system (CMS). The application frequently performs SELECT queries with ORDER BY and LIMIT. Recently, the response time for these queries has increased. The database has 4 vCPUs and 15 GB memory. You check the slow query log and find many queries that are taking over 1 second. The 'rows_examined' is much higher than 'rows_sent'. The EXPLAIN plan shows 'Using filesort' and 'Using temporary'. There is currently an index on the column used in the WHERE clause but not on the ORDER BY columns. The table has 5 million rows. What should you do to improve query performance?

A.Increase the buffer pool size to 80% of memory.
B.Disable the query cache to reduce overhead.
C.Remove the ORDER BY clause and sort the results in application code.
D.Add a composite index on the columns used in the WHERE clause and the ORDER BY clause.
AnswerD

A covering index eliminates sorting and temporary table usage.

Why this answer

Adding a composite index on the columns used in the WHERE clause and the ORDER BY clause allows MySQL to avoid the expensive 'Using filesort' and 'Using temporary' operations. With a covering index, the database can retrieve rows in the required order directly from the index, eliminating the need to sort the result set after filtering. This dramatically reduces 'rows_examined' and improves query response time for SELECT queries with ORDER BY and LIMIT.

Exam trap

Google Cloud often tests the misconception that adding more memory or disabling features like the query cache can solve performance issues, when the real problem is a missing or poorly designed index that forces filesort and temporary tables.

How to eliminate wrong answers

Option A is wrong because increasing the buffer pool size (InnoDB buffer pool) does not address the root cause of filesort and temporary table usage; it only caches more data in memory, which may reduce disk I/O but does not eliminate the sorting overhead. Option B is wrong because disabling the query cache (which is deprecated in MySQL 8.0 and removed in 8.0+) does not affect queries that perform sorting; the query cache is only useful for identical SELECT statements and does not help with ORDER BY performance. Option C is wrong because removing the ORDER BY clause and sorting in application code shifts the sorting burden to the application server, which may still be inefficient and does not reduce the number of rows examined by the database; it also breaks the semantics of the query if the application relies on database-side ordering for pagination or consistency.

Practice this question →
82
MCQmedium

You are running a production workload on Cloud Bigtable and notice that read latency has increased. Upon reviewing the monitoring dashboard, you see that CPU utilization is below 50% but the number of active tablets is high. What is the most likely cause of the increased read latency?

A.Read requests are being throttled due to exceeding IOPS limits.
B.There are too many tablets, causing increased metadata operations and slower reads.
C.A hot node is throttling read requests.
D.The cluster is underprovisioned, causing resource contention.
AnswerB

Excessive tablets increase the overhead of metadata lookups and tablet splitting, leading to higher latency.

Why this answer

In Cloud Bigtable, each tablet is a contiguous range of rows managed by a tablet server. When the number of active tablets is high, the tablet server must perform more metadata operations (e.g., splitting, merging, and serving multiple tablets) which increases per-request overhead and can degrade read latency. This is true even when CPU utilization is below 50%, because the overhead is not purely CPU-bound but involves increased I/O and coordination.

Exam trap

Google Cloud often tests the misconception that high tablet count is always beneficial for parallelism, when in fact it can degrade performance due to metadata overhead, especially when CPU is not the bottleneck.

How to eliminate wrong answers

Option A is wrong because Cloud Bigtable does not enforce a hard IOPS limit; it scales with the number of nodes, and throttling would typically manifest as increased error rates or retries, not simply increased latency with low CPU. Option C is wrong because a hot node would cause high CPU utilization on that node, not low overall CPU, and throttling would be localized to that node's requests. Option D is wrong because underprovisioning would lead to high CPU utilization and resource contention across the cluster, not low CPU with a high tablet count.

Practice this question →
83
MCQmedium

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?

A.Increase the instance size.
B.Create a read replica for reporting.
C.Use query rewriting with materialized views.
D.Add a composite index on the filtered column and join columns.
AnswerD

Adding an index on the filtered and join columns allows the query to use index seek instead of full table scan, reducing CPU usage.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that scaling up or offloading is the first optimization step, when in fact index tuning is the cheapest and most effective initial action for query performance issues caused by full table scans.

How to eliminate wrong answers

Option A is wrong because increasing the instance size (vertical scaling) only masks the symptom of high CPU usage without fixing the inefficient query plan; the full table scans will continue to consume resources, and costs increase without performance guarantee. Option B is wrong because creating a read replica for reporting offloads the query to another instance but does not eliminate the full table scan on the replica; the same inefficient query will still cause high CPU on the replica. Option C is wrong because query rewriting with materialized views pre-computes and stores the result set, which can improve performance for repeated complex queries, but it does not address the immediate full table scan caused by missing indexes; materialized views also require maintenance and may become stale.

Practice this question →
84
MCQhard

Your company runs a large e-commerce application on Google Cloud using Cloud SQL for MySQL (version 8.0) with 2 TB of data. The database experiences intermittent performance degradation during peak hours (10am-2pm). Cloud Monitoring shows a spike in CPU utilization to 90% and increased query latency. The database has been running for 6 months with default settings. You notice many slow queries like "SELECT * FROM orders WHERE customer_id=12345 ORDER BY order_date DESC LIMIT 10" that take 5-10 seconds. The orders table has 50 million rows, customer_id has a B-tree index, and order_date is not indexed. The query execution plan indicates a full table scan and a filesort. What is the most effective course of action to resolve the performance issue?

A.Add a composite index on (customer_id, order_date)
B.Create multiple read replicas to offload read traffic
C.Partition the orders table by month using range partitioning
D.Increase the memory size of the Cloud SQL instance to 30 GB
AnswerA

A composite index on both columns enables the query to use index for filtering and sorting, eliminating the full table scan and filesort.

Why this answer

The slow query uses a WHERE clause on customer_id (which is indexed) and an ORDER BY on order_date (not indexed). The index on customer_id alone is insufficient because the query still requires sorting, leading to a filesort. Adding a composite index on (customer_id, order_date) allows the database to retrieve rows for a specific customer in sorted order without a full scan or filesort.

Option B (increasing memory) may help but does not address the root cause. Option C (read replicas) offloads read traffic but does not fix the query plan. Option D (partitioning) might help with data management but is not as direct or efficient as adding the appropriate index.

Practice this question →
85
MCQeasy

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

A.Lock waits
B.Active connections
C.CPU utilization
D.Queries
AnswerA

This metric measures the number of queries waiting for locks.

Why this answer

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.

Exam trap

The trap here is that candidates confuse 'Queries' (total throughput) with 'Lock waits' (blocked queries), assuming that a high query count implies lock contention, when in fact lock waits are a specific subset of queries that are actively waiting for locks.

How to eliminate wrong answers

Option B is wrong because 'Active connections' shows the total number of open connections to the database, not queries waiting for locks; a high active connection count does not necessarily indicate lock contention. Option C is wrong because 'CPU utilization' measures processor usage, which may be high due to many reasons (e.g., heavy queries, indexing issues) but does not specifically indicate queries waiting for locks. Option D is wrong because 'Queries' typically refers to the total number of queries executed per second, not the subset of queries that are blocked waiting for locks.

Practice this question →
86
MCQhard

A Bigtable cluster has 10 nodes and is experiencing 90% CPU utilization, causing increased latency. The workload is mostly random reads (70%) and writes (30%). The table has 50TB of data, and the row key design is efficient. What is the best way to reduce CPU utilization?

A.Increase the number of nodes to 20.
B.Add SSDs instead of HDDs.
C.Enable replication for read offloading.
D.Compact the table to reduce SSTable count.
AnswerA

Adding nodes increases total throughput and reduces per-node CPU, alleviating the bottleneck.

Why this answer

Increasing the number of nodes distributes the load and reduces CPU per node, directly addressing the high utilization. Adding SSDs or compaction may help marginally but not as effectively as adding nodes.

Practice this question →
87
Drag & Dropmedium

Order the steps to troubleshoot a connection timeout from an application to Cloud SQL.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Start with logs, then verify configuration, authorization, test connectivity, and check instance status.

Practice this question →
88
MCQmedium

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

A.Set up a Cloud Monitoring alert on CPU
B.Use Query Insights
C.Enable slow query log
D.Use INFORMATION_SCHEMA.INNODB_TRX
AnswerD

This table shows transaction details including lock waits and blockers.

Why this answer

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

Exam trap

The trap here is that candidates confuse performance monitoring tools (Query Insights, slow query log) with transaction-level diagnostics, failing to recognize that only InnoDB metadata tables expose the blocking transaction chain.

How to eliminate wrong answers

Option A is wrong because a CPU alert monitors resource utilization, not locking or blocking queries; high CPU may be a symptom but does not identify the specific blocking transaction. Option B is wrong because Query Insights in Cloud SQL provides query performance metrics and execution plans, but it does not expose InnoDB transaction lock wait information or the blocking transaction ID. Option C is wrong because the slow query log captures queries that exceed a time threshold, but it does not show which queries are currently blocked or blocking; it may miss short-lived blocking queries entirely.

Practice this question →
89
MCQmedium

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?

A.Enable persistence (RDB)
B.Increase number of replicas
C.Decrease timeout
D.Set maxmemory-policy to allkeys-lru
AnswerD

This evicts least recently used keys, ideal for session data.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that increasing replicas or enabling persistence can solve memory pressure issues, when in fact only adjusting the eviction policy or increasing `maxmemory` directly addresses evictions.

How to eliminate wrong answers

Option A is wrong because enabling persistence (RDB) does not reduce evictions; it creates point-in-time snapshots of data to disk, which consumes CPU and I/O resources without affecting the memory eviction policy. Option B is wrong because increasing the number of replicas does not reduce evictions on the primary instance; replicas are read-only copies that do not increase the primary's memory capacity or change its eviction behavior. Option C is wrong because decreasing the timeout (i.e., reducing the TTL for keys) would cause keys to expire sooner, potentially increasing evictions as more keys are removed by expiration, not reducing them.

Practice this question →
90
MCQhard

You are managing a Cloud Spanner instance that supports a global e-commerce application. Queries that join two large tables (Orders and OrderItems) have high latency. The tables use the CustomerID as the primary key prefix. The join condition is on OrderID, which is the second part of the primary key in both tables. What should you do to improve performance?

A.Increase the number of nodes to handle the load.
B.Create a secondary index on OrderID in both tables.
C.Change the primary key of both tables to start with OrderID, then CustomerID.
D.Recreate the tables as interleaved tables with Orders as parent.
AnswerC

This ensures that related rows for the same order are co-located, allowing local joins.

Why this answer

By making OrderID the first part of the primary key, the join becomes a local join within the same split, reducing cross-node communication. Option A is wrong because increasing nodes may not fix the join strategy and costs more. Option B is wrong because secondary indexes do not help with joins across tables.

Option D is wrong because interleaving tables requires a parent-child relationship by primary key, which is not the case here (OrderID is not a prefix of the parent's primary key).

Practice this question →
91
MCQeasy

Your organization uses Cloud SQL for MySQL as the backend for a content management system. The Operations team reports that the database performance degrades every weekday morning at 9 AM, coinciding with a batch job that updates thousands of rows. You need to minimize the impact on end users. What is the best approach?

A.Increase the Cloud SQL instance memory and CPU before the job starts.
B.Break the batch job into smaller transactions with a delay between batches.
C.Disable binary logging during the batch window.
D.Move batch reads to a read replica.
AnswerB

Smaller transactions release locks faster and reduce contention.

Why this answer

Breaking the batch job into smaller transactions with a delay between batches reduces lock contention and transaction log pressure on the primary Cloud SQL instance. This prevents a single large transaction from blocking concurrent user queries, thereby minimizing performance degradation for end users during the batch window.

Exam trap

The trap here is that candidates often assume scaling up resources (Option A) is the universal fix for performance issues, but the PCDE exam tests understanding that write-heavy batch jobs on Cloud SQL require transaction management and concurrency control, not just vertical scaling.

How to eliminate wrong answers

Option A is wrong because simply increasing memory and CPU does not address the root cause of lock contention and transaction log buildup; it only delays resource exhaustion and may not prevent query blocking. Option C is wrong because disabling binary logging during the batch window would break point-in-time recovery (PITR) and replication, which is not a recommended or supported practice in Cloud SQL for MySQL; binary logs are essential for replication and backup integrity. Option D is wrong because moving batch reads to a read replica does not help with write-heavy batch jobs that update thousands of rows; the writes still occur on the primary instance, causing the same lock contention and performance impact.

Practice this question →
92
MCQhard

In Cloud Bigtable, a table has a high ratio of garbage collection (GC) that causes performance degradation during compaction. What is the best practice to monitor and optimize this?

A.Use the bigtableadmin API to view table stats
B.Compact the table manually
C.Use Cloud Monitoring to track garbage collection count and adjust GC settings
D.Increase node count
AnswerC

Monitoring GC count and tuning GC settings can reduce compaction overhead.

Why this answer

Cloud Monitoring provides the metrics (e.g., 'bigtable.googleapis.com/table/garbage_collection_count') needed to track GC activity, and adjusting GC settings (e.g., column family max versions or TTL) directly reduces the compaction overhead caused by excessive stale data. This aligns with best practices for proactive performance optimization in Cloud Bigtable.

Exam trap

The trap here is that candidates confuse operational scaling (adding nodes) with tuning data retention policies, failing to recognize that GC-related compaction degradation is a schema design issue, not a capacity issue.

How to eliminate wrong answers

Option A is wrong because the bigtableadmin API is used for administrative operations like creating or modifying tables, not for real-time monitoring of garbage collection metrics; it does not expose GC counts or compaction performance data. Option B is wrong because manual compaction is a reactive, disruptive operation that does not address the root cause (high GC ratio) and can temporarily degrade performance further. Option D is wrong because increasing node count only scales throughput and storage capacity, not the compaction efficiency; it does not reduce the GC ratio or the compaction workload caused by excessive garbage.

Practice this question →
93
Multi-Selecteasy

A Bigtable instance is running out of storage and performance is degraded. The schema design is known to be efficient. Which THREE actions can help?

Select 3 answers
A.Delete unused column families.
B.Reduce the number of tablets.
C.Add SSDs.
D.Increase node count.
E.Enable compression.
AnswersA, D, E

Deleting column families triggers garbage collection, freeing up storage used by that data.

Why this answer

Deleting unused column families is correct because each column family in Bigtable stores data in separate SSTable files, and unused families consume storage and memory resources without providing value. Removing them frees up space and reduces the amount of data that must be scanned during reads and compactions, directly improving performance.

Exam trap

Google Cloud often tests the misconception that adding SSDs is a valid optimization for Bigtable, but Bigtable abstracts storage via Colossus and does not allow direct SSD configuration, so candidates must recognize that only node count, compression, and column family management are actionable.

Practice this question →
94
MCQeasy

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?

A.All of the above
B.Clustering
C.Materialized Views
D.Partitioning
AnswerA

All three features reduce data processed.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that a single optimization technique (like partitioning or clustering) is sufficient to solve all performance issues, when in fact the correct answer requires combining multiple features to achieve the greatest reduction in data scanned.

How to eliminate wrong answers

Option B is wrong because clustering alone does not reduce the amount of data processed; it only reorganizes data within partitions to improve filter and aggregation efficiency, but without partitioning, a query can still scan the entire table. Option C is wrong because materialized views alone do not reduce data processed for all common queries; they only help for queries that match the view's definition, and they require manual creation and maintenance. Option D is wrong because partitioning alone only limits scans to specific partitions based on the partition key, but if queries do not filter on that key, full-table scans still occur.

Practice this question →
95
Multi-Selectmedium

Which TWO metrics are most important to monitor for a Cloud SQL for PostgreSQL instance to detect performance degradation?

Select 2 answers
A.Memory usage
B.CPU utilization
C.Query latency
D.Disk IOPS
E.Network throughput (bytes sent/received)
AnswersB, D

High CPU indicates query processing load.

Why this answer

CPU utilization (B) is a primary indicator of performance degradation because sustained high CPU usage (e.g., >80%) can lead to query queuing, reduced throughput, and increased latency. Disk IOPS (D) is equally critical because Cloud SQL for PostgreSQL relies on disk I/O for WAL writes, checkpointing, and query execution; hitting the IOPS limit of the underlying persistent disk (e.g., 3,000 IOPS for a pd-standard disk) causes throttling and severe performance drops.

Exam trap

Google Cloud often tests the misconception that query latency is a primary monitoring metric, but the trap here is that latency is a downstream effect—you must monitor the underlying resource metrics (CPU and IOPS) to detect degradation before users experience slow queries.

Practice this question →
96
MCQmedium

You are managing a Cloud SQL for PostgreSQL instance that is experiencing high CPU usage and slow query performance. You notice that the database has a high number of idle-in-transaction connections. Which immediate action should you take to reduce CPU load without disrupting active transactions?

A.Use VPC firewall rules to block new connections until the issue resolves.
B.Kill all idle-in-transaction connections using pg_terminate_backend.
C.Set the cloudsql.enable_idle_in_transaction_session_timeout flag to true and configure idle_in_transaction_session_timeout.
D.Set a statement_timeout at the session level for new connections.
AnswerC

This flag automatically terminates idle-in-transaction sessions after a specified timeout, reducing CPU usage without manual intervention.

Why this answer

Setting the `cloudsql.enable_idle_in_transaction_session_timeout` flag to true and configuring `idle_in_transaction_session_timeout` allows Cloud SQL to automatically terminate idle-in-transaction connections after a specified timeout, reducing CPU load without manually killing connections or disrupting active transactions. This is a built-in, non-disruptive mechanism that targets only connections that are holding resources while idle, freeing up CPU and memory for active queries.

Exam trap

Google Cloud often tests the distinction between `statement_timeout` (which limits query execution time) and `idle_in_transaction_session_timeout` (which limits idle time within a transaction), and candidates mistakenly choose the session-level timeout thinking it will handle idle transactions, but it only applies to individual statements, not the idle period between statements within a transaction.

How to eliminate wrong answers

Option A is wrong because using VPC firewall rules to block new connections would prevent all new traffic, including legitimate active transactions, and does not address the existing idle-in-transaction connections that are already consuming CPU. Option B is wrong because killing all idle-in-transaction connections with `pg_terminate_backend` would abruptly terminate those backends, potentially causing application errors and disrupting any transactions that might be in a brief idle state but still holding locks or resources. Option D is wrong because setting `statement_timeout` at the session level only limits the duration of a single query, not the idle time of a transaction; it would not automatically terminate connections that are idle in a transaction, leaving the CPU load unaddressed.

Practice this question →
97
MCQeasy

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?

A.Change the persistent disk type from pd-standard to pd-ssd.
B.Use a regional persistent disk for higher availability and performance.
C.Add more replicas of the StatefulSet to distribute writes across multiple disks.
D.Increase the size of the persistent disks to improve IOPS limits.
AnswerA

SSD provides lower latency and higher IOPS for random write workloads, solving the problem cost-effectively.

Why this answer

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.

Exam trap

The trap here is that candidates may think increasing disk size (Option D) is the cheapest way to improve IOPS, but they overlook that pd-standard's IOPS/GB ratio is so low that the cost to reach equivalent pd-ssd performance would be much higher, making a disk type change the more cost-effective choice.

How to eliminate wrong answers

Option B is wrong because regional persistent disks provide higher availability through synchronous replication across zones, but they do not improve IOPS or latency performance over the base disk type; they would still use pd-standard performance if that type is selected. Option C is wrong because adding more replicas of the StatefulSet does not reduce write latency on the existing disks; writes to the MySQL database are typically concentrated on a single primary instance, and distributing writes across multiple disks would require application-level sharding, which is not described. Option D is wrong because increasing disk size improves IOPS limits for pd-standard disks only marginally (IOPS scale linearly with size but remain far below pd-ssd levels), and it would be less cost-effective than switching to pd-ssd since you would need a much larger pd-standard volume to match pd-ssd IOPS.

Practice this question →
98
Multi-Selecthard

You are managing a Cloud SQL for MySQL instance that is experiencing high latency and connection timeouts during peak hours. The current configuration uses 4 vCPUs, 15 GB memory, and 100 GB SSD storage. The database workload is a mix of transactional queries and batch inserts. Which TWO actions would most effectively reduce latency and improve performance?

Select 2 answers
A.Disable binary logging to reduce write I/O.
B.Increase the storage size to 200 GB to improve IOPS.
C.Increase the instance to 8 vCPUs and 30 GB memory.
D.Decrease the max_connections parameter to reduce overhead.
E.Enable the Cloud SQL proxy and use connection pooling.
AnswersC, E

Provides more resources to handle peak load.

Why this answer

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

Exam trap

Google Cloud often tests the misconception that increasing storage always improves IOPS, but in Cloud SQL for MySQL, IOPS scaling is tied to storage size only up to a baseline, and the real bottleneck in this scenario is compute and memory, not storage throughput.

Practice this question →
99
Multi-Selecthard

Which THREE factors should you consider when configuring Cloud SQL for MySQL query caching to optimize performance?

Select 3 answers
A.High write workloads can lead to frequent cache invalidation, reducing its effectiveness.
B.The query cache can become fragmented and require periodic defragmentation.
C.Query cache uses disk storage for cached results.
D.The query cache is deprecated and removed in MySQL 8.0+, so it should not be relied upon for new deployments.
E.Prepared statements always bypass the query cache.
AnswersA, B, D

Every table modification invalidates cached queries for that table, making cache less useful for write-heavy workloads.

Why this answer

In Cloud SQL for MySQL, high write workloads cause frequent updates to tables, which invalidates the query cache entries for those tables. This means the cache must be repopulated often, reducing the hit rate and potentially adding overhead from cache maintenance, making it less effective for performance optimization.

Exam trap

Google Cloud often tests the misconception that the query cache uses disk storage, when in fact it uses memory, and that prepared statements always bypass the cache, which is not universally true across all MySQL configurations.

Practice this question →
100
Matchingmedium

Match each BigQuery feature to its purpose.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Column-based ordering to improve query performance

Dividing tables into segments for cost and performance

Guaranteed query capacity

In-memory analysis for sub-second query response

Automated data ingestion from various sources

Why these pairings

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.

Practice this question →
101
MCQhard

Your Bigtable cluster is experiencing high CPU utilization and write latency during a daily batch job that writes 500 GB of data. The job uses bulk mutations with 1000 rows per request. What is the most effective way to reduce CPU usage?

A.Disable write-ahead logs
B.Increase the number of rows per mutation to 10,000
C.Increase the number of nodes temporarily
D.Create a new cluster in a different zone
AnswerB

Increasing the number of rows per mutation reduces the overhead per row, lowering CPU usage.

Why this answer

Larger mutations reduce overhead per row. Option A is wrong because disabling write-ahead logs does not significantly reduce CPU and risks data integrity. Option C is wrong because increasing nodes does not reduce CPU per request.

Option D is wrong because creating a new cluster in a different zone does not directly reduce CPU usage for writes.

Practice this question →
102
MCQmedium

A social media application uses Memorystore for Redis to cache user profiles and session data. Recently, the application experienced intermittent errors and high latency. You observe that the Redis CPU utilization is consistently above 90% and the cache hit ratio is 85%. The instance type is a Standard tier M2 (30 GB) with a maxmemory setting of 25 GB. The eviction policy is allkeys-lru. The number of keys is 10 million with an average value size of 2 KB. You suspect memory pressure is causing CPU spikes. What should you do to reduce CPU utilization and improve performance?

A.Reduce the maxmemory setting to force more aggressive eviction and free up memory.
B.Change the eviction policy to volatile-lru to prefer evicting keys with TTL.
C.Upgrade the Memorystore instance to a larger size (e.g., M5 with 60 GB) to provide more CPU and memory resources.
D.Implement Redis monitoring with Cloud Monitoring and set alerts to notify when CPU exceeds 80%.
AnswerC

A larger instance has more CPU cores and memory, reducing pressure.

Why this answer

Upgrading to a larger instance (e.g., M5 with 60 GB) directly addresses both the high CPU utilization and memory pressure. The M2 instance is CPU-bound with sustained >90% usage, and the 85% cache hit ratio indicates that evictions are already occurring under the allkeys-lru policy, causing CPU spikes from eviction overhead and key lookups. A larger instance provides more CPU cores and memory, reducing eviction frequency and allowing the cache to serve more requests without thrashing.

Exam trap

In the Google PCDE exam, a common trap is the misconception that tuning eviction policies or reducing memory limits can solve CPU pressure, when in fact the root cause is resource exhaustion that requires vertical scaling.

How to eliminate wrong answers

Option A is wrong because reducing maxmemory would force even more aggressive eviction, increasing CPU overhead from eviction processing and further degrading cache hit ratio, worsening the problem. Option B is wrong because changing to volatile-lru would only evict keys with TTLs, but the question does not indicate that keys have TTLs; if most keys lack TTLs, volatile-lru would evict nothing, leaving memory pressure unchanged and CPU spikes unresolved. Option D is wrong because monitoring and alerts only provide visibility into the problem; they do not reduce CPU utilization or improve performance, which requires a resource scaling action.

Practice this question →
← PreviousPage 2 of 2 · 102 questions total

Ready to test yourself?

Try a timed practice session using only Db Performance questions.

Start 20-question session