AWS Redshift Performance Analysis: Tables and Views

Accessing Performance Information for AWS Redshift Cluster

Question

ConsumersHalt (CH) is an Indian department collection chain.

There are 63 branches across 32 towns in India, with clothing, accessories, bags, shoes, jewelry, scents, faces, health and exquisiteness products, home furnishing and decor products. CH runs their existing operations and analytics infrastructure out of AWS which includes S3, EC2, Auto Scaling, CDN and also Redshift.

The Redshift platform is being used for advanced analytics, real time analytics and being actively used for past 2 years.

Suddenly performance issues are occurring in the application and administrator being a superuser needs to provide a list of reports in terms of current and historical performance of the cluster.

What types of tables/views can help access the performance related info for diagnosis. Select 3 options.

Answers

Explanations

Click on the arrows to vote for the correct answer

A. B. C. D. E. F.

Answer : A, D, F.

Option A is correct -STL system tables are generated from Amazon Redshift log files to provide a history of the system.

https://docs.aws.amazon.com/redshift/latest/dg/c_intro_STL_tables.html

Option B is incorrect -STL system tables are generated from Amazon Redshift log files to provide a history of the system.

https://docs.aws.amazon.com/redshift/latest/dg/c_intro_STL_tables.html

Option C is incorrect - STV tables are actually virtual system tables that contain snapshots of the current system data.

https://docs.aws.amazon.com/redshift/latest/dg/c_intro_STV_tables.html

Option D is correct - STV tables are actually virtual system tables that contain snapshots of the current system data.

https://docs.aws.amazon.com/redshift/latest/dg/c_intro_STV_tables.html

Option E is incorrect -System tables contain only subset of data.

https://docs.aws.amazon.com/redshift/latest/dg/c_intro_system_views.html

Option F is correct -The system catalogs store schema metadata, such as information about tables and columns.

System catalog tables have a PG prefix.

https://docs.aws.amazon.com/redshift/latest/dg/c_intro_catalog_views.html

In order to diagnose performance issues in an Amazon Redshift cluster, administrators can use various tables and views that provide information about the system's current and historical performance. Here are the three options that can help:

  1. STL system tables: The STL (System Table Log) tables are generated from Redshift log files and contain a history of the system. These tables serve as a log of system activity and can help administrators diagnose performance issues by providing information about queries that were executed, errors that occurred, and other system events. Some examples of STL tables include STL_QUERY, which contains information about queries that were executed, and STL_LOAD_ERRORS, which contains information about errors that occurred during data loads.

  2. STV system tables: Like the STL tables, the STV (System Table Virtual) tables are also generated from Redshift log files and contain a history of the system. However, unlike the STL tables, the STV tables contain snapshots of the current system data rather than a log of system activity. These tables serve as a snapshot of the current state of the system and can help administrators diagnose performance issues by providing information about system configuration, resource usage, and other system metrics. Some examples of STV tables include STV_BLOCKLIST, which contains information about blocks that are currently in use, and STV_INFLIGHT, which contains information about currently running queries.

  3. System views: System views are virtual tables that contain data from multiple STL and STV tables. These views provide a consolidated view of the system's current and historical performance and can help administrators diagnose performance issues by providing a more complete picture of the system's activity. Some examples of system views include SVL_QUERY_METRICS, which contains information about query performance, and SVL_S3QUERY_SUMMARY, which contains information about S3 query performance.

Overall, by using these tables and views, administrators can gain valuable insight into the performance of their Redshift cluster and diagnose performance issues that may be affecting their application.