This document describes how you can use AI assistance in Cloud SQL to troubleshoot high database load 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.
By accessing the Query insightsdashboard in the Google Cloud console, you can analyze your database and troubleshoot events when your system experiences a higher database load than average. Cloud SQL uses the 24 hours of data that occurs prior to your selected time range to calculate the expected load of your database. You can look into the reasons for the higher load events and analyze the evidence behind reduced performance. Finally, Cloud SQL provides recommendations for optimizing your database to improve performance.
Before you begin
To troubleshoot high database load with AI assistance, do the following:
- Review limitations with AI-assisted troubleshooting .
- Enable AI-assisted troubleshooting . This step includes enabling Gemini Cloud Assist.
Required roles and permissions
For the roles and permissions required to troubleshoot high database load with AI assistance, see Observe and troubleshoot with AI .
For Gemini Cloud Assist, you need the Gemini Cloud Assist Investigation Owner
( roles/geminicloudassist.investigationOwner
)
IAM role. For more information, see Troubleshoot issues with Gemini Cloud Assist Investigations
.
Use AI assistance
To use AI assistance with troubleshooting high database load, go to the Instance Overviewpage or the Query insightsdashboard in the Google Cloud console.
Instance overview page
Troubleshoot high database load with AI assistance in the Instance overviewpage by using the following steps:
-
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 Overview page, from the Chart menu, select a metric for the database. You can select any metric, for example, CPU utilization .
- Optional: To select a specific analysis time period, use the Time range
filter
to select either 1 hour, 6 hours, 1 day, 7 days, 30 days or a custom range.
You can zoom in to specific sections of the chart where you notice areas of high load that you want to analyze. For example, an area of high load might display CPU utilization levels closer to 100%. To zoom in, you can click and select a portion of the chart.
Click the Investigate performance button to start troubleshooting high database load 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.
Query insights dashboard
Troubleshoot high database load with AI assistance in the Query insightsdashboard using the following steps:
-
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.
- Optional: Use the Time range filter to select either 1 hour, 6 hours, 1 day, 7 days, 30 days or a custom range.
- 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.
You can zoom in to specific sections of the chart where you notice areas of higher database load by query execution time. To zoom in, you can click and select a portion of the chart.
In the Database load chart , click the Investigate performance button to start troubleshooting high database load with AI assistance from Gemini Cloud Assist .
After about two minutes, the Investigation details pane opens with the following sections:
Analyze high database load
Using AI assistance, you can analyze and troubleshoot the details of your database load.
Analysis time period
Cloud SQL analyzes your database for the time period that you select in your database load chart from the Query insightsdashboard or the Instance overviewpage. If you select a time period of less than 24 hours, then Cloud SQL analyzes the entire time period. If you select a time period greater than 24 hours, then Cloud SQL selects only the last 24 hours of the time period for analysis.
To calculate the baseline performance analysis of your database, Cloud SQL includes 24 hours of a baseline time period in its analysis time period. If your selected time period occurs on a day other than Monday, then Cloud SQL uses a baseline time period of the 24 hours previous to your selected time period. If your selected time period occurs on a Monday, then Cloud SQL uses a baseline time period of the 7th day previous to your selected time period.
Metrics analysis
When Cloud SQL starts the analysis, Cloud SQL checks for significant changes in the various metrics, including but not limited to the following:
- Queries per second (QPS)
- CPU
- Memory
- Disk I/O
Cloud SQL compares the baseline aggregated data for your database within the performance data of your analysis time window. If Cloud SQL detects a significant change in threshold for a key metric, then Cloud SQL indicates a possible situation with your database. The identified situation might explain a root cause for the high load on your database over the selected time period.
Recommendations
When Gemini Cloud Assist completes analysis, the Hypothesessection of the Investigation detailspane lists actionable insights to help remediate the issue.
For some situations, based on the analysis, there might not be a recommendation.
If you want to continue troubleshooting or get more assistance with system performance, then you can also open Gemini Cloud Assist .
