@Rocky I've refreshed mat view it runs for hours and I see in global_current_queries that there is -168272131520.000 B in memory usage what does that mean?

Seeing a negative memory usage value (like

-168272131520.000 B

, which is approximately -156.7 GB) in

global_current_queries

is a known display or tracking anomaly in StarRocks. What it means This does not mean your query is actually consuming "negative" memory. Instead, it indicates a bug or mismatch in the internal memory tracker (MemTracker). This typically happens for one of two reasons: * Accounting Mismatch: The query execution logic reported more memory being "released" than was originally "allocated" to the tracker. This is common in long-running tasks like Materialized View (MV) refreshes where complex operators might fail to account for an initial allocation but correctly account for the cleanup. * Counter Overflow/Underflow: In some versions, if the memory usage tracking uses a signed integer and hits a specific limit or underflows from zero, it can wrap around to a very large negative number. How to verify actual usage Since the value in

global_current_queries

is unreliable in this state, you should check the backend (BE) nodes directly for accurate information: 1. BE MemTracker: Visit

http://<be_ip>:<be_http_port>/mem_tracker

in your browser. This provides a detailed breakdown of which modules (query_pool, load, etc.) are actually using memory. 2. System Metrics: Run

curl -XGET http://<be_ip>:<be_http_port>/metrics | grep 'mem'

to see the actual physical memory consumption of the BE process. Recommendation for MV Refreshes If your MV refresh is running for hours, it is likely processing a massive amount of data. To prevent it from crashing the system or showing these tracking errors: * Enable Spilling: If the query is memory-intensive, ensure you have configured

spill_local_storage_dir

in your

be.conf

