/block-redshift-admin

Primary LanguageLookMLOtherNOASSERTION

What does this Block do for me?

(1) ETL and Data Flow Management - review ETL history, tables, and capacity, and set up alerts to trigger whenever errors occur in ETL or data movement processes.

(2) Monitor Database Performance - provides a comprehensive view of query performance across all queries and users to understand how quickly your database is performing, allowing users to see exactly which queries are causing delays (if any), and why.

(3) Inspect Queries - gain full detail around any particular query, from the time to return to the bytes scanned to the specific execution steps. This Block helps highlight those execution steps that are particularly slow moving and complex.

(4) Understand User Behavior - see which users or user groups are issuing the most or least performant queries, and help architect your schema and data pipeline to empower the users that need it most.

Redshift System Tables Data Structure

  • Redshift comes with System Tables, which provide metadata around database performance and operations. These tables come natively, with the same table structures, for each Redshift instance. More information on System Tables can be found in AWS documentation.

Block Structure

Redshift Views - This Block consists of several derived tables based on the System Tables, which we've consolidated into one single view file.

Dashboards - Due to the nature in which this data is displayed, this Block only exposes Dashboards as starting points for exploration, rather than using individual "Explore" pages. Start from a dashboard, then drill into any tile to start free-form exploration.

Implementation Instructions / Customizations

  • Connection and Permission

  • Installation

    • Marketplace Installation via the Looker Marketplace will prompt configuration of the Looker Redshift connection constant desired for use with this block.
  • Customization

    • Refinements This block uses refinements for customization. For more information on using refinements to customize marketplace blocks, please see this documentation.
  • [Optional] Change daily PDT trigger - The default PDT trigger (00:00 UTC) is not selected for any particular timezone, so you may want to offset it so that it does not trigger during your peak hours.

  • [Optional] Unhide Explores - Explores are hidden by default.

How do I optimize the performance of my database?

Check our Looker Discourse article for an overview of common performance issues, and suggestions to resolve them.

Known Issues

  • Sometimes, drilling into a list of queries doesn't return any records. As far as we can tell, this is due to categorically wrong result sets from Redshift for certain where filters. As a workaround, remove some filters, such as redshift_tables.sortkey1_enc
  • Sometimes, the query execution table has 0 distribution bytes, despite the query plan and table distributions both suggesting that there was distribution activity. This zeroing out is present in each of SVL_QUERY_SUMMARY, SVL_QUERY_REPORT, and STL_DIST. Always check query execution metrics to ensure they're in the right ballpark before relying on them.
  • "Rows out" according to the query plan are estimates. The are often highly inflated. If anything, this is an indication that you should update table statistics in Redshift so it can generate better query plans.
  • Need to think about metrics for scans (e.g. bytes, rows emitted, and emitted rows to table rows ratio). When a table has dist style='all', the measures are increased by a factor of the number of slices. This is unintuitive since, for example the ratio is then typically >100%, but this may be a good thing.