Use Query Insights to improve query performance

MySQL   |  PostgreSQL   |  SQL Server

This page describes how to use the Query Insights dashboard to detect and analyze performance problems.

You can use Gemini in Databases assistance to help you observe and troubleshoot your Cloud SQL for MySQL resources. For more information, see Observe and troubleshoot with Gemini assistance.

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.

With Query Insights, you can monitor performance at an application level and trace the source of a problematic query across the application stack by model, view, controller, route, user, and host. The Query Insights tool can integrate with your existing application monitoring (APM) tools and Google Cloud services by using open standards and APIs. This way, you can monitor and troubleshoot query problems by using your favorite tool.

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

1. View the database load for top queries.
2. Identify a potentially problematic query or tag.
3. Examine the query or tag to identify issues.
4. Trace the source of the problem.

Query Insights is supported on all Cloud SQL machine types and available in all Google Cloud regions.

Query Insights is available for MySQL 5.7 and later versions.

Pricing

There's no additional cost for Query Insights. You can access one week of data on the Query Insights dashboard.

Note: By default, Query Insights displays data only from completed queries, unless you are monitoring active queries as part of Gemini in Databases. To monitor all queries running in real time, see Monitor active queries. Gemini in Databases is in Preview.

Query Insights doesn't occupy any storage space in your Cloud SQL instance storage space. Metrics are stored in Cloud Monitoring. For API requests, see the Cloud Monitoring Pricing. Cloud Monitoring has a tier that you can use at no additional cost.

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 for free.

Before you begin

To view a query plan or perform end-to-end tracing, you need specific IAM permissions. Create a custom role and add the cloudtrace.traces.get IAM permission to it. Then, add this role to each user account that needs to use Query Insights.

To view query plans and their end-to-end views, your Google Cloud project must have the Trace API enabled. This setting lets your Google Cloud project receive trace data from authenticated sources at no additional cost. This data can help you detect and diagnose performance issues in your instance.

To confirm that the Trace API is enabled, follow these steps:

1. From the Google Cloud console, go to APIs and Services:

   Go to APIs and Services

2. Click Enable APIs and Services.
3. In the search bar, enter Trace API.
4. If API enabled is displayed, then this API is enabled and there's nothing for you to do. Otherwise, click Enable.

Enable Query Insights

Query Insights metrics are encrypted at rest. Users who have access to the Cloud SQL dashboard can access Query Insights metrics on the Query Insights dashboard. If you have permission to update instances, you can configure 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 Configuration options section, expand Query insights.
5. Select the Enable Query insights checkbox.
6. Optional: Select one or more of the following Query Insights options:

Store client IP addresses

Default: false

Stores the client IP addresses where queries are coming from and helps you group that data to run metrics against it. Queries come from more than one host. Reviewing graphs for queries from client IP addresses can help identify the source of a problem.

Store application tags

Default: false

Stores application tags that help you determine the APIs and model-view-controller (MVC) routes that are making requests and group the data to run metrics against it. This option requires you to comment queries with a specific set of tags using the sqlcommenter open source object-relational mapping (ORM) auto-instrumentation library. This information helps Query Insights identify the source of a problem and the MVC from which the problem is coming. Application paths help you with application monitoring.

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. You can still add tags to queries that exceed the length limit.

Set the maximum sampling rate

Default: 5

Sets the maximum sampling rate. Sampling rate is the number of executed query plan samples that are captured per minute across all databases on the instance. Change this value to a number from 0 (to disable sampling) to 20. Increasing the sampling rate is likely to give you more data points but might increase performance overhead.

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 box, select the Enable Query insights for multiple instances check box.
5. Click Enable.
6. In the subsequent dialog box, 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-record-client-address

  Stores the client IP addresses where queries are coming from and helps you group that data to run metrics against it. Queries come from more than one host. Reviewing graphs for queries from client IP addresses can help identify the source of a problem.

• --insights-config-record-application-tags

  Stores application tags that help you determine the APIs and model-view-controller (MVC) routes that are making requests and group the data to run metrics against it. This option requires you to comment queries with a specific set of tags. You can do this by using the sqlcommenter open source object-relational mapping (ORM) auto-instrumentation library. This information helps Query Insights identify the source of a problem and the MVC the problem is coming from. Application paths help you with application monitoring.

• --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. You can still add tags to queries that exceed the length limit.

• --query_plans_per_minute

  By default, a maximum of 5 executed query plan samples are captured per minute across all databases on the instance. Change this value to a number from 0 (to disable sampling) to 20. Increasing the sampling rate is likely to give you more data points but might add a performance overhead.

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 \
--query_plans_per_minute=QUERY_PLANS_PER_MINUTE \
--insights-config-record-application-tags \
--insights-config-record-client-address \
--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:

