Long running queries
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.
- 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