Monitor and optimize database performance

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 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?

Your Cloud SQL for MySQL instance is experiencing intermittent performance degradation. You suspect that the issue is due to a sudden spike in connections from a specific application. Which metric and monitoring approach would best help you correlate the connection spike with performance degradation?

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 developer has deployed a new version of an application that uses Cloud SQL. After the deployment, you notice a sharp increase in the number of slow queries. What should you do first to identify the problematic queries?

Which TWO actions can help reduce the number of read replicas needed for a Cloud SQL for PostgreSQL instance that serves a read-heavy workload?

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

Your Cloud SQL for PostgreSQL instance is experiencing intermittent slowdowns during peak hours. You notice that the CPU utilization spikes to 80% and the number of connections increases. The application team confirms they are not running any new queries. What should you do first to diagnose the issue?

Your Spanner instance is running a workload with high read throughput. You notice that read latency has increased significantly. Upon investigating, you find that the instance is experiencing high CPU utilization on the Spanner nodes. The workload consists of many small point lookups (reads by primary key). Which action is most likely to reduce read latency?

A bigquery job is running slower than expected. Checking the job information, you see that the slot usage is at 100% for the entire duration of the query. You are using on-demand pricing. What is the most effective way to improve query performance?

You are running a Memorystore for Redis instance with a high write volume. You notice that the eviction rate is high and the cache hit ratio has dropped significantly. Which configuration change would most directly reduce the eviction rate?

You are monitoring a Cloud Spanner instance and see that the average commit latency is high. The application performs many single-row inserts. Which metric would you check first to understand the root cause?

You are troubleshooting a slow-performing query on Cloud Spanner. The query scans a large table with a secondary index. Which TWO metrics from the Query Insights dashboard would most directly indicate the source of the performance issue?

You are managing a Cloud SQL for MySQL instance that is experiencing high replication lag. The instance uses semi-synchronous replication. Which THREE actions could reduce the replication lag?

Refer to the exhibit. You restored a Spanner database from a backup and are checking the status of the optimize operation. The operation has been running for 15 minutes and is 45% complete. The database is already accessible but queries on it are slower than expected. What should you do?

Your Cloud SQL for PostgreSQL instance is experiencing high CPU utilization during peak hours. You notice that the query `SELECT * FROM orders WHERE order_date >= '2024-01-01'` is frequently run against a table with 10 million rows. The table has a B-tree index on `order_date`. What is the most likely cause of the high CPU usage, and how should you address it?

You are managing a Spanner instance for a global financial application. The database has a table `transactions` with columns `transaction_id` (INT64), `user_id` (INT64), `amount` (FLOAT64), `timestamp` (TIMESTAMP), and `region` (STRING). The table is interleaved with a parent table `users`. Recently, you observed that point-read queries by `transaction_id` are taking over 100ms on average, whereas they used to take under 10ms. The instance CPU utilization is below 40%, and there are no contention issues. The `transactions` table has a primary key `(user_id, transaction_id)`. Queries filter on `transaction_id` only, without specifying `user_id`. Which optimization should you implement to improve point-read latency?

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?

You are a database engineer for an e-commerce platform running on Cloud SQL for PostgreSQL. The application team reports that a critical report query taking 5 seconds last week now takes over 30 seconds. The database CPU usage has increased from 40% to 85%. The query plan shows a sequential scan on the orders table. Which action should you take first to diagnose the problem?

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?

Refer to the exhibit. You are analyzing a slow query in Cloud SQL for PostgreSQL. The execution plan shows a sequential scan. Which index should you create to most effectively improve query performance?

Order the steps to set up a Cloud Spanner instance with a global database.

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

Match each BigQuery feature to its purpose.

Match each Cloud SQL backup type to its retention policy.

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 Cloud Spanner database is experiencing high latency for point reads. The table has a primary key of (CustomerID, OrderDate). Most reads are by CustomerID only. What should the engineer do?

A Bigtable instance stores time-series data with row keys formatted as `#deviceID#timestamp`. The application often queries recent data for a specific device. Monitoring shows high read latency when scanning multiple devices. The row key design is causing hotspotting. What is the best redesign?

A Firestore database is used for a social app. A collection of posts has indexes on fields `author` and `timestamp`. The query `where author == 'user1' order by timestamp desc limit 10` is performing a large number of document reads. What is the likely cause?

A Cloud SQL for MySQL instance is configured with 8 vCPUs and 30GB RAM. The Database Advisor suggests adding an index on a table, but after adding, write performance degrades. The table has 10 million rows and receives 500 writes per second. What is the most likely reason?

