- Introduction to StarRocks
- Quick Start
- Table Design
- Data Loading
- Overview of data loading
- Load data from a local file system or a streaming data source using HTTP PUT
- Load data from HDFS or cloud storage
- Continuously load data from Apache Kafka®
- Bulk load using Apache Spark™
- Load data using INSERT
- Realtime synchronization from MySQL
- Continuously load data from Apache Flink®
- Change data through loading
- Transform data at loading
- Data Unloading
- Using StarRocks
- Data Recovery
- User Privilege and Authentication
- Performance Tuning
- SQL Reference
- User Account Management
- Cluster Management
- ADMIN CANCEL REPAIR
- ADMIN CHECK TABLET
- ADMIN REPAIR
- ADMIN SET CONFIG
- ADMIN SET REPLICA STATUS
- ADMIN SHOW CONFIG
- ADMIN SHOW REPLICA DISTRIBUTION
- ADMIN SHOW REPLICA STATUS
- ALTER SYSTEM
- CANCEL DECOMMISSION
- CREATE FILE
- DROP FILE
- INSTALL PLUGIN
- SHOW BACKENDS
- SHOW BROKER
- SHOW FRONTENDS
- SHOW FULL COLUMNS
- SHOW INDEX
- SHOW PLUGINS
- SHOW TABLE STATUS
- SHOW FILE
- UNINSTALL PLUGIN
- ALTER DATABASE
- ALTER TABLE
- ALTER VIEW
- ALTER RESOURCE
- CANCEL BACKUP
- CANCEL RESTORE
- CREATE DATABASE
- CREATE INDEX
- CREATE MATERIALIZED VIEW
- CREATE REPOSITORY
- CREATE RESOURCE
- CREATE TABLE AS SELECT
- CREATE TABLE LIKE
- CREATE TABLE
- CREATE VIEW
- CREATE FUNCTION
- DROP DATABASE
- DROP INDEX
- DROP MATERIALIZED VIEW
- DROP REPOSITORY
- DROP RESOURCE
- DROP TABLE
- DROP VIEW
- DROP FUNCTION
- SHOW RESOURCES
- SHOW FUNCTION
- TRUNCATE TABLE
- ALTER ROUTINE LOAD
- BROKER LOAD
- CANCEL LOAD
- GROUP BY
- PAUSE ROUTINE LOAD
- RESUME ROUTINE LOAD
- ROUTINE LOAD
- SHOW ALTER
- SHOW BACKUP
- SHOW CREATE TABLE
- SHOW CREATE VIEW
- SHOW DATA
- SHOW DATABASES
- SHOW DELETE
- SHOW DYNAMIC PARTITION TABLES
- SHOW EXPORT
- SHOW LOAD
- SHOW PARTITIONS
- SHOW PROPERTY
- SHOW REPOSITORIES
- SHOW RESTORE
- SHOW ROUTINE LOAD
- SHOW ROUTINE LOAD TASK
- SHOW SNAPSHOT
- SHOW TABLES
- SHOW TABLET
- SHOW TRANSACTION
- SPARK LOAD
- STOP ROUTINE LOAD
- STREAM LOAD
- Data Types
- Auxiliary Commands
- Function Reference
- Date Functions
- Aggregate Functions
- Geographic Functions
- String Functions
- JSON Functions
- Bitmap Functions
- Array Functions
- Cast Functions
- Hash Functions
- Cryptographic Functions
- Math Functions
- Utility Functions
- System variables
- Error code
- System limits
- SQL Reference
How to optimize query performance is a frequently asked question. Slow queries impair user experience as well as cluster performance. It is important to analyze and optimize query performance.
You can view query information in
fe/log/fe.audit.log. Each query corresponds to a
QueryID that can be used to search for the
Profile of a query.
QueryPlan is the execution plan generated by FE by parsing SQL statements.
Profile is BE execution result and contains information such as the time consumed by each step and the volume of data processed at each step.
In StarRocks, the lifecycle of an SQL statement can be divided into three phases: query parsing, query planning, and query execution. Query parsing is generally not a bottleneck because the required QPS of analytical workloads is not high.
Query performance in StarRocks is determined by query planning and query execution. Query planning is responsible for coordinating operators (Join/Order/Aggregate), and query execution is responsible for running specific operations.
A query plan provides the DBA with a macro perspective to access query information. A query plan is the key to query performance and a good resource for the DBA to reference. The following code snippet uses
TPCDS query96 as an example to show how to view a query plan.
-- query96.sql select count(*) from store_sales ,household_demographics ,time_dim , store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 8 and time_dim.t_minute >= 30 and household_demographics.hd_dep_count = 5 and store.s_store_name = 'ese' order by count(*) limit 100;
There are two types of query plans – logical query plan and physical query plan. The query plan described here refers to the logical query plan. The query plan corresponding to
TPCDS query96.sql is shown below.
+------------------------------------------------------------------------------+ | Explain String | +------------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS:<slot 11> | | PARTITION: UNPARTITIONED | | RESULT SINK | | 12:MERGING-EXCHANGE | | limit: 100 | | tuple ids: 5 | | | | PLAN FRAGMENT 1 | | OUTPUT EXPRS: | | PARTITION: RANDOM | | STREAM DATA SINK | | EXCHANGE ID: 12 | | UNPARTITIONED | | | | 8:TOP-N | | | order by: <slot 11> ASC | | | offset: 0 | | | limit: 100 | | | tuple ids: 5 | | | | | 7:AGGREGATE (update finalize) | | | output: count(*) | | | group by: | | | tuple ids: 4 | | | | | 6:HASH JOIN | | | join op: INNER JOIN (BROADCAST) | | | hash predicates: | | | colocate: false, reason: left hash join node can not do colocate | | | equal join conjunct: `ss_store_sk` = `s_store_sk` | | | tuple ids: 0 2 1 3 | | | | | |----11:EXCHANGE | | | tuple ids: 3 | | | | | 4:HASH JOIN | | | join op: INNER JOIN (BROADCAST) | | | hash predicates: | | | colocate: false, reason: left hash join node can not do colocate | | | equal join conjunct: `ss_hdemo_sk`=`household_demographics`.`hd_demo_sk`| | | tuple ids: 0 2 1 | | | | | |----10:EXCHANGE | | | tuple ids: 1 | | | | | 2:HASH JOIN | | | join op: INNER JOIN (BROADCAST) | | | hash predicates: | | | colocate: false, reason: table not in same group | | | equal join conjunct: `ss_sold_time_sk` = `time_dim`.`t_time_sk` | | | tuple ids: 0 2 | | | | | |----9:EXCHANGE | | | tuple ids: 2 | | | | | 0:OlapScanNode | | TABLE: store_sales | | PREAGGREGATION: OFF. Reason: `ss_sold_time_sk` is value column | | partitions=1/1 | | rollup: store_sales | | tabletRatio=0/0 | | tabletList= | | cardinality=-1 | | avgRowSize=0.0 | | numNodes=0 | | tuple ids: 0 | | | | PLAN FRAGMENT 2 | | OUTPUT EXPRS: | | PARTITION: RANDOM | | | | STREAM DATA SINK | | EXCHANGE ID: 11 | | UNPARTITIONED | | | | 5:OlapScanNode | | TABLE: store | | PREAGGREGATION: OFF. Reason: null | | PREDICATES: `store`.`s_store_name` = 'ese' | | partitions=1/1 | | rollup: store | | tabletRatio=0/0 | | tabletList= | | cardinality=-1 | | avgRowSize=0.0 | | numNodes=0 | | tuple ids: 3 | | | | PLAN FRAGMENT 3 | | OUTPUT EXPRS: | | PARTITION: RANDOM | | STREAM DATA SINK | | EXCHANGE ID: 10 | | UNPARTITIONED | | | | 3:OlapScanNode | | TABLE: household_demographics | | PREAGGREGATION: OFF. Reason: null | | PREDICATES: `household_demographics`.`hd_dep_count` = 5 | | partitions=1/1 | | rollup: household_demographics | | tabletRatio=0/0 | | tabletList= | | cardinality=-1 | | avgRowSize=0.0 | | numNodes=0 | | tuple ids: 1 | | | | PLAN FRAGMENT 4 | | OUTPUT EXPRS: | | PARTITION: RANDOM | | STREAM DATA SINK | | EXCHANGE ID: 09 | | UNPARTITIONED | | | | 1:OlapScanNode | | TABLE: time_dim | | PREAGGREGATION: OFF. Reason: null | | PREDICATES: `time_dim`.`t_hour` = 8, `time_dim`.`t_minute` >= 30 | | partitions=1/1 | | rollup: time_dim | | tabletRatio=0/0 | | tabletList= | | cardinality=-1 | | avgRowSize=0.0 | | numNodes=0 | | tuple ids: 2 | +------------------------------------------------------------------------------+ 128 rows in set (0.02 sec)
Query 96 shows a query plan that involves several StarRocks concepts.
|avgRowSize||The average size of the scanned data rows|
|cardinality||The total number of data rows in the scanned table|
|colocate||Whether the table is in colocate mode|
|numNodes||The number of nodes to be scanned|
|predicates||Predicates, the query filters|
The query plan of Query 96 is divided into five fragments, numbered from 0 to 4. The query plan can be read one by one in a bottom-up manner.
Fragment 4 is responsible for scanning the
time_dim table and executing the related query condition (i.e.
time_dim.t_hour = 8 and time_dim.t_minute >= 30) in advance. This step is also known as predicate pushdown. StarRocks decides whether to enable
PREAGGREGATION for aggregation tables. In the previous figure, preaggregation of
time_dim is disabled. In this case, all dimension columns of
time_dim are read, which may negatively affect performance if there are many dimension columns in the table. If the
time_dim table selects
range partition for data division, several partitions will be hit in the query plan and irrelevant partitions will be automatically filtered out. If there is a materialized view, StarRocks will automatically select the materialized view based on the query. If there is no materialized view, the query will automatically hit the base table (for example,
rollup: time_dim in the previous figure).
When the scan is complete, Fragment 4 ends. Data will be passed to other fragments, as indicated by EXCHANGE ID : 09 in the previous figure, to the receiving node labeled 9.
For the query plan of Query 96, Fragment 2, 3, and 4 have similar functions but they are responsible for scanning different tables. Specifically, the
Order/Aggregation/Join operations in the query are performed in Fragment 1.
Fragment 1 uses the
BROADCAST method to perform
Order/Aggregation/Join operations i, that is, to broadcast the small table to the large table. If both tables are large, we recommend that you use the
SHUFFLE method. Currently, StarRocks only supports
HASH JOIN. The
colocate field is used to show that the two joined tables are partitioned and bucketed in the same way, so that the join operation can be performed locally without migrating the data. When the Join operation is complete, the upper-level
order by, and
top-n operations will be performed.
By removing the specific expressions (only keep the operators), the query plan can be presented in a more macroscopic view, as shown in the following figure.
SQL fingerprint is used to optimize slow queries and improve system resource utilization. StarRocks uses the SQL fingerprint feature to normalize SQL statements in the slow query log (
fe.audit.log.slow_query), categorizes the SQL statements into different types, and calculates the MD5 hash value of each SQL type to identify slow queries. The MD5 hash value is specified by the field
2021-12-27 15:13:39,108 [slow_query] |Client=172.26.xx.xxx:54956|User=root|Db=default_cluster:test|State=EOF|Time=2469|ScanBytes=0|ScanRows=0|ReturnRows=6|StmtId=3|QueryId=824d8dc0-66e4-11ec-9fdc-00163e04d4c2|IsQuery=true|feIp=172.26.92.195|Stmt=select count(*) from test_basic group by id_bigint|Digest=51390da6b57461f571f0712d527320f4
SQL statement normalization transforms a statement text into a more normalized format and preserves only important statement structure.
Preserves object identifiers, such as database and table names.
Converts constants into a question mark (?).
Deletes comments and formats spaces.
For example, the following two SQL statements belong to the same type after normalization.
- SQL statements before normalization
SELECT * FROM orders WHERE customer_id=10 AND quantity>20 SELECT * FROM orders WHERE customer_id = 20 AND quantity > 100
- SQL statement after normalization
SELECT * FROM orders WHERE customer_id=? AND quantity>?