©2019 by Raghavendra Kambhampati

Queries to optimize Redshift Performance and Monitoring

Updated: Aug 16, 2019


1.Time Consuming Queries

With the following query, you can monitor the most time-consuming queries along with the average, minimum and maximum execution time. Knowing which queries are most problematic is the first step in debugging the situation.

SELECT max(query) AS max_query_id,

min(run_minutes) AS "min",

max(run_minutes) AS "max",

avg(run_minutes) AS "avg",

sum(run_minutes) AS total

FROM

(SELECT userid,

label,

stl_query.query,

trim(DATABASE) AS DATABASE,

trim(querytxt) AS qrytext,

md5(trim(querytxt)) AS qry_md5,

starttime,

endtime,

(datediff(seconds, starttime,endtime)::numeric(12,2))/60 AS run_minutes,

alrt.num_events AS alerts,

aborted

FROM stl_query

LEFT OUTER JOIN

(SELECT query,

1 AS num_events

FROM stl_alert_event_log

GROUP BY query) AS alrt ON alrt.query = stl_query.query

WHERE userid <> 1

AND starttime >= dateadd(DAY, -7, CURRENT_DATE))

GROUP BY DATABASE,

label,

qry_md5,

aborted

ORDER BY total DESC

LIMIT 50;


2.Column encoding

When creating a table in Amazon Redshift you can choose the type of compression encoding you want. The chosen compression encoding determines the amount of disk used when storing the columnar values and in general lower storage utilization leads to higher query performance. If no compression is selected, the data will be stored as RAW, resulting in a significant penalty in query’s performance. The below URL specifies different types of column encodings.

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

SELECT "table" tablename,CASE WHEN encoded = 'Y' THEN 1 ELSE 0 END has_col_encodingFROM svv_table_info tiJOIN (SELECT tbl,MIN(c) min_blocks_per_slice,MAX(c) max_blocks_per_slice,        COUNT(DISTINCT slice) dist_slice   FROM    (SELECT b.tbl,            b.slice,            COUNT(*) AS c    FROM STV_BLOCKLIST b    GROUP BY b.tbl,            b.slice)   WHERE tbl IN    (SELECT table_id        FROM svv_table_info)   GROUP BY tbl) iq ON iq.tbl = ti.table_idORDER BY SCHEMA,        "Table";

3. Skew Ratio

To ensure your database’s optimal performance, the key factor lies in the uniform data distribution into these nodes and slices. In the opposite case, you will end up with skewed tables resulting in uneven node utilization in terms of CPU load or memory creating a bottleneck to the database performance.

It is important to ensure that the skew ratio of your tables is as close to zero as possible and the following query can help you to monitor exactly this:

SELECT "table" tablename,100* (ROUND(100*CAST(max_blocks_per_slice - min_blocks_per_slice AS FLOAT) / GREATEST(NVL (min_blocks_per_slice,0)::int,1),2)) ratio_skew_across_slicesFROM svv_table_info tiJOIN  (SELECT tbl,        MIN(c) min_blocks_per_slice,        MAX(c) max_blocks_per_slice,        COUNT(DISTINCT slice) dist_slice   FROM    (SELECT b.tbl,            b.slice,            COUNT(*) AS c    FROM STV_BLOCKLIST b    GROUP BY b.tbl,            b.slice)   WHERE tbl IN    (SELECT table_id        FROM svv_table_info)   GROUP BY tbl) iq ON iq.tbl = ti.table_idORDER BY SCHEMA,        "Table";

You can also keep track of the CPU and memory utilization of each node with the following queries.

CPU load

 SELECT slice,MAXVALUEFROM svv_diskusageWHERE name= 'real_time_data'  AND col = 0ORDER BY slice;

Available space

SELECT sum(used)::float / sum(capacity) AS pct_fullFROM stv_partitions;

4. Sort Keys

