For many, compaction is vital for improving the efficiency and performance of their storage. This includes StarRocks users. There's more to it than just merging smaller data files into larger ones, reclaiming space, and optimizing data layout for better read and write performance, however. A better understanding of compaction, and how it impacts StarRocks, can unlock even greater performance than you could achieve doing the bare minimum.
In this article, we'll review the role compaction plays in shared-data architectures, what this means for optimizing your StarRocks experience, and what best practices you should be following, all without having to go deep into the weeds to figure all of this out yourself.
Understanding the Benefits and Importance of Compaction
To better understand the value of compaction, let's start with an example (and if you're already a compaction expert you can skip to the next section):
In the diagram below, after performing compaction on the data files of version 1 and version 2, the old version data in version 1 (id = 2, value = 11, id = 5, value = 30) is eliminated, resulting in the creation of a new data version file, version 3.
Figure 1: An example of compaction
Compaction is crucial as it provides several benefits:
-
Improved Query Performance: Over time, as data is written to a database or data lake, it can become fragmented across multiple files or segments. Compaction merges these fragments into larger, contiguous blocks, reducing the number of I/O operations required for reading data and thereby improving read performance. Compaction also affects the
order bykeys. Once compaction is finished, all data is merged in order, eliminating the need for the sort-merge stage during data scans. -
Space Reclamation: StarRocks uses log-structured merge (LSM) trees, and frequent writes can lead to a lot of deleted or obsolete data scattered across various segments. Compaction helps in reclaiming this space by removing these unnecessary data points.
-
Preventing File Explosion: the continuous ingestion of data can lead to a proliferation of small files, often referred to as file explosion. Compaction helps in merging these small files into larger ones to reduce metadata burden and save memory.
Compaction is a built-in feature of StarRocks. Under a shared-nothing architecture, it consists of a set of threads on the BE (Back End) that periodically executes according to the compaction policy. In shared-data architectures, StarRocks has made further modifications to allow the FE (Front End) to handle scheduling while the BE executes the tasks. Enabling more flexible decoupling means it’s possible to schedule a group of compute nodes (CNs) to perform compaction independently without interfering with the user’s workload.
StarRocks Compaction Explained
We now understand, at a high level, what compaction is, but what does it look like in StarRocks specifically?
Version Management
The compaction process is similar to an import operation, where each import generates a new version within the FE, marked on the partition. Upon successful commitment of the import transaction, the partition's visible data version number is updated, increasing monotonically.
A partition may contain multiple tablets (refer to data distribution), all sharing the same data version number. Even if an import operation involves only some tablets, once the transaction is committed, the version of all tablets in the partition is updated accordingly.
Figure 2: Version management with compaction
For example, in the diagram above, Partition X contains Tablet 1 to Tablet N, with the current visible version being 12. Once a new import transaction (New Load Txn) is generated and successfully committed, the visible version of Partition X will become 13.
Architecture
Compaction in shared-data mode is composed of two roles: the scheduler (compaction scheduler) and the executor (compaction executor). The scheduler initiates compaction tasks (compaction job) via RPC, while the executor is responsible for executing the compaction job.
The FE acts as the compaction scheduler, and both the BE and CN serve as compaction executors. Each compaction executor contains a thread pool dedicated to executing compaction jobs.
Figure 3: FE and CN components of compaction in StarRocks
Compaction Scheduling
The FE has a periodically running thread called the compaction scheduler, responsible for scheduling all compaction tasks. The FE uses partition as the basic unit for scheduling.
The FE holds information about the compaction score of each partition, which represents the priority of compaction for all tablets within the partition. The higher the compaction score, the more urgent the need for consolidation of the partition.
Each time the compaction scheduler thread runs, it selects the partition with the highest compaction score and constructs compaction tasks for these partitions. Of course, the compaction scheduler also controls the maximum number of compaction tasks initiated each time.
The logic for constructing compaction tasks is relatively straightforward: For each partition, the scheduler obtains all its tablets and then constructs a compaction task for each CN. The task contains the list of tablets that need to execute the compaction task on that CN and is then sent to the CN node.
The entire process is illustrated in the following diagram:
Figure 4: The compaction scheduling process
In the above diagram, two Partitions on the FE need to execute compaction, namely Partition X and Partition Y. Partition X contains 4 tablets (1 to 4), while Partition Y contains 3 tablets (5 to 7).
The scheduler determines the following:
-
For Partition X, Tablet-2 and Tablet-4 are located in the same CN-1, while Tablet-1 and Tablet-3 are located in the same CN-2. Therefore, two compaction tasks (Task-1 and Task-2) are constructed for Partition X. Task-1 contains Tablet-2 and Tablet-4, while Task-2 contains Tablet-1 and Tablet-3.
-
For Partition Y, Tablet-5 and Tablet-7 are located in the same CN-1, while Tablet-6 is located in another CN-2. Therefore, two compaction tasks (Task-3 and Task-4) are also constructed for Partition Y. Task-3 contains Tablet-5 and Tablet-7, while Task-4 contains Tablet-6.
Finally, each task is sent to its respective CN.
MVCC and Data Recycling
Currently, StarRocks' storage and computing separation tables utilize the MVCC model, with the overall storage structure illustrated in the following diagram:
Figure 5: Storage structure example
Three data import transactions have been generated:
-
Load Txn 1: Creates files 1 and 2, resulting in Tablet Meta V1, which lists {file-1, file-2}.
-
Load Txn 2: Creates files 3 and 4, resulting in Tablet Meta V2, which lists {file-1, file-2, file-3, file-4}.
-
Load Txn 3: Creates file 5, resulting in Tablet Meta V3, which lists {file-1, file-2, file-3, file-4, file-5}.
System background compaction tasks also generate new data versions by merging small files into larger ones, reducing random IO operations, and eliminating duplicate records. Each compaction creates a new version. For example, if Txn 4 is a compaction that merges files 1 to 4 into file 6, Tablet Meta V4 will list {file-5, file-6}.
Without Compaction, data files can't be deleted. For instance, files 1 to 4 remain referenced by Tablet Meta V3. However, after compaction, files 1 to 4 can be deleted once versions V1, V2, and V3 are no longer accessed, as their content exists in file 6. The current data version is shown below:
Figure 6: Example tablet references
From the above discussion, it is evident that only after compaction is completed can the original data files be deleted (although the cleaning of tablet meta files depends on other rules). Therefore, the most straightforward rule for determining whether a data file can be safely deleted is that the data file is no longer referenced by any tablet meta.
Compaction Tuning In StarRocks: A Guide
Now that we better understand how compaction works with StarRocks, let's optimize it.
Checking Your Compaction Score
Note: The following commands need to be executed on the Leader FE node.
StarRocks internally maintains a compaction score for each partition, which reflects the current data file merging status of the partition. The higher the score, the lower the degree of data file merging.
StarRocks provides commands to view the current compaction score of a partition. The FE uses this as a reference to initiate compaction tasks, and users can use this to determine whether the current partition has too many versions:
Method 1:
MySQL [(none)]> show proc '/DBS/load_benchmark/store_sales/partitions';
+-------------+---------------+----------------+----------------+-------------+--------+--------------+-------+------------------------------+---------+----------+-----------+----------+------------+-------+-------+-------+
| PartitionId | PartitionName | CompactVersion | VisibleVersion | NextVersion | State | PartitionKey | Range | DistributionKey | Buckets | DataSize | RowCount | CacheTTL | AsyncWrite | AvgCS | P50CS | MaxCS |
+-------------+---------------+----------------+----------------+-------------+--------+--------------+-------+------------------------------+---------+----------+-----------+----------+------------+-------+-------+-------+
| 38028 | store_sales | 913 | 921 | 923 | NORMAL | | | ss_item_sk, ss_ticket_number | 64 | 15.6GB | 273857126 | 2592000 | false | 10.00 | 10.00 | 10.00 |
+-------------+---------------+----------------+----------------+-------------+--------+--------------+-------+------------------------------+---------+----------+-----------+----------+------------+-------+-------+-------+
1 row in set (0.20 sec)
Method 2:
Since versions 3.1.9 & 3.2.4, we have added the partitions_meta table to the system tables, making it convenient for users to view all partition information through various complex SQL queries:
mysql> select * from information_schema.partitions_meta order by Max_CS;
+--------------+----------------------------+----------------------------+--------------+-----------------+-----------------+----------------------+--------------+---------------+-----------------+-----------------------------------------+---------+-----------------+----------------+---------------------+-----------------------------+--------------+---------+-----------+------------+------------------+----------+--------+--------+--------------------------------------------------------------------------------------------------------+
| DB_NAME | TABLE_NAME | PARTITION_NAME | PARTITION_ID | COMPACT_VERSION | VISIBLE_VERSION | VISIBLE_VERSION_TIME | NEXT_VERSION | PARTITION_KEY | PARTITION_VALUE | DISTRIBUTION_KEY | BUCKETS | REPLICATION_NUM | STORAGE_MEDIUM | COOLDOWN_TIME | LAST_CONSISTENCY_CHECK_TIME | IS_IN_MEMORY | IS_TEMP | DATA_SIZE | ROW_COUNT | ENABLE_DATACACHE | AVG_CS | P50_CS | MAX_CS | STORAGE_PATH |
+--------------+----------------------------+----------------------------+--------------+-----------------+-----------------+----------------------+--------------+---------------+-----------------+-----------------------------------------+---------+-----------------+----------------+---------------------+-----------------------------+--------------+---------+-----------+------------+------------------+----------+--------+--------+--------------------------------------------------------------------------------------------------------+
| tpcds_1t | call_center | call_center | 11905 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | cc_call_center_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 12.3KB | 42 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11906/11905 |
| tpcds_1t | web_returns | web_returns | 12030 | 3 | 3 | 2024-03-17 08:40:48 | 4 | | | wr_item_sk, wr_order_number | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 3.5GB | 71997522 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/12031/12030 |
| tpcds_1t | warehouse | warehouse | 11847 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | w_warehouse_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 4.2KB | 20 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11848/11847 |
| tpcds_1t | ship_mode | ship_mode | 11851 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | sm_ship_mode_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 1.7KB | 20 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11852/11851 |
| tpcds_1t | customer_address | customer_address | 11790 | 0 | 2 | 2024-03-17 08:32:19 | 3 | | | ca_address_sk | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 120.9MB | 6000000 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11791/11790 |
| tpcds_1t | time_dim | time_dim | 11855 | 0 | 2 | 2024-03-17 08:30:48 | 3 | | | t_time_sk | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 864.7KB | 86400 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11856/11855 |
| tpcds_1t | web_sales | web_sales | 12049 | 3 | 3 | 2024-03-17 10:14:20 | 4 | | | ws_item_sk, ws_order_number | 128 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 47.7GB | 720000376 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/12050/12049 |
| tpcds_1t | store | store | 11901 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | s_store_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 95.6KB | 1002 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11902/11901 |
| tpcds_1t | web_site | web_site | 11928 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | web_site_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 13.4KB | 54 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11929/11928 |
| tpcds_1t | household_demographics | household_demographics | 11932 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | hd_demo_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 2.1KB | 7200 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11933/11932 |
| tpcds_1t | web_page | web_page | 11936 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | wp_web_page_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 43.5KB | 3000 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11937/11936 |
| tpcds_1t | customer_demographics | customer_demographics | 11809 | 0 | 2 | 2024-03-17 08:30:49 | 3 | | | cd_demo_sk | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 2.7MB | 1920800 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11810/11809 |
| tpcds_1t | reason | reason | 11874 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | r_reason_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 1.9KB | 65 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11875/11874 |
| tpcds_1t | promotion | promotion | 11940 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | p_promo_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 69.6KB | 1500 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11941/11940 |
| tpcds_1t | income_band | income_band | 11878 | 0 | 2 | 2024-03-17 08:30:48 | 3 | | | ib_income_band_sk | 1 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 727B | 20 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11879/11878 |
| tpcds_1t | catalog_page | catalog_page | 11944 | 0 | 2 | 2024-03-17 08:30:52 | 3 | | | cp_catalog_page_sk | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 1.8MB | 30000 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11945/11944 |
| tpcds_1t | item | item | 11882 | 0 | 2 | 2024-03-17 08:30:51 | 3 | | | i_item_sk | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 37.1MB | 300000 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11883/11882 |
| tpcds_1t | store_returns | store_returns | 11755 | 3 | 3 | 2024-03-17 09:02:48 | 4 | | | sr_item_sk, sr_ticket_number | 32 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 11.3GB | 287999764 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11756/11755 |
| tpcds_1t | date_dim | date_dim | 11828 | 0 | 2 | 2024-03-17 08:30:47 | 3 | | | d_date_sk | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 1.5MB | 73049 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11829/11828 |
| tpcds_1t | catalog_sales | catalog_sales | 12215 | 3 | 3 | 2024-03-17 11:44:37 | 4 | | | cs_item_sk, cs_order_number | 256 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 94.7GB | 1439982416 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/12216/12215 |
| tpcds_1t | store_sales | store_sales | 12474 | 3 | 3 | 2024-03-17 13:33:04 | 4 | | | ss_item_sk, ss_ticket_number | 256 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 133.4GB | 2879987999 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/12475/12474 |
| _statistics_ | histogram_statistics | histogram_statistics | 11729 | 0 | 1 | 2024-03-17 08:29:45 | 2 | | | table_id, column_name | 10 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 0B | 0 | 1 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10004/11730/11729 |
| _statistics_ | external_column_statistics | external_column_statistics | 11742 | 0 | 1 | 2024-03-17 08:29:45 | 2 | | | table_uuid, partition_name, column_name | 10 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 0B | 0 | 1 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10004/11743/11742 |
| tpcds_1t | catalog_returns | catalog_returns | 12180 | 3 | 3 | 2024-03-17 08:51:32 | 4 | | | cr_item_sk, cr_order_number | 32 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 7.4GB | 143996756 | 0 | 0 | 0 | 0 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/12181/12180 |
| _statistics_ | table_statistic_v1 | table_statistic_v1 | 11703 | 0 | 4 | 2024-03-17 10:24:32 | 5 | | | table_id, column_name, db_id | 10 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 55KB | 77 | 1 | 2.8 | 3 | 3 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10004/11704/11703 |
| tpcds_1t | inventory | inventory | 11963 | 3 | 3 | 2024-03-17 08:52:40 | 4 | | | inv_item_sk | 64 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 1.4GB | 783000000 | 0 | 1.046875 | 0 | 4 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11964/11963 |
| tpcds_1t | customer | customer | 11909 | 0 | 2 | 2024-03-17 08:33:36 | 3 | | | c_customer_sk | 16 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 640.6MB | 12000000 | 0 | 3.0625 | 3 | 5 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10328/11910/11909 |
| _statistics_ | column_statistics | column_statistics | 11716 | 14 | 23 | 2024-03-17 10:19:27 | 24 | | | table_id, partition_id, column_name | 10 | 1 | HDD | 9999-12-31 23:59:59 | NULL | 0 | 0 | 1.4MB | 348 | 1 | 7.7 | 8 | 9 | s3://starrocks-cloud-data/dingkai/536a3c77-52c3-485a-8217-781734a970b1/db10004/11717/11716 |
+--------------+----------------------------+----------------------------+--------------+-----------------+-----------------+----------------------+--------------+---------------+-----------------+-----------------------------------------+---------+-----------------+----------------+---------------------+-----------------------------+--------------+---------+-----------+------------+------------------+----------+--------+--------+--------------------------------------------------------------------------------------------------------+
28 rows in set (0.04 sec)
Focus on the following parameters:
-
AvgCS: The average compaction score of all tablets in the current partition
-
MaxCS: The maximum compaction score of all tablets in the current partition
Viewing Compaction Tasks
As import tasks are executed, the system continuously schedules and executes compaction tasks. These tasks are sent to the computation node for execution. The system also provides a series of commands to view the current status of compaction tasks.
Compaction Task Commands
First, users can use the following command to observe the overall status of all compaction tasks in the system:
MySQL [(none)]> show proc '/compactions';
+----------------------------------------------------+--------+---------------------+------------+---------------------+---------------------------------------------------------------------------------+
| Partition | TxnID | StartTime | CommitTime | FinishTime | Error |
+----------------------------------------------------+--------+---------------------+------------+---------------------+---------------------------------------------------------------------------------+
| load_benchmark.store_sales.store_sales | 197562 | 2023-05-24 15:50:33 | 2023-05-24 15:51:00 | 2023-05-24 15:51:02 | NULL |
+----------------------------------------------------+--------+---------------------+------------+---------------------+---------------------------------------------------------------------------------+
13 rows in set (0.21 sec)
The above shows that there is currently one compaction task in progress, with the following fields:
-
Partition: The Partition targeted by the current compaction task
-
TxnID: The transaction ID assigned to the current compaction task by FE
-
StartTime: The start time of the compaction task
-
CommitTime: The commit time of the compaction task
-
FinishTime: The finish time of the compaction task
-
Error: Error information for the compaction task, if any; otherwise, it will be NULL
The above command shows the overall status of Compaction tasks for each Partition. Each Compaction task is divided into multiple subtasks at the Tablet level. The system also provides the following command to view the detailed progress of each compaction subtask:
MySQL [(none)]> select * from information_schema.be_cloud_native_compactions where TXN_ID = 197562;
+-------+--------+-----------+---------+---------+------+---------------------+---------------------+----------+--------+
| BE_ID | TXN_ID | TABLET_ID | VERSION | SKIPPED | RUNS | START_TIME | FINISH_TIME | PROGRESS | STATUS |
+-------+--------+-----------+---------+---------+------+---------------------+---------------------+----------+--------+
| 36027 | 197562 | 38033 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 36027 | 197562 | 38038 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 36027 | 197562 | 38039 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:39 | 100 | OK |
| 36027 | 197562 | 38040 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:39 | 100 | OK |
| 36027 | 197562 | 38044 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:42 | 100 | OK |
| 36027 | 197562 | 38047 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:43 | 100 | OK |
| 36027 | 197562 | 38055 | 365 | 0 | 1 | 2023-05-24 15:50:39 | 2023-05-24 15:50:43 | 100 | OK |
| 36027 | 197562 | 38056 | 365 | 0 | 1 | 2023-05-24 15:50:39 | 2023-05-24 15:50:43 | 100 | OK |
| 36027 | 197562 | 38058 | 365 | 0 | 1 | 2023-05-24 15:50:42 | 2023-05-24 15:50:47 | 100 | OK |
| 36027 | 197562 | 38060 | 365 | 0 | 1 | 2023-05-24 15:50:43 | 2023-05-24 15:50:47 | 100 | OK |
| 36027 | 197562 | 38063 | 365 | 0 | 1 | 2023-05-24 15:50:43 | 2023-05-24 15:50:48 | 100 | OK |
| 36027 | 197562 | 38066 | 365 | 0 | 1 | 2023-05-24 15:50:43 | 2023-05-24 15:50:48 | 100 | OK |
| 36027 | 197562 | 38070 | 365 | 0 | 1 | 2023-05-24 15:50:47 | NULL | 85 | OK |
| 36027 | 197562 | 38071 | 365 | 0 | 1 | 2023-05-24 15:50:47 | NULL | 61 | OK |
| 36027 | 197562 | 38080 | 365 | 0 | 1 | 2023-05-24 15:50:48 | NULL | 33 | OK |
| 36027 | 197562 | 38083 | 365 | 0 | 1 | 2023-05-24 15:50:48 | NULL | 29 | OK |
| 36027 | 197562 | 38085 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36027 | 197562 | 38086 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36027 | 197562 | 38090 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36027 | 197562 | 38091 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36027 | 197562 | 38094 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36026 | 197562 | 38031 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:39 | 100 | OK |
| 36026 | 197562 | 38037 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 36026 | 197562 | 38042 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 36026 | 197562 | 38043 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 36026 | 197562 | 38045 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:42 | 100 | OK |
| 36026 | 197562 | 38048 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:43 | 100 | OK |
| 36026 | 197562 | 38049 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:43 | 100 | OK |
| 36026 | 197562 | 38051 | 365 | 0 | 1 | 2023-05-24 15:50:39 | 2023-05-24 15:50:43 | 100 | OK |
| 36026 | 197562 | 38054 | 365 | 0 | 1 | 2023-05-24 15:50:42 | 2023-05-24 15:50:46 | 100 | OK |
| 36026 | 197562 | 38057 | 365 | 0 | 1 | 2023-05-24 15:50:43 | 2023-05-24 15:50:47 | 100 | OK |
| 36026 | 197562 | 38062 | 365 | 0 | 1 | 2023-05-24 15:50:43 | 2023-05-24 15:50:47 | 100 | OK |
| 36026 | 197562 | 38069 | 365 | 0 | 1 | 2023-05-24 15:50:43 | 2023-05-24 15:50:48 | 100 | OK |
| 36026 | 197562 | 38073 | 365 | 0 | 1 | 2023-05-24 15:50:46 | NULL | 98 | OK |
| 36026 | 197562 | 38074 | 365 | 0 | 1 | 2023-05-24 15:50:47 | NULL | 77 | OK |
| 36026 | 197562 | 38075 | 365 | 0 | 1 | 2023-05-24 15:50:47 | NULL | 51 | OK |
| 36026 | 197562 | 38077 | 365 | 0 | 1 | 2023-05-24 15:50:48 | NULL | 33 | OK |
| 36026 | 197562 | 38078 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36026 | 197562 | 38079 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36026 | 197562 | 38081 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36026 | 197562 | 38082 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36026 | 197562 | 38084 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 36026 | 197562 | 38089 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 10004 | 197562 | 38032 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 10004 | 197562 | 38034 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 10004 | 197562 | 38035 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 10004 | 197562 | 38036 | 365 | 0 | 1 | 2023-05-24 15:50:34 | 2023-05-24 15:50:38 | 100 | OK |
| 10004 | 197562 | 38041 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:42 | 100 | OK |
| 10004 | 197562 | 38046 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:42 | 100 | OK |
| 10004 | 197562 | 38050 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:42 | 100 | OK |
| 10004 | 197562 | 38052 | 365 | 0 | 1 | 2023-05-24 15:50:38 | 2023-05-24 15:50:42 | 100 | OK |
| 10004 | 197562 | 38053 | 365 | 0 | 1 | 2023-05-24 15:50:42 | 2023-05-24 15:50:46 | 100 | OK |
| 10004 | 197562 | 38059 | 365 | 0 | 1 | 2023-05-24 15:50:42 | 2023-05-24 15:50:47 | 100 | OK |
| 10004 | 197562 | 38061 | 365 | 0 | 1 | 2023-05-24 15:50:42 | 2023-05-24 15:50:46 | 100 | OK |
| 10004 | 197562 | 38064 | 365 | 0 | 1 | 2023-05-24 15:50:42 | 2023-05-24 15:50:47 | 100 | OK |
| 10004 | 197562 | 38065 | 365 | 0 | 1 | 2023-05-24 15:50:46 | NULL | 99 | OK |
| 10004 | 197562 | 38067 | 365 | 0 | 1 | 2023-05-24 15:50:46 | NULL | 92 | OK |
| 10004 | 197562 | 38068 | 365 | 0 | 1 | 2023-05-24 15:50:47 | NULL | 87 | OK |
| 10004 | 197562 | 38072 | 365 | 0 | 1 | 2023-05-24 15:50:47 | NULL | 89 | OK |
| 10004 | 197562 | 38076 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 10004 | 197562 | 38087 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 10004 | 197562 | 38088 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 10004 | 197562 | 38092 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
| 10004 | 197562 | 38093 | 365 | 0 | 0 | NULL | NULL | 0 | OK |
+-------+--------+-----------+---------+---------+------+---------------------+---------------------+----------+--------+
64 rows in set (0.22 sec)
Focus on two fields:
-
PROGRESS: Represents the current progress of the tablet compaction as a percentage
-
STATUS: Represents the task status; if there is an error, it will show detailed error information
Canceling Compaction Tasks
Users can also cancel specific compaction tasks with the following command. Note that this command needs to be executed on the Leader FE node:
CANCEL COMPACTION WHERE TXN_ID = 123;
Parameter Tuning
Lastly, StarRocks has the following parameters to control the compaction behavior under shared-data architectures.
FE Parameters
# The minimum Compaction score; partitions below this value will not initiate Compaction tasks
lake_compaction_score_selector_min_score = 10.0;
# The number of Compaction tasks that can be initiated simultaneously on FE
# The default value is -1, meaning FE will automatically calculate based on the number of BEs in the system
# If set to 0, FE will not initiate any Compaction tasks
lake_compaction_max_tasks = -1;
# Control the number of results displayed by show proc '/compactions', default is 12
lake_compaction_history_size = 12;
lake_compaction_fail_history_size = 12;
All the above compaction-related parameters in the FE can be dynamically modified through SQL commands, for example:
admin set frontend config ("lake_compaction_max_tasks" = "0");
BE / CN Parameters
# Control the number of threads executing Compaction tasks on BE/CN, default is 4
# This means BE can simultaneously perform Compaction on multiple Tablets
compact_threads = 4
# The size of the Compaction task queue on BE, controlling the maximum number of Compaction tasks received from FE
# Default value is 100
compact_thread_pool_queue_size = 100
# The maximum number of data files merged in a single Compaction task, default is 1000
# In practice, we recommend adjusting this value to 100 so that each Compaction Task can finish faster and consume fewer resources
max_cumulative_compaction_num_singleton_deltas=100
All the above Compaction-related parameters in the BE can now be dynamically modified in the latest version. You can modify them as follows:
mysql> update information_schema.be_configs set value = 8 where name = "compact_threads";
Query OK, 0 rows affected (0.01 sec)
Additional Best Practices
Since Compaction significantly impacts query performance, we recommend users constantly monitor the background data merging status of tables and partitions in the system. Here are some best practices to keep performance optimal:
-
Monitor Your Compaction Score: Set up alerts based on this metric to track the urgency of compaction tasks. StarRocks' Grafana monitoring template includes this metric.
-
Monitor Resource Consumption: Pay special attention to memory usage during compaction. This metric is also included in StarRocks' Grafana monitoring template.
-
Optimize Parallel Work: Increase the number of parallel compaction threads on computation nodes during idle times to accelerate compaction tasks.
Improve Your Approach to Compaction Today
Even if you love your performance with StarRocks, your approach to compaction may be holding you back. Hopefully, with this advice, you'll be able to further accelerate your analytics. If you have questions about compaction or are just curious about how others in the community are tackling it, join us on the StarRocks Slack right now.
