@Rocky In query profile what does QuerySpillBytes mean?

In a StarRocks query profile,

QuerySpillBytes

represents the total amount of intermediate data that was written (spilled) to disk during the execution of a query. Key Details: * Purpose: StarRocks typically processes data in-memory. If a query requires more memory than is available (for operators like Joins, Aggregates, Sorts, or CTEs), it can "spill" intermediate results to disk to avoid an Out-of-Memory (OOM) error. * Normal Range: The documentation notes that a value of < 1GB is generally considered normal. * Performance Impact: High values indicate that the query is memory-intensive. While spilling prevents the query from failing, it usually results in slower performance because disk I/O is much slower than memory access. When will you see this? This metric will only be greater than zero if: 1. The system variable

enable_spill

is set to

true

. 2. The query's memory consumption for specific operators exceeds the allocated memory limits. If you see a large value for

QuerySpillBytes

, you may want to consider increasing the memory limit for the query, optimizing the query to use less memory, or ensuring your cluster has enough RAM for the workload. References • best_practices/query_tuning/query_profile_operator_metrics.md • data_source/data_cache.md • administration/management/resource_management/spill_to_disk.md • administration/management/BE_configuration.md • sql-reference/System_variable.md

@Rocky root@amplmkt-gcp-prod-usce1-starrocks-cluster00-be00:/opt/starrocks# ./be/bin/meta_tool.sh --root_path=/opt/starrocks/data --operation=get_meta_stats WARNING: Logging before InitGoogleLogging() is written to STDERR I20260622 162733.222124 22960508824960 data_dir.cpp:133] path: /opt/starrocks/data, hash: 952809429870501535 ^[[D Non-update tablets: tablet: 59 253747 rst: 51 9093 Update tablets: tablet: 858 2830482 log: 199727 7076931 delete vector: 421338 69369926698 rowset: 136050 83059182 pending rowset: 0 0 Total: 758083 69463156133 Error: 0

The output you provided from

meta_tool.sh

shows the internal metadata statistics of a StarRocks Backend (BE) node. The most notable observation is that your

delete vector

size under

Update tablets

is extremely large: 69,369,926,698 bytes (~64.6 GB). What these fields mean: 1. Update tablets (Primary Key Model): This section specifically tracks metadata for tables using the Primary Key storage model. 2. delete vector (DelVector): In Primary Key tables, StarRocks uses a Delete+Insert strategy. When you update or delete a row, the system doesn't immediately remove the old data; instead, it records the location of the old row in a "delete vector" and writes the new data (for updates). This allows for very fast queries because it avoids complex merges during read time. 3. tablet / rowset / log: * `tablet`: General metadata for the tablets. * `rowset`: Information about the groups of data files. * `log`: The edit log/WAL used for metadata consistency. Why is your

delete vector

size so high (64.6 GB)? A 64 GB delete vector metadata size is unusually large and typically indicates one of the following: * High Update/Delete Churn: You are performing a massive number of updates or deletes on Primary Key tables. Each operation adds markers to the delete vector. * Compaction Backlog: StarRocks removes old delete markers during the compaction process (merging small data files into larger ones). If compaction is not keeping up with your ingestion rate, the metadata for these markers will continue to grow in the BE's local RocksDB. * Stale Versions: If queries are holding onto very old snapshots or if there are many historical versions of data that haven't been cleaned up, the corresponding metadata must be preserved. Recommendations: 1. Check Compaction Status: Check if there is a compaction backlog by running:

sql
    -- Check for high 'max_compaction_score' (values > 100 indicate a backlog)
    SHOW PROC '/compactions';

2. Optimize DML Operations: * Avoid small, frequent deletes: If you are clearing out old data, try to use

TRUNCATE PARTITION

or

DROP PARTITION

if the data is partitioned by time. These are metadata-only operations and do not bloat the delete vector. * Batch your updates: Instead of many small updates, batch them into larger transactions. 3. Tuning BE Configurations (be.conf): If your hardware allows, you can increase compaction resources for Primary Key tables: * `update_compaction_num_threads_per_disk`: Increase this (default is 1) to allow more concurrent compaction threads for PK tables. * `update_compaction_per_tablet_min_interval_seconds`: Lower this to trigger compaction more frequently on tablets with high churn. 4. Monitor Disk Space: Since this metadata is stored in the

storage_root_path/meta

directory (RocksDB), ensure the disk hosting your metadata has enough headroom. A 64GB metadata file can lead to performance degradation if the underlying RocksDB becomes too large to heavy. If you continue to see growth without it stabilizing after compaction, you may need to review the schema design (e.g., whether the Primary Key is unnecessarily large or if a different table model is more appropriate for your write pattern). References • administration/management/monitoring/metrics.md • administration/management/FE_configuration.md • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md

@Rocky for primary key tables, is it an actual tradeoff of StarRocks the whole writes and compaction limitation?

