Long running queries
Advertisement
RStoolKit Results: Long running queries (> 30mins)
This is a new to you. Its just telling the queries are running more than 30mins.
Find the long running queries:
select
a.query,
a.service_class,
a.query_execution_time,
a.query_queue_time,
case
when b.concurrency_scaling_status = 1 then 'Ran on a concurrency scaling cluster'
else 'Ran on the main cluster' end as concurrency_scaling_status,
b.querytxt
from
SVL_QUERY_METRICS a
join stl_query b on
a.query = b.query
where
a.query_execution_time>1800
How to fix the problem:
This will be solved by optimizing your queries only. But still you can solve many long running queries if you have right table design in place.
- Sort Keys are important.
- Choose the right dist key, if you are not sure then use auto dist style.
- Run vacuum and analyze frequently.
- Tune the WLM settings, if you are not sure about this then go for Auto WLM.
- Use concurrency scaling if need.
- Find the Most frequent alert from the STL_ALERT_EVENT_LOG table.
Run the below query to find exactly which step in that query consumed more space.
External Links:
- svl_query_metrics - Documentation
- RedShift table design - Playbook
- RedShift vacuum Utility - Python Based
- Automate Vacuum Analyze Utility - Shell based with more control
- STL_ALERT_EVENT_LOG - documentation
- Concurrency Scaling in RedShift
Loading Comments