Query Profile Structure and Metrics
Overview
A Query Profile is a detailed report that provides insights into the execution of a SQL query within StarRocks. It offers a comprehensive view of the query's performance, including the time spent on each operation, the amount of data processed, and other relevant metrics. This information is invaluable for optimizing query performance, identifying bottlenecks, and troubleshooting issues.
80% of real-world slow queries are solved by spotting one of three red-flag metrics. This cheat-sheet gets you there before you drown in numbers.
Quick-Start
Profile a recent query:
1. List recent query IDs
A query ID is needed to analyze a query profile. Use SHOW PROFILELIST;
:
SHOW PROFILELIST;
SHOW PROFILELIST
is detailed in Text-based Query Profile Visualized Analysis. See that page if you are getting started.
2. Open the profile side-by-side with your SQL
Run ANALYZE PROFILE FOR <query_id>\G
or click Profile in the CelerData Web UI.
3. Skim the “Execution Overview” banner
Examine key metrics for overall execution performance:
- QueryExecutionWallTime: Total wall clock time for query execution
- QueryPeakMemoryUsagePerNode: Peak memory usage per node, with values exceeding 80% of BE memory indicating potential risks of data spill or Out-of-Memory (OOM) errors
- QueryCumulativeCpuTime / WallTime < 0.5 * num_cpu_cores means CPU is waiting (likely I/O or network)
If none fire, your query is usually fine—stop here.
4. Drill one level deeper
Identify the operators that consume the most time or the most memory, analyze their metrics, and determine the underlying cause to pinpoint performance bottlenecks.
The "Operator Metrics" section offers numerous guidelines to aid in identifying the root cause of performance issues.
Core Concepts
Query Execution Flow
The comprehensive execution flow of a SQL query involves the following stages:
- Planning: The query undergoes parsing, analysis, and optimization, culminating in the generation of a query plan.
- Scheduling: The scheduler and coordinator work together to distribute the query plan to all participating backend nodes.
- Execution: The query plan is executed using the pipeline execution engine.
Query Plan Structure
The StarRocks execution engine is designed to execute queries in a distributed manner, and the structure of a Query Profile reflects this design. The following components make up the distributed query plan:
- Fragment: The highest level of the execution tree, representing a logical unit of work. A query can be divided into one or more fragments.
- FragmentInstance: Each fragment is instantiated multiple times, with each instance (FragmentInstance) executed on a different computing node. This allows for parallel processing across nodes.
- Pipeline: A FragmentInstance is further divided into multiple pipelines, which are sequences of connected Operator instances. Pipelines define the execution path for a FragmentInstance.
- PipelineDriver: To maximize the utilization of computing resources, each pipeline can have multiple instances, known as PipelineDrivers. These drivers execute the pipeline in parallel, leveraging multiple computing cores.
- Operator: The fundamental execution unit, an Operator instance is a part of a PipelineDriver. Operators implement specific algorithms, such as aggregation, join, or scan, to process data.
Pipeline Execution Engine Concepts
The Pipeline Engine is a key component of the StarRocks execution engine. It is responsible for executing the query plan in a parallel and efficient manner. The Pipeline Engine is designed to handle complex query plans and large volumes of data, ensuring high performance and scalability.
Key concepts in the Pipeline Engine:
- Operator: A fundamental unit of execution responsible for implementing specific algorithms (e.g., aggregation, join, scan)
- Pipeline: A sequence of connected Operator instances representing the execution path
- PipelineDriver: Multiple instances of a pipeline for parallel execution
- Schedule: Non-blocking scheduling of pipelines using user-space time-slicing
Metric Merging Strategy
By default, StarRocks merges the FragmentInstance and PipelineDriver layers to reduce profile volume, resulting in a simplified three-layer structure:
- Fragment
- Pipeline
- Operator
You can control this merging behavior through the session variable pipeline_profile_level
:
1
(Default): Merged three-layer structure2
: Original five-layer structure- Other values: Treated as
1
When merging metrics, different strategies are used based on metric type:
-
Time-related metrics: Take the average
- Example:
OperatorTotalTime
is the average time consumption __MAX_OF_OperatorTotalTime
and__MIN_OF_OperatorTotalTime
record extremes
- Example:
-
Non-time-related metrics: Sum the values
- Example:
PullChunkNum
is the sum across all instances __MAX_OF_PullChunkNum
and__MIN_OF_PullChunkNum
record extremes
- Example:
-
Constant metrics: Same value across all instances (e.g.,
DegreeOfParallelism
)
Significant differences between MIN and MAX values often indicate data skew, particularly in aggregation and join operations.
Query Profile Metrics
Summary Metrics
Basic information about the query execution:
Metric | Description |
---|---|
Total | The total time consumed by the query, including Planning, Executing, and Profiling phase durations. |
Query State | Query state, possible states include Finished, Error, and Running. |
Query ID | Unique identifier for the query. |
Start Time | Timestamp when the query started. |
End Time | Timestamp when the query ended. |
Total | Total duration of the query. |
Query Type | Type of the query. |
Query State | Current state of the query. |
StarRocks Version | Version of StarRocks used. |
User | User who executed the query. |
Default Db | Default database used for the query. |
Sql Statement | SQL statement executed. |
Variables | Important variables used for the query. |
NonDefaultSessionVariables | Non-default session variables used for the query. |
Collect Profile Time | Time taken to collect the profile. |
IsProfileAsync | Indicates if the profile collection was asynchronous. |