Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/sql/docs/postgres/troubleshoot-slow-queries below:

Troubleshoot slow queries with AI assistance | Cloud SQL for PostgreSQL

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. You can process personal data for this feature as outlined in the Cloud Data Processing Addendum, subject to the obligations and restrictions described in the agreement under which you access Google Cloud. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

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.

To troubleshoot slow queries with AI assistance, do the following:

1. Review limitations with AI-assisted troubleshooting.
2. Enable AI-assisted troubleshooting.

For the roles and permissions required to troubleshoot slow queries with AI assistance, see Observe and troubleshoot with AI.

To use AI assistance with troubleshooting your slow queries, go to the Query insights dashboard for your Cloud SQL instance in Google Cloud console.

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:

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. In the SQL navigation menu, click Query insights.
4. 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.
5. In the Top queries table, review the list of queries for your database.
6. 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.
7. Click Analyze latency to start troubleshooting with AI assistance. This generates the Analyzing query latency page.

If no queries display Analyze latency next to Avg Execution Time, then the reason might be one of the following:

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

You can also troubleshoot a slow query with AI assistance from the Query details page.

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. Click Query insights to open the Query insights dashboard.
4. In the Query insights dashboard, click the query in the Top queries that you want to view. The Query details page appears.
5. If Cloud SQL detects an anomaly for the query, then one or more of the following indicators appears in the Query details page:
6. 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.
7. 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.

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
• Time called
• Avg rows returned

The page also provides a latency chart that shows P50, P95, and P99 latency values over the selected time period. P50 shows you median latency where 50% of query users are experiencing high latency and 50% are experiencing lower latency. Similarly, 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.

You can see the specific time period when a significant increase in query latency occurred.

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.

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.

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 Change in data volume.

In identifying this particular situation, Cloud SQL has detected an anomalous increase in data volume for this query. Cloud SQL also lists other possible situations where no anomalies are detected, so you can eliminate them as possible root causes.

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.

In this example, to support the situation of a Change in data volume, you might see the following pieces of evidence:

• Data processed by the query: Up to 5989.97% increase in average data processed by the query.
• Query execution time: Up to 1506.59% increase in average execution time.
• Shared block hits: Average shared block hits increased by 2397.83%

You might also see evidence that supports typical or non-anomalous query performance, such as:

• Rows returned: No significant changes detected.
• Data processing time: Insufficient data to evaluate change.

Evidence of standard query execution can help you decide which recommendation might be better for you to implement.

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 recommendation:

• Create recommended indexes: Create a new index on country to improve the performance of this query.

To implement this recommendation, do the following:

1. In the Recommendations table, click View details next to the recommendation that you want to implement. The Insight and recommendation window appears.
2. In the Insight and recommendation window, review the Recommended action and Tables impacted.
3. Optional: If you want to learn more about index recommendations, then click How this helps to open Gemini Cloud Assist.
4. Click Copy all index commands.
5. In the SQL navigation menu, click Cloud SQL Studio.
6. Authenticate to Cloud SQL Studio.
7. Paste the CREATE INDEX command into Cloud SQL Studio to create the index. For more information about running SQL commands in Cloud SQL Studio, see Manage your data using Cloud SQL Studio.

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.

• Optimize queries with high memory usage

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4