E-MapReduce:Query Profile performance diagnostics and optimization case studies

Query Profile visualization

StarRocks Manager supports visual analytics for Query Profiles. For more information, see Query Profile introduction.

Identify query bottlenecks

In the Query Profile visualization in StarRocks Manager, operators that take longer to run appear in darker colors. The three operators with the longest execution duration are highlighted, which makes query bottlenecks easy to identify

Why is the CONNECTOR_SCAN time longer than the SCANTIME?

CONNECTOR_SCAN represents the cumulative active time of an operator across all of its parallel instances. This value includes SCANTIME, IOTaskExecTime, OperatorTotalTime, and other components. As a result, CONNECTOR_SCAN can be numerically larger than the SCANTIME of a single instance.

Optimize large-field queries that cause out-of-memory (OOM) errors

When you query a table that contains large fields such as TEXT or VARCHAR, reading or computing too much large-field data can cause an out-of-memory (OOM) error. For memory limit errors that data skew causes instead, see Optimization measures for memory limit errors (Memory of process exceed limit) caused by data skew. Apply the following measures to reduce memory consumption:

• Avoid SELECT *. Explicitly specify only the columns that you need to reduce the data volume that is scanned and loaded into memory.

• Keep large fields out of aggregate operations such as GROUP BY and DISTINCT to prevent oversized intermediate result sets.

• Enable the spill to disk feature to relieve memory pressure by setting the session variable enable_spill=true.

• Optimize the table schema by moving large text fields into a separate table, and keep only indexes and small fields in the primary table. This table-splitting approach does not take effect in aggregation scenarios.

Create an index

• Create a Bitmap index when you create a table.