When using Amazon Redshift, you can specify a column as sort key. This means that data will be stored on the disk sorted by this key. An Amazon Redshift optimizer will take the sort key into consideration when evaluating different execution plans, ultimately determining the optimal way. When it comes to deciding the best key for your table you need to consider how the table data is being used. For example, if two tables are joined together very often it makes sense to declare the join column as the sort key, while for tables with temporal locality the date column. The below link specifies different sort keys:

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

 SELECT "table" tablename,CASE WHEN sortkey1 IS NOT NULL THEN 1        ELSE 0    END has_sort_keyFROM svv_table_info tiJOIN  (SELECT tbl,        MIN(c) min_blocks_per_slice,        MAX(c) max_blocks_per_slice,        COUNT(DISTINCT slice) dist_slice   FROM    (SELECT b.tbl,            b.slice,            COUNT(*) AS c    FROM STV_BLOCKLIST b    GROUP BY b.tbl,            b.slice)   WHERE tbl IN    (SELECT table_id        FROM svv_table_info)   GROUP BY tbl) iq ON iq.tbl = ti.table_idORDER BY SCHEMA,        "Table"

5. Table Size

Monitoring your table size on a regular basis can save you from a lot of pain. Knowing the rate at which your database is growing is important in order not to end up running out of space out of the blue.

For this reason, the following query will help you settle things down and monitor the top space consuming tables in your Amazon Redshift cluster

SELECT "table" tablename,SIZE size_in_mbFROM svv_table_info tiJOIN  (SELECT tbl,        MIN(c) min_blocks_per_slice,        MAX(c) max_blocks_per_slice,        COUNT(DISTINCT slice) dist_slice   FROM    (SELECT b.tbl,            b.slice,            COUNT(*) AS c    FROM STV_BLOCKLIST b    GROUP BY b.tbl,            b.slice)   WHERE tbl IN    (SELECT table_id        FROM svv_table_info)   GROUP BY tbl) iq ON iq.tbl = ti.table_idORDER BY SCHEMA,        "Table";

6. Queries Waiting on Queue Slots

In an Amazon Redshift cluster, each query is being assigned to one of the queues defined via the workload management (WLM). This means that it is possible that a query may take some time to be executed if the assigned queue is busy. As this is suboptimal, to decrease the waiting time you may increase the concurrency by allowing more queries to be executed in parallel. However, increased concurrency comes with a significant penalty in the memory share allocated to each query. When the memory share available for a query’s execution is not sufficient, disk storage will be used leading to poor performance as accessing the disk is much slower than accessing the memory.

With the following queries you can monitor the total execution time of your query and how this is divided between waiting time and actual execution along with the total number of disk based queries been executed:

 SELECT w.query,w.total_queue_time / 1000000 AS queue_seconds       ,w.total_exec_time / 1000000 exec_seconds       ,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_secondsFROM stl_wlm_query w  LEFT JOIN stl_query q         ON q.query = w.query        AND q.userid = w.useridWHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE)AND   w.total_queue_Time > 0ORDER BY w.total_queue_time DESC,w.queue_start_time DESC limit 35

Number of disk based queries

SELECT count(*)FROM (SELECT q.query,trim(q.cat_text)   FROM     ( SELECT query,              replace(listagg(text,' ') withIN                      GROUP (                             ORDER BY SEQUENCE), '\\n', ' ') AS cat_text      FROM stl_querytext      WHERE userid>1      GROUP BY query) q   JOIN     ( SELECT DISTINCT query      FROM svl_query_summary      WHERE is_diskbased='t'        AND (LABEL LIKE 'hash%'             OR LABEL LIKE 'sort%'             OR LABEL LIKE 'aggr%')        AND userid > 1) qs ON qs.query = q.query)tmp

7. Commit Queue Waits

With the commit_stats.sql script provided by the AWS team you can monitor the wait time on your commit queue. As an Amazon Redshift cluster is primarily designed for the execution of analytical queries, the cost of frequent commits is terms of execution time is quite increased.

 SELECT startqueue,node,datediff(ms,startqueue,startwork) AS queue_time,datediff(ms, startwork, endtime) AS commit_time,       queuelenFROM stl_commit_statsWHERE startqueue >= dateadd(DAY, -2, CURRENT_DATE)ORDER BY queuelen DESC,         queue_time DESC;

