How to Kill Queries in Amazon Redshift

Killing a Query

Before you can kill a query, you need to identify which queries are causing problems. Use the following SQL to list all running queries along with their duration:

SELECT
    pid,
    user_name,
    starttime,
    query,
    elapsed
FROM
    stv_recents
WHERE
    state = 'Running'
ORDER BY
    elapsed DESC;

Expected Output:

The elapsed column shows how long the query has been running.

Once you’ve identified the problematic query (using the pid from the list above), you can terminate it. Use the following command:

CANCEL pid;

Example:

CANCEL 123;

Verifying the Query Termination

To ensure the query has been terminated, re-run the initial query:

SELECT
    pid,
    user_name,
    starttime,
    query,
    elapsed
FROM
    stv_recents
WHERE
    state = 'Running'
ORDER BY
    elapsed DESC;

The terminated query should no longer appear in the results.

Handling Blocked Queries

Sometimes, queries get blocked and need to be killed to release the resources. You can identify blocked queries using the STV_BLOCKED table.

SELECT
    b.pid,
    b.user_name,
    b.db,
    b.query,
    l.pid AS blocked_by_pid,
    l.user_name AS blocked_by_user
FROM
    stv_blocked b
JOIN
    stv_locks l
ON
    b.lock_owner_pid = l.pid;

Expected Output:

To kill a blocked query, use the CANCEL command as shown earlier:

CANCEL 5678;

Automating Query Termination

For databases with stringent performance requirements, automating the detection and termination of long-running queries can be beneficial. Below is an example procedure for terminating queries running longer than a specified threshold:

DO $$ 
DECLARE
    long_running_query RECORD;
BEGIN
    FOR long_running_query IN
        SELECT pid
        FROM stv_recents
        WHERE state = 'Running'
        AND elapsed > interval '00:30:00' -- Replace with desired threshold
    LOOP
        EXECUTE 'CANCEL ' || long_running_query.pid;
    END LOOP;
END;
$$;

Important Considerations

• Permissions: Only users with appropriate permissions can cancel queries.
• Impact: Terminating a query can affect application performance or cause data inconsistency if performed incorrectly.
• Monitoring: Regular monitoring of query performance helps in early detection of issues.

Conclusion

Managing long-running queries effectively can ensure optimal performance of your Amazon Redshift cluster. By following these steps, you can quickly identify and terminate any problematic queries. Implementing automated solutions for regular monitoring and query termination can further help maintain performance.

Ensure to test all commands and scripts in a non-production environment before deploying them to production to avoid unintentional disruptions.

Other posts you may like:

• Comparing Amazon RDS and Redshift
• Amazon Athena vs Redshift