Troubleshoot slow queries with AI assistance

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 AlloyDB for PostgreSQL to troubleshoot slow queries in AlloyDB. You can use the AI assistance capabilities of AlloyDB and Gemini Cloud Assist to investigate, analyze, obtain recommendations, and finally implement those recommendations to optimize your queries in AlloyDB.

Before you begin

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

1. Review limitations with AI-assisted troubleshooting.
2. Enable AI-assisted troubleshooting. This step includes enabling Gemini Cloud Assist.

Required roles and permissions

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

Use AI assistance

To use AI assistance with troubleshooting your slow queries, go to the Query insights dashboard for your AlloyDB 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.

AlloyDB 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, AlloyDB 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, AlloyDB refreshes the Top queries table and reruns anomaly detection based on the new list of queries and an updated detection time period.

When AlloyDB detects an anomaly, AlloyDB performs baseline performance analysis for your query.

Every listed query in the table has either an Investigate icon or Warning warning_spark icon displayed next to the query's Avg execution time (ms) value.

If a query is running slower than expected, then a Warning warning_spark icon is displayed. When you click either icon, Gemini Cloud Assist is used to help analyze the query execution and offers observations about what might have caused any issue. Based on these observations, Gemini Cloud Assist generates a hypothesis that can help you address the issue.

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 Clusters page.
Go to Clusters
2. From the list of clusters and instances, click an instance.
3. 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, under the Queries tab, review the list of queries for your database.
6. If a Warning warning_spark icon appears next to the query's Avg execution time (ms) value for a query, then AlloyDB has detected an anomaly in your query performance. AlloyDB checks for anomalies within the 24-hour time period that occurs before the end of your selected time range.
7. Click the Warning warning_spark icon.
8. In the Query is slower than usual dialog, click New Investigation to start troubleshooting with AI assistance from Gemini Cloud Assist. After about two minutes, the Investigation details pane opens with the following sections:
   • Issue. A description of the issue being investigated, including the investigation’s start and stop time.
   • Observations. A list of observations about the issue. For example, these can include lock contention details, such as a longer than expected lock wait ratio for the query.
   • Hypotheses. A list of AI-recommended actions to take to help address the slow running query.

9. If you want to see all investigations associated with the query, in the Query is slower than usual dialog, click View all investigations. The Gemini Cloud Assist page opens where you can view all currently running and previously completed investigations. You can filter the page by project or label, for example, to find the specific investigation you need.

   Alternatively, to see all previous investigations, click the Notifications icon, then select a notification associated with any investigation to open the Gemini Cloud Assist page.

10. Alternatively, if you want to investigate the latency of any query, complete the following steps:
1. Identify the specific query you want to investigate.
2. In the Actions column, click the Actions icon associated with that query.
3. Select Investigate latency in the menu to run a Gemini Cloud Assist investigation.

Query details

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

1. In the Google Cloud console, go to the Clusters page.
Go to Clusters
2. From the list of clusters and instances, click an instance.
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. 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 AlloyDB reruns anomaly detection.
   While the Time range filter you choose applies to most results, the Analysis time period is always set to 48 hours.
6. If AlloyDB doesn't detect an anomaly for the query, then you can still run an analysis on the query by clicking the Investigate button in the Query latency card.

Analyze query latency

Using AI assistance, you can analyze and troubleshoot the details of your query latency.

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. AlloyDB 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 AlloyDB has detected an anomaly with the query, then after you select the query from the Query insights dashboard, AlloyDB performs a baseline performance analysis for the query using the last 24 hours from the end of the anomaly. If AlloyDB hasn't detected an anomaly with the query and runs anomaly detection on the query again, then AlloyDB 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 AlloyDB finds an anomalous change in query performance. AlloyDB uses the baseline performance measured for the query during the analysis time period.

If AlloyDB detects multiple anomalies for a query within a selected time period, then AlloyDB uses the last detected anomaly.

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.

What's next

• Use index advisor
• Monitor active queries