Best practices for killing bad SQL-E-MapReduce(EMR)-阿里云帮助中心

In high-QPS clusters, abnormal SQL statements can consume large amounts of CPU and memory, degrading cluster performance or even causing out-of-memory (OOM) errors. This topic describes how to quickly detect and terminate abnormal SQL statements, and how to configure automatic protection policies to prevent resource exhaustion.

Step 1: Detect bad SQL

Run the following commands to view running queries and identify SQL statements with abnormal CPU or memory consumption.

Note

We recommend that you use SHOW PROC '/global_current_queries' (requires v3.4 or later), which lists running queries across all FE nodes. For versions earlier than v3.4, use SHOW PROC '/current_queries', which only shows queries on the current FE node.

-- View running queries across all FE nodes (v3.4+, recommended)
SHOW PROC '/global_current_queries';

-- For versions earlier than v3.4, only the current FE node is shown
SHOW PROC '/current_queries';

In the query results, pay attention to the following fields:

Metric	High-risk threshold	Description
CPUTime	> 60s	Abnormal CPU consumption, possibly caused by a Cartesian product or a missing predicate.
MemoryUsage	> 2 GB	Excessive memory usage, with a risk of OOM.

Step 2: Kill bad SQL

After you locate the abnormal SQL statement, terminate the query by its ConnectionId or QueryId.

-- Terminate by ConnectionId (obtained from the Step 1 results)
KILL QUERY <ConnectionId>;

-- Terminate by QueryId (v3.1+)
KILL QUERY '<QueryId>';

Note

Terminating a query by QueryId requires StarRocks v3.1 or later. ConnectionId is a number and does not need quotation marks; QueryId is a string and must be enclosed in single quotation marks.

Step 3: Analyze the root cause (optional)

After you terminate the abnormal SQL statement, you can Enable slow query Profile collection to analyze the root cause and prevent the issue from recurring.

Enable slow query Profile collection

Set a slow query threshold so that Profiles are collected only for queries that exceed the threshold, with no performance impact on normal queries.

-- Enable slow query Profile collection (applies only to queries over the threshold; zero overhead for normal queries)
SET GLOBAL big_query_profile_threshold = '30s';

View and analyze Profiles

-- View collected Profiles
SHOW PROFILELIST LIMIT 50;

-- Analyze the execution bottleneck of a specific query
ANALYZE PROFILE FROM '<query_id>';

Configure automatic protection

Beyond manual termination, you can use resource groups and query queues for automatic protection, preventing abnormal SQL from exhausting cluster resources at the source.

Resource groups: automatically reject over-limit queries

Use resource groups to set upper limits on CPU and memory. Queries that exceed these limits are automatically rejected.

SET GLOBAL enable_pipeline_engine = true;

CREATE RESOURCE GROUP bigQueryGuard
TO (db='your_db')
WITH (
    'cpu_weight' = '10',
    'mem_limit' = '20%',
    'big_query_cpu_second_limit' = '300',      -- Automatically reject when CPU exceeds 300s
    'big_query_mem_limit' = '5368709120'        -- Automatically reject when memory exceeds 5 GB
);

Important

The parameter values above are for reference only. Adjust big_query_cpu_second_limit and big_query_mem_limit based on your actual cluster configuration and business requirements.

Query queues: prevent concurrency overload

When cluster resource usage reaches the threshold, new queries wait in a queue to avoid overload.

SET GLOBAL enable_query_queue_select = true;
SET GLOBAL query_queue_concurrency_limit = 100;
SET GLOBAL query_queue_mem_used_pct_limit = 0.9;
SET GLOBAL query_queue_cpu_used_permille_limit = 800;
SET GLOBAL query_queue_max_queued_queries = 100;
SET GLOBAL query_queue_pending_timeout_second = 480;

The following table describes the parameters.

Parameter	Description
enable_query_queue_select	Whether to enable the query queue. Set to `true` to enable it.
query_queue_concurrency_limit	The maximum number of concurrent queries. Queries beyond this value wait in the queue.
query_queue_mem_used_pct_limit	The memory usage percentage threshold. New queries are queued when it is exceeded.
query_queue_cpu_used_permille_limit	The CPU usage per-mille threshold (800 means 80%). New queries are queued when it is exceeded.
query_queue_max_queued_queries	The maximum number of queued queries. New queries are rejected when it is exceeded.
query_queue_pending_timeout_second	The queue timeout in seconds. Queries are canceled when the timeout is reached.

Note

The parameter values above are for reference only. Adjust them based on your actual cluster size and workload.

Quick reference

Operation	Command or method
Detect bad SQL	`SHOW PROC '/global_current_queries'`
Kill	`KILL QUERY <ConnectionId>`
Profile collection	`SET GLOBAL big_query_profile_threshold='30s'`
Automatic protection	Resource group `big_query_cpu/mem_limit` + query queue