This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. You can process personal data for this feature as outlined in theCloud 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 thelaunch stage descriptions.

This document describes how you can use AI assistance in Cloud SQLto troubleshoot high database load in Cloud SQL.You can use the AI assistance capabilitiesof Cloud SQLand Gemini Cloud Assist to investigate, analyze, obtain recommendations,and finally implement those recommendations to optimize your queries inCloud SQL.

By accessing theQuery insights dashboard in the Google Cloud console, you cananalyze your database and troubleshoot events when your system experiences ahigher database load than average. Cloud SQL usesthe 24 hours of data that occurs prior to your selected time range to calculatethe expected load of your database. You can look into the reasons for the higherload events and analyze the evidence behind reduced performance.Finally, Cloud SQL provides recommendations for optimizing yourdatabase to improve performance.

Before you begin

To troubleshoot high database load with AI assistance, do the following:

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

Required roles and permissions

For the roles and permissions required to troubleshoot high database load with AI assistance,seeObserve and troubleshoot with AI.

Use AI assistance

To use AI assistance with troubleshooting high database load,go to theInstance Overview page or theQuery insights dashboard inthe Google Cloud console.

Instance overview page

Troubleshoot high database load with AI assistancein theInstance overview page by using the following steps:

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

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. In theOverview page, from theChart menu, select a metric for the database. You can select any metric.
4. Optional: To select a specific analysis time period, use theTime 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, click and select a portion of the chart.


5. ClickAnalyze instance performance to start troubleshooting high database load with AI assistance. This generates theAnalyzing database load page.

Query insights dashboard

Troubleshoot high database load with AI assistancein theQuery insights dashboard using the following steps:

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

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. ClickQuery insights to open theQuery insights dashboard.
4. Optional: Use theTime 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 higher database load by query execution time. To zoom in, click and select a portion of the chart.

5. In theDatabase load chart, clickAnalyze instance performance to start troubleshooting high database load with AI assistance. This generates theAnalyzing database load page.

Analyze high database load

Using AI assistance, you can analyze and troubleshoot the details of yourdatabase load.

In theAnalyzing database load page, you can view following details foryour Cloud SQL instance:

• Analysis time period
• CPU utilization (p99)
• Memory utilization (p99)

Cloud SQL displays aTransactions/sec chartwhere you can look at the transactional activity during the selected timeperiod. You can check for sudden spikes in activity during a particulartime period.

Analysis time period

Cloud SQL analyzes your database for the time period thatyou select in your database load chart from theQuery insights dashboardor theInstance overview page. If you select a time period of lessthan 24 hours, then Cloud SQL analyzes the entire time period.If you select a time period greater than 24 hours, thenCloud SQL selects only the last 24 hours of the time periodfor analysis.

To calculate the baseline performance analysis of your database, Cloud SQLincludes 24 hours of a baseline time period in its analysis time period.If your selected time period occurs on aday other than Monday, then Cloud SQLuses a baseline time period of the24 hours previous to your selected time period.If your selected time period occurs on a Monday, then Cloud SQLuses a baseline time period of the7th day previous to your selected time period.

Situation

When Cloud SQL starts the analysis, Cloud SQLchecks for significant changes in the following key metrics:

• Queries per second (QPS)
• CPU
• Memory
• Disk I/O

Cloud SQL compares the baseline aggregated data for your databasewithin the performance data of your analysis time window. IfCloud SQL detects a significant change in threshold for a keymetric, then Cloud SQL indicates a possible situationwith your database. The identified situation might explain a root causefor the high load on your database over the selected time period.

For example, one situation for why your database is experiencing highload might be identified asLock contention.

During analysis, Cloud SQL might determine there'sbeen a significant increase in lock-wait ratio.Cloud SQL might list other situations where key metricsindicate a significant increase. For example, you might also see thefollowing situations listed:

• Contention on system resources
• Insufficient buffer
• Excessive logging

Evidence

For each situation, Cloud SQL provides a list of evidenceto support the finding. Cloud SQL bases evidence onmetrics gathered from the instance.

Each situation has supporting evidence that's used to detect anomalies insystem performance. Cloud SQL detects an anomaly when systemperformance surpasses certain thresholds or meets specific time-bound criteria.Cloud SQL defines these thresholds or criteria for each situation.

To support the situation ofLock contention, you might seethe following pieces of evidence:

• Lock wait ratio: A 40,786.04% increase in lock wait ratio compared tobaseline observation period detected.

To view the evidence retrieved during analysis, click each situation.The evidence appears in the pane next to its corresponding situation.

Recommendations

Based on all of the situations analyzed, Cloud SQL provides you withone or more actionable recommendations to help remediate the problems of your highdatabase load. Cloud SQL presents the recommendations with acost-benefit analysis so you can make an informed decision on whether to implementthe recommendation.

For some situations, based on the analysis, there might not a recommendation.

For example, you might receive the following recommendation:

• Identify blockers: Identify potential blocking queries and review themfor optimization opportunities.

To find out how to implement this first recommendation, click theLearn more link.

If you want to continue troubleshooting or get more assistance with system performance,then you can also openGemini Cloud Assist.For more information, seeObserve and troubleshoot with AI assistance.

What's next

• Monitor instances
• Optimize high CPU usage
• Optimize high memory usage
• Use system insights to improve system performance