Which tool is best for identifying hot spots in a Cloud Spanner database?

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 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 Firestore database has a collection with a composite index on (status, timestamp desc). The query `where status == 'active' order by timestamp desc limit 50` is returning empty results even though there are active documents. What could be the issue?

A Cloud Spanner database contains the Orders table as defined above. The query `SELECT * FROM Orders WHERE CustomerID=123` takes a long time. What is the most likely reason?

A slow query log entry shows the above for a Cloud SQL for MySQL instance. Which index would most improve performance?

A user runs the above command and expects a row to be returned because the user exists. Which index is missing?

A Cloud SQL for PostgreSQL instance is experiencing high replication lag between primary and read replica. Which TWO actions would reduce the lag?

A Cloud Spanner database has a table with a primary key (UserId, Timestamp). Queries that filter by Timestamp range for a specific UserId are fast, but queries that filter only by Timestamp range across all users are slow. Which TWO improvements would help?

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?

Your application uses Cloud SQL for PostgreSQL. You notice that the database CPU utilization has been consistently above 90% during peak hours, causing increased query latency. You have already tuned the most expensive queries. What is the most cost-effective next step?

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?

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?

You are designing a Cloud Spanner schema for a global social media application. The application reads the most recent 100 posts for a user's timeline. The Posts table has a primary key of (UserId, PostTimestamp DESC). You observe that queries for the timeline are hitting high read latency and the transaction abort rate is increasing. What is the most likely cause?

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?

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?

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?

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

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

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

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?

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?

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?

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?

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 Cloud SQL for MySQL database has frequent table locks causing contention and slow queries. Which diagnostic approach helps identify the blocking queries?

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?

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

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?

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 company is experiencing slow query performance in Cloud SQL for PostgreSQL. Which TWO tools can help identify the root cause?

In Cloud Spanner, which TWO strategies can help reduce read latency for globally distributed applications?

Which THREE metrics from Cloud Monitoring are important for monitoring Cloud Bigtable performance?

Based on the exhibit from Cloud Spanner Query Insights, what is the most likely performance issue?

A DBA notices high query cache lock contention on this Cloud SQL for MySQL instance. Which configuration change should be recommended?

What is this alert likely monitoring?

You are monitoring a Cloud SQL for PostgreSQL instance and notice that the CPU utilization is consistently above 90% during peak hours. What is the most cost-effective initial action to reduce CPU load?

Your Cloud Spanner instance has high latency for point reads. The workload is evenly distributed across all nodes. Which metric should you examine first to identify the bottleneck?

A Cloud SQL for MySQL instance is experiencing increased replica lag. The write workload is constant. What is the most likely cause?

Your Bigtable cluster is showing high read latency for row key lookups. The application accesses rows with keys in the format 'user_id#timestamp'. You notice that most reads are for recent timestamps. Which optimization should you implement?

You are tuning a Cloud SQL for PostgreSQL instance that runs reporting queries. The slowest query performs a full table scan on a 100 GB table. Which action is most likely to improve performance?

Your Cloud Spanner instance has several tables with interleaved parent-child relationships. You notice that queries that join parent and child tables are slow. What is the best practice to optimize these joins?

You are managing a Memorystore for Redis cluster with standard tier (persistence disabled). The application experiences occasional latency spikes while performing SET operations. You observe that the 'evicted_keys' metric spikes during the spikes. What is the most effective solution?

Your Cloud SQL for SQL Server instance has a query that uses a non-clustered index to filter rows but then performs key lookups to retrieve additional columns. The query is slow. Which database tuning option would most likely reduce I/O?

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?

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

Refer to the exhibit. You receive an alert from this policy for a Cloud Spanner instance. Which action should you take first?

Refer to the exhibit. You notice replication latency is 15ms. What is the most likely cause of this latency?

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

Which THREE actions can help reduce read latency in Cloud Spanner?

Which TWO are best practices for optimizing write performance in Cloud Bigtable?

You manage a Cloud SQL for PostgreSQL instance that handles OLTP workloads. Users in a different region report slow query response times. You notice that the database CPU utilization is below 30%, but network latency is high. What is the most cost-effective solution to reduce query latency without migrating the database?

You are using Cloud Monitoring to track performance of a Cloud Spanner instance. Which metric indicates that a database is approaching its throughput limits and may need a split or additional nodes?