resource "google_sql_database_instance" "INSTANCE_NAME" {
 name                = "INSTANCE_NAME"
 database_version    = "MYSQL_VERSION"
 region              = "REGION"
 root_password       = "PASSWORD"
 deletion_protection = false # set to true to prevent destruction of the resource
 settings {
   tier = "DB_TIER"
   insights_config {
     query_insights_enabled  = true
     query_string_length     = 2048 # Optional
     record_application_tags = true # Optional
     record_client_address   = true # Optional
     query_plans_per_minute  = 10 # Optional
   }
 }
}

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. Review the Cloud Monitoring data retention policy. Query Insights traces are stored in Cloud Trace. Review the Cloud Trace data retention policy.

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.
• User: Filters query load from a specific user account.
• Client address: Filters query load from a specific IP address.
• Time range: Filters query load by time ranges, such as hour, day, week, month, or a custom range.
• Database load graph: Displays the query load graph, based on filtered data.
• CPU capacity, CPU and CPU wait, IO wait, and Lock wait: Filters loads based on the options that you select. See View the database load for top queries for details about each of these filters.
• Queries and tags. Filters query load by either a selected query or a selected SQL query tag. See Filter the database load.

Note: If you enable Gemini in Databases, the data retention policy for query metrics is 30 days. 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 — all top queries graph. Drop-down menus on the dashboard let you filter the graph for a specific database, user, or client address.

Colored lines in the graph show the query load, split into four categories:

• CPU capacity: The number of CPUs available on the instance.

• CPU and CPU Wait: The ratio of the time taken by queries in an active state to wall-clock time. IO and Lock waits don't block queries that are in an active state. This metric might mean that the query is either using the CPU or waiting for the Linux scheduler to schedule the server process running the query while other processes are using the CPU.

  Note: CPU load accounts for both the runtime and the time waiting for the Linux scheduler to schedule the server process that's running. As a result, the CPU load can go beyond the maximum core line.

• IO Wait: The ratio of time taken by queries that are waiting for IO to wall-clock time. IO wait includes Read IO Wait and Write IO Wait.

  If you want a breakdown of information for IO waits, you can see it in Cloud Monitoring. See Cloud SQL metrics for more information.

• Lock Wait: The ratio of time taken by queries that are waiting for Locks to wall-clock time. It includes Lock Waits, LwLock Waits, and Buffer pin Lock waits. To see a breakdown of information for lock waits, use Cloud Monitoring. See Cloud SQL metrics for more information.

Review the graph and use the filtering options to explore these questions:

1. 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.
2. 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.
3. What's causing the high load? You can select options to examine the CPU capacity, CPU and CPU wait, Lock wait, or IO wait. The graph for each of these options is a different color so that you can easily spot the one with the highest load. The dark blue line on the graph shows the maximum CPU capacity of the system. It lets you compare the query load with the maximum CPU system capacity. This comparison helps you determine whether an instance is running out of CPU resources.
4. Which database is experiencing the load? Select different databases from the Databases drop-down menu to find the databases with the highest loads.
5. Do specific users or IP addresses cause higher loads? Select different users and addresses from the drop-down menus to identify the ones that are causing higher loads.

Filter the database load

You can filter the database load by queries or tags.

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 or a property from Filter queries. The table shows the following properties:

• Query: The normalized query string. Query Insights shows only 1024 characters in the query string by default.

  Queries labeled UTILITY COMMAND usually include BEGIN, COMMIT, and EXPLAIN commands or wrapper commands.

• Database: The database against which the query was run.

• Load by total time/Load by CPU/Load by IO wait/Load by lock wait: The options by which you can filter specific queries to find the largest load.

• Avg execution time (ms): The average 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.

• Avg rows scanned: The average number of rows scanned for the query.

Query Insights stores and displays only normalized queries. By default, Query Insights doesn't collect IP addresses or tag information. You can enable Query Insights to collect this information and, when required, disable collection. Query plan traces don't collect or store any constant values and removes any PII information that the constant might show.

Query Insights displays normalized queries, that is, ? replaces the literal constant value. In the following example, the name constant is removed and ? replaces it.

UPDATE
  "demo_customer"
SET
  "customer_id" = ?::uuid,
  "name" = ?,
  "address" = ?,
  "rating" = ?,
  "balance" = ?,
  "current_city" = ?,
  "current_location" = ?
