Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Monitoring Amazon Redshift cluster performance Monitoring cluster performance Amazon Redshift provides performance metrics and data so that you can track the health and performance of your clusters and databases. In this section, we discuss the types of data that you can work with in Amazon Redshift, specifically in the Amazon Redshift console. The performance data that you can use in the Amazon Redshift console falls into two categories: + **Amazon CloudWatch metrics – **Amazon CloudWatch metrics help you monitor physical aspects of your cluster, such as CPU utilization, latency, and throughput. Metric data is displayed directly in the Amazon Redshift console. You can also view it in the CloudWatch console. Alternatively, you can consume it in any other way you work with metrics, such as with the AWS CLI or one of the AWS SDKs. + **Query/Load performance data – **Performance data helps you monitor database activity and performance. This data is aggregated in the Amazon Redshift console to help you easily correlate what you see in CloudWatch metrics with specific database query and load events. You can also create your own custom performance queries and run them directly on the database. Query and load performance data is displayed only in the Amazon Redshift console. It is not published as CloudWatch metrics. Performance data is integrated into the Amazon Redshift console, yielding a richer experience in the following ways: + Performance data associated with a cluster is displayed contextually when you view a cluster, where you might need it to make decisions about the cluster such as resizing. + Some performance metrics are displayed in more appropriately scaled units in the Amazon Redshift console as compared to CloudWatch. For example, `WriteThroughput`, is displayed in GB/s (as compared to bytes/s in CloudWatch), which is a more relevant unit for the typical storage space of a node. + You can easily display performance data for the nodes of a cluster together on the same graph. This way, you can easily monitor the performance of all nodes of a cluster. You can also view performance data for each node. Amazon Redshift provides performance data (both CloudWatch metrics and query and load data) at no additional charge. Performance data is recorded every minute. You can access historical values of performance data in the Amazon Redshift console. For detailed information about using CloudWatch to access the Amazon Redshift performance data that is exposed as CloudWatch metrics, see [What is CloudWatch?](https://docs.aws.amazon.com/AmazonCloudWatch/latest/DeveloperGuide/WhatIsCloudWatch.html.html) in the *Amazon CloudWatch User Guide*. # Performance data in Amazon Redshift Performance data Using CloudWatch metrics for Amazon Redshift, you can get information about your cluster's health and performance and see information at the node level. When working with these metrics, keep in mind that each metric has one or more dimensions associated with it. These dimensions tell you what the metric is applicable to, that is the scope of the metric. Amazon Redshift has the following two dimensions: + Metrics that have a `NodeID` dimension are metrics that provide performance data for nodes of a cluster. This set of metrics includes leader and compute nodes. Examples of these metrics include `CPUUtilization`, `ReadIOPS`, `WriteIOPS`. + Metrics that have only a `ClusterIdentifier` dimension are metrics that provide performance data for clusters. Examples of these metrics include `HealthStatus` and `MaintenanceMode`. **Note** In some metric cases, a cluster-specific metric represents an aggregation of node behavior. In these cases, take care in the interpretation of the metric value because the leader node's behavior is aggregated with the compute node. For general information about CloudWatch metrics and dimensions, see [CloudWatch concepts](https://docs.aws.amazon.com/AmazonCloudWatch/latest/DeveloperGuide/cloudwatch_concepts.html) in the *Amazon CloudWatch User Guide*. For a further description of CloudWatch metrics for Amazon Redshift, see the following sections. **Topics** + [ ## Amazon Redshift metrics ](#redshift-metrics) + [ ## Dimensions for Amazon Redshift metrics ](#metrics-filterable-dimensions) + [ ## Amazon Redshift query and load performance data ](#custom-metrics-listing) ## Amazon Redshift metrics The `AWS/Redshift` namespace includes the following metrics. Unless stated otherwise, metrics are collected at 1-minute intervals. | Metric | Description | | --- | --- | | CommitQueueLength | The number of transactions waiting to commit at a given point in time. Units: Count Dimensions: `ClusterIdentifier` | | ConcurrencyScalingActiveClusters | The number of concurrency scaling clusters that are actively processing queries at any given time. Units: Count Dimensions: `ClusterIdentifier` | | ConcurrencyScalingSeconds | The number of seconds used by concurrency scaling clusters that have active query processing activity. Units: Count Dimensions: `ClusterIdentifier` | | CPUUtilization | The percentage of CPU utilization. For clusters, this metric represents an aggregation of all nodes (leader and compute) CPU utilization values. Units: Percent Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | DatabaseConnections | The number of database connections to a cluster. Units: Count Dimensions: `ClusterIdentifier` | | HealthStatus | Indicates the health of the cluster. Every minute the cluster connects to its database and performs a simple query. If it is able to perform this operation successfully, the cluster is considered healthy. Otherwise, the cluster is unhealthy. An unhealthy status can occur when the cluster database is under extremely heavy load or if there is a configuration problem with a database on the cluster. In Amazon CloudWatch, this metric is reported as 1 or 0 whereas in the Amazon Redshift console, this metric is displayed with the words `HEALTHY` or `UNHEALTHY` for convenience. When this metric is displayed in the Amazon Redshift console, sampling averages are ignored and only `HEALTHY` or `UNHEALTHY` are displayed. In Amazon CloudWatch, values different than 1 and 0 might occur because of sampling issue. Any value below 1 for `HealthStatus` is reported as 0 (`UNHEALTHY`). Units: Count (1/0) (`HEALTHY`/`UNHEALTHY` in the Amazon Redshift console) Dimensions: `ClusterIdentifier` | | MaintenanceMode | Indicates whether the cluster is in maintenance mode. In Amazon CloudWatch, this metric is reported as 1 or 0 whereas in the Amazon Redshift console, this metric is displayed with the words `ON` or `OFF` for convenience. When this metric is displayed in the Amazon Redshift console, sampling averages are ignored and only `ON` or `OFF` are displayed. In Amazon CloudWatch, values different than 1 and 0 might occur because of sampling issues. Any value greater than 0 for `MaintenanceMode` is reported as 1 (`ON`). Units: Count (1/0) (`ON`/`OFF` in the Amazon Redshift console). Dimensions: `ClusterIdentifier` | | MaxConfiguredConcurrencyScalingClusters | Maximum number of concurrency scaling clusters configured from the parameter group. For more information, see [Amazon Redshift parameter groups](working-with-parameter-groups.md). Units: Count Dimensions: `ClusterIdentifier` | | NetworkReceiveThroughput | The rate at which the node or cluster receives data. Units: Bytes/Second (MB/s in the Amazon Redshift console) Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | NetworkTransmitThroughput | The rate at which the node or cluster writes data. Units: Bytes/Second (MB/s in the Amazon Redshift console) Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | PercentageDiskSpaceUsed | The percent of disk space used. Units: Percent Dimensions: `ClusterIdentifier` Dimensions: `ClusterIdentifier`, `NodeID` | | QueriesCompletedPerSecond | The average number of queries completed per second. Reported in 5-minute intervals. This metric isn't supported on single-node clusters. Units: Count/Second Dimensions: `ClusterIdentifier`, `latency` Dimensions: `ClusterIdentifier`, `wlmid` | | QueryDuration | The average amount of time to complete a query. Reported in 5-minute intervals. This metric isn't supported on single-node clusters. Units: Microseconds Dimensions: `ClusterIdentifier`, `NodeID`, `latency` Dimensions: `ClusterIdentifier`, `latency` Dimensions: `ClusterIdentifier`, `NodeID`, `wlmid` | | QueryRuntimeBreakdown | The total time queries spent running by query stage. Reported in 5-minute intervals. Units: Milliseconds Dimensions: ClusterIdentifier, NodeID, stage Dimensions: ClusterIdentifier, stage | | ReadIOPS | The average number of disk read operations per second. Units: Count/Second Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | ReadLatency | The average amount of time taken for disk read I/O operations. Units: Seconds Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | ReadThroughput | The average number of bytes read from disk per second. Units: Bytes (GB/s in the Amazon Redshift console) Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | RedshiftManagedStorageTotalCapacity | Total managed storage capacity. Units: Megabytes Dimensions: `ClusterIdentifier` | | TotalTableCount | The number of user tables open at a particular point in time. This total doesn't include Amazon Redshift Spectrum tables. Units: Count Dimensions: `ClusterIdentifier` | | WLMQueueLength | The number of queries waiting to enter a workload management (WLM) queue. Units: Count Dimensions: `ClusterIdentifier`, `service class` Dimensions: `ClusterIdentifier`, `QueueName` | | WLMQueueWaitTime | The total time queries spent waiting in the workload management (WLM) queue. Reported in 5-minute intervals. Units: Milliseconds. Dimensions: `ClusterIdentifier`, `QueryPriority` Dimensions: `ClusterIdentifier`, `wlmid` Dimensions: `ClusterIdentifier`, `QueueName` | | WLMQueriesCompletedPerSecond | The average number of queries completed per second for a workload management (WLM) queue. Reported in 5-minute intervals. This metric isn't supported on single-node clusters. Units: Count/Second Dimensions: `ClusterIdentifier`, `wlmid` Dimensions: `ClusterIdentifier`, `QueueName` | | WLMQueryDuration | The average length of time to complete a query for a workload management (WLM) queue. Reported in 5-minute intervals. This metric isn't supported on single-node clusters. Units: Microseconds Dimensions: `ClusterIdentifier`, `wlmid` Dimensions: `ClusterIdentifier`, `QueueName` | | WLMRunningQueries | The number of queries running from both the main cluster and concurrency scaling cluster per WLM queue. Units: Count Dimensions: `ClusterIdentifier`, `wlmid` Dimensions: `ClusterIdentifier`, `QueueName` | | WriteIOPS | The average number of write operations per second. Units: Count/Second Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | WriteLatency | The average amount of time taken for disk write I/O operations. Units: Seconds Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | WriteThroughput | The average number of bytes written to disk per second. Units: Bytes (GB/s in the Amazon Redshift console) Dimensions: `ClusterIdentifier`, `NodeID` Dimensions: `ClusterIdentifier` | | SchemaQuota | The configured quota for a schema. Units: Megabytes Dimensions: `ClusterIdentifier`, `Database`, `Schema` Periodic/Push: `Periodic` Frequency: `5 minutes` Stop criteria: Schema dropped or quota removed | | NumExceededSchemaQuotas | The number of schemas with exceeded quotas. Units: Count Dimensions: `ClusterIdentifier` Periodic/Push: `Periodic` Frequency: `5 minutes` Stop criteria: N/A | | StorageUsed | The disk or storage space used by a schema. Units: Megabytes Dimensions: `ClusterIdentifier`, `Database`, `Schema` Periodic/Push: `Periodic` Frequency: `5 minutes` Stop criteria: Schema dropped or quota removed | | PercentageQuotaUsed | The percentage of disk or storage space used relative to the configured schema quota. Units: Percent Dimensions: `ClusterIdentifier`, `Database`, `Schema` Periodic/Push: `Periodic` Frequency: `5 minutes` Stop criteria: Schema dropped or quota removed | | UsageLimitAvailable | Depending on the FeatureType, UsageLimitAvailable returns the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/metrics-listing.html) Units: Minutes or TBs Dimensions: `ClusterIdentifier`, `FeatureType`, `UsageLimitId` | | UsageLimitConsumed | Depending on the FeatureType, UsageLimitConsumed returns the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/metrics-listing.html) Units: Minutes or TBs Dimensions: `ClusterIdentifier`, `FeatureType`, `UsageLimitId` | ## Dimensions for Amazon Redshift metrics Amazon Redshift data can be filtered along any of the dimensions in the table following. | Dimension | Description | | --- | --- | | latency | Possible values are as follows: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/metrics-listing.html) | | NodeID | Filters requested data that is specific to the nodes of a cluster. `NodeID` is either "Leader", "Shared", or "Compute-N" where N is 0, 1, ... for the number of nodes in the cluster. "Shared" means that the cluster has only one node, that is the leader node and compute node are combined. Metrics are reported for the leader node and compute nodes only for `CPUUtilization`, `NetworkTransmitThroughput`, and `ReadIOPS`. Other metrics that use the `NodeId` dimension are reported only for compute nodes. | | ClusterIdentifier | Filters requested data that is specific to the cluster. Metrics that are specific to clusters include `HealthStatus`, `MaintenanceMode`, and `DatabaseConnections`. General metrics for this dimension (for example, `ReadIOPS`) that are also metrics of nodes represent an aggregate of the node metric data. Take care in interpreting these metrics because they aggregate behavior of leader and compute nodes. | | service class | The identifier for a `WLM` service class. | | stage | The execution stages for a query. The possible values are as follows: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/metrics-listing.html) | | wlmid | The identifier for a workload management queue. | | QueryPriority | The priority of the query. Possible values are `CRITICAL`, `HIGHEST`, `HIGH`, `NORMAL`, `LOW`, and `LOWEST`. | | QueueName | The name of the workload management queue. | | FeatureType | The feature that is limited by a usage limit. Possible values are `CONCURRENCY_SCALING`, `CROSS_REGION_DATASHARING`, and `SPECTRUM`. | | UsageLimitId | The identifier for a usage limit. | ## Amazon Redshift query and load performance data In addition to the CloudWatch metrics, Amazon Redshift provides query and load performance data. Query and load performance data can be used to help you understand the relation between database performance and cluster metrics. For example, if you notice that a cluster's CPU spiked, you can find the spike on the cluster CPU graph and see the queries that were running at that time. Conversely, if you are reviewing a specific query, metric data (like CPU) is displayed in context so that you can understand the query's impact on cluster metrics. Query and load performance data are not published as CloudWatch metrics and can only be viewed in the Amazon Redshift console. Query and load performance data are generated from querying with your database's system tables (for more information, see [System tables reference](https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_system-tables.html) in the *Amazon Redshift Developer Guide*). You can also generate your own custom database performance queries, but we recommend starting with the query and load performance data presented in the console. For more information about measuring and monitoring your database performance yourself, see [Managing performance](https://docs.aws.amazon.com/redshift/latest/dg/c-optimizing-query-performance.html) in the *Amazon Redshift Developer Guide.* The following table describes different aspects of query and load data you can access in the Amazon Redshift console. | Query/Load data | Description | | --- | --- | | Query summary | A list of queries in a specified time period. The list can be sorted on values such as query ID, query runtime, and status. View this data in the **Query monitoring** tab of the cluster detail page. | | Query detail | Provides details on a particular query including: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/metrics-listing.html) | | Load summary | Lists all the loads in a specified time period. The list can be sorted on values such as query ID, query runtime, and status. View this data in the **Query monitoring** tab of the cluster detail page. | | Load detail | Provides details on a particular load operation including: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/metrics-listing.html) | # Viewing performance data Viewing performance data In this section, you can find how to view performance data in the Amazon Redshift console, which includes information about cluster and query performance. Additionally, you can create alarms on cluster metrics directly from the Amazon Redshift console. When you view performance data in the Amazon Redshift console, you view it by cluster. The performance data graphs for a cluster are designed to give you access to data to answer your most common performance questions. For some performance data (see [Performance data in Amazon Redshift](metrics-listing.md)), you can also use CloudWatch to further customize your metrics graphs. For example, you can choose longer times or combine metrics across clusters. For more information about working with the CloudWatch console, see [Performance metrics in the CloudWatch console](using-cloudwatch-console.md). To learn how to monitor, isolate, and optimize your queries using the query monitoring features on the Amazon Redshift console, watch the following video. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/Wdvb5iYVnLg/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/Wdvb5iYVnLg) **Topics** + [ # Viewing cluster performance data ](performance-metrics-perf.md) + [ # Viewing query history data ](performance-metrics-query-history.md) + [ # Viewing database performance data ](performance-metrics-database-performance.md) + [ # Viewing workload concurrency and concurrency scaling data ](performance-metrics-concurrency-scaling.md) + [ # Viewing automatic optimization data ](performance-metrics-autonomics.md) + [ # Viewing queries and loads ](performance-metrics-queries.md) + [ # Viewing and analyzing query details ](performance-metrics-query-execution-details.md) + [ # Viewing cluster performance as queries run ](performance-metrics-query-cluster.md) + [ # Viewing cluster metrics during load operations ](performance-metrics-loads.md) + [ # Viewing the cluster workload breakdown chart ](analyze-workload-performance.md) # Viewing cluster performance data By using cluster metrics in Amazon Redshift, you can do the following common performance tasks: + Determine if cluster metrics are abnormal over a specified time range and, if so, identify the queries responsible for the performance hit. + Check if historical or current queries are impacting cluster performance. If you identify a problematic query, you can view details about it including the cluster performance during the query's execution. You can use this information in diagnosing why the query was slow and what can be done to improve its performance. **To view performance data** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the name of a cluster from the list to open its details. The details of the cluster are displayed, which can include **Cluster performance**, **Query monitoring**, **Databases**, **Datashares**, **Schedules**, **Maintenance**, and **Properties** tabs. 1. Choose the **Cluster performance** tab for performance information including the following: + **CPU utilization** + **Percentage disk space used** + **Database connections** + **Health status** + **Query duration** + **Query throughput** + **Concurrency scaling activity** Many more metrics are available. To see the available metrics and choose which are displayed, choose the **Preferences** icon. ## Cluster performance graphs The following examples show some of the graphs that are displayed in the new Amazon Redshift console. + **CPU utilization** – Shows the percentage of CPU utilization for all nodes (leader and compute). To find a time when the cluster usage is lowest before scheduling cluster migration or other resource-consuming operations, monitor this chart to see CPU utilization per individual or all of nodes. ![\[An image of the CPU utilization graph in the Amazon Redshift console that shows the percentage of CPU utilization for each node.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-cpu-utilization.png) + **Maintenance mode** – Shows whether the cluster is in the maintenance mode at a chosen time by using `On` and `Off` indicators. You can see the time when the cluster is undergoing maintenance. You can then correlate this time to operations that are done to the cluster to estimate its future downtimes for recurring events. ![\[An image of the graph in the Amazon Redshift console that shows whether maintenance mode is On or Off.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-maintenance-mode.png) + **Percentage disk space used** – Shows the percentage of disk space usage per each compute node, and not for the cluster as a whole. You can explore this chart to monitor the disk utilization. Maintenance operations like VACUUM and COPY use intermediate temporary storage space for their sort operations, so a spike in disk usage is expected. ![\[An image of the graph in the Amazon Redshift console that shows the percentage of disk space used for each node.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-percentage-disk-space-used.png) + **Read throughput** – Shows the average number of megabytes read from disk per second. You can evaluate this chart to monitor the corresponding physical aspect of the cluster. This throughput doesn't include network traffic between instances in the cluster and its volume. ![\[An image of the graph in the Amazon Redshift console that shows the read throughput for each node.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-read-throughput.png) + **Read latency** – Shows the average amount of time taken for disk read I/O operations per millisecond. You can view the response times for the data to return. When latency is high, it means that the sender spends more time idle (not sending any new packets), which reduces how fast throughput grows. ![\[An image of the graph in the Amazon Redshift console that shows the read latency for each node.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-read-latency.png) + **Write throughput** – Shows the average number of megabytes written to disk per second. You can evaluate this metric to monitor the corresponding physical aspect of the cluster. This throughput doesn't include network traffic between instances in the cluster and its volume. ![\[An image of the graph in the Amazon Redshift console that shows the write throughput for each node.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-write-throughput.png) + **Write latency** – Shows the average amount of time in milliseconds taken for disk write I/O operations. You can evaluate the time for the write acknowledgment to return. When latency is high, it means that the sender spends more time idle (not sending any new packets), which reduces how fast throughput grows. ![\[An image of the graph in the Amazon Redshift console that shows the write latency for each node.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-write-latency.png) + **Database connections** – Shows the number of database connections to a cluster. You can use this chart to see how many connections are established to the database and find a time when the cluster usage is lowest. ![\[Graph in the Amazon Redshift console that shows the number of database connections to a cluster.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-database-connections.png) + **Total table count** – Shows the number of user tables open at a particular point in time within a cluster. You can monitor the cluster performance when open table count is high. ![\[An image of the graph in the Amazon Redshift console that shows the total table count for a cluster.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-total-table-count.png) + **Health status** – Indicates the health of the cluster as `Healthy` or `Unhealthy`. If the cluster can connect to its database and performs a simple query successfully, the cluster is considered healthy. Otherwise, the cluster is unhealthy. An unhealthy status can occur when the cluster database is under extremely heavy load or if there is a configuration problem with a database on the cluster. ![\[An image of the graph in the Amazon Redshift console that shows the health status for a cluster.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-health-status.png) + **Query duration** – Shows the average amount of time to complete a query in microseconds. You can benchmark the data on this chart to measure I/O performance within the cluster and tune its most time-consuming queries if necessary. ![\[An image of the graph in the Amazon Redshift console that shows the query duration for a cluster.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-query-duration.png) + **Query throughput** – Shows the average number of completed queries per second. You can analyze data on this chart to measure database performance and characterize the ability of the system to support a multiuser workload in a balanced way. ![\[An image of the graph in the Amazon Redshift console that shows the query throughput for a cluster.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-query-throughput.png) + **Query duration per WLM queue** – Shows the average amount of time to complete a query in microseconds. You can benchmark the data on this chart to measure I/O performance per WLM queue and tune its most time-consuming queries if necessary. ![\[An image of the graph in the Amazon Redshift console that shows the query duration per WLM queue.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-query-duration-per-wlm-queue.png) + **Query throughput per WLM queue** – Shows the average number of completed queries per second. You can analyze data on this chart to measure database performance per WLM queue. ![\[An image of the graph in the Amazon Redshift console that shows the query throughput per WLM queue.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-query-throughput-per-wlm-queue.png) + **Concurrency scaling activity** – Shows the number of active concurrency scaling clusters. When concurrency scaling is enabled, Amazon Redshift automatically adds additional cluster capacity when you need it to process an increase in concurrent read queries. ![\[An image of the graph in the Amazon Redshift console that shows the number of active concurrency scaling clusters.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/cluster-performance-concurrency-scaling-activity.png) # Viewing query history data You can use query history metrics in Amazon Redshift to do the following: + Isolate and diagnose query performance problems. + Compare query runtime metrics and cluster performance metrics on the same timeline to see how the two might be related. Doing so helps identify poorly performing queries, look for bottleneck queries, and determine if you need to resize your cluster for your workload. + Drill down to the details of a specific query by choosing it in the timeline. When **Query ID** and other properties are displayed in a row below the graph, then you can choose the query to see query details. Details include, for example, the query's SQL statement, execution details, and query plan. For more information, see [Viewing and analyzing query details](performance-metrics-query-execution-details.md). + Determine if your load jobs complete successfully and meet your service level agreements (SLAs). **To display query history data** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the cluster name from the list to open its details. The details of the cluster are displayed, which can include **Cluster performance**, **Query monitoring**, **Databases**, **Datashares**, **Schedules**, **Maintenance**, and **Properties** tabs. 1. Choose the **Query monitoring** tab for metrics about your queries. 1. In the **Query monitoring** section, choose the **Query history** tab. Using controls on the window, you can toggle between **Query list** and **Cluster metrics**. When you choose **Query list**, the tab includes the following graphs: + **Query runtime** – The query activity on a timeline. Use this graph to see which queries are running in the same timeframe. Choose a query to view more query execution details. The x-axis shows the selected period. You can filter the graphed queries by running, completed, loads, and so on. Each bar represents a query, and the length of the bar represents its runtime from the start of the bar to the end. The queries can include SQL data manipulation statements (such as SELECT, INSERT, DELETE) and loads (such as COPY). By default, the top 100 longest running queries are shown for the selected time period. + **Queries and loads** – List of queries and loads that ran on the cluster. The window includes an option to **Terminate query** if a query is currently running. When you choose **Cluster metrics**, the tab includes the following graphs: + **Query runtime** – The query activity on a timeline. Use this graph to see which queries are running in the same timeframe. Choose a query to view more query execution details. + **CPU utilization** – The CPU utilization of the cluster by leader node and average of compute nodes. + **Storage capacity used** – The percent of the storage capacity used. + **Active database connections** – The number of active database connections to the cluster. Consider the following when working with the query history graphs: + Choose a bar that represents a specific query on the **Query runtime** chart to see details about that query. You can also, choose a query ID on **Queries and loads** list to see its details. + You can swipe to select a section of the **Query runtime** chart to zoom in to display a specific time period. + On the **Query runtime** chart, to have all data considered by your chosen filter, page forward through all pages listed on the **Queries and loads** list. + You can change which columns and the number of rows displayed on the **Queries and loads** list using the preferences window displayed by the **settings gear icon**. + The **Queries and loads** list can also be displayed by navigating from the left navigator **Queries** icon, **Queries and loads**. For more information, see [Viewing queries and loads](performance-metrics-queries.md). ## Query history graphs The following examples show graphs that are displayed in the new Amazon Redshift console. **Note** The Amazon Redshift console graphs only contain data for the latest 100,000 queries. + **Query runtime** ![\[Graph showing the query activity on a timeline to illustrate which queries are running in the same timeframe.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-history-query-runtime.png) + **Queries and loads** ![\[Table showing a list of queries and loads, with attributes such as start time, duration, status, and query ID as the columns.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-history-queries-and-loads.png) + **CPU utilization** ![\[Line graph showing the CPU utilization in increments of 15 minutes.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-history-cpu-utilization.png) + **Storage capacity used** ![\[Line graph showing the percentage of storage capacity used in increments of 15 minutes.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-history-storage-capacity-used.png) + **Active database connections** ![\[Line graph showing the number of active database connections in increments of 15 minutes.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-history-active-database-connections.png) # Viewing database performance data You can use database performance metrics in Amazon Redshift to do the following: + Analyze the time spent by queries by processing stages. You can look for unusual trends in the amount of time spent in a stage. + Analyze the number of queries, duration, and throughput of queries by duration ranges (short, medium, long). + Look for trends in the about of query wait time by query priority (Lowest, Low, Normal, High, Highest, Critical). + Look for trends in the query duration, throughput, or wait time by WLM queue. **To display database performance data** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the cluster name from the list to open its details. The details of the cluster are displayed, including **Cluster performance**, **Query monitoring**, **Databases**, **Datashares**, **Schedules**, **Maintenance**, and **Properties** tabs. 1. Choose the **Query monitoring** tab for metrics about your queries. 1. In the **Query monitoring** section, choose **Database performance** tab. Using controls on the window, you can toggle between **Cluster metrics** and **WLM queue metrics**. When you choose **Cluster metrics**, the tab includes the following graphs: + **Workload execution breakdown** – The time used in query processing stages. + **Queries by duration range** – The number of short, medium, and long queries. + **Query throughput** – The average number of queries completed per second. + **Query duration** – The average amount of time to complete a query. + **Average queue wait time by priority** – The total time queries spent waiting in the WLM queue by query priority. When you choose **WLM queue metrics**, the tab includes the following graphs: + **Query duration by queue** – The average query duration by WLM queue. + **Query throughput by queue** – The average number of queries completed per second by WLM queue. + **Query wait time by queue** – The average duration of queries spent waiting by WLM queue. ## Database performance graphs The following examples show graphs that are displayed in the new Amazon Redshift console. + **Workload execution breakdown** ![\[Vertical bar graph showing duration of query processing stages in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-workload-execution-breakdown.png) + **Queries by duration range** ![\[Vertical bar graph showing number of queries in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-queries-by-duration.png) + **Query throughput** ![\[Line graph showing the average query throughput in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-query-throughput.png) + **Query duration** ![\[Line graph showing the average query duration in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-query-duration.png) + **Average queue wait time by priority** ![\[Line graph showing the average queue wait time in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-queue-wait-by-priority.png) + **Query duration by queue** ![\[Line graph showing the average query duration by queue in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-query-duration-by-queue.png) + **Query throughput by queue** ![\[Line graph showing the average query throughput by queue in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-query-throughput-by-queue.png) + **Query wait time by queue** ![\[Line graph showing the average query wait time by queue in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-queue-wait-by-queue.png) # Viewing workload concurrency and concurrency scaling data By using concurrency scaling metrics in Amazon Redshift, you can do the following: + Analyze whether you can reduce the number of queued queries by enabling concurrency scaling. You can compare by WLM queue or for all WLM queues. + View concurrency scaling activity in concurrency scaling clusters. This can tell you if concurrency scaling is limited by the `max_concurrency_scaling_clusters`. If so, you can choose to increase the `max_concurrency_scaling_clusters` in the DB parameter. + View the total usage of concurrency scaling summed across all concurrency scaling clusters. **To display concurrency scaling data** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the cluster name from the list to open its details. The details of the cluster are displayed, which can include **Cluster performance**, **Query monitoring**, **Databases**, **Datashares**, **Schedules**, **Maintenance**, and **Properties** tabs. 1. Choose the **Query monitoring** tab for metrics about your queries. 1. In the **Query monitoring** section, choose **Workload concurrency** tab. The tab includes the following graphs: + **Queued vs. Running queries on the cluster** – The number of queries running (from the main cluster and concurrency scaling cluster) compared to the number of queries waiting in all WLM queues in the cluster. + **Queued vs. Running queries per queue** – The number of queries running (from the main cluster and concurrency scaling cluster) compared to the number or queries waiting in each WLM queue. + **Concurrency scaling activity** – The number of concurrency scaling clusters that are actively processing queries. + **Concurrency scaling usage** – The usage of concurrency scaling clusters that have active query processing activity. ## Workload concurrency graphs The following examples show graphs that are displayed in the new Amazon Redshift console. To create similar graphs in Amazon CloudWatch, you can use the concurrency scaling and WLM CloudWatch metrics. For more information about CloudWatch metrics for Amazon Redshift, see [Performance data in Amazon Redshift](metrics-listing.md). + **Queued vs. Running queries on the cluster** ![\[Vertical bar graph in increments of days, with each bar divided into two different colors, one showing the average queued queries on a cluster, and the other showing the average running queries on the same cluster.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/workload-concurrency-queued-vs-running-cluster.png) + **Queued vs. Running queries per queue** ![\[Vertical bar graph in increments of days showing the number of queries running compared to the number of queries waiting in each queue.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/workload-concurrency-queued-vs-running-per-queue.png) + **Concurrency scaling activity** ![\[Horizontal dot graph in increments of days showing the number of concurrency scaling clusters that are actively processing queries.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/workload-concurrency-concurrency-scaling-activity.png) + **Concurrency scaling usage** ![\[Horizontal line graph in increments of days showing the usage of concurrency scaling clusters that have active query processing activity.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/workload-concurrency-concurrency-scaling-usage.png) # Viewing automatic optimization data The Amazon Redshift console provides information about automatic optimizations, or autonomics, run using extra compute resources. You can use this information to track usage and monitor whether usage limits have been reached. Though Amazon Redshift doesn't bill you for autonomics run on the provisioned cluster itself, it does bill you for autonomics run using extra compute resources. For more information, see [ Allocating extra compute resources for automatic database optimizations](https://docs.aws.amazon.com/redshift/latest/dg/t_extra-compute-autonomics.html) in the *Amazon Redshift Database Developer Guide*. **To view extra compute autonomics data** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the name of a cluster from the list to open its details. 1. From the cluster's details page, select **Manage usage limit** from the **Actions** drop-down menu. You can also select the **Maintenance** tab for a cluster, then scroll down and select **Create usage limits**. 1. The graph showing extra compute autonomics data appears under the section titled **Usage limit for extra compute for automatic optimization**. The graph displays the amount of time that Amazon Redshift runs autonomics using extra compute resources in a given time period. # Viewing queries and loads The Amazon Redshift console provides information about queries and loads that run in the database. You can use this information to identify and troubleshoot queries that take a long time to process and that create bottlenecks preventing other queries from processing efficiently. You can use the queries information in the Amazon Redshift console to monitor query processing. **To display query performance data** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Queries and loads** to display the list of queries for your account. By default, the list displays queries for all your clusters over the past 24 hours. You can change the scope of the displayed date in the console. **Important** The **Queries and loads** list displays the longest running queries in the system, up to 100 queries. # Viewing and analyzing query details With a query identifier, you can view details of a query. Details can include, for example, the query's completion status, duration, SQL statement and whether it's a user query or one that was rewritten by Amazon Redshift. A *user query* is a query that is submitted to Amazon Redshift, either from an SQL client or generated by a business intelligence tool. Amazon Redshift might rewrite the query to optimize it, and this can result in multiple rewritten queries. Although the process is done by Amazon Redshift, you see the rewritten queries on the query details page along with the user query. **To view a query** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Queries and loads** to display the list of queries for your account. You might need to change settings on this page to find your query. 1. Choose the **Query** identifier in the list to display **Query details**. The **Query details** page includes **Query details** and **Query plan** tabs with metrics about the query. Metrics include details about a query such as start time, query ID, status, and duration. Other details include whether a query ran on a main cluster or a concurrency scaling cluster, and if it's a parent or rewritten query. # Viewing cluster performance as queries run You can monitor the performance of your clusters as queries run to identify potential bottlenecks and optimize query execution. Viewing cluster performance as queries run provides a real-time view of the system-level metrics, such as CPU utilization, disk I/O, and network traffic, as well as query-level details like execution time, data processed, and query steps. The following procedures guides you through accessing and interpreting the performance metrics to effectively manage and optimize your provisioned clusters. **To display cluster performance as queries run** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the cluster name from the list to open its details. The details of the cluster are displayed, which can include **Cluster performance**, **Query monitoring**, **Databases**, **Datashares**, **Schedules**, **Maintenance**, and **Properties** tabs. 1. Choose the **Query monitoring** tab for more details. For more information, see [Viewing query history data](performance-metrics-query-history.md). # Viewing cluster metrics during load operations When you view cluster performance during load operations, you can identify queries that are consuming resources and act to mitigate their effect. You can terminate a load if you don't want it to run to completion. **Note** The ability to terminate queries and loads in the Amazon Redshift console requires specific permission. If you want users to be able to terminate queries and loads, make sure to add the `redshift:CancelQuerySession` action to your AWS Identity and Access Management (IAM) policy. This requirement applies whether you select the **Amazon Redshift Read Only** AWS-managed policy or create a custom policy in IAM. Users who have the **Amazon Redshift Full Access** policy already have the necessary permission to terminate queries and loads. For more information about actions in IAM policies for Amazon Redshift, see [Managing access to resources](redshift-iam-access-control-overview.md#redshift-iam-accesscontrol-managingaccess). **To display cluster performance during load operations** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the cluster name from the list to open its details. The details of the cluster are displayed, which can include **Cluster performance**, **Query monitoring**, **Databases**, **Datashares**, **Schedules**, **Maintenance**, and **Properties** tabs. 1. Choose the **Query monitoring** tab for more details. 1. In the **Queries and loads** section, choose **Loads** to view the load operations of a cluster. If a load is running, you can end it by choosing **Terminate query**. # Viewing the cluster workload breakdown chart You can get a detailed view of your workload's performance by looking at the Workload execution breakdown chart in the console. We build the chart with data provided by the QueryRuntimeBreakdown metric. With this chart, you can see how much time your queries spend in the various processing stages, such as waiting and planning. **Note** The Workload execution breakdown chart isn't shown for single-node clusters. The following list of metrics describes the various processing stages: + `QueryPlanning`: Time spent parsing and optimizing SQL statements. + `QueryWaiting`: Time spent waiting in the workload management (WLM) queue. + `QueryExecutingRead`: Time spent running read queries. + `QueryExecutingInsert`: Time spent running insert queries. + `QueryExecutingDelete`: Time spent running delete queries. + `QueryExecutingUpdate`: Time spent running update queries. + `QueryExecutingCtas`: Time spent running CREATE TABLE AS queries. + `QueryExecutingUnload`: Time spent running unload queries. + `QueryExecutingCopy`: Time spent running copy queries. For example, the following graph in the Amazon Redshift console shows the amount of time that queries have spent in the plan, wait, read, and write stages. You can combine the findings from this graph with other metrics for further analysis. In some cases, your graph might show that queries with a short duration (as measured by the `QueryDuration` metric) are spending a long time in the wait stage. In these cases, you can increase the WLM concurrency rate for a particular queue to increase throughput. Following, is an example of the workload execution breakdown chart. In the chart, the y-axis value is the average duration of each stage at the specified time shown as a stacked bar graph. ![\[Vertical bar graph showing duration of query processing stages in increments of days.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/database-performance-workload-execution-breakdown.png) The following diagram illustrates how Amazon Redshift aggregates query processing for concurrent sessions. ![\[Horizonal bar graph showing session number on the x-axis and time on the y-axis. Each session is a horizontal bar that's divided into different query statuses.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/querybreakdownschematic.png) **To view the cluster workload breakdown chart** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Clusters**, then choose the cluster name from the list to open its details. The details of the cluster are displayed, which can include **Cluster performance**, **Query monitoring**, **Databases**, **Datashares**, **Schedules**, **Maintenance**, and **Properties** tabs. 1. Choose the **Query monitoring** tab for metrics about your queries. 1. In the **Query monitoring** section, choose **Database performance**, and choose **Cluster metrics**. The following metrics are graphed for the chosen time range as a stacked bar chart: + **Plan** time + **Wait** time + **Commit** time + **Execution** time # Analyzing query execution You can analyze the execution details of a query to understand how it performed and identify potential areas for optimization. Analyzing a query provides insights into the query plan, including the steps involved, the time taken by each step, and the amount of data processed. Common use cases include troubleshooting slow-running queries, optimizing data distribution strategies, and identifying opportunities for query rewriting or indexing. **To analyze a query** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Queries and loads** to display the list of queries for your account. You might need to change settings on this page to find your query. 1. Choose the **Query** identifier in the list to display **Query details**. The **Query details** page includes **Query details** and **Query plan** tabs with metrics about the query. **Note** You can also navigate to the **Query details** page from a **Cluster details** page, **Query history** tab when you drill down into a query in a **Query runtime** graph. The **Query details** page contains the following sections: + A list of **Rewritten queries**, as shown in the following screenshot. ![\[Table showing a list of rewritten queries, with attributes such as start time, duration, status, and query ID as the column names.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-details-rewritten-queries.png) + A **Query details** section, as shown in the following screenshot. ![\[The query details section in the console showing attributes for a specific query.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-details-query.png) + A **Query details** tab that contains the **SQL** that was run and **Execution details** about the run. + A **Query plan** tab that contains the **Query plan** steps and other information about the query plan. This table also contains graphs about the cluster when the query ran. + **Cluster health status** ![\[The cluster health status section of the console showing the cluster health during the workload.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-details-cluster-health-status.png) + **CPU utilization** ![\[The CPU utilization section in the console showing a line graph of CPU utilization of the cluster in increments of minutes.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-details-cpu-utilization.png) + **Storage capacity used** ![\[The storage capacity used section in the console showing a line graph of the percent of stoage capacity used in increments of minutes.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-details-storage-capacity-used.png) + **Active database connections** ![\[The active database connections section in the console showing a line graph of the number of active database connections to the cluster over time.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/query-details-active-database-connections.png) # Creating an alarm Alarms you create in the Amazon Redshift console are CloudWatch alarms. They are useful because they help you make proactive decisions about your cluster or serverless instance. You can set one or more alarms on any of the metrics listed in [Performance data in Amazon Redshift](metrics-listing.md). For example, setting an alarm for high `CPUUtilization` on a cluster node helps indicate when the node is overutilized. An alarm for high `DataStorage` would keep track of the storage space that your serverless namespace is using for your data. From **Actions**, you can modify or delete alarms. You can also create a chime or slack alert to send an alert from CloudWatch to Slack or Amazon Chime by specifying a Slack or Amazon Chime webhook URL. In this section, you can find how to create an alarm using the Amazon Redshift console. You can create an alarm using the CloudWatch console or any other way you work with metrics, such as with the AWS CLI or an AWS SDK. **To create a CloudWatch alarm with the Amazon Redshift console** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). If you're using Amazon Redshift Serverless, Choose **Go to Serverless ** on the upper right of the dashboard. 1. On the navigation menu, choose **Alarms**, then choose **Create alarm**. 1. On the **Create alarm** page, enter the properties to create a CloudWatch alarm. 1. Choose **Create alarm**. # Ending a running query You can also use the **Queries** page to end a query that is currently in progress. **Note** The ability to terminate queries and loads in the Amazon Redshift console requires specific permission. If you want users to be able to terminate queries and loads, make sure to add the `redshift:CancelQuerySession` action to your AWS Identity and Access Management (IAM) policy. This requirement applies whether you select the **Amazon Redshift Read Only** AWS managed policy or create a custom policy in IAM. Users who have the **Amazon Redshift Full Access** policy already have the necessary permission to terminate queries and loads. For more information about actions in IAM policies for Amazon Redshift, see [Managing access to resources](redshift-iam-access-control-overview.md#redshift-iam-accesscontrol-managingaccess). **To end a running query** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. On the navigation menu, choose **Queries and loads** to display the list of queries for your account. 1. Choose the running query that you want to end in the list, and then choose **Terminate query**. # Performance metrics in the CloudWatch console When working with Amazon Redshift metrics in the CloudWatch console, keep a couple of things in mind: + Query and load performance data is only available in the Amazon Redshift console. + Some Metrics in the CloudWatch have different units than those used in the Amazon Redshift console. For example, `WriteThroughput` is displayed in GB/s (as compared to Bytes/s in CloudWatch), which is a more relevant unit for the typical storage space of a node. When working with Amazon Redshift metrics in the CloudWatch console, command line tools, or an Amazon SDK, keep these concepts in mind: 1. First, specify the metric dimension to work with. A dimension is a name-value pair that helps you to uniquely identify a metric. The dimensions for Amazon Redshift are `ClusterIdentifier` and `NodeID`. In the CloudWatch console, the `Redshift Cluster` and `Redshift Node` views are provided to easily select cluster and node-specific dimensions. For more information about dimensions, see [Dimensions](https://docs.aws.amazon.com/AmazonCloudWatch/latest/DeveloperGuide/cloudwatch_concepts.html#Dimension) in the *CloudWatch Developer Guide*. 1. Then, specify the metric name, such as `ReadIOPS`. The following table summarizes the types of Amazon Redshift metric dimensions that are available to you. Depending on the metric, data is available in either 1-minute or 5-minute intervals at no charge. For more information, see [Amazon Redshift metrics](metrics-listing.md#redshift-metrics). | CloudWatch namespace | Dimension | Description | | --- | --- | --- | | AWS/Redshift | NodeID | Filters requested data that is specific to the nodes of a cluster. `NodeID` is either "Leader", "Shared", or "Compute-N" where N is 0, 1, ... for the number of nodes in the cluster. "Shared" means that the cluster has only one node, that is the leader node and compute node are combined. | | AWS/Redshift | ClusterIdentifier | Filters requested data that is specific to the cluster. Metrics that are specific to clusters include `HealthStatus`, `MaintenanceMode`, and `DatabaseConnections`. General metrics for this dimension (for example, `ReadIOPS`) that are also metrics of nodes represent an aggregate of the node metric data. Take care in interpreting these metrics because they aggregate behavior of leader and compute nodes. | Working with gateway and volume metrics is similar to working with other service metrics. Many of the common tasks are outlined in the CloudWatch documentation, including the following: + [View available metrics](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/viewing_metrics_with_cloudwatch.html) + [Get statistics for a metric](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/getting-metric-statistics.html) + [Creating CloudWatch alarms](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/AlarmThatSendsEmail.html) # Query profiler This document describes Query profiler, a graphical tool for analyzing a query's components and performance. Query profiler is a query monitoring and troubleshooting feature that's viewable through the Amazon Redshift console. It's helpful for analyzing query performance. Its primary purpose is to show a visual and graphical run order, execution plan, and statistics about a query, and make them easier to understand and troubleshoot. Query profiler helps you analyze the following types of query components: + **Child queries** – A child query is a portion of work from a query. Amazon Redshift may split a query into multiple child queries if it's more efficient than processing it as a single large query. In the profiler you can see the properties of each child query. A child query is made up of streams and additional sub-components. The types of child query that Query profiler shows typically include the following: + **Temp table query**: The text of this child query starts with the `CREATE TEMP TABLE` command. This child query creates temporary tables for other child queries to process. + **Statistics query**: Query profiler adds the following comment to the start of this child query to help identify it: ``` -- collect statistics of child query queryID ``` This child query collects information that the Amazon Redshift query engine uses to optimize performance. **Note** Query Profiler shows the query that the user provides as the final child query that Amazon Redshift runs. + **Streams** – A stream is a collection of segments parceled over available compute-node slices. Each child query is made up of one or more segments. In Query profiler, you can see the properties of each stream, such as its execution time. By glancing through the list of streams, it's likely that you can quickly find performance bottlenecks. + **Segments** – A segment is a combination of several steps that a single process can run. A segment is also the smallest compilation unit executable by a compute node slice. A slice is the unit of parallel processing in Amazon Redshift. The segments in a stream run in parallel. Query profiler doesn't show segments graphically, but you can access the segment information for a step in that step's details pane. + **Steps** – Each segment is made up of a collection of steps. A step is a piece of work in a query. Steps can include a *hashjoin*, for example, or a *scan*, which is the reading of records from a table. For more information about streams, segments, and steps, see [ Query planning and execution workflow](https://docs.aws.amazon.com/redshift/latest/dg/c-query-planning.html) in the *Amazon Redshift Database Developer Guide*. Query profiler displays information returned by the `SYS_QUERY_HISTORY`, `SYS_QUERY_DETAIL`, `SYS_QUERY_EXPLAIN`, and `SYS_CHILD_QUERY_TEXT` views. For more information about these views, see [SYS\$1QUERY\$1HISTORY](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_HISTORY), [SYS\$1QUERY\$1DETAIL](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_DETAIL.html), [SYS\$1QUERY\$1EXPLAIN](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_EXPLAIN.html), and [SYS\$1CHILD\$1QUERY\$1TEXT](https://docs.aws.amazon.com/redshift/latest/dg/SYS_CHILD_QUERY_TEXT.html) in the *Amazon Redshift Database Developer Guide*. Query profiler only displays query information for queries that have recently run on the database. A query that completes using prepopulated cache data rather than running against the database won't have a query profile if information wasn't previously available for it. This is because Amazon Redshift doesn't generate a query plan for it. ## Prerequisites for using Query profiler The SYS monitoring views are designed for ease of use and reduced complexity, providing a complete array of metrics for effective monitoring and troubleshooting. The SYS monitoring views also guarantee query history for the past seven days regardless of the size or activity of the cluster. Users only have visibility for queries that they ran, while super users have visibility for queries from all users. Your IAM user account or role needs permissions to access the **Query and database monitoring** section of the console. This section describes how to add permissions to a user account or role. Use the following policy to add minimum permissions to your IAM user account or role: ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "redshift:DescribeClusters", "redshift-serverless:ListNamespaces", "redshift-serverless:ListWorkgroups", "redshift-data:ExecuteStatement", "redshift-data:DescribeStatement", "redshift-data:GetStatementResult" ], "Resource": [ "arn:aws:redshift-serverless:us-east-1:111122223333:*", "arn:aws:redshift:us-east-1:111122223333:*" ] } ] } ``` ------ **Topics** + [ ### Granting query monitoring permissions for a role ](#using-query-plan-profiler-prereqs-role) + [ ### Granting query monitoring permissions for a user ](#using-query-plan-profiler-prereqs-user) + [ ### Temporary credentials using your IAM identity ](#using-query-plan-profiler-prereqs-temp-iam) ### Granting query monitoring permissions for a role Users with a role that has the `sys:monitor` permission can view all queries. Users with a role that has the `sys:operator` permission can cancel queries, analyze query history, and perform vacuum operations. **To grant query monitoring permission for a role** 1. Use 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"; ``` 1. (Optional) Use 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"; ``` ### Granting query monitoring permissions for a user Users with the `sys:monitor` permission can view all queries. Users with the `sys:operator` permission can cancel queries, analyze query history, and perform vacuum operations. **To grant query monitoring permission for a user** 1. Use the following command to provide system monitor access, where *user-name* is the name of the user for which you want to provide access. ``` grant role sys:monitor to "IAMR:user-name"; ``` 1. (Optional) Use the following command to provide system operator access, where *-name* is the name of the user for which you want to provide access. ``` grant role sys:operator to "IAMR:user-name"; ``` ### Temporary credentials using your IAM identity This option is only available when connecting to a cluster. With this method, Query Profiler 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. ## Accessing Query profiler in the Amazon Redshift console to analyze a query You can access Query Profiler for either Amazon Redshift serverless or Amazon Redshift provisioned. See the following sections for details: **Topics** + [ ### Accessing Query profiler in the Amazon Redshift console for Amazon Redshift Serverless ](#using-query-plan-profiler-accessing-serverless) + [ ### Accessing Query profiler in the Amazon Redshift console for Amazon Redshift Provisioned ](#using-query-plan-profiler-accessing-provisioned) ### Accessing Query profiler in the Amazon Redshift console for Amazon Redshift Serverless To access Query profiler for Amazon Redshift Serverless, do the following: + Open the Amazon Redshift serverless console. + On the navigation pane, under **Monitoring**, choose **Query and database monitoring**. + Choose a workgroup. + Choose **Queries and database monitoring**. + Choose a query. + Choose the **Query plan** tab in the **Query details** page. If a query plan is available, you will see a list of child queries. Choose a query to view it in Query profiler. ### Accessing Query profiler in the Amazon Redshift console for Amazon Redshift Provisioned To access Query profiler for Amazon Redshift Provisioned, do the following: + Open the the Amazon Redshift Provisioned clusters dashboard. + Choose a cluster + Choose **Query monitoring**. + Connect to a database + Choose **Queries and database monitoring.** + Choose a query. If a query plan is available, you will see a list of child queries. Choose a query to view it in Query profiler. ## Query profiler user interface Query profiler uses the following pages to display information about your query: + ** [Query details page](#using-query-plan-profiler-ui-query-details) ** – This page displays statistics and child queries for your query. + ** [Child query page](#using-query-plan-profiler-ui-child-query) ** – This page displays statistics, streams, and a visual representation of the execution plan for a child query. The console displays this page when you choose a child query from the **Child queries** list in the **Query and database monitoring** page. ### Query details page ![\[Query details interface showing execution time, data returned, and performance breakdown for query 4960.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_qp_query_details.png) The Query details page has the following components: + **Top pane** – The pane at the top of the page shows details about the query, such as the status and type. For information about the source of the information that the top pane shows, see [SYS\$1QUERY\$1HISTORY](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_HISTORY.html) in the *Amazon Redshift Database Developer Guide*. + **SQL tab** – This tab of the bottom panel shows the SQL text for the original user query. + **Query plan tab** – This tab of the bottom panel shows a list of the child queries that Amazon Redshift uses to prepare data and statistics for the user query. By default, the **Child queries** list shows information and aggregated statistics about each child query. For information about the source of the information that this page shows, see [SYS\$1QUERY\$1DETAIL](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_DETAIL.html) in the *Amazon Redshift Database Developer Guide*. You can add or remove columns from the **Child queries** list using the Preferences menu. + **Related metrics** – This tab of the bottom panel shows the following CloudWatch metrics for the query: + **RPU capacity used** (for serverless workgroups): The compute capacity used by the query, measured in Redshift Processing Units (RPUs). For more information, see [Compute capacity for Amazon Redshift Serverless](serverless-capacity.md). + **Cluster health status**, **CPU Utilization**, **Storage capacity used** (for provisioned clusters): The status and system resources the query used. + **Active database connections**: the `DatabaseConnections` metric for the query. For more information about CloudWatch metrics, see [Performance data in Amazon Redshift](metrics-listing.md). ### Child query page ![\[Query execution plan showing stages from Distribute to Aggregate with execution times.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_qp_child_query_plan.png) The Child query page has the following components: + **Child queries pulldown** – This control shows the sequence name and the execution time for each child query. You can navigate to other child queries by selecting them in this control. + **Side panel** – This panel contains tabs for displaying the child query streams and the child query text. + **Child query streams tab** – This tab of the top panel displays the following: + **Streams** – This pane shows the list of streams in the child query. This pane shows information and aggregated data about the streams that Amazon Redshift uses to optimize the query. For information about the details in this pane, see [SYS\$1QUERY\$1DETAIL](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_DETAIL.html) in the *Amazon Redshift Database Developer Guide*. You can add or remove columns from the **Streams** pane using the Preferences menu. You access the Preferences menu using the gear icon in the **Streams** pane. + **Child query pane** – A graphical representation of the steps in the child query. For information about the **Child query** pane, see [Child query pane](#using-query-plan-profiler-child-query-pane) following. + **Child query text tab** – This tab of the top panel shows the SQL code for the child query. + **Child query details pane** – This pane in the right panel shows details about the child query. For information about the details in this pane, see [SYS\$1QUERY\$1DETAIL](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_DETAIL.html) in the *Amazon Redshift Database Developer Guide*. + **Stream details pane** – When you choose a stream in the **Streams** pane, the **Stream details** pane shows information about the stream. For information about the details in this panel, see [SYS\$1QUERY\$1DETAIL](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_DETAIL.html) in the *Amazon Redshift Database Developer Guide*. + **Step details pane** – When you choose a step in either the **Streams** pane or the graph in the **Child query plan**, the **Step details** pane shows information about the step, For information about the details in this pane, see [SYS\$1QUERY\$1DETAIL](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_DETAIL.html) in the *Amazon Redshift Database Developer Guide*.: ## Child query pane Query profiler displays the child query in the **Child query** pane as a graphical representation of the steps in the selected child query. The **Child query** pane displays the run order and the relationships between the steps. For example, if a step joins the output from two other steps, the **Child query** pane shows the step as a tree node with two nodes feeding into it: ![\[Tree diagram showing Hashjoin step with Scan + Item and Hash steps as child nodes.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_qp_hashjoin.png) The **Child query** pane doesn't show the streams that contain the steps by default. To show the streams that Amazon Redshift uses to logically partition the steps in the child query, choose **View streams**. When you choose **View streams**, the **Child query** pane shows the steps contained by the query's streams. The **Child query** pane doesn't show segment information. To see the segment for a step, choose the step. The **Step details** pane then shows the segment for the step. ### Navigation in the Child query pane In the **Child query** pane, you can choose steps to see detailed information about them. You can also pan and zoom the workspace to better visualize the steps in your query plan. You can choose nodes, pan, and zoom in the **Child query** plan using the following methods: + **Using the mouse** – You can choose nodes, click and drag the workspace to pan, and hold **Ctrl** (Windows) or **CMD** (Mac) and mousewheel to zoom. Choosing a node causes the workspace to zoom and pan to highlight that node. If you choose a stream in the workspace, that stream highlights in the **Streams** list. If you choose a step in the workspace, the **Step details** pane shows information about that step. + **Using the zoom and fit controls in the upper left of the workspace** – These controls let you zoom in, zoom out, zoom to fit the entire workspace, and enter fullscreen mode. When you zoom to fit the entire query plan, the workspace centers the query plan both horizontally and vertically. + **Using the minimap in the lower right of the workspace** – You can pan or zoom the workspace by using the minimap control in the lower left corner of the workspace. + **Choosing a stream in the **Streams** pane** – If you choose a stream in the **Streams** pane, the workspace pans and zooms to show the selected stream, and shows information about the stream in the **Stream details** pane. + **Choosing a step in the **Stream details** pane** – If you choose a step in the **Stream details** pane, the workspace pans and zooms to show the selected step, and shows information about the step in the **Step details** pane. **Note** When you choose a step, either in the workspace or a different pane, the workspace attempts to zoom and pan such that the selected step is best visible. When you choose a stream or step from either the workspace or another pane, the workspace will only zoom and pan to that stream or step if you have **Pan & Zoom** selected in the upper right control in the workspace. You can restrict this behavior to panning and zooming, panning only, or no movement by choosing the appropriate setting in the pulldown. ![\[Dropdown menu showing pan and zoom options for workspace navigation control.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_qp_pan_zoom.png) ## Troubleshooting queries with Query profiler If you're troubleshooting a query, you can choose a child query to determine which stream uses the highest **Percentage of total query time**. This is a quick way to determine which part of your query should be analyzed further. After you know which child query is taking the most time, view its steps to see which join or scan might be causing slow performance. # Query and Database Monitoring This document describes the Query and Database Monitoring page, an AWS 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: + Monitor data warehouse metrics during a specified time period + How a query contribues to the overall performance of a data warehouse + See a breakdown of a query run time into its life cycle events such as lock wait time, compile time, and execution time + Which users run the most resource-intensive queries during a specified time period + Monitor how patch events affect query performance **Topics** + [ ## Permissions ](#metrics-enhanced-query-monitoring-permissions) + [ ## Query and Database Monitoring Console ](#metrics-enhanced-query-monitoring-console) ## Permissions The privileges of the AWS 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: 1. Choose the **Tags** tab. 1. Choose **Manage tags**. 1. Add a tag with key **RedshiftDbRoles** and value **monitor**. 1. Save changes To add database credentials to a user, run the following command: ``` grant role sys:monitor to ``` For information about using the GRANT command, see [GRANT](https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html) 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: + **Increased security** — You need elevated privileges to monitor queries for other users. For more information, see [Permissions](#metrics-enhanced-query-monitoring-permissions). + **Seven-day query history** — Guaranteed access to seven days of query history + **Query monitoring** — You can monitor queries in provisioned clusters and serverless workgroups at the user query level. + **Query trend analysis** — You can compare the performance of similar queries that match specified criteria. To access the Query and Database Monitoring page, do the following: 1. Sign in to the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. Choose **Query and database monitoring** from the navigation pane. The **Query and database monitoring** console page appears as follows: ![\[Query and database monitoring dashboard showing data warehouse overview, performance summary, and profiler sections.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_eqm_homepage.png) The **Query and database monitoring** page has the following components: + **Data warehouse overview** — Monitor the average query performance for your provisioned clusters and serverless workgroups. You can quickly see performance issues for a specific cluster or workgroup by examining the statistics on this page for spikes or periods of high activity. + **Query performance summary** — Monitor the average query performance for a specific provisioned cluster or serverless workgroup. You can also acccess the **Query performance summary** page by clicking on one of the clusters or workgroups in the **Data warehouse overview** list. **Topics** + [ ### Query performance summary ](#metrics-enhanced-query-monitoring-console-summary) + [ ### Query details ](#metrics-enhanced-query-monitoring-console-query-details) + [ ### Query pattern ](#metrics-enhanced-query-monitoring-console-query-pattern) ### 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. ![\[Query and database monitoring interface showing performance graph and query history for redshift-cluster-1.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_eqm_clusterdetails.png) This page has the following components: + **Cluster or workgroup dropdown** — Choose the cluster or workgroup you want to analyze. + **Data warehouse performance** — This pane shows a history of the cluster or workgroup within the specified time period, showing the amount of time spent on each phase of queries. If you see a spike in a certain query phase, such as the increased planning and execution time on January 20 in the preceding graphic, you can use this information to identify issues with the performance of your queries. The default time period shown is the last seven days, but you can adjust the time period according to your analysis needs. + **Query history** — This pane shows a history of the performance of every query run within the specified filters. You can use this information to troubleshoot the performance of an individual query. To further analyze a specific query, you can choose it from this graph or from the **Data warehouse performance** graph. When you hover over a data point on the chart, a popup appears. This popup displays the breakdown of time metrics for that data point. If a data point contains query data, you can choose **Drill down** to update the time range of the chart to the next smallest time unit of the data point. These time units are as follows: + Day + Hour + 15 minutes + 5 minutes + 1 minute For example, if you choose **Drill down** on a data point, the range of the graph changes to one day. Choosing **Drill down** again changes the range of the graph to one hour. + **Query profiler** — A graphical tool for monitoring query performance. For more information, see [Query profiler](using-query-plan-profiler.md). ### 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. ![\[Query details page showing query information, elapsed time breakdown, and SQL details.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_eqm_querydetails.png) For information about the **Query details** page, see [Query details page](using-query-plan-profiler.md#using-query-plan-profiler-ui-query-details). ### 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. ![\[Query pattern page showing performance trend graph and history table of queries.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/metrics_eqm_querypattern.png) The **Query pattern** page has the following components: + **Query pattern** — The SQL statement that retrieves the queries being analyzed. + **Query performance trend analysis** — A bar graph showing the elapsed time of all the queries that the query pattern selects. The results are grouped by day. + **History of queries with the same pattern** — The time taken for each phase of the queries that the query pattern selects. Using the **Query pattern** page, you can gain the following insights: + Trends for queries that run at a specific time every day + Spikes in run time for queries that you run regularly against the data warehouse. # Sys View-based Queries and Database Monitoring This document describes the `SYS` views that provide data for the Queries and Database Monitoring page in the Amazon Redshift Console, a tool for analyzing a query's components and performance. For information about the Queries and Database Monitoring page, see [Query and Database Monitoring](metrics-enhanced-query-monitoring.md). The **Queries and Database Monitoring** page has a functionality that displays information provided by `SYS` views. The console view includes the query profiler, which shows the graphical execution plan of a query. To switch to the `SYS`-based view, follow the steps here to grant the right access and permissions for the new **Queries and Database Monitoring** page. The `SYS`-based view feature of the **Queries and Database Monitoring** page has the following functionality: + **Increased security** — You need elevated privileges to monitor queries for other users + **Seven-day query history** — Guaranteed access to seven days of query history + **Query profiler** — A graphical tool for monitoring query performance. For more information, see [Query profiler](using-query-plan-profiler.md) 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, grant the user the `SYS:OPERATOR` privilege. To grant the privilege to view queries owned by all users to a database user or role, run the following commands: ``` grant role sys:monitor to "IAM:role-name"; grant role sys:monitor to "IAM:user-name"; ``` 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: 1. Choose the **Tags** tab. 1. Choose **Manage tags**. 1. Add a tag with key **RedshiftDbRoles** and value **monitor**. 1. Save changes To add database credentials to a user, run the following command: ``` grant role sys:monitor to ``` ## Permissions 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" }, ``` ## Connect to the database Before using the enhanced query monitoring feature, you must first connect to your database for access to `SYS` view-based information. To connect to the database, use one of the following credentials: + Username and password + Temporary credentials associated with your IAM role + A database user Note the following about using enhanced query monitoring: + For provisioned clusters, you must connect to a database, because enhanced query monitoring uses `SYS` views. These views have increased security, and require elevated privileges to access data about queries owned by other users. + When using the `SYS` view-based queries and database monitoring page, only your `user_id` is visible if your user account doesn't have the database superuser role. Usernames are hidden from non-superusers. + As part of the sys view-based queries and database monitoring page experience, the query execution process ID (`p_id`) appears under the column heading `session_id`.