This page describes how to use the Query insights dashboard todetect and analyze performance problems with your queries.

Introduction

Query insights helps you detect, diagnose, and prevent query performance problemsfor Cloud SQL databases. It supports intuitive monitoring and providesdiagnostic information that helps you go beyond detection to identify theroot cause of performance problems.

With query insights, you can monitor performance at an application level andtrace the source of a problematic query across the application stack by model,view, controller, route, user, and host. The query insights tool can integratewith your existing application monitoring (APM) tools and Google Cloudservices by using open standards and APIs. This way, you can monitor andtroubleshoot query problems by using your favorite tool.

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

• View the database load for top queries
• Identify a potentially problematic query or tag
• Examine the query or tag to identify issues
• Examine a trace generated by a sample query

Query insights for Cloud SQL Enterprise Plus edition

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

• Capture and analyze wait events for all executed queries.
• Filter aggregated database load by additional dimensions such as queries, tags, wait event types, and more.
• Capture query plans for all executed queries.
• Sample up to 200 query plans per minute.
• Capture longer query text up to 1 MB.
• Obtain near real-time updates for metrics (in the order of seconds).
• Maintain a longer 30-day retention of metrics.
• Obtain index recommendations from index advisor.
• Terminate a session or long-running transaction in active queries.
• Access AI-assisted troubleshooting (Preview).

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

Enable query insights for Cloud SQL Enterprise Plus edition

To enable query insights for Cloud SQL Enterprise Plus edition, selectEnable Enterprise Plus features when youEnable query insights on your Cloud SQL Enterprise Plus edition instance.

Pricing

Sign up to try Cloud SQL at no charge.

There's no additional cost for query insights on Cloud SQL Enterprise edition orCloud SQL Enterprise Plus edition instances.

Storage requirements

Query insights for Cloud SQL Enterprise edition doesn't occupy any storage space in your Cloud SQLinstance storage space. Metrics are stored in Cloud Monitoring. For API requests,see Cloud MonitoringPricing.Cloud Monitoring has a tier that you can use at no additional cost.

Query insights for Cloud SQL Enterprise Plus editionstores metrics data on the same disk attached toyour Cloud SQL instance and requires that youkeep the automatic storage increases setting enabled.

Metrics storage limitations

The following limitations apply to query insights for Cloud SQL Enterprise Plus edition instances:

• If your instance is undergoing heavy system load, then when you querymetrics data in theQuery insights dashboard, your queries might be slow toload or time out.
• If you recreate a read replica, then the recreated read replicadoesn't maintain its previous metrics history.
• If you restore an instance with an old backup, then you can lose themetrics between the time of your backup and the time that you restore theinstance for query insights for Cloud SQL Enterprise Plus edition.For example, if you restoreyour instance on April 30 with a backup taken on April 25, then you mightlose all the metrics between April 25 and April 30.

Before you begin

Before you use query insights, do the following.

1. Add required roles and permissions.
2. Enable the Cloud Trace API.
3. If you're using Query insights for Cloud SQL Enterprise Plus edition, then make sure thatEnable automatic storage increases isenabled for the instance.

Required roles and permissions

To use query insights, you need to grant a predefined role,create a custom role, or provide a user accountwith the required Identity and Access Management permissions.

For more information about granting roles, seeManage 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, seeManage 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 theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

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

Enable the Cloud Trace API

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

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

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

   Go to APIs and Services

2. ClickEnable APIs and Services.
3. In the search bar, enterCloud Trace API.
4. IfAPI enabled is displayed, then this API is enabled and there's nothing for you to do. Otherwise, clickEnable.

Enable automatic storage increases

If you're using query insights for Cloud SQL Enterprise Plus edition, then make sure that the instance settingtoenable automatic storage increasesremains enabled. By default, this option is enabled for Cloud SQLinstances.

If you previously disabled this instance setting and want toenable query insights for Cloud SQL Enterprise Plus edition, then re-enable automatic storage increases first.You can't turn off automatic storage increases and enable query insights for Cloud SQL Enterprise Plus edition.

Enable query insights

gcloudsqlinstancespatchINSTANCE_ID\--insights-config-query-insights-enabled

gcloudsqlinstancespatchINSTANCE_ID\--insights-config-query-insights-enabled\--insights-config-query-string-length=INSIGHTS_CONFIG_QUERY_STRING_LENGTH\--insights-config-query-plans-per-minute=QUERY_PLANS_PER_MINUTE\--insights-config-record-application-tags\--insights-config-record-client-address\--tier=API_TIER_STRING\--region=REGION

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID

{  "settings" : {     "insightsConfig" : {       "queryInsightsEnabled" : true,       "recordClientAddress" : true,       "recordApplicationTags" : true,       "queryStringLength" : 1024,       "queryPlansPerMinute" : 20,   }  }}

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/v1/projects/PROJECT_ID/instances/INSTANCE_ID"

