Skip to main content
Version: 3.2

Query Profile Overview

This topic introduces how to view and analyze the Query Profile. The Query Profile records execution information for all working nodes involved in a query. You can quickly identify bottlenecks affecting the query performance through Query Profile.

Enable Query Profile

You can enable Query Profile by setting the variable enable_profile to true:

SET enable_profile = true;

Enable Query Profile for Slow Queries

It is not recommended to enable Query Profile in a production environment on a global, long-term basis. This is because the data collection and processing of Query Profile may impose additional burdens on the system. However, if you need to capture and analyze slow queries, you can enable Query Profile only for slow queries. This can be achieved by setting the variable big_query_profile_threshold to a time duration greater than 0s. For example, if this variable is set to 30s, it means that only queries with an execution time exceeding 30 seconds will trigger Query Profile. This ensures system performance while effectively monitoring slow queries.

-- 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';

Enable Runtime Query Profile

Some queries may take a long time to execute, ranging from seconds to hours. Often, it is challenging to determine whether a query is still in progress or the system has crashed before the query completes. To address this issue, StarRocks introduced the Runtime Query Profile feature in v3.1 and later. This feature allows you to collect and report Query Profile data at fixed time intervals during query execution. This gives you the insight into the query's execution progress and potential bottlenecks in real-time, without waiting for the query to finish. In this way, you can monitor and optimize the query process more effectively.

When Query Profile is enabled, this feature is automatically activated, with a default reporting interval of 10 seconds. You can adjust the interval by modifying the variable runtime_profile_report_interval:

SET runtime_profile_report_interval = 30;

Runtime Query Profile has the same format and content as regular Query Profile. You can analyze Runtime Query Profile just like analyzing regular Query Profile to understand the performance metrics of queries running in the cluster.

Configure Query Profile Behavior

Configuration TypeConfiguration ItemValid ValuesDefault ValueDescription
Session Variableenable_profiletrue/falsefalseWhether to enable Query Profile. true means to enable this feature.
Session Variablepipeline_profile_level1/21Set the level of Query Profile. 1 indicates merging the metrics of the Query Profile; 2 indicates retaining the original structure of the Query Profile. If this item is set as 2, all visualization analysis tools will no longer be applicable, therefore, it is generally not recommended to change this value.
Session Variableruntime_profile_report_intervalPositive integer10The report interval of Runtime Query Profile. Unit: second.
Session Variablebig_query_profile_thresholdString0sIf the execution time of a big query exceeds this value, Query Profile is automatically enabled for this query. Setting this item to 0s indicates this feature is disabled. Its value can be represented by a integral number followed by a unit, where the units can be ms, s, m.
FE Dynamic Configuration Itemenable_statistics_collect_profiletrue/falsefalseWhether to enable Query Profile for statistics collection-related queries. true means to enable this feature.

Obtain Query Profile via Web UI

Follow these steps to obtain Query Profile:

  1. Access http://<fe_ip>:<fe_http_port> in your browser.
  2. On the page that appears, click queries in the top navigation.
  3. In the Finished Queries list, select the query you want to analyze and click the link in the Profile column.

img

You will be redirected to the detailed page of the selected Query Profile.

img

Obtain Query Profile via get_query_profile

The following example shows how to obtain Query Profile via the function get_query_profile:

-- 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 |
+--------------------------------------+
-- Obtain the query profile.
select get_query_profile('502f3c04-8f5c-11ee-a41f-b22a2c00f66b')\G

Analyze Query Profile

The raw content generated by Query Profile may contain numerous metrics. For detailed description of these metrics, see Query Profile Structure and Detailed Metrics.

However, most users may find that it is not easy to analyze this raw text directly. To address this issue, StarRocks provides a Text-based Query Profile Visualized Analysis method. You can use this feature to gain a more intuitive understanding of the complex Query Profile.