Monitor active queries

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

To view your active queries, you must do the following:

  1. Set up Gemini in Databases.
  2. Create a custom role and add the following permissions:
    Tasks Roles Permissions
    View recommendations databaseinsights.viewer databaseinsights.activeQueries.fetch
    databaseinsights.activitySummary.fetch
    Terminate recommendations 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. The Active query analysis checkbox appears.
  6. If this checkbox is cleared, then select it.
  7. 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 query view 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.
    • Summary scorecard of all active queries: provides an overview of all the active queries by displaying the total connections based on the following parameters:
      1. Distribution of active connections categorized by connection state.
      2. Distribution of active transactions based on transaction state.
      3. Distribution of query durations.
    • Longest running transactions: provides an overview of the top 50 running queries (in the active and idle in transaction state) based on descending execution time. You can filter and sort active queries in the table.

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
Processlist ID Unique identifier of the connection or thread ID.
Query SQL query text.
Status Status of the connection.
Session duration Duration of the last session running.
Transaction duration Duration of the active transaction running.
Query duration Duration of the last query running in that transaction.
Wait event type Wait event type of the occurring wait event.
Wait event Occurring wait event.
Database Database name on which this connection is running.
Application name Application name on which this connection is running.
User name Name of the user connected to the database.
Client address Specific IP address of the client that sent a query.
Action Contains a link for terminating a transaction.

The display is refreshed every 60 seconds automatically.

Terminate a process

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 transaction, complete the following steps:

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

If the system successfully terminates the query or transaction, then a success message appears. The system also performs a rollback if needed.