8. Finding Common Alerts

Using an Amazon Redshift cluster makes it easy to keep an eye on the most common alerts your queries produce in order to investigate them further. The following query does the trick for you.

SELECT max(l.query) AS "query id",trim(split_part(l.event,':',1)) AS event,       trim(l.solution) AS solution,       count(*) AS "times occured"FROM stl_alert_event_log AS lLEFT JOIN stl_scan AS s ON s.query = l.queryAND s.slice = l.sliceAND s.segment = l.segmentAND s.step = l.stepWHERE l.event_time >= dateadd(DAY, -7, CURRENT_DATE)GROUP BY 2,3

9. Stale or Missing Statistics

Another common alert is raised when tables with missing plan statistics are detected. During query optimization and execution planning the Amazon Redshift optimizer will refer to the statistics of the involved tables in order to make the best possible decision. For this, having tables with stale or missing statistics may lead the optimizer to choose a suboptimal plan. Defining the problematic tables with the following queries will help you proceeding with the necessary VACUUM actions.

Stale Statistics

 SELECT schema || '.' || "table" AS "table", stats_offFROM svv_table_infoWHERE stats_off > 5ORDER BY 2;

Missing Statistics

 select count(tmp.cnt) "Table Count", tmp.cnt "Missing Statistics"from(SELECT substring(trim(plannode),34,110) AS plannode    ,COUNT(*) as cntFROM stl_explainWHERE plannode LIKE '%missing statistics%'AND plannode NOT LIKE '%redshift_auto_health_check_%'GROUP BY plannodeORDER BY 2 DESC) tmpgroup by tmp.cnt

10. Data Load Statistics

Regarding data loading there are best practices that the Amazon Redshift team advises users to implement. These include compressing files and loading many smaller files instead of a single huge one. Furthermore, ensuring that the number of files to load is a multiple of the number of slice results in even utilization of cluster nodes.

Some queries that help you ensure all the above are shown below.

Rows insert rate

SELECT   trim(b.relname) AS "tablename",(sum(a.rows_inserted)*1000000/SUM(a.insert_micro)) AS insert_rate_rows_ps FROM   (SELECT query,     tbl,     sum(rows) AS rows_inserted,     max(endtime) AS endtime,     datediff('microsecond',min(starttime),max(endtime)) AS insert_micro   FROM stl_insert   GROUP BY query, tbl) a,        pg_class b,   pg_namespace c,                   (SELECT b.query,     count(distinct b.bucket||b.key) AS distinct_files,     sum(b.transfer_size)/1024/1024 AS MB_scanned,     sum(b.transfer_time) AS load_micro   FROM stl_s3client b   WHERE b.http_method = 'GET'   GROUP BY b.query) d WHERE a.tbl = b.oid AND b.relnamespace = c.oid AND d.query = a.query GROUP BY 1

Scanned data (MB)

SELECT   trim(b.relname) AS "tablename",sum(d.MB_scanned) AS MB_scanned  FROM   (SELECT query,     tbl,     sum(rows) AS rows_inserted,     max(endtime) AS endtime,     datediff('microsecond',min(starttime),max(endtime)) AS insert_micro   FROM stl_insert   GROUP BY query, tbl) a,        pg_class b,   pg_namespace c,                   (SELECT b.query,     count(distinct b.bucket||b.key) AS distinct_files,     sum(b.transfer_size)/1024/1024 AS MB_scanned,     sum(b.transfer_time) AS load_micro   FROM stl_s3client b   WHERE b.http_method = 'GET'   GROUP BY b.query) d WHERE a.tbl = b.oid AND b.relnamespace = c.oid AND d.query = a.query GROUP BY 1

Scan rate (kbps)

