How can I understand why compaction isn't happenin...
# questions-and-troubleshootingb
Brent Echols
09/26/2025, 10:56 PMHow can I understand why compaction isn't happening better/more often?
I'm seeing fairly bad compaction scores - but no settings I"ve found have effectively improved it. Details in comment
Brent Echols
09/26/2025, 10:56 PM Copy code
StarRocks > SHOW PARTITIONS FROM test.statsig_custom_events_dist;
+-------------+---------------+----------------+----------------+-------------+--------+--------------+------------------------------------------------------------------------------------------------------+--------------------------+---------+----------+-------------+-----------+-----------------+------------+---------+---------+---------+-------------+--------------------+----------------+
| PartitionId | PartitionName | CompactVersion | VisibleVersion | NextVersion | State | PartitionKey | Range | DistributionKey | Buckets | DataSize | StorageSize | RowCount | EnableDataCache | AsyncWrite | AvgCS | P50CS | MaxCS | DataVersion | VersionEpoch | VersionTxnType |
+-------------+---------------+----------------+----------------+-------------+--------+--------------+------------------------------------------------------------------------------------------------------+--------------------------+---------+----------+-------------+-----------+-----------------+------------+---------+---------+---------+-------------+--------------------+----------------+
| 30596561 | p2025092618 | 595 | 2146 | 2147 | NORMAL | ts | [types: [DATETIME]; keys: [2025-09-26 18:00:00]; ..types: [DATETIME]; keys: [2025-09-26 19:00:00]; ) | company_id, custom_event | 36 | 95.3GB | 95.3GB | 117185958 | true | false | 1649.97 | 1724.00 | 1810.00 | 2144 | 379737229066502144 | TXN_NORMAL |
| 30613610 | p2025092619 | 862 | 1296 | 1297 | NORMAL | ts | [types: [DATETIME]; keys: [2025-09-26 19:00:00]; ..types: [DATETIME]; keys: [2025-09-26 20:00:00]; ) | company_id, custom_event | 36 | 68.4GB | 68.4GB | 82440649 | true | false | 318.22 | 272.00 | 619.00 | 1283 | 379744776724938752 | TXN_NORMAL |
| 30629760 | p2025092620 | 468 | 515 | 516 | NORMAL | ts | [types: [DATETIME]; keys: [2025-09-26 20:00:00]; ..types: [DATETIME]; keys: [2025-09-26 21:00:00]; ) | company_id, custom_event | 36 | 34.1GB | 34.1GB | 39245863 | true | false | 47.69 | 42.00 | 74.00 | 494 | 379752423878557696 | TXN_NORMAL |
| 30644965 | p2025092621 | 1446 | 1520 | 1521 | NORMAL | ts | [types: [DATETIME]; keys: [2025-09-26 21:00:00]; ..types: [DATETIME]; keys: [2025-09-26 22:00:00]; ) | company_id, custom_event | 36 | 68.2GB | 68.2GB | 80275643 | true | false | 269.00 | 237.00 | 468.00 | 1503 | 379759984451256320 | TXN_NORMAL |
| 30663161 | p2025092622 | 501 | 780 | 781 | NORMAL | ts | [types: [DATETIME]; keys: [2025-09-26 22:00:00]; ..types: [DATETIME]; keys: [2025-09-26 23:00:00]; ) | company_id, custom_event | 36 | 22.2GB | 22.2GB | 25110867 | true | false | 317.72 | 309.00 | 428.00 | 772 | 379767425305935872 | TXN_NORMAL |
+-------------+---------------+----------------+----------------+-------------+--------+--------------+------------------------------------------------------------------------------------------------------+--------------------------+---------+----------+-------------+-----------+-----------------+------------+---------+---------+---------+-------------+--------------------+----------------+
5 rows in set (0.00 sec)Brent Echols
09/26/2025, 10:56 PMCompaction jobs:
Copy code
StarRocks > SHOW PROC '/compactions';
+------------------------------------------+----------+---------------------+---------------------+---------------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Partition | TxnID | StartTime | CommitTime | FinishTime | Error | Profile |
+------------------------------------------+----------+---------------------+---------------------+---------------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| _statistics_.column_statistics.14252 | 14301519 | 2025-09-26 22:53:39 | 2025-09-26 22:53:40 | 2025-09-26 22:53:41 | | {"sub_task_count":10,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":10,"write_segment_mb":0,"in_queue_sec":1} |
| _statistics_.task_run_history.30408246 | 14301516 | 2025-09-26 22:53:38 | 2025-09-26 22:53:40 | 2025-09-26 22:53:41 | | {"sub_task_count":8,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":28,"write_segment_count":7,"write_segment_mb":0,"in_queue_sec":4} |
| test.statsig_custom_events_dist.30629760 | 14301511 | 2025-09-26 22:53:36 | | | | |
| _statistics_.column_statistics.14252 | 14301458 | 2025-09-26 22:52:14 | 2025-09-26 22:52:35 | 2025-09-26 22:52:36 | | {"sub_task_count":10,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":10,"write_segment_mb":0,"in_queue_sec":41} |
| test.statsig_custom_events_dist.30629760 | 14301441 | 2025-09-26 22:51:57 | | 2025-09-26 22:52:36 | invalid pos | {"sub_task_count":36,"read_local_sec":2,"read_local_mb":3380,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":1717,"write_segment_count":35,"write_segment_mb":3421,"in_queue_sec":245} |
| test.statsig_custom_events_dist.30663161 | 14301410 | 2025-09-26 22:51:13 | | | | |
| _statistics_.loads_history.30409111 | 14301409 | 2025-09-26 22:51:12 | 2025-09-26 22:51:44 | 2025-09-26 22:51:45 | | {"sub_task_count":3,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":29,"write_segment_count":3,"write_segment_mb":0,"in_queue_sec":52} |
| test.statsig_custom_events_dist.30596561 | 14301400 | 2025-09-26 22:51:04 | | 2025-09-26 22:53:10 | invalid pos | {"sub_task_count":36,"read_local_sec":19,"read_local_mb":20843,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":18000,"write_segment_count":37,"write_segment_mb":19916,"in_queue_sec":455} |
| test.statsig_custom_events_dist.30644965 | 14301395 | 2025-09-26 22:51:03 | | 2025-09-26 22:53:45 | invalid pos | {"sub_task_count":36,"read_local_sec":9,"read_local_mb":12287,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":9686,"write_segment_count":38,"write_segment_mb":12657,"in_queue_sec":374} |
| _statistics_.column_statistics.14252 | 14301386 | 2025-09-26 22:50:51 | 2025-09-26 22:50:52 | 2025-09-26 22:50:53 | | {"sub_task_count":10,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":10,"write_segment_mb":0,"in_queue_sec":0} |
| _statistics_.task_run_history.30408246 | 14301373 | 2025-09-26 22:50:37 | 2025-09-26 22:50:38 | 2025-09-26 22:50:40 | | {"sub_task_count":8,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":28,"write_segment_count":7,"write_segment_mb":0,"in_queue_sec":6} |
| test.statsig_custom_events_dist.30613610 | 14301364 | 2025-09-26 22:50:23 | | 2025-09-26 22:53:21 | invalid pos | {"sub_task_count":36,"read_local_sec":13,"read_local_mb":19672,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":11225,"write_segment_count":34,"write_segment_mb":19595,"in_queue_sec":57} |
| test.statsig_custom_events_dist.30629760 | 14301361 | 2025-09-26 22:50:20 | | 2025-09-26 22:50:37 | invalid pos | {"sub_task_count":36,"read_local_sec":2,"read_local_mb":3380,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":1717,"write_segment_count":35,"write_segment_mb":3421,"in_queue_sec":33} |
| _statistics_.column_statistics.14252 | 14301326 | 2025-09-26 22:49:39 | 2025-09-26 22:49:40 | 2025-09-26 22:49:41 | | {"sub_task_count":10,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":10,"write_segment_mb":0,"in_queue_sec":8} |
| test.statsig_custom_events_dist.30629760 | 14301291 | 2025-09-26 22:49:01 | | 2025-09-26 22:49:20 | invalid pos | {"sub_task_count":36,"read_local_sec":2,"read_local_mb":3380,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":1717,"write_segment_count":35,"write_segment_mb":3421,"in_queue_sec":83} |
| test.statsig_custom_events_dist.30596561 | 14301244 | 2025-09-26 22:48:28 | | 2025-09-26 22:50:03 | invalid pos | {"sub_task_count":36,"read_local_sec":18,"read_local_mb":20843,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":18000,"write_segment_count":37,"write_segment_mb":19916,"in_queue_sec":32} |
| _statistics_.column_statistics.14252 | 14301230 | 2025-09-26 22:48:14 | 2025-09-26 22:48:15 | 2025-09-26 22:48:16 | | {"sub_task_count":10,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":10,"write_segment_mb":0,"in_queue_sec":0} |
| _statistics_.task_run_history.30408246 | 14301168 | 2025-09-26 22:47:40 | 2025-09-26 22:47:56 | 2025-09-26 22:47:57 | | {"sub_task_count":8,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":28,"write_segment_count":7,"write_segment_mb":0,"in_queue_sec":15} |
| test.statsig_custom_events_dist.30629760 | 14301151 | 2025-09-26 22:47:33 | | 2025-09-26 22:48:01 | invalid pos | {"sub_task_count":36,"read_local_sec":2,"read_local_mb":3380,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":1717,"write_segment_count":35,"write_segment_mb":3421,"in_queue_sec":131} |
| test.statsig_custom_events_dist.30644965 | 14301133 | 2025-09-26 22:47:21 | | 2025-09-26 22:50:02 | invalid pos | {"sub_task_count":36,"read_local_sec":9,"read_local_mb":12287,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":9686,"write_segment_count":38,"write_segment_mb":12657,"in_queue_sec":60} |
| test.statsig_custom_events_dist.30613610 | 14301016 | 2025-09-26 22:46:23 | | 2025-09-26 22:49:23 | invalid pos | {"sub_task_count":36,"read_local_sec":14,"read_local_mb":19462,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":11225,"write_segment_count":34,"write_segment_mb":19595,"in_queue_sec":196} |
| test.statsig_custom_events_dist.30663161 | 14300914 | 2025-09-26 22:45:30 | | 2025-09-26 22:50:12 | invalid pos | {"sub_task_count":36,"read_local_sec":98,"read_local_mb":27961,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":17470,"write_segment_count":40,"write_segment_mb":21945,"in_queue_sec":7} |
+------------------------------------------+----------+---------------------+---------------------+---------------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec) Copy code
# Increased these to try to improve throughput of compaction
compact_threads = 16
compact_thread_pool_queue_size = 100
# Increased these to make sure they were not conflicting
lake_ingest_slowdown_threshold = 10000
lake_compaction_score_upper_bound = 20000