Query monitoring for Multi-AZ - Amazon Redshift
Viewing queries and loads for Multi-AZ data warehousesMonitoring a query in a Multi-AZ deploymentTerminating a query for clusters
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Query monitoring for Multi-AZ

You can view information on queries that ran in the past 7 days irrespective of the type, size, and status (pause or resume) of your cluster.

Viewing queries and loads for Multi-AZ data warehouses

The information shown on the Queries and loads page is populated with information from Amazon Redshift system tables (SYS_* views). This information lets you display additional information about your queries and offers rolling 7 days of retention. Query diagnostics become faster, letting you filter data by database, username, or SQL statement type. To see these additional filters and information on all queries that ran, note the following prerequisites:

  • You must connect to a database by choosing Connect to database.

  • Your database user must have the sys:operator or sys:monitor roles and permissions to perform query monitoring. For information about system roles, see Amazon Redshift system-defined roles in the Amazon Redshift Database Developer Guide.

You will see these additional filters and query information once you connect to a database.

To display query performance data from Queries and loads

  1. Sign in to the Amazon Web Services Management Console and open the Amazon Redshift console at https://console.amazonaws.cn/redshiftv2/.

  2. On the navigation menu, choose Queries and loads to display the list of queries for your account.

  3. You might have to connect to a database to see additional filter. If required, click Connect to database and follow the prompts to connect to a database.

    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.

To display query performance data from Query monitoring

  1. Sign in to the Amazon Web Services Management Console and open the Amazon Redshift console at https://console.amazonaws.cn/redshiftv2/.

  2. On the navigation menu, choose Clusters. Under Clusters, select a cluster.

  3. Choose Query monitoring.

  4. Depending on the configuration or version of your cluster, you might have to connect to a database to see additional filters. If required, click Connect to database and follow the prompts to connect to a database.

Monitoring a query in a Multi-AZ deployment

A Multi-AZ deployment uses compute resources that are deployed in both Availability Zones and can continue operating in the event that the resources in a given Availability Zone aren't available. All the compute resources will be used at all times. This allows full operation across two Availability Zones in an active-active fashion for both read and write operations.

You can query SYS_ views in pg_catalog schema to monitor query runtime in a Multi-AZ deployment. The SYS_ views display query runtime activities or statistics from primary and secondary clusters. For a list of monitoring views, see Monitoring views.

Follow these steps to monitor query runtime for each Availability Zone within the Multi-AZ deployment:

  1. Navigate to the Amazon Redshift console and connect to the database in your Multi-AZ deployment and run queries through the query editor.

  2. Run any sample query on the Multi-AZ Amazon Redshift deployment.

  3. For a Multi-AZ deployment, you can identify a query and the Availability Zone where it is run by using the compute_type column in the SYS_QUERY_HISTORY table. primary stands for queries run on the primary cluster in the Multi-AZ deployment, and secondary stands for queries run on the secondary cluster in the Multi-AZ deployment.

    The following query uses compute_type column to monitor a query.

    select (compute_type) as compute_type, left(query_text, 50) query_text from sys_query_history order by start_time desc;
    
 compute_type | query_text
--------------+-------------------------
   secondary  | select count(*) from t1;

Terminating a query for clusters

Terminating a query for clusters

The procedure is applicable to both Multi-AZ and Single-AZ clusters.

To terminate a query

You can also use the Queries page to end a query that is currently in progress.

Your database user must have the sys:operator role and permissions to end a running query. For information about system roles, see Amazon Redshift system-defined roles in the Amazon Redshift Database Developer Guide.

  1. Sign in to the Amazon Web Services Management Console and open the Amazon Redshift console at https://console.amazonaws.cn/redshiftv2/.

  2. On the navigation menu, choose Queries and loads to display the list of queries for your account.

  3. Choose the running query that you want to end in the list, and then choose Terminate query.