SELECT trim(b.relname) AS "tablename",(sum(d.MB_scanned)*1024*1000000/SUM(d.load_micro)) AS scan_rate_kbps FROM   (SELECT query,     tbl,     sum(rows) AS rows_inserted,     max(endtime) AS endtime,     datediff('microsecond',min(starttime),max(endtime)) AS insert_micro   FROM stl_insert   GROUP BY query, tbl) a,        pg_class b,   pg_namespace c,                   (SELECT b.query,     count(distinct b.bucket||b.key) AS distinct_files,     sum(b.transfer_size)/1024/1024 AS MB_scanned,     sum(b.transfer_time) AS load_micro   FROM stl_s3client b   WHERE b.http_method = 'GET'   GROUP BY b.query) d WHERE a.tbl = b.oid AND b.relnamespace = c.oid AND d.query = a.query GROUP BY 1

Files Scanned

SELECT trim(b.relname) AS "tablename",sum(d.distinct_files) AS files_scanned FROM   (SELECT query,     tbl,     sum(rows) AS rows_inserted,     max(endtime) AS endtime,     datediff('microsecond',min(starttime),max(endtime)) AS insert_micro   FROM stl_insert   GROUP BY query, tbl) a,        pg_class b,   pg_namespace c,                   (SELECT b.query,     count(distinct b.bucket||b.key) AS distinct_files,     sum(b.transfer_size)/1024/1024 AS MB_scanned,     sum(b.transfer_time) AS load_micro   FROM stl_s3client b   WHERE b.http_method = 'GET'   GROUP BY b.query) d WHERE a.tbl = b.oid AND b.relnamespace = c.oid AND d.query = a.query GROUP BY 1

Average file size (mb)

SELECT trim(b.relname) AS "tablename",(sum(d.MB_scanned)/sum(d.distinct_files)::numeric(19,3))::numeric(19,3) AS avg_file_size_mbFROM   (SELECT query,     tbl,     sum(rows) AS rows_inserted,     max(endtime) AS endtime,     datediff('microsecond',min(starttime),max(endtime)) AS insert_micro   FROM stl_insert   GROUP BY query, tbl) a,        pg_class b,   pg_namespace c,                   (SELECT b.query,     count(distinct b.bucket||b.key) AS distinct_files,     sum(b.transfer_size)/1024/1024 AS MB_scanned,     sum(b.transfer_time) AS load_micro   FROM stl_s3client b   WHERE b.http_method = 'GET'   GROUP BY b.query) d WHERE a.tbl = b.oid AND b.relnamespace = c.oid AND d.query = a.query GROUP BY 1

Rows inserted

SELECT trim(b.relname) AS "tablename",sum(a.rows_inserted) AS "rows_inserted"FROM  (SELECT query,          tbl,          sum(ROWS) AS rows_inserted,          max(endtime) AS endtime,          datediff('microsecond',min(starttime),max(endtime)) AS insert_micro   FROM stl_insert   GROUP BY query,            tbl) a,     pg_class b,     pg_namespace c,   (SELECT b.query,          count(DISTINCT b.bucket||b.key) AS distinct_files,          sum(b.transfer_size)/1024/1024 AS MB_scanned,          sum(b.transfer_time) AS load_micro   FROM stl_s3client b   WHERE b.http_method = 'GET'   GROUP BY b.query) dWHERE a.tbl = b.oid  AND b.relnamespace = c.oid  AND d.query = a.queryGROUP BY 1

11. SVL_QUERY_METRICS_SUMMARY

The SVL_QUERY_METRICS_SUMMARY view shows the maximum values of metrics for completed queries. This view is derived from the STL_QUERY_METRICS system table. Use the values in this view as an aid to determine threshold values for defining query monitoring rules.

select userid,query,query_cpu_time,query_blocks_read,query_execution_time,query_cpu_usage_percent,scan_row_count from svl_query_metrics_summary where query='789';

12. STL_USERLOG

Records details for the following changes to a database user:

Create user

Drop user

Alter user (rename)

Alter user (alter properties)

select userid, username, oldusername, action, usecreatedb, usesuper from stl_userlog order by recordtime desc;

