Monitor active queries

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 monitor and troubleshoot the queries that are active in your database. Monitoring these queries can help identify causes of system latency and high CPU usage.

On the Query insights dashboard, you can view a summary of active transactions and a list of transactions with associated query and statistics. The transactions are displayed by the transaction start time. If there is a large number of queries running, the results may be limited to a subset of total queries.

Before you begin

Create a custom role and add the following permissions. Then, grant the role to each user account that needs to use query insights.

Tasks	Roles	Permissions
View database activity summary and long running transactions	databaseinsights.viewer	databaseinsights.activeQueries.fetch databaseinsights.activitySummary.fetch
View instance activity summary	cloudsql.viewer	N/A
Terminate sessions	cloudsql.editor databaseinsights.operationsAdmin	N/A

Enable active queries

To enable active queries, complete the following 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. In the Configuration tile, click Edit configuration.
4. Expand the Query insights pane.
5. If the Enable query insights checkbox is cleared, then select it. Active queries is available when query insights is enabled.
6. Click Save.

View active queries

To view your active queries, complete the following steps:

1. Add this role for each user that is viewing active queries.

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

   Go to Cloud SQL Instances

3. To open the Overview page of an instance, click the instance name.

4. Do one of the following to display the Query insights dashboard:

   • Select the Query insights tab.
   • Click Go to Query insights for more in-depth info on queries and performance. The Query insights dashboard opens. It shows details about the instance at the top.

5. Click the Active queries tab.

   From here, you can modify the query information that appears in the Longest running transactions table:

   • Database: filters query load on a specific database or all databases.
   • User: filters query load from a specific user account.

View normalized active queries

You can view a list of top long-running transactions with normalized active queries on the Query insights dashboard. A normalized active query removes sensitive data and returns a digest. The digest is the same for different values used in the following example:

• Regular queries

  • select * from test_table where id=1;
  • select * from test_table_where id=2;

• Digest or normalized query

  • select * from test_table where id=?;

A query that runs in different sessions is displayed as different entries on the dashboard.

View top longest running transactions

The Longest running transactions table on the Query insights dashboard contains the following columns:

Column name	Description
Session ID	Unique identifier of the session..
Query	SQL query text.
State of session	The state of the session.
Query duration	How long the query has been running.
Wait event type	The type of wait event, if the request is blocked for query execution.
Blocking session ID	The id of the session that is blocking the session.
Transaction wait duration	The duration of the current wait, if the session is blocked.
Transaction rows modified	Number of rows returned to client by this query.
% complete	Percent of work completed, if the query reports progress.
Database	Database where the session is running.
Application name	Name of application that initiated the session.
User	Name of the user connected to the database.
Client address	The specific IP address of the client that sent a query.
Action	Contains a link for terminating a session.

The display is refreshed every 60 seconds automatically.

Terminate a session

To terminate a query or a transaction, you must have the databaseinsights.operationsAdmin role. For more information, see IAM basic and predefined roles reference.

To terminate a query or a transaction, query insights for Cloud SQL Enterprise Plus edition must also be enabled.

Long running operations may take longer to terminate.

To terminate a query or transaction, complete the following steps:

1. In the Longest running transaction table, select a query.
2. In the Action column, click Terminate session.
3. In the Terminate session window, click Confirm.

Note: Requires enabling query insights for Cloud SQL Enterprise Plus edition, which is available on Cloud SQL Enterprise Plus edition instances.

Note: If the Terminate button is disabled, you might have insufficient IAM permissions. For more information, see Before you begin.

What's next

• Use query insights
• Use system insights