Use query insights to improve query performance

MySQL   |  PostgreSQL   |  SQL Server

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 page describes how to use the Query insights dashboard to detect and analyze performance problems.

Introduction

Query insights helps you detect, diagnose, and prevent query performance problems for Cloud SQL databases. It supports intuitive monitoring and provides diagnostic information that helps you go beyond detection to identify the root cause of performance problems.

Query insights helps you improve Cloud SQL query performance by guiding you through the following steps:

• View the database load for top queries
• Identify a potentially problematic query

Query insights for Cloud SQL Enterprise Plus edition

If you're using Cloud SQL Enterprise Plus edition, then you can access additional capabilities in query insights to perform advanced query performance diagnostics. In addition to the standard capabilities of the Query insights dashboard, query insights for Cloud SQL Enterprise Plus edition lets you do the following:

• Capture longer query text up to 20 KB
• Maintain a longer 30-day retention of metrics
• Obtain index recommendations from index advisor
• Terminate a session in active queries

The following table compares functional requirements and capabilities of query insights for Cloud SQL Enterprise edition with query insights for Cloud SQL Enterprise Plus edition.

Comparison area	Query insights for Cloud SQL Enterprise edition	Query insights for Cloud SQL Enterprise Plus edition
Supported database versions	All versions of the following instances: SQL Server Web SQL Server Standard SQL Server Enterprise Query insights isn't supported on SQL Server Express edition instances	SQL Server Enterprise version 2019 or 2022
Supported machine types	Supported on all machine types	Not supported on instances that use a shared core machine type
Supported regions	Cloud SQL regional locations	Cloud SQL Enterprise Plus edition regional locations
Metrics retention period	7 days	30 days
Query length limit maximum	4500 bytes	20 KB
Index advisor recommendations	Not available	Available
Terminate sessions in active queries	Not available	Available

To enable query insights for Cloud SQL Enterprise Plus edition during Preview for your Cloud SQL Enterprise Plus edition instance, follow the steps in Enable query insights for Cloud SQL Enterprise Plus edition.

Pricing

You can create an account to evaluate how Cloud SQL performs in real-world scenarios. New customers also get $300 in free credits to spend on Cloud SQL to run, test, and deploy workloads. You won't be charged until you upgrade.

Sign up to try Cloud SQL at no charge.

There's no additional cost for query insights. In addition, there's no cost for enabling query insights for Cloud SQL Enterprise Plus edition, which is in Preview.

Note: By default, query insights displays data only from completed queries, unless you're monitoring active queries. To monitor all queries that are running in real time, see Monitor active queries. Active queries is in Preview.

Storage requirements

Query insights stores metrics data on the instance and requires that you keep the automatic storage increases setting enabled. Applicable storage fees apply.

Before you begin

Before you use query insights, do the following.

1. Add required roles and permissions.
2. Make sure that Enable automatic storage increases is enabled for the instance.

Required roles and permissions

To use query insights, you need to grant roles with the required Identity and Access Management permissions or provide a user account with the required permissions.

For more information about granting roles, see Manage access.

To get the permissions that you need to access historical query execution data in the Query insights dashboard, ask your administrator to grant you the following IAM roles on the project that hosts the Cloud SQL instance:

• Database Insights Monitoring Viewer (roles/databaseinsights.monitoringViewer)
• Cloud SQL Viewer (roles/cloudsql.viewer)

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to access historical query execution data in the Query insights dashboard. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to access historical query execution data in the Query insights dashboard:

• databaseinsights.aggregatedStats.query
• databaseinsights.timeSeries.query

You might also be able to get these permissions with custom roles or other predefined roles.

For example, in Database Insights, you can ask your administrator to grant you the Database Insights Viewer (roles/databaseinsights.viewer) predefined role. Then, in Cloud SQL, you can ask your administrator to grant you one of the following predefined roles:

• Cloud SQL Editor (roles/cloudsql.editor)
• Cloud SQL Admin (roles/cloudsql.admin)

Enable automatic storage increases

Make sure that the instance setting to enable automatic storage increases remains enabled.

If you previously disabled this instance setting, then re-enable automatic storage increases before you enable query insights.

Enable query insights

Note: Query insights metrics are encrypted at rest.

Users who have access to the Cloud SQL dashboard can access query insights metrics. If you have permission to update instances, then you can enable query insights. For a list of permissions required for Cloud SQL instances, see Cloud SQL project access control. If you don't have these permissions and you want to enable query insights on your instances, then contact your administrator.

Note: When you enable query insights, all other operations are temporarily suspended. These operations include health checks, logging, monitoring, and other instance operations. However, the instance itself doesn't restart, and you still have uninterrupted access to your database.

