Monitoring queries and workloads with Amazon Redshift Serverless - Amazon Redshift
Monitoring queries and workload with Amazon Redshift Serverless
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).

Monitoring queries and workloads with Amazon Redshift Serverless

Monitoring queries and workload with Amazon Redshift Serverless

You can monitor your Amazon Redshift Serverless queries and workload with the provided system views.

Granting access to monitor queries

A superuser can provide access to users who aren't superusers so that they can perform query monitoring for all users. First, you add a policy for a user or a role to provide query monitoring access. Then, you grant query monitoring permission to the user or role.

To add the query monitoring policy

  1. Choose https://console.amazonaws.cn/iam/.

  2. Under Access management, choose Policies.

  3. Choose Create Policy.

  4. Choose JSON and paste the following policy definition.

    {
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
    "redshift-data:ExecuteStatement",
    "redshift-data:DescribeStatement",
    "redshift-data:GetStatementResult",
    "redshift-data:ListDatabases"
],
"Resource": "*"
},
{
"Effect": "Allow",
"Action": "redshift-serverless:GetCredentials",
"Resource": "*"
}
]
}

  5. Choose Review policy.

  6. For Name, enter a name for the policy, such as query-monitoring.

  7. Choose Create policy.

After you create the policy, you can grant the appropriate permissions.

To provide access, add permissions to your users, groups, or roles:

To grant query monitoring permission for a user

Users with sys:monitor permission can view all queries. In addition, users with sys:operator permission can cancel queries, analyze query history, and perform vacuum operations.

  1. Enter the following command to provide system monitor access, where user-name is the name of the user for whom you want to provide access.

    grant role sys:monitor to "IAM:user-name";

  2. (Optional) Enter the following command to provide system operator access, where user-name is the name of the user for whom you want to provide access.

    grant role sys:operator to "IAM:user-name";
To grant query monitoring permission for a role

Users with a role that has sys:monitor permission can view all queries. In addition, users with a role that has sys:operator permission can cancel queries, analyze query history, and perform vacuum operations.

  1. Enter the following command to provide system monitor access, where role-name is the name of the role for which you want to provide access.

    grant role sys:monitor to "IAMR:role-name";

  2. (Optional) Enter the following command to provide system operator access, where role-name is the name of the role for which you want to provide access.

    grant role sys:operator to "IAMR:role-name";

Monitoring views

Monitoring views are system views in Amazon Redshift Serverless that are used to monitor query and workload usage. These views are located in the pg_catalog schema. The system views available have been designed to give you the information needed to monitor Amazon Redshift Serverless, which is much simpler than that needed for provisioned clusters. The SYS system views have been designed to work with Amazon Redshift Serverless. To display the information provided by these views, run SQL SELECT statements.

System views are defined to support the following monitoring objectives.

Workload monitoring

You can monitor your query activities over time to:

  • Understand workload patterns, so you know what is normal (baseline) and what is within business service level agreements (SLAs).

  • Rapidly identify deviation from normal, which might be a transient issue or something that warrants further action.

Data load and unload monitoring

Data movement in and out of Amazon Redshift Serverless is a critical function. You use COPY and UNLOAD to load or unload data, and you must monitor progress closely in terms of bytes/rows transferred and files completed to track adherence to business SLAs. This is normally done by running system table queries frequently (that is, every minute) to track progress and raise alerts for investigation/corrective action if significant deviations are detected.

Failure and problem diagnostics

There are cases where you must take action for query or runtime failures. Developers rely on system tables to self-diagnose issues and determine correct remedies.

Performance tuning

You might need to tune queries that are not meeting SLA requirements either from the start, or have degraded over time. To tune, you must have runtime details including run plan, statistics, duration, and resource consumption. You need baseline data for offending queries to determine the cause for deviation and to guide you how to improve performance.

User objects event monitoring

You need to monitor actions and activities on user objects, such as refreshing materialized views, vacuum, and analyze. This includes system-managed events like auto-refresh for materialized views. You want to monitor when an event ends if it is user initiated, or the last successful run if system initiated.

Usage tracking for billing

You can monitor your usage trends over time to:

  • Inform budget planning and business expansion estimates.

  • Identify potential cost-saving opportunities like removing cold data.

You can't query STL, STV, SVCS, SVL, and some SVV system tables and views with Amazon Redshift Serverless, except the following:

You can query the following SYS system views to monitor Amazon Redshift Serverless.