WHERE
  "demo_customer"."id" = ?

Filter by query tags

To troubleshoot an application, you must first add tags to your SQL queries. Query load tags provide a breakdown of the query load of the selected tag over time.

Query Insights provides application-centric monitoring to diagnose performance problems for applications built using ORMs. If you're responsible for the entire application stack, Query Insights provides query monitoring from an application view. Query tagging helps you find issues at higher-level constructs, such as with the business logic or a microservice.

You might tag queries by the business logic, for example, the payment, inventory, business analytics, or shipping tags. You can then find the query load that the various business logic create. For example, you might observe unexpected events, such as spikes for a business analytics tag at 1 PM or abnormal growth for a payment service trending over the previous week.

To calculate the Database load for tag, Query Insights uses the amount of time taken by every query that uses the tag that you select. The tool calculates the completion time at the minute boundary by using wall-clock time.

On the Query Insights dashboard, to view the tags table, select Tags. The table sorts tags by their total load by total time.

You can sort the table by selecting a property from Filter tags, or by clicking a column heading. The table shows the following properties:

• Action, Controller, Framework, Route, Application, DB driver: Each property that you added to your queries appears as a column. At least one of these properties must be added if you want to filter by tags.
• Load by total time/Load by CPU/Load by IO wait/Load by lock wait: Options to filter specific queries to find the largest load for each option.
• Avg execution time (ms): The average time for the query to run.
• Avg rows returned: The average number of rows returned for the query.
• Avg rows scanned: The average number of rows scanned for the query.
• Times called: The number of times the application called the query.
• Database: The database against which the query was run.

Examine a specific query or tag

To determine whether a query or a tag is the root cause of the problem, do the following from the Queries tab or Tags tab, respectively:

1. To sort the list in descending order, click the Load by total time header.
2. Click the query or tag at the top of the list. It has the highest load and is taking more time than the others.

A dashboard opens showing the details of the selected query or tag.

Examine a specific query load

The dashboard for a selected query appears as follows:

The Database load — specific query graph shows a measure of the work (in CPU seconds) that your normalized query has performed in your selected query over time. To calculate load, it uses the amount of time taken by the normalized queries that are completed at the minute boundary to the wall-clock time. At the top of the table, the first 1024 characters of the normalized query, with literals removed for aggregation and PII reasons, are displayed.

As with the total queries graph, you can filter the load for a specific query by Database, User, and Client address. Query load is split into CPU capacity, CPU and CPU wait, IO wait, and Lock wait.

Examine a specific tagged query load

The dashboard for a selected tag appears as follows. For example, if all queries from a microservices payment are tagged as payment, you can see the amount of query load that's trending by viewing the tag payment.

The Database load — specific tags graph shows a measure of the work (in CPU seconds) that queries matching your selected tags have performed in your selected database over time. As with the total queries graph, you can filter the load for a specific tag by Database, User, and Client address.

Examine operations in a sampled query plan

A query plan takes a sample of your query and breaks it down into individual operations. It explains and analyzes each operation in the query.

MySQL 5.7 provides an estimated query plan with the EXPLAIN view whereas MySQL 8.0 and later versions provide an executed query plan with the EXPLAIN ANALYZE view. An estimated query plan gives the estimated execution time of a query whereas an executed query plan provides real-time information of each execution step of a given query.

Query plans in MySQL 5.7 are supported for the following queries that are supported by the EXPLAIN statement:

• For single tables: INSERT, SELECT, UPDATE, and DELETE.

• For multiple tables: SELECT, UPDATE, and DELETE.

You can generate query plans in MySQL 8.0 and later for all queries supported by EXPLAIN ANALYZE. This tool shows you where MySQL spends time on your SELECT queries for single tables and multiple tables. Cloud SQL doesn't support the generation of multi-table Data Manipulation Language (DML) query plans because these queries aren't supported by EXPLAIN ANALYZE.

The Sample Query Plan provides an EXPLAIN ANALYZE view for the query plan samples that are related to the normalized query. These are executed query plans that provide a breakdown of the active time taken by each operation in the query plan.

The Query plan samples graph shows all the query plans running at particular times and the amount of time each plan took to run. You can change the rate at which query plan samples are captured per minute. See Enable Query Insights.

By default, the panel on the right shows the details for the sample query plan that takes the longest time, as visible on the Query plan samples graph. To see the details for another sample query plan, click the relevant circle on the graph. Expanded details show a model of all the operations in the query plan. Each operation shows the latency, rows returned, and the cost of the operation. When you select an operation, you can see more details, such as shared hit blocks, the type of schema, loops, and plan rows.

Try to narrow down the problem by looking into the following questions:

1. What's the resource consumption?
2. How does it relate to other queries?
3. Does consumption change over time?

Examine latency

Latency is the time taken for the normalized query to complete, in wall-clock time. You use the Latency graph to examine latency on the query or tag. The latency dashboard shows the 50th, 95th, and 99th percentile latencies to find outlier behaviors.

The following image shows the database load graph at the 50th percentile for a specific query with filters selected for CPU capacity, CPU and CPU wait, IO wait, and Lock wait.

The latency of parallel queries is measured in wall-clock time even though the query load can be higher for the query due to multiple cores being used to run part of the query.

Try to narrow down the problem by looking into the following questions:

1. What's causing the high load? Select options to look at the CPU capacity, CPU and CPU wait, I/O wait, or Lock wait.
2. How long has the load been high? Is it only high now? Or has it been high for a long time? Change the time range to find the date and time that the load started performing poorly.
3. Were there spikes in latency? Change the time window to study the historical latency for the normalized query.

Trace the source of the problem

When you find the areas and times where the load was the highest, identify the source of the problem by using tracing to drill down further.

To help you identify the specific source of the problem, such as a model, view, controller, route, host, or user, Query Insights provides an in-context end-to-end application trace view. This view helps you understand what's going on at the Database Layer for a specific request and to find the source of a problematic query by model, view, controllers, and route.

If you enable OpenCensus or OpenTelemetry, opencensus span information is sent to the database along with the tag information inside SQL comments. Any traces from the application to Cloud Logging are linked with database query plan traces to help identify the source of the problem.

Click the End to end tab in the Sample Query screen to look at the in-context trace.

To determine the client and user causing the problem, use the Top client addresses and Top users tables to find the highest loads. You can add a user or IP address to the filter to further analyze a specific user or client address. The details in the tables include the percentage of the query load, the average execution time in milliseconds, and the times called.

You can use Cloud Trace to see end-to-end tracing for each step in the query plan. On the Query Insights dashboard, click the View in trace link to open the Cloud Trace tool. The trace graph shows all the traces that have been run for the selected period.

For details, see Finding and viewing traces.

Add tags to SQL queries

Tagging SQL queries simplifies application troubleshooting. You can use sqlcommenter to add tags to your SQL queries either automatically or manually.

Use sqlcommenter with ORM

When you use ORM instead of directly writing SQL queries, you might not find application code that's causing performance challenges. You might also have trouble analyzing how your application code affects query performance. To tackle this issue, Query Insights provides an open source library called sqlcommenter. This library is useful for developers and administrators using ORM tools to detect which application code is causing performance problems.

If you're using ORM and sqlcommenter together, the tags are automatically created. You don't need to add or change code in your application.

You can install sqlcommenter on the application server. The instrumentation library allows application information related to your MVC framework to be propagated to the database along with the queries as a SQL comment. The database picks up these tags and starts recording and aggregating statistics by tags, which are orthogonal to statistics aggregated by normalized queries. Query Insights shows the tags so that you know which application is causing the query load and can find the application code that's causing performance problems.

When you examine results in SQL database logs, they appear as follows:

SELECT * from USERS /*action='run+this',
controller='foo%3',
traceparent='00-01',
tracestate='rojo%2'*/

Supported tags include the controller name, route, framework, and action.

The set of ORM tools in sqlcommenter is supported for the following programming languages:

Python	Django psycopg2 Sqlalchemy Flask
Java	Hibernate Spring
Ruby	Rails
Node.js	Knex.js Sequelize.js Express.js

For more information about sqlcommenter and how to use it in your ORM framework, see the sqlcommenter documentation.

Use sqlcommenter to add tags

If you're not using ORM, you must manually add sqlcommenter tags or comments in the correct SQL comment format to your SQL query. You must also augment each SQL statement with a comment containing a serialized key-value pair. Use at least one of the following keys:

• action=''
• controller=''
• framework=''
• route=''
• application=''
• db driver=''

Query Insights drops all other keys.

Disable Query Insights

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"
}

What's next

• Launch blog: Database observability for developers: introducing Cloud SQL Insights

• See Google Cloud metrics.

• Blog: Boost your query performance troubleshooting skills with Cloud SQL Insights
• Video: Introducing Cloud SQL Insights
• Podcast: Cloud SQL Insights
• Insights Codelab
• Blog: Introducing Sqlcommenter: An open source ORM auto-instrumentation library
• Blog: Enable query tagging with Sqlcommenter