This document describes how you can use AI assistance in Cloud SQL to troubleshoot slow queries in Cloud SQL. You can use the AI assistance capabilities of Cloud SQL and Gemini Cloud Assist to investigate, analyze, obtain recommendations, and finally implement those recommendations to optimize your queries in Cloud SQL.
Before you begin
To troubleshoot slow queries with AI assistance, do the following:
Required roles and permissions
For the roles and permissions required to troubleshoot slow queries with AI assistance, see Observe and troubleshoot with AI.
Use AI assistance
To use AI assistance with troubleshooting your slow queries, go to the Query insights dashboard for your Cloud SQL instance in Google Cloud console.
Top queries table
You can start troubleshooting slow queries with AI assistance in the Top queries table section of the Query insights dashboard.
Cloud SQL can help you identify which queries are performing slower than average during a specific detection time period. After you select a time range in the Query insights dashboard, Cloud SQL checks whether any queries are performing slower than average by using a detection time period of 24 hours before the end of your selected time range.
When you adjust the time range filter of the Database load chart, or any other filter such as database or user, Cloud SQL refreshes the Top queries table and reruns anomaly detection based on the new list of queries and an updated detection time period.
When Cloud SQL detects an anomaly, Cloud SQL performs baseline performance analysis for your query after you click Analyze latency. Cloud SQL maps the metrics during the anomaly to the slow query and searches for situations that might have caused the slow performance. If Cloud SQL finds a potential situation, then it lets you view the evidence for the situation. Finally, Cloud SQL provides recommendations to fix and optimize your query performance.
To troubleshoot slow queries in the Top queries table in the Query insights dashboard, do the following:
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- In the SQL navigation menu, click Query insights.
- In the Executed queries chart, use the Time range filter to select either 1 hour, 6 hours, 1 day, 7 days, 30 days or a custom range.
- In the Top queries table, review the list of queries for your database.
- If Analyze latency appears next to the Avg Execution Time for a query, then Cloud SQL has detected an anomaly in your query performance. Cloud SQL checks for anomalies within the 24-hour time period that occurs before the end of your selected time range.
- Click Analyze latency to start troubleshooting with AI assistance. This generates the Analyzing query latency page.
- None of the queries listed have experienced an anomaly within the 24-hour detection period of the selected time range.
- Gemini Cloud Assist isn't set up. Ensure that Gemini Cloud Assist is set up for your Google Cloud user account and project.
If no queries display Analyze latency next to Avg Execution Time, then the reason might be one of the following:
Query details
You can also troubleshoot a slow query with AI assistance from the Query details page.
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Click Query insights to open the Query insights dashboard.
- In the Query insights dashboard, click the query in the Top queries that you want to view. The Query details page appears.
- If Cloud SQL detects an anomaly for the query, then one or more of the following indicators appears in the Query details page:
- A message on the details screen that says
This query is slower than usualand an Analyze query performance option. - A message in the Query latency chart that says
Query slower than usual. If this message appears, then click Analyze to start troubleshooting with AI assistance. This generates the Analyzing query latency page. - Optional: Use the Time range filter to select either 1 hour, 6 hours, 1 day, 7 days, 30 days or a custom range. When you adjust the Time range filter of the Query details page, or any other filter such as Database or User, Cloud SQL reruns anomaly detection.
- If Cloud SQL doesn't detect an anomaly for the query, then you can still run an analysis on the query by clicking Analyze query performance. This generates the Analyzing query latency page.
Analyze query latency
Using AI assistance, you can analyze and troubleshoot the details of your query latency.
In the Analyzing query latency page, you can view following details for your query and the text of your query:
- Analysis time period
- Detected anomaly period
- Database
- Avg. execution time
- Times called
The page also provides a latency chart that shows P95 and P99 latency values over the selected time period. The P95 and P99 lines show you that 95% and 99% of query users experience the indicated query latencies. For P95, 5% are experiencing a higher latency while for P99, only 1% are experiencing a higher latency. By examining the chart you can see the specific time period when a significant increase in query latency occurred.
Analysis time period
The analysis time period consists of the 24 hours that occur before the end of the time range that you select in the Database load chart of the Query insights dashboard or the Query details page. Cloud SQL uses this time period to compare baseline metrics with the metrics retrieved during the time period of the anomaly.
On the Query details page, if Cloud SQL has detected an anomaly with the query, then after you select the query from the Query insights dashboard, Cloud SQL performs a baseline performance analysis for the query using the last 24 hours from the end of the anomaly. If Cloud SQL hasn't detected an anomaly with the query and runs anomaly detection on the query again, then Cloud SQL uses 48 hours before the end of the selected time range as the performance baseline for the analysis time period.
Detected anomaly period
The detected anomaly period represents a time period when Cloud SQL finds an anomalous change in query performance. Cloud SQL uses the baseline performance measured for the query during the analysis time period.
If Cloud SQL detects multiple anomalies for a query within a selected time period, then Cloud SQL uses the last detected anomaly.
Situation
After you start your investigation, Cloud SQL analyzes your query, any historical data, and tries to identify an underlying situation that might explain the slower performance of your query over the selected time period.
For example, one situation for why your query is slow might be identified as Lock contention.
In identifying this particular situation, Cloud SQL has detected an anomalous increase in row lock wait time for this query. Cloud SQL also lists other possible situations where no anomalies are detected, so you can eliminate them as possible root causes.
Evidence
For each situation, Cloud SQL provides a list of evidence to support the finding. Cloud SQL bases evidence on metrics gathered from the instance, the database, and historical runs of the query.
The evidence presented for each situation reflects any anomaly that Cloud SQL detects for the query within the detection time period. Cloud SQL defines an anomaly as when a metric surpasses certain thresholds or meets specific criteria.
To support the situation of a Lock contention, you might see the following pieces of evidence:
- Lock wait time: There is a 7782.49% increase in row lock wait time during query execution.
The evidence details can help you decide which recommendation might be better for you to implement.
Recommendations
Based on all of the situations analyzed, Cloud SQL provides you with one or more actionable recommendations to help remediate the problems with your slow query performance. Cloud SQL presents the recommendations with a cost-benefit analysis so you can make an informed decision on whether to implement the recommendation.
For example, you might receive the following recommendations:
Review concurrent queries: Review the concurrent queries to identify lock contentions, which mostly happen among data modification queries (DMLs - inserts/updates/deletes).
To start implementing the first recommendation, click Learn more to read about reviewing concurrent queries in the Query insights dashboard. For more information, see Monitor active queries.
If you want to continue troubleshooting or get more assistance with query performance, then you can also open Gemini Cloud Assist. For more information, see Observe and troubleshoot with AI assistance.