Your Cloud Spanner database is experiencing a high volume of read-write conflicts, causing many aborts and high latency. You have already increased the compute capacity. Upon analyzing the schema, you find that most conflicts occur on a single table with frequent updates to the same row. What index or schema change would most effectively reduce contention?

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?

You are monitoring a Memorystore for Redis instance serving as a cache for an e-commerce application. The cache hit ratio has dropped from 95% to 70%. Which action is most likely to restore the hit ratio?

You are optimizing a Cloud SQL for MySQL instance for an OLTP application. You observe frequent buffer pool contention and high disk reads per second. The instance has 16 vCPUs and 120 GB memory. What is the most effective initial tuning action?

Your application uses Firestore for real-time updates. You notice increasing read latency during peak hours. The database is in Native mode with a single-location (us-central1). After reviewing metrics, you see that the number of document reads has not changed significantly, but the database size has grown. What is the most likely cause and solution?

You need to set up a Cloud Monitoring alert for a Cloud Spanner instance to notify when the CPU utilization exceeds a threshold that could indicate performance degradation. What is the recommended CPU utilization threshold for Cloud Spanner?

You are monitoring a Cloud Spanner instance that is experiencing high CPU utilization (consistently above 70%). You want to identify the root cause. Which TWO metrics should you examine first? (Choose two.)

You manage a Cloud SQL for PostgreSQL instance that is experiencing high read latency. You have already tuned the buffer cache and queries. Which THREE actions can further reduce read latency? (Choose three.)

A data engineering team wants to monitor BigQuery query performance and slot utilization. Which TWO tools or features should they use? (Choose two.)

Your company runs a global gaming platform using Cloud Spanner as the backend database. The platform has millions of users who play concurrently. You receive reports that during peak hours (7-10 PM UTC), some users experience 'DEADLINE_EXCEEDED' errors and high latency on write operations. You have already verified that there are no hot keys and that the schema uses primary keys with hash prefixes. Monitoring shows CPU utilization averages 60% but spikes to 80% during the peak. The average commit latency is 50ms during peak, and the transaction rate is 10,000 writes per second. The instance currently has 100 nodes. The application team indicates that writes are primarily player score updates. What should you do to resolve the performance issue?

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?

Your company runs a business intelligence (BI) dashboard on BigQuery that refreshes every hour. The dashboard queries are complex with multiple JOINs and aggregations. Recently, the queries started taking longer than 30 minutes, causing timeouts. You check the BigQuery monitoring and see that the slot utilization consistently reaches 100% during the dashboard refresh. The project uses a flat-rate pricing model with 1000 slots. Other team members run ad-hoc queries during the same period. What is the most effective action to improve the dashboard performance?

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?

You are a Cloud Database Engineer managing a Cloud Spanner instance. You notice that some queries are taking longer than expected. You suspect that the queries are not using secondary indexes efficiently. Which TWO metrics should you monitor in Cloud Monitoring to validate your suspicion? (Choose two.)

You are managing a Cloud SQL for MySQL instance that supports a web application. Recently, users have reported that the application is responding slowly during peak hours. You examine the Query Insights dashboard and see that a specific query is running frequently and has a high execution time. The query involves JOINs on three tables, each with tens of thousands of rows. The query plan shows a full table scan on two tables. What should you do first to improve performance?

Your team uses Cloud Bigtable for a time-series data analytics platform. You observe that the write throughput has dropped significantly, and Cloud Monitoring shows that most of the CPU usage is concentrated on a few nodes. The remaining nodes have low CPU usage. The data model uses sequential timestamps as row keys, and the application writes data for many different sensors. Each sensor ID is part of the row key. What is the most effective action to resolve this hot spotting?

A Cloud Spanner instance is experiencing high CPU utilization (above 80%) on multiple nodes. The database is used for an e-commerce application with a high volume of read-write transactions. The application uses the googlesql dialect and runs typical OLTP queries. You have already reviewed the query performance and found that most queries are efficient. Which initial step should you take to reduce CPU utilization?

You administer a Cloud SQL for PostgreSQL instance running with 4 vCPUs and 15 GB of memory. The application frequently runs complex read-only reporting queries during business hours. Recently, the database started throwing 'out of memory' errors, and the instance's memory usage is consistently above 90%. You have enabled the pgtune recommendations and applied them. The workload is read-heavy and does not require a high write throughput. Which change would most effectively reduce memory pressure while maintaining query performance?

What is the most likely cause of the high execution time?

Which TWO actions would help optimize a Cloud SQL for PostgreSQL database experiencing high read latency?

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?