Query Profile Overview
Introduction
Query Profile records execution information for all working nodes involved in a query, helping you quickly identify bottlenecks affecting query performance. It is a powerful tool for diagnosing and tuning query performance in StarRocks.
From v3.3.0 onwards, StarRocks supports providing Query Profile for data loading with INSERT INTO FILES() and Broker Load. For details of the metrics involved, see OlapTableSink Operator.
How to Enable Query Profile
Enable Query Profile
You can enable Query Profile by setting the variable enable_profile
to true
:
SET enable_profile = true;
SET GLOBAL enable_profile = true;
Query Profile for Slow Queries
It is not recommended to enable Query Profile globally in production for long periods, as it may impose additional system overhead. To capture and analyze only slow queries, set the variable big_query_profile_threshold
to a time duration greater than 0s
. For example, setting it to 30s
means only queries exceeding 30 seconds will trigger Query Profile.
-- 30 seconds
SET global big_query_profile_threshold = '30s';
-- 500 milliseconds
SET global big_query_profile_threshold = '500ms';
-- 60 minutes
SET global big_query_profile_threshold = '60m';
Runtime Query Profile
For long-running queries, it can be difficult to determine progress or detect issues before completion. The Runtime Query Profile feature (v3.1+) collects and reports Query Profile data at fixed intervals during execution, providing real-time insight into query progress and bottlenecks.
When Query Profile is enabled, Runtime Query Profile is automatically activated with a default reporting interval of 10 seconds. Adjust the interval with runtime_profile_report_interval
:
SET runtime_profile_report_interval = 30;
Configurations
Configuration Item | Type | Valid Values | Default | Description |
---|---|---|---|---|
enable_profile | Session Var | true/false | false | Enable Query Profile |
pipeline_profile_level | Session Var | 1/2 | 1 | 1: merge metrics; 2: retain original structure (disables visualization tools) |
runtime_profile_report_interval | Session Var | Positive integer | 10 | Runtime Query Profile report interval (seconds) |
big_query_profile_threshold | Session Var | String | 0s | Enable Query Profile for queries exceeding this duration (e.g., '30s', '500ms', '60m') |
enable_statistics_collect_profile | FE Dynamic | true/false | false | Enable Query Profile for statistics collection-related queries |
How to Obtain Query Profile
Via Web UI
- Access
http://<fe_ip>:<fe_http_port>
in your browser. - Click queries in the top navigation.
- In the Finished Queries list, select the query you want to analyze and click the link in the Profile column.
You will be redirected to the detailed page of the selected Query Profile.
Via SQL Function (get_query_profile
)
Example workflow:
last_query_id()
: Returns the ID of the most recently executed query in your session. Useful for quickly retrieving the profile of your last query.show profilelist;
: Lists recent queries along with their IDs and status. Use this to find thequery_id
needed for profile analysis.get_query_profile('<query_id>')
: Returns the detailed execution profile for the specified query. Use this to analyze how a query was executed and where time or resources were spent.
-- Enable the profiling feature.
set enable_profile = true;
-- Run a simple query.
select 1;
-- Get the query_id of the query.
select last_query_id();
+--------------------------------------+
| last_query_id() |
+--------------------------------------+
| bd3335ce-8dde-11ee-92e4-3269eb8da7d1 |
+--------------------------------------+
-- Get the list of profiles
show profilelist;
-- Obtain the query profile.
select get_query_profile('502f3c04-8f5c-11ee-a41f-b22a2c00f66b')\G
In Managed Version
In StarRocks Managed (Enterprise) environments, you can conveniently access query profiles directly from the query history in the web console. The managed UI provides an intuitive, visual representation of each query's execution profile, making it easy to analyze performance and identify bottlenecks without manual SQL commands.
Interpret Query Profile
Explain Analyze
Most users may find it challenging to analyze the raw text directly. StarRocks provides a Text-based Query Profile Visualized Analysis method for a more intuitive understanding.
Manged Version
In the StarRocks Enterprise Edition (EE), the Managed Version provides a built-in visualization tool for query profiles. This tool offers an interactive, graphical interface that makes it much easier to interpret complex query execution details compared to raw text output.
Key features of the visualization tool include:
- Operator-level breakdown: View the execution plan as a tree or graph, with each operator's metrics (time, rows, memory) clearly displayed.
- Bottleneck highlighting: Quickly identify slow or resource-intensive operators through color-coded indicators.
- Drill-down capability: Click on any operator to see detailed statistics, including input/output rows, CPU time, memory usage, and more.
How to use:
- Open the StarRocks Managed web console.
- Navigate to the Query or Query History section.
- Select a query and click the Profile or Visualize button.
- Explore the visualized profile to analyze performance and identify optimization opportunities.
This visualization tool is exclusive to the Managed/Enterprise Edition and is designed to accelerate troubleshooting and performance tuning for complex workloads.