CREATE TABLE `student_info` (
  `s_stukey` bigint(20) NULL COMMENT "",
  `s_name` varchar(65533) NULL COMMENT "",
  `s_gender` varchar(65533) NULL COMMENT "",
  INDEX index1 (s_gender) USING BITMAP COMMENT 'index1'
) ENGINE=OLAP
DUPLICATE KEY(`s_stukey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`s_stukey`);
INSERT INTO student_info
VALUES
    (001,'student#000000019','male'),
    (002,'student#000000020','male'),
    (003,'student#000000021','male'),
    (004,'student#000000022','female');

• Create a Bitmap index on an existing table by using CREATE INDEX.

CREATE INDEX index_name ON table_name (column_name) [USING BITMAP] [COMMENT ''];

Check the index creation progress

Run the following command to check the progress of an index creation task:

SHOW ALTER TABLE COLUMN [FROM db_name];

View an index

Run the following command to view the indexes on a table:

SHOW {INDEX[ES] | KEY[S] } FROM [db_name.]table_name [FROM db_name];

The following output is returned:

MySQL [db_test]> show index from student_info;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| db_test.student_info   |            | index1   |              | s_gender    |           |             |          |        |      | BITMAP     | index1  |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.01 sec)

Drop an index

Run the following command to drop an index:

DROP INDEX index_name ON [db_name.]table_name;

Test a single-column query

1. Run a query that filters the s_gender column.

   select * from student_info where s_gender='male';

2. View the Profile.

   Click OLAP_SCAN, then click the Node Details tab on the right. Filter the metrics for Bitmap to confirm that the Bitmap index has taken effect.

Create an index

When you create a table, you can create a Bloom filter index by specifying bloom_filter_columns in the PROPERTIES clause. The following example shows how to do this.

CREATE TABLE table1
(
  k1 BIGINT,
  k2 LARGEINT,
  v1 VARCHAR(2048) REPLACE,
  v2 SMALLINT DEFAULT "10"
)
ENGINE = olap
PRIMARY KEY(k1, k2)
DISTRIBUTED BY HASH (k1, k2) BUCKETS 10
PROPERTIES("bloom_filter_columns" = "k1,k2"); -- Separate multiple index columns with commas (,).

View an index

Run the following command to view the indexes on a table:

SHOW CREATE TABLE table1;

Modify an index

For example:

• Add a Bloom filter index for column v1.

ALTER TABLE table1 SET ("bloom_filter_columns" = "k1,k2,v1");

• Remove the Bloom filter index for column k2.

ALTER TABLE table1 SET ("bloom_filter_columns" = "k1");

• Delete all Bloom filter indexes from table1.

ALTER TABLE table1 SET ("bloom_filter_columns" = "");

Example

1. Take the customer table from TPC-H as an example. Add a Bloom filter index on a high-cardinality column that is not a sort key, such as the c_phone column.

   ALTER TABLE tpc_h_sf100.customer SET ("bloom_filter_columns" = "c_custkey, c_phone");

2. View the index to confirm that the Bloom filter index has been added.

   SHOW CREATE TABLE tpc_h_sf100.customer;

   | customer | CREATE TABLE `customer` (
     `c_custkey` bigint(20) NULL COMMENT "",
     `c_name` varchar(65533) NULL COMMENT "",
     `c_address` varchar(65533) NULL COMMENT "",
     `c_nationkey` bigint(20) NULL COMMENT "",
     `c_phone` varchar(65533) NULL COMMENT "",
     `c_acctbal` double NULL COMMENT "",
     `c_mktsegment` varchar(65533) NULL COMMENT "",
     `c_comment` varchar(65533) NULL COMMENT "",
     `Gender` varchar(65533) NULL DEFAULT "default_value" COMMENT ""
   ) ENGINE=OLAP
   DUPLICATE KEY(`c_custkey`)
   COMMENT "OLAP"
   DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 24
   PROPERTIES (
   "replication_num" = "1",
   "bloom_filter_columns" = "c_custkey, c_phone",
   "in_memory" = "false",
   "storage_format" = "DEFAULT",
   "enable_persistent_index" = "false",
   "compression" = "LZ4"
   ); |

3. Run a query that filters the c_phone column.

   select * from tpc_h_sf100.customer where c_phone = "10-334-921-5346";

4. View the Profile.

   Click OLAP_SCAN, then click the Node Details tab on the right. Find the BloomFilterFilterRows metric to confirm that the Bloom filter index is active.

Optimization measures for memory limit errors (Memory of process exceed limit) caused by data skew

Data skew can also cause queries to fail with a Memory of process exceed limit error. For OOM errors that large fields cause instead, see Optimize large-field queries that cause out-of-memory (OOM) errors. If you encounter memory limit errors caused by data skew, try the following optimizations in addition to changing the bucketing key:

• Optimize the bucketing strategy: Use a high-cardinality column as the bucketing key, combine multiple columns for bucketing, or apply salting or random bucketing to hot values.

• Adjust the JOIN execution strategy: Use Broadcast Join when joining a small table with a large table. When joining two large tables, make sure the join keys are evenly distributed, or use DISTRIBUTE BY to redistribute the data.

• Dynamically adjust and optimize resource parameters: Change the bucketing method of the existing table, increase BE concurrent execution parameters such as parallel_fragment_exec_instance_num, or add more BE nodes or replicas to spread the load.

Test a query

1. Take the lineitem table from TPC-H as an example and run a query.

   select l_returnflag,l_linestatus,l_shipmode,sum(l_extendedprice) from lineitem group by l_returnflag,l_linestatus,l_shipmode;

2. The initial query takes 1115 ms to complete because no materialized view has been created.

3. View the Profile.

   Click OLAP_SCAN and go to the Node tab on the right. You can see that the Rollup scanned the lineitem table itself.

Create a materialized view

Run the following command to create a single-table materialized view:

CREATE MATERIALIZED VIEW material_test AS select l_returnflag,l_linestatus ,l_shipmode,sum(l_extendedprice) from lineitem group by l_returnflag,l_linestatus,l_shipmode;

Verify materialized view hit

1. Use the EXPLAIN command to check whether the query hits the single-table materialized view.

   explain select l_returnflag,l_linestatus ,l_shipmode,sum(l_extendedprice) from lineitem group by l_returnflag,l_linestatus,l_shipmode;

   In the returned result, rollup: material_test indicates that the query hit the materialized view named material_test.

   0:OlapScanNode
        TABLE: lineitem
        PREAGGREGATION: OFF. Reason: null
        partitions=1/1
        rollup: material_test
        tabletRatio=96/96
        tabletList=15646,15648,15650,15652,15654,15656,15658,15660,15662,15664 ...
        cardinality=600037902
        avgRowSize=14.285666
        numNodes=0

2. View the Profile.

   Click OLAP_SCAN. On the Node tab on the right, you can see that the query hits the materialized view and the query time is reduced to 0.05 ms.

Create a colocated table

Run the following statement to create a table in a Colocation Group:

CREATE TABLE tbl (k1 int, v1 int sum)
DISTRIBUTED BY HASH(k1)
BUCKETS 8
PROPERTIES(
    "colocate_with" = "group1"
);

Automatic deletion of a Colocation Group

When the last table in a Group is permanently deleted, the Group is also automatically deleted. Permanent deletion means that the table is removed from the Recycle Bin. After you delete a table by using the DROP TABLE command, the table remains in the Recycle Bin for one day by default before it is permanently deleted.

View group information

For example, run the following command to view group information.

SHOW PROC '/colocation_group';

The command returns the following information.

+-------------+--------------+----------+------------+----------------+----------+----------+
| GroupId     | GroupName    | TableIds | BucketsNum | ReplicationNum | DistCols | IsStable |
+-------------+--------------+----------+------------+----------------+----------+----------+
| 11912.11916 | 11912_group1 | 11914    | 8          | 3              | int(11)  | true     |
+-------------+--------------+----------+------------+----------------+----------+----------+

The following table describes the columns.

You can run the following command to view the data distribution of a specific group.

SHOW PROC '/colocation_group/GroupId';
SHOW PROC '/colocation_group/11912.11916';

The command returns the following information.

+-------------+---------------------+
| BucketIndex | BackendIds          |
+-------------+---------------------+
| 0           | 10002, 10004, 10003 |
| 1           | 10002, 10004, 10003 |
| 2           | 10002, 10004, 10003 |
| 3           | 10002, 10004, 10003 |
| 4           | 10002, 10004, 10003 |
| 5           | 10002, 10004, 10003 |
| 6           | 10002, 10004, 10003 |
| 7           | 10002, 10004, 10003 |
+-------------+---------------------+
8 rows in set (0.00 sec)

Modify group properties

Run the following statement to move a table to a different Colocation Group:

ALTER TABLE tbl SET ("colocate_with" = "group_name");

Example

1. Assign the orders and lineitem tables in the TPC-H dataset to the same Colocation Group.

   use tpc_h_sf100;
   ALTER TABLE orders SET ("colocate_with" = "cg_tpc_orders");
   ALTER TABLE lineitem SET ("colocate_with" = "cg_tpc_orders");

2. Run the following query.

   select count(1) from orders as o join lineitem as l on o.o_orderkey = l.l_orderkey;

3. Check whether the Colocate Join is active on the Query Plan tab for the query in the StarRocks Manager UI.

   When colocate is true, Colocate Join has taken effect. In the execution plan, this appears as colocate: true. The corresponding query plan fragment is:

2:HASH JOIN
|  join op: INNER JOIN (COLOCATE)
|  colocate: true
|  equal join conjunct: 10: l_orderkey = 1: o_orderkey
|
|----1:OlapScanNode
|       TABLE: orders
|       PREAGGREGATION: ON
|       PREDICATES: 1: o_orderkey IS NOT NULL
|       partitions=1/1
|       rollup: orders
|       tabletRatio=96/96
|       tabletList=12403,12405,12407,12409,12411,12413,12415,12417,12419,12421 ...
|       cardinality=135000000
|       avgRowSize=1.0
|       numNodes=0