$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/v1/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2025-03-28T22:43:40.009Z",  "operationType": "UPDATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_ID",  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

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 resourcesettings{tier="DB_TIER"insights_config{query_insights_enabled=truequery_string_length=2048# Optionalrecord_application_tags=true# Optionalrecord_client_address=true# Optionalquery_plans_per_minute=10# Optional}}}

Metrics are expected to be available in query insights within minutes of querycompletion. Review theCloud Monitoring dataretention policy.

Query insights traces are stored in Cloud Trace. Review theCloud Trace dataretention policy.

View the Query insights dashboard

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

To open theQuery insights dashboard, follow these steps:

1. To open theOverview page of an instance, click the instance name.
2. In the Cloud SQL navigation menu, clickQuery insights, or clickGo to Query insights for more in-depth info on queries and performance on theInstance overview page.

TheQuery insights dashboard opens. Depending on whether you're usingquery insights for Cloud SQL Enterprise edition or query insights for Cloud SQL Enterprise Plus edition, the Query insights dashboardshows the following information aboutyour instance:

Cloud SQL Enterprise Plus edition

• All queries: Shows database load for all queries for the selected time range. Each query is individually color-coded. To view a point in time for a specific query, hold the pointer over the chart for the query.
• Database: 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 1 hour, 6 hours, 1 day, 7 days, 30 days, or a custom range.
• Wait event types: Filters query load by CPU and Lock wait event types.
• Queries,Wait event types,Databases,Users,Tags, andClient addresses: Sort by the top dimensions that contribute the most to database load in the chart. SeeFilter the database load.

Cloud SQL Enterprise edition

• Database: 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 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.
• CPU capacity, CPU and CPU wait, IO wait, and Lock wait: Filters loads based on the options that you select. SeeView the database load for top queries for details about each of these filters.
• Queries andTags: Filters query load by either a selected query or a selected SQL query tag. SeeFilter the database load.

Cloud SQL Enterprise Plus edition

Cloud SQL Enterprise edition

Colored lines in the graph show the query load, split into 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. SeeCloud 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. SeeCloud SQL metrics for more information.

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.
• 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 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.
• Which database is experiencing the load? Select different databases from the Databases drop-down menu to find the databases with the highest loads.
• 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

• All queries

• Wait event types

• Databases

• Users

• Tags

• Client addresses

To customize the database load chart, select a dimensionfrom theDatabase 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 theTop dimensions by database load table.TheTop dimensions by database load table displays the top contributors forthe time period and dimension that you select in theDatabase Load by execution time chart drop-down. You can modify thetime period or dimension to view the top contributors for a different dimensionor time period.

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

Filter by queries

TheTop queries table provides an overview of the queries that cause themost query load. The table shows all the normalized queries for the time rangeand options selected on the Query insights dashboard. It sorts queries by thetotal execution time during the time range that you selected.

Cloud SQL Enterprise Plus edition

To sort the table, select a column heading.

Cloud SQL Enterprise edition

To sort the table, select a column heading or a property fromFilter queries.

The table shows the following properties:

• Query: the normalized query string. By default, query insights shows only 1024 characters in the query string. Queries labeledUTILITY COMMAND usually includeBEGIN,COMMIT, andEXPLAIN commands or wrapper commands.
• Database: the database against which the query was run.
• Recommendations: the suggested recommendations, such asCreate indexes, to improve query performance.
• 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.
• % load by Queries: The percentage load by individual query.
• Analyze latency: if you enabledAI-assisted troubleshooting (Preview) for this instance, then you can click this link to troubleshoot slow queries.
• 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. Youcanenable query insights to collect this informationand, when required, disable collection.

Query plan traces don't collect or store any constant values and removes anyPII information that the constant might show.

Query insights displays normalized queries, that is,? replacesthe literal constant value. In the following example, the name constant isremoved 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 mustfirstadd tags to your SQL queries.Query load tags provide a breakdown of the query load of the selected tag overtime.

Query insights provides application-centric monitoring to diagnose performanceproblems for applications built using ORMs. If you're responsible for the entireapplication stack, query insights provides query monitoring from an applicationview. Query tagging helps you find issues at higher-level constructs, such aswith 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 queryload that the various business logic create. For example, you might observeunexpected events, such as spikes for a business analytics tag at 1 PM orunexpected growth for a payment service trending over the previous week.

To calculate theDatabase load for tag, query insights uses the amountof time taken by every query that uses the tag that you select. The toolcalculates the completion time at the minute boundary by using wall-clock time.

On the query insights dashboard, to view the tags table, selectTags.The table sorts tags by their total load by total time.

You can sort the table by selecting a property fromFilter tags, or byclicking 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.

View query details for a specific query or tag

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

1. To sort the list in descending order, click theLoad 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.

TheQuery details page opens and shows the details of the selectedquery or tag.

Examine a specific query load

TheQuery details page for a selected query appears as follows:

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

As with the total queries graph, you can filter the load for a specific query byDatabase,User, andClient address. Query load is split intoCPUcapacity,CPU and CPU wait,IO wait, andLock wait.

Examine a specific tagged query load

The dashboard for a selected tag appears as follows. For example, if allqueries from a microservices payment are tagged aspayment, you cansee the amount of query load that's trending by viewing the tagpayment.

TheDatabase load — specific tags graph shows a measure ofthe work (in CPU seconds) that queries matching your selected tags haveperformed in your selected database over time. As with the total queries graph,you can filter the load for a specific tag byDatabase,User,andClient address.

Examine operations in a sampled query plan

Aquery plan takes asample of your query and breaks it down into individual operations. It explainsand analyzes each operation in the query.

MySQL 5.7 provides an estimated query plan with the EXPLAIN view whereas MySQL8.0 and later versions provide an executed query plan with the EXPLAIN ANALYZEview. An estimated query plan gives the estimated execution time of a querywhereas an executed query plan provides real-time information of each executionstep of a given query.

Query plans in MySQL 5.7 are supported for the following queries that aresupported 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 byEXPLAIN ANALYZE.This tool shows you where MySQL spends time on your SELECT queries for singletables and multiple tables. Cloud SQL doesn't support the generation ofmulti-table Data Manipulation Language (DML) query plans because these queriesaren't supported by EXPLAIN ANALYZE.

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

TheQuery plan samples graph shows all the query plans running atparticular times and the amount of time each plan took to run. You can changethe rate at which query plan samples are captured per minute. SeeEnable Query Insights.

By default, the panel on the right shows the details for the sample query planthat takes the longest time, as visible on theQuery plan samples graph.To see the details for another sample query plan, click the relevant circle onthe 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 theoperation. When you select an operation, you can see more details, such asshared 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 a trace generated by a sample query

In addition to viewing thesample query plan, you can use query insightsto view an in-context, end-to-end application trace for a sample query. Thistrace can help you identify the source of a problematic query by displayingdatabase activity for a specific request. Also, log entries that the applicationsends to Cloud Logging during the request are linked to the trace, whichhelps you with your investigation.

To view the in-context trace, do the following:

1. In theSample Query screen, click theEnd-to-end Trace tab. This tab displays a Gantt chart that details the spans, which are records of individual operations, for the trace generated by the query.
2. To view more details about each span, such as attributes and metadata, select the span.

You can also view the trace in theTrace Explorer page. To do so,clickView in Cloud Trace. For details about how to use theTrace Explorer page to explore your trace data, seeFind and explore traces.

Examine latency

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

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

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

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

• What's causing the high load? Select options to look at the CPU capacity, CPU and CPU wait, I/O wait, or Lock wait.
• 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.
• Were there spikes in latency? Change the time window to study the historical latency for the normalized query.

Add tags to SQL queries

Tagging SQL queries simplifies application troubleshooting. You can usesqlcommenter to add tags toyour SQL queries either automatically or manually.

Use sqlcommenter with ORM

When you useORMinstead of directly writing SQL queries, you might not find application codethat's causing performance challenges. You might also have trouble analyzinghow your application code affects query performance. To tackle this issue, QueryInsights provides an open source library called sqlcommenter. Thislibrary is useful for developers and administrators using ORM tools todetect which application code is causing performance problems.

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

You can install sqlcommenter on the application server. Theinstrumentation library allows application information related to your MVCframework to be propagated to the database along with the queries as a SQLcomment. The database picks up these tags and starts recording and aggregatingstatistics by tags, which are orthogonal to statistics aggregated bynormalized queries. Query insights shows the tags so that you know whichapplication is causing the query load and can find theapplication code that's causing performance problems.

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

SELECT*fromUSERS/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 programminglanguages:

For more information about sqlcommenter and how to use it in your ORMframework, see thesqlcommenter documentation.

Use sqlcommenter to add tags

If you're not using ORM, you must manually add sqlcommenter tags or commentsin the correctSQL comment formatto your SQL query. You must also augment each SQL statement with a commentcontaining 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

gcloudsqlinstancespatchINSTANCE_ID\--no-insights-config-query-insights-enabled

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

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

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"

$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

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "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

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. ClickEdit.
4. In theCustomize your instance section, expandQuery insights.
5. Clear theEnable Enterprise Plus features checkbox.
6. ClickSave.

• Launch blog:Solve database bottlenecks faster with the latest query insights for Cloud SQL Enterprise Plus edition
• SeeGoogle Cloud metrics.
• Blog:Boost your query performance troubleshooting skills with Cloud SQL Insights
• Video:Introducing Cloud SQL Insights
• Podcast:Cloud SQL Insights
• Insights Codelab
• Optimize high CPU usage
• Optimize high memory usage
• Blog:Introducing Sqlcommenter: An open source ORM auto-instrumentation library
• Blog:Enable query tagging with Sqlcommenter