13. STL_S3CLIENT

Records transfer time and other performance metrics.Use the STL_S3CLIENT table to find the time spent transferring data from Amazon S3 as part of a COPY command.

select slice, key, transfer_time  from stl_s3client  where query = pg_last_copy_id();

14. STV_LOAD_STATE

Use the STV_LOAD_STATE table to find information about current state of ongoing COPY statements. The COPY command updates this table after every million records are loaded.

select slice , bytes_loaded, bytes_to_load , pct_complete from stv_load_state where query = pg_last_copy_id();

15. STL_EXPLAIN

Displays the EXPLAIN plan for a query that has been submitted for execution.

select query,nodeid,parentid,substring(plannode from 1 for 30),substring(info from 1 for 20) from stl_explain where query=10 order by 1,2;

16. STL_FILE_SCAN

Returns the files that Amazon Redshift read while loading data via the COPY command.Querying this table can help troubleshoot data load errors. STL_FILE_SCAN can be particularly helpful with pinpointing issues in parallel data loads because parallel data loads typically load many files with a single COPY command.

select trim(name)as name, loadtime from stl_file_scan where loadtime > 1000000;

17. PG_LAST_QUERY_ID

Returns the query ID of the most recently executed query in the current session. If no queries have been executed in the current session, PG_LAST_QUERY_ID returns -1. PG_LAST_QUERY_ID does not return the query ID for queries that execute exclusively on the leader node

select query, trim(querytxt) as sqlquery from stl_query where query = pg_last_query_id();

18. STL_LOAD_ERRORS

Displays the records of all Amazon Redshift load errors.

STL_LOAD_ERRORS contain a history of all Amazon Redshift load errors. Query STL_LOADERROR_DETAIL for additional details, such as the exact data row and column where a parse error occurred, after you query STL_LOAD_ERRORS to find out general information about the error.

select d.query, substring(d.filename,14,20), d.line_number as line, substring(d.value,1,16) as value,substring(le.err_reason,1,48) as err_reason from stl_loaderror_detail d, stl_load_errors le where d.query = le.query and d.query = pg_last_copy_id();

19. STL_S3CLIENT_ERROR

Records errors encountered by a slice while loading a file from Amazon S3.Use the STL_S3CLIENT_ERROR to find details for errors encountered while transferring data from Amazon S3 as part of a COPY command.

select query, sliceid, substring(key from 1 for 20) as file, substring(error from 1 for 35) as error  from stl_s3client_error  where pid = pg_backend_pid() order by query desc;

20. SVV_QUERY_INFLIGHT

Use the SVV_QUERY_INFLIGHT view to determine what queries are currently running on the database. This view joins STV_INFLIGHT to STL_QUERYTEXT.

select slice, query, pid, starttime, suspended, trim(text) as statement, sequence from svv_query_inflight order by query, sequence;

21. STV_PARTITIONS

Use the STV_PARTITIONS table to find out the disk speed performance and disk utilization for Amazon Redshift.STV_PARTITIONS contain one row per node per logical disk partition, or slice.

select owner, host, diskno, used, capacity,(used-tossed)/capacity::numeric *100 as pctused from stv_partitions order by owner;

22. SVV_TABLE_INFO

Shows summary information for tables in the database. The view filters system tables and shows only user-defined tables.

You can use the SVV_TABLE_INFO view to diagnose and address table design issues that can influence query performance, including issues with compression encoding, distribution keys, sort style, data distribution skew, table size, and statistics. The SVV_TABLE_INFO view doesn’t return any information for empty tables.

The SVV_TABLE_INFO view summarizes information from the STV_BLOCKLISTSTV_PARTITIONSSTV_TBL_PERM, and STV_SLICES system tables and from the PG_DATABASEPG_ATTRIBUTEPG_CLASSPG_NAMESPACE, and PG_TYPE catalog tables.

select "table", encoded, diststyle, sortkey1, skew_sortkey1, skew_rowsfrom svv_table_infoorder by 1;