Console

Note: If you don't have query insights enabled on any of your instances, then you can enable query insights by clicking Enable or Enable for multiple instances on the Query insights dashboard.

Enable query insights for an instance

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. On the Configuration tile, click Edit configuration.
4. In the Customize your instance section, expand Query insights.
5. Select the Enable Query insights checkbox.
6. Optional. Select one or more of the following query insights additional features:

Customize query lengths

Default: 1024

Sets the query length limit to a specified value from 256 bytes to 4500 bytes. Higher query lengths are more useful for analytical queries, but they also require more memory. Changing the query length requires you to restart the instance.

7. Click Save.

Enable query insights for multiple instances

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

   Go to Cloud SQL Instances

2. Click the More Actions menu on any row.
3. Select Enable Query insights.
4. In the dialog, select the Enable Query insights for multiple instances checkbox.
5. Click Enable.
6. In the subsequent dialog, select the instances for which you want to enable query insights.
7. Click Enable Query insights.
   Note: This step enables query insights on your instances based on the default configuration.

gcloud

To enable query insights for a Cloud SQL instance by using gcloud, run gcloud sql instances patch with the --insights-config-query-insights-enabled flag as follows after replacing INSTANCE_ID with the ID of the instance.

    gcloud sql instances patch INSTANCE_ID \
    --insights-config-query-insights-enabled
  

Also, use one or more of the following optional flags:

• --insights-config-query-string-length

  Sets the default query length limit to a specified value from 256 to 4500 bytes. The default query length is 1024 bytes. Higher query lengths are more useful for analytical queries, but they also require more memory. Changing the query length requires you to restart the instance.

Replace the following:

• INSIGHTS_CONFIG_QUERY_STRING_LENGTH: The query string length to be stored, in bytes.
• API_TIER_STRING: The custom instance configuration to use for the instance.
• REGION: The region for the instance.

gcloud sql instances patch INSTANCE_ID \
--insights-config-query-insights-enabled \
--insights-config-query-string-length=INSIGHTS_CONFIG_QUERY_STRING_LENGTH \
--tier=API_TIER_STRING \
--region=REGION
  

REST v1

To enable query insights for a Cloud SQL instance by using the REST API, call the instances.patch method with insightsConfig settings.

Before using any of the request data, make the following replacements:

• project-id: The project ID.
• instance-id: The instance ID.

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings" : { "insightsConfig" : { "queryInsightsEnabled" : true } }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell, which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2021-01-28T22:43:40.009Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Terraform

To use Terraform to enable query insights for a Cloud SQL instance, set the query_insights_enabled flag to true.

Also, you can use one or more of the following optional flags:

• query_string_length: Default is 1024 and you can configure it to a value between 256 and 4500 in bytes.
• record_application_tags: Set the value to true if you want to record application tags from the query.
• record_client_address: Set the value to true if you want to record the client IP address.
• query_plans_per_minute: Default is 5 and you can configure it to a value between 5 and 20.

Here's an example:

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project.

Metrics are expected to be available in query insights within minutes of query completion.

Enable query insights for Cloud SQL Enterprise Plus edition

You can enable query insights for Cloud SQL Enterprise Plus edition on your Cloud SQL instance only by using the Google Cloud console.

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 Edit.
4. In the Storage section, verify that Enable automatic storage increases checkbox is selected.
5. In the Customize your instance section, expand Query insights.
6. Select the Enable Enterprise Plus features checkbox.

7. After you enable query insights for Cloud SQL Enterprise Plus edition, you can update the following fields:

   • Customize query lengths: specify the limit, in bytes, of the query length. You can specify a number from 256 to 20480. Any query string that goes above the specified limit becomes truncated in the display. A higher query length limit requires more memory. The default value is 10240 bytes (10 KB).

8. Click Save.

View the Query insights dashboard

The Query insights dashboard shows the query load based on factors that you select. Query load is a measurement of the total work for all the queries in the instance in the selected time range. The dashboard provides a series of filters that help you view query load.

To open the Query insights dashboard, follow these steps:

1. To open the Overview page of an instance, click the instance name.
2. Either select the Query insights tab in the left navigation panel or click the Go to Query insights for more in-depth info on queries and performance link.

The Query insights dashboard opens. It shows the following information about your instance:

• Databases: Filters query load on a specific database or all databases.
• Time range: Filters query load by time ranges, such as 1 hour, 6 hours, 1 day, 7 days, 30 days, or a custom range.
• Database load graph: Displays the query load graph, based on filtered data.
• Queries and Databases: Filters query load by either a selected query or a selected database. See Filter the database load.