. This allows StarRocks to spill intermediate results to disk instead of failing when it hits memory limits. * Check Version: Tracking bugs like this (e.g., GitHub Issue #40965) are frequently addressed in patch releases. Check if you are on an older version and consider upgrading to a recent stable release (like v3.2.x or v3.3.x). * Partitioned Refresh: If your MV is partitioned, try refreshing specific partitions one by one using

REFRESH MATERIALIZED VIEW <mv_name> PARTITION START (...) END (...)

to reduce the resource load per task. You can monitor the specific progress of the refresh task in the

information_schema.task_runs

table. References • administration/management/monitoring/metrics.md • using_starrocks/caching/block_cache.md • sql-reference/System_variable.md

@Rocky level Label Parent Limit Current Consumption Peak Consumption 1 process 91G 38G 91G 2 jemalloc_metadata process none 1551M 1552M 2 query_pool process 82G -53G 65G 2 load process 5G 64M 3G 2 compaction process 91G 0 3G 2 schema_change process none 0 0 2 page_cache process none 2046M 2047M 2 jit_cache process none 2M 2M 2 clone process none 0 1037K 2 consistency process 10G 0 298M 2 datacache process none 2046M 2047M 2 replication process none 0 0 2 metadata process none 8G 8G 2 update process 55G 2G 7G

The negative memory usage you see in

global_current_queries

and the

mem_tracker

(e.g., -53G or -168GB) is a known reporting bug in StarRocks' internal memory tracking mechanism (MemTracker). What it means This does not mean your query is using "negative" memory. It indicates a mismatch in the internal accounting logic: 1. Accounting Leak: A specific operator in your Materialized View (MV) refresh released memory that it didn't correctly register as "allocated" in that specific tracker. This causes the counter to drop below zero. 2. Known Bug: This is documented in several community issues (e.g., GitHub #40965, #70228). It is typically caused by complex long-running queries or ingestion tasks where the tracker loses sync. How to see the real memory usage Because the

query_pool

tracker is currently unreliable, you should look at the

process

line in your

mem_tracker

output: * Current Consumption (38G): This is the actual physical memory the BE process is holding right now. * Peak Consumption (91G): This shows that at some point, the BE used up its full limit, which might be why the refresh is slow or struggling. Troubleshooting your MV Refresh If the refresh has been running for hours and the memory counters are negative: 1. Check Progress: Do not rely on the memory value. Run

SELECT * FROM information_schema.task_runs WHERE mv_name = 'your_mv_name' ORDER BY create_time DESC;

to see the

status

and

progress

. 2. View the Query Profile: Go to the FE web UI (

http://<fe_ip>:8030/queries

) and check the Profile for the active refresh query. This will show you exactly which operator (e.g., Scan, Aggregate, Join) is currently processing and how many rows it has handled. 3. Spilling to Disk: If your peak consumption hit the limit (91G), the query might be bottlenecked by memory. Ensure intermediate results can spill to disk by checking if

spill_local_storage_dir

is configured in your

be.conf

. 4. Refresh in Batches: If the data volume is too large for a single refresh, try refreshing specific partitions:

sql
    REFRESH MATERIALIZED VIEW your_mv_name PARTITION START ("2023-01-01") END ("2023-01-02");

Recommended Version Tracking bugs like this are frequently fixed in patch releases. If you are on an older version (e.g., < 3.2.x), consider upgrading to the latest stable release (v3.2.11+ or v3.3.x) where memory accounting has been significantly improved. To reset the negative counter, you would need to restart the BE node, but this will kill the current refresh task. If it is still running. References • administration/management/resource_management/Memory_management.md • faq/Sql_faq.md • administration/management/monitoring/metrics.md References • administration/management/resource_management/Memory_management.md • administration/management/monitoring/metrics.md • faq/Sql_faq.md

@Rocky I even cant run kill query on that query it says

unknown query id

The "unknown query id" error occurs because Materialized View (MV) refreshes are managed as background tasks, not standard client queries. While they show up in

global_current_queries

with a QueryID, you cannot always terminate them using the standard

KILL

command. Here is how to properly stop the refresh and resolve the "unknown" ID issue: 1. Use the specific MV Cancel Command Instead of

KILL QUERY

, use the dedicated command for materialized views. This is the most reliable way to stop the task:

sql
-- Try a normal cancel first
CANCEL REFRESH MATERIALIZED VIEW your_mv_name;

-- If it remains running, use the FORCE flag
CANCEL REFRESH MATERIALIZED VIEW your_mv_name FORCE;

2. Disable the MV temporarily If the cancel command doesn't work, you can force all associated tasks to stop by setting the MV to an inactive state:

sql
ALTER MATERIALIZED VIEW your_mv_name INACTIVE;

Note: You will need to set it back to

ACTIVE

once you are ready to try refreshing again. 3. Why

KILL QUERY

failed There are two common reasons for the

unknown query id

error: * Background Task vs. Query: The ID you see in

global_current_queries

for an MV refresh often refers to a background task run. Standard

KILL

commands are designed for active user sessions. * Leader FE vs. Follower FE: If you are in a multi-FE cluster, you must be connected to the Leader FE to kill or cancel a global task. If you are connected to a Follower FE, it may not "own" that query ID and will return "unknown." Check your FE status with

SHOW FRONTENDS

. 4. How to find the correct Task ID If you want to see exactly what is happening with that specific run, query the task metadata:

sql
SELECT * FROM information_schema.task_runs 
WHERE mv_name = 'your_mv_name' 
ORDER BY create_time DESC;

This table will show you the

query_id

and the

state

(e.g., RUNNING). If the state here is

FAILED

or

SUCCESS

but the BE still shows negative memory or high CPU, you have a zombie query stuck on the BE. Summary of Actions 1. Connect to the Leader FE. 2. Run

CANCEL REFRESH MATERIALIZED VIEW <name> FORCE;

. 3. If it persists, run

ALTER MATERIALIZED VIEW <name> INACTIVE;

. 4. If the BE still shows -168GB memory usage after the task is cancelled, the BE's internal counter is "glitched." It will likely require a BE restart to reset that specific memory tracker, though it usually won't impact other new queries. References • sql-reference/sql-statements/materialized_view/CANCEL_REFRESH_MATERIALIZED_VIEW.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md References • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • administration/management/monitor_manage_big_queries.md • using_starrocks/running_queries.md