Monitor and optimize database performance practice questions

Use VPC firewall rules to block new connections until the issue resolves.

Why wrong: Firewall rules block new connections but do not resolve existing idle-in-transaction connections or CPU issues.

Kill all idle-in-transaction connections using pg_terminate_backend.

Why wrong: Killing all idle-in-transaction connections indiscriminately may disrupt legitimate long-running transactions.

Set a statement_timeout at the session level for new connections.

Why wrong: Setting statement_timeout affects new connections only and does not terminate existing idle-in-transaction connections.

Use a multi-region configuration with default leader preference set to the region where the application runs.

Why wrong: Multi-region configurations introduce higher write latency and eventual consistency, which may not be optimal for low-latency reads.

Use a single-region instance and configure the leader preference to the application's zone.

Why wrong: Leader preference is only available in multi-region configurations, not single-region.

Use a single-region instance and enable read-only replicas in multiple zones.

Why wrong: Cloud Spanner does not support read-only replicas; all replicas can serve reads and writes.

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Why wrong: Network bytes received is a throughput metric, not a direct indicator of connection spikes.

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Why wrong: This metric tracks replication lag, which is unrelated to connection spikes causing performance issues.

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Why wrong: Uptime indicates availability, not connection spikes or performance.

Read requests are being throttled due to exceeding IOPS limits.

Why wrong: Throttling would increase CPU, but CPU is low.

A hot node is throttling read requests.

Why wrong: CPU utilization is below 50%, so a hot node is unlikely.

The cluster is underprovisioned, causing resource contention.

Why wrong: Underprovisioning would cause high CPU, not low CPU.

Check the slow query log in Cloud Logging and look for queries with high rows_examined.

Why wrong: While slow query log is useful, Query Insights is more comprehensive and easier to use for first-level diagnosis.

Increase the instance tier to reduce the impact of slow queries.

Why wrong: Scaling up without diagnosis may mask the problem and increase costs.

Enable the general query log and parse the log file to find slow queries.

Why wrong: Enabling general query log can degrade performance and is not the most efficient diagnostic approach.

Enable synchronous replication on all read replicas.

Why wrong: Synchronous replication increases write latency and does not help with read capacity.

Use smaller machine types for read replicas.

Why wrong: Smaller replicas have less capacity, potentially requiring more replicas.

Increase the max_connections parameter on the primary instance.

Why wrong: Increasing max_connections may cause resource exhaustion and does not directly reduce the need for replicas.

Query cache uses disk storage for cached results.

Why wrong: The query cache is stored in memory, not on disk.

Prepared statements always bypass the query cache.

Why wrong: Prepared statements can use the query cache if they are identical binary representations.

Increase the machine type of the Cloud SQL instance to add more CPU.

Why wrong: This addresses symptoms but not root cause.

Enable connection pooling to reduce the number of connections.

Why wrong: Connection pooling helps with connection overhead but does not identify the CPU-intensive queries.

Set a maximum connection limit and reduce the connection lifetime.

Why wrong: This may reduce connections but does not diagnose the cause of CPU spikes.

Add secondary indexes on the primary key columns.

Why wrong: Secondary indexes are redundant for point lookups by primary key.

Redesign the schema to use interleaved tables.

Why wrong: Interleaved tables optimize joins, not point lookups.

Reduce the number of Spanner nodes to lower CPU overhead.

Why wrong: Fewer nodes may increase load per node.

Create materialized views for common aggregations.

Why wrong: Materialized views can pre-compute results but may not address the slot limit of the current query.

Cluster the tables on frequently filtered columns.

Why wrong: Clustering improves I/O but not slot availability.

Partition the tables by date.

Why wrong: Partitioning reduces scanned data but does not increase slot usage.

Use many small keys instead of a few large keys.

Why wrong: Small keys increase memory overhead due to key storage, potentially increasing evictions.

Decrease the maxmemory setting to force earlier eviction.

Why wrong: Lowering maxmemory would increase eviction rate.

Set maxmemory-policy to noeviction.

Why wrong: This would cause writes to fail instead of evicting keys.

Read latency.

Why wrong: Read latency is separate from write commit latency.

Storage utilization.

Why wrong: Storage utilization is capacity-related, not a direct cause of commit latency.

CPU utilization per node.

Why wrong: CPU may be high but does not pinpoint contention.

Storage utilization

Why wrong: Storage utilization is a capacity metric.

Commit latency

Why wrong: Commit latency applies to write transactions, not read queries.

Lock wait time

Why wrong: Lock wait time is for write contention, not read queries.

Reduce the binlog retention period on the primary.

Why wrong: Binlog retention affects storage, not replication speed.

Enable binary log compression on the primary.

Why wrong: Compression reduces storage but not replication latency.

Wait for the operation to finish before allowing any queries.

Why wrong: Queries are allowed during optimize, and waiting is unnecessary.

Drop and restore the database again to start fresh.

Why wrong: This would restart the optimize process and waste time.

Cancel the optimize operation to reduce resource usage.

Why wrong: Canceling would leave indexes unoptimized and statistics outdated, hurting performance long-term.

The index is fragmented; rebuild the index to improve performance.

Why wrong: Index fragmentation is unlikely to be the primary cause; the query pattern itself is inefficient.

Increase the instance machine type to provide more CPU capacity.

Why wrong: While scaling up might alleviate symptoms, it does not address the root cause of inefficient query design.

The index on `order_date` is not being used; add a hint to force index usage.

Why wrong: The index is likely being used, but the issue is not index usage; it's the retrieval of unnecessary columns.

Add a secondary index on `user_id` to help narrow down the search.

Why wrong: Indexing `user_id` does not help because the query does not filter by `user_id`.

Use a Spanner query hint to force a specific index scan.

Why wrong: Without an appropriate index, a hint cannot improve performance; the index must exist first.

Change the primary key to `(transaction_id, user_id)` to enable direct access by transaction_id.

Why wrong: Changing the primary key of a large table is a disruptive operation and may affect interleaving with the parent table. Not recommended without careful planning.

Use a regional persistent disk for higher availability and performance.

Why wrong: Regional persistent disks provide synchronous replication across zones, increasing availability but not improving latency; in fact, they may add overhead.

Add more replicas of the StatefulSet to distribute writes across multiple disks.

Why wrong: Adding replicas would require application-level sharding and does not directly address disk latency; also increases complexity and cost.

Increase the size of the persistent disks to improve IOPS limits.

Why wrong: Increasing disk size improves IOPS limits linearly but does not reduce latency as much as switching to SSD; also less cost-effective if size is not needed.

Migrate the database to Cloud Spanner for better scaling.

Why wrong: Overly aggressive and costly; not a first diagnostic step.

Add a composite index on the orders table based on the query's WHERE clause.

Why wrong: Should only be done after analyzing the query plan and identifying missing indexes.

Increase the machine type to add more vCPUs.

Why wrong: Increases cost and does not address the underlying query performance issue.

Disable binary logging to reduce write I/O.

Why wrong: Compromises point-in-time recovery capability.

Increase the storage size to 200 GB to improve IOPS.

Why wrong: Does not directly reduce latency; IOPS are already sufficient.

Decrease the max_connections parameter to reduce overhead.

Why wrong: Could cause connection failures during peak hours.