Note: The data retention policy for query metrics is 7 days for query insights for Cloud SQL Enterprise edition and 30 days for query insights for Cloud SQL Enterprise Plus edition. 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.

View the database load for all queries

Database query load is a measure of the work (in CPU seconds) that the executed queries in your selected database perform over time. Each running query is either using or waiting for CPU resources, IO resources, or lock resources. Database query load is the ratio of the amount of time taken by all the queries that are completed in a given time window to the wall-clock time.

The top-level query insights dashboard shows the Database load by execution time graph. Drop-down menus on the dashboard let you filter the graph for all databases or a specific database.

Colored lines in the graph show per database load by execution time. Review the graph and use the filtering options to explore these questions:

• Is the query load high? Is the graph spiking or elevated over time? If you don't see a high load, then the problem isn't with your query.
• How long has the load been high? Is it high only now or has it been high for a long time? Use the range selector to select various time periods to find out how long the problem has lasted. Zoom in to view a time window where query load spikes are observed. Zoom out to view up to one week of the timeline.
• Which database is experiencing the load? Select different databases from the Databases drop-down menu to find the databases with the highest loads.

Filter the database load

You can filter the database load by queries. If you're using query insights for Cloud SQL Enterprise Plus edition, then you can customize the database load chart to break down the displayed data using any of the following dimensions:

• All queries

• Databases

To customize the database load chart, select a dimension from the Database Load by execution time drop-down.

View the top contributors to database load

To view the top contributors to the database load, you can use the Top dimensions by database load table. The Top dimensions by database load table displays the top contributors for the time period and dimension that you select in the Database Load by execution time chart drop-down. You can modify the time period or dimension to view the top contributors for a different dimension or time period.

In the Top dimensions by data load table, you can select the following tabs.

Tab	Description
Queries	The table displays the top normalized queries by total execution time. For each query, the data shown in columns are listed as follows: Avg execution time (ms): average time for the query to execute. Total execution time (ms): the total execution time taken by the specific query. Avg rows returned: the average number of rows fetched for the query. Times called: the number of times the query was called by the application.
Databases	The table shows the list of top databases that contributed to the load during the chosen time period across all executed queries. Avg time spent in database (ms): average time the queries spent in the specific database. Total time spent in database (ms): the total execution time the queries spent in the specific database.

Filter by queries

The Queries table provides an overview of the queries that cause the most query load. The table shows all the normalized queries for the time window and options selected on the Query insights dashboard. It sorts queries by the total execution time during the time window that you selected.

To sort the table, select a column heading. The table shows the following properties:

• Query: the normalized query string. By default, Query insights shows only 1024 characters in the query string.
• Database: the database against which the query was run.
• Avg execution time (ms): The average time for the query to execute.
• Total execution time (ms): the total time for the query to execute.
• Times called: the number of times the application called the query.
• Avg rows returned: the average number of rows returned for the query.

Query insights stores and displays only normalized queries.

Disable query insights

Note: When you disable query insights, Cloud SQL doesn't delete any data from disk.

Console

To disable query insights for a Cloud SQL instance by using the Google Cloud console, follow these steps:

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. On the Configuration tile, click Edit configuration.
4. In the Configuration options section, expand Query insights.
5. Clear the Enable Query insights checkbox.
6. Click Save.

gcloud

To disable query insights for a Cloud SQL instance by using gcloud, run gcloud sql instances patch with the --no-insights-config-query-insights-enabled flag as follows, after replacing INSTANCE_ID with the ID of the instance.

gcloud sql instances patch INSTANCE_ID 

--no-insights-config-query-insights-enabled
    

REST

To disable query insights for a Cloud SQL instance by using the REST API, call the instances.patch method with queryInsightsEnabled set to false as follows.

Before using any of the request data, make the following replacements:

• project-id: The project ID.
• instance-id: The instance ID.

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings" : { "insightsConfig" : { "queryInsightsEnabled" : false } }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell, which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2021-01-28T22:43:40.009Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Disable query insights for Cloud SQL Enterprise Plus edition

To disable query insights for Cloud SQL Enterprise Plus edition, 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. Click Edit.
4. In the Customize your instance section, expand Query insights.
5. Clear the Enable Enterprise Plus features checkbox.
6. Click Save.

Note: If you clear the Enable Query insights checkbox, then you also turn off query insights for Cloud SQL Enterprise Plus edition.

What's next

• Launch blog: Database observability for developers: introducing Cloud SQL Insights
• Blog: Boost your query performance troubleshooting skills with Cloud SQL Insights
• Video: Introducing Cloud SQL Insights
• Podcast: Cloud SQL Insights
• Insights Codelab