Yes, it is a deliberate and fundamental architectural trade-off. StarRocks' Primary Key (PK) model is designed with a "Read-Optimized" philosophy. To achieve 3x–10x faster query performance than other models, it moves the "work" from the query phase to the write (loading) and compaction phases. Here is the breakdown of the trade-off: 1. The Strategy: "Delete + Insert" In other models (like Unique Key), StarRocks uses Merge-on-Read. Multiple versions of a row exist, and the system merges them at query time. This is easy on the writer but heavy on the reader. The Primary Key model uses Delete + Insert. When you update a row: 1. It looks up the old row location using a Primary Key Index. 2. It marks the old row as deleted in a Delete Vector (DelVector). 3. It inserts the new row into a new file. The Trade-off: Queries are blazing fast because they only read the "clean" latest data. However, the system pays for this during ingestion with high CPU, memory, and metadata overhead. 2. The Write/Compaction Trade-off Because the system must manage these "Delete Vectors" and "Primary Key Indexes," the write and compaction processes face specific constraints: * Compaction Resources vs. Query Speed: If you have high-frequency writes, you create many small data versions and many delete markers. If you don't have enough compaction threads to merge these quickly, your query performance will eventually degrade, and your metadata (like the 64GB

delete vector

you saw) will bloat. * Ingestion Limits: To prevent the system from being overwhelmed by metadata versions, StarRocks has a

tablet_max_versions

limit (default 1000). If compaction can't keep up, the system will stop or slow down ingestion to protect itself. * Memory vs. Performance: The Primary Key Index needs to be managed. If stored fully in memory, it's fast but consumes RAM. If you enable the Persistent Index, you save RAM but increase Disk I/O during writes. 3. Why your

delete vector

is 64GB The huge size you observed in

meta_tool.sh

is a direct result of this trade-off. * Every update/delete adds a marker. * Those markers stay in the metadata until a Full Compaction or a deep enough Base Compaction merges the underlying data files and permanently removes the rows marked as deleted. * If your update rate is much higher than your compaction rate, the

delete vector

metadata grows significantly, which can eventually slow down the "Apply" phase of your load jobs. Summary of the Trade-off Feature | Primary Key Table | Unique Key Table Query Speed | Fastest (No merging) | Slower (Merge-on-read) Write Overhead | High (Index lookup + DelVector) | Low (Just write data) Compaction | Crucial (To prune DelVectors) | Less critical for speed Memory Cost | High (PK Index needs RAM/SSD) | Low How to manage it: If you see the

delete vector

bloating like in your example, you usually need to increase compaction resources to help the BE "catch up" and clean up that metadata: * Increase

update_compaction_num_threads_per_disk

in

be.conf

. * Reduce

update_compaction_per_tablet_min_interval_seconds

to make it trigger more often. * Ensure your metadata is on fast SSD storage, as the

meta

directory (RocksDB) becomes a bottleneck when it reaches tens of gigabytes in size. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • administration/management/BE_configuration.md • administration/management/resource_management/spill_to_disk.md

@Rocky what is the meta_tool.sh script?

The

meta_tool.sh

script is a low-level diagnostic and maintenance utility located in the

be/bin/

directory of a StarRocks Backend (BE) node. It is designed to interact directly with the BE's local metadata storage (implemented using RocksDB). Because StarRocks stores critical tablet information (versions, rowsets, delete markers, and indexes) in this local metadata store, this tool allows administrators to inspect and occasionally repair that data when standard SQL commands are insufficient. Common Operations The tool uses the

--operation

flag to define what it should do. Some of the most common operations include: *

get_meta_stats

: (The one you used) Provides a high-level summary of all metadata stored on the BE, including the number of tablets and the total size of rowsets and delete vectors. *

get_meta

: Retrieves detailed metadata for a specific tablet (requires

--tablet_id

). This is often used by developers to debug data consistency or versioning issues. *

delete_persistent_index_meta

: Deletes the persistent index metadata for a Primary Key table's tablet. This is a common troubleshooting step if a Primary Key index becomes corrupted or inconsistent. *

load_meta

: Allows you to manually load a JSON-formatted metadata file back into the BE's store. *

lake_datafile_gc

: Used in Shared-data (compute-storage separation) clusters to clean up "garbage" data files from object storage (like S3) that are no longer referenced by any tablet metadata. *

compact_meta

: Triggers a manual compaction of the local RocksDB metadata store to reclaim disk space. Key Considerations and Risks 1. Direct Metadata Access: This tool bypasses the FE and the standard query layer. Incorrect use (like deleting metadata manually) can lead to permanent data loss or "orphan" data files on your disks. 2. File Locks: Since the tool accesses the same RocksDB files as the running BE process, you may see errors like

Resource temporarily unavailable

if you try to run certain write operations while the BE is still running. In some cases, the BE must be stopped to perform metadata repairs. 3. Usage Requirement: Most operations require you to specify the

--root_path

(the data directory of the BE) so the tool knows where the RocksDB metadata files are located. Summary: You generally use this tool only when troubleshooting "under-the-hood" storage issues, such as the delete vector bloat you noticed, or when instructed by StarRocks support to reset a repair a specific tablet. References • knowledge/admin_and_operation/shared_data_garbage_cleanup.md • administration/Meta_recovery.md • administration/data_migration_tool.md • integrations/loading_tools/SMT.md • sql-reference/sql-statements/cbo_stats/SHOW_META.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/monitoring/metrics.md • administration/management/FE_configuration.md • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md • administration/management/BE_configuration.md

sorry mate, wrong thread

No worries @Francisco Silva, was going to ask. All good.