Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Query and Database Monitoring

This document describes the Query and Database Monitoring page, an Amazon Web Services Management Console feature for analyzing the performance of an Amazon Redshift provisioned cluster or serverless workgroup, and the queries run against them.

You can investigate the following scenarios using the Queries and Database Monitoring page:

Permissions

The privileges of the Amazon Web Services account you use to access the console affects which queries the Query and Database Monitoring page shows. By default, you can only view your own queries. To view queries owned by other users, grant the SYS:MONITOR role to your account. To allow a user to end running queries from the Query and Database Monitoring page, grant the user the SYS:OPERATOR privilege.

To automatically assign the sys:monitor role to an IAM user or role for Amazon Redshift Serverless or provisioned, run the following commands:

create role monitor;
grant role sys:monitor to role monitor;

To update the IAM role used for query monitoring, do the following:

To add database credentials to a user, run the following command:

grant role sys:monitor to <username>

For information about using the GRANT command, see GRANT in the Amazon Redshift Database Developer Guide.

To use query monitoring, your IAM user needs permissions to access the Amazon Redshift data plane. Ensure that your IAM user has the following permissions in their permissions policy:

{
    "Sid": "DataAPIPermissions",
    "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:CancelStatement",
        "redshift-data:GetStatementResult",
        "redshift-data:DescribeStatement",
        "redshift-data:ListDatabases"
    ],
    "Effect": "Allow",
    "Resource": "arn:aws:redshift-serverless:us-west-2:123456789012:workgroup/01234567-89ab-cdef-0123-456789abcdef"
},

Temporary credentials using your IAM identity

This option is only available when connecting to a cluster. With this method, Query and Database Monitoring maps a user name to your IAM identity and generates a temporary password to connect to the database as your IAM identity. A user using this method to connect must be allowed IAM permission to redshift:GetClusterCredentialsWithIAM. To prevent users from using this method, modify their IAM user or role to deny this permission.

Query and Database Monitoring Console

This section describes using the Query and Database Monitoring console page.

You can use the Query and Database Monitoring console to quickly get an overview of your data warehouse's performance. You can monitor your data warehouse's performance over time, and examine the performance of a data warehouse's provisioned clusters or individual queries, so as to best identify bottlenecks and other areas to improve.

The Query and Database Monitoring page has the following features:

To access the Query and Database Monitoring page, do the following:

The Query and database monitoring console page appears as follows:

The Query and database monitoring page has the following components:

Query performance summary

When you choose Query performance summary from the Query and database monitoring page, or a cluster or workgroup from the Data warehouse overview page, the console shows a summary of the performance for an individual provisioned cluster or serverless workgroup.

This page has the following components:

Query details

When you choose a query from the Data warehouse performance or Query history pane of the cluster or workgroup detail page, the Query details page opens.

For information about the Query details page, see Query details page.

Query pattern

You can see a history of queries with the same pattern by choosing the View query pattern button in the Query history pane of the Query performance summary page. The Query pattern page shows all of the queries from the past week that are retrieved by a SQL statement that you specify.

The Query pattern page has the following components:

Using the Query pattern page, you can gain the following insights: