http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Where are the audit logs available ...
# questions-and-troubleshooting
r
@Rocky Where are the audit logs available in celerdata/starrocks?
r
In StarRocks (and the CelerData core), audit logs are primarily available in two forms: as local log files and, if configured, as internal database tables via a plugin. 1. Local Log Files By default, the Frontend (FE) node records all query and connection activities in a specific audit log file. * File Name: 
fe.audit.log
* Default Location: 
${STARROCKS_HOME}/log/fe.audit.log
* Configuration: The location and retention behavior are controlled in 
fe.conf
using the following parameters: * `audit_log_dir`: The directory where audit logs are stored. * `audit_log_modules`: Specifies which modules to log (e.g., 
slow_query
, 
query
, 
connection
). * `audit_log_roll_num`: Number of log files to keep (default is 90). * `audit_log_delete_age`: Retention period for logs (e.g., 
7d
or 
30d
). * `audit_log_json_format`: Can be set to 
true
(available from v3.2.7+) to output logs in JSON for easier parsing by tools like ELK or SIEMs. 2. Internal Database Tables (AuditLoader) You can view audit logs directly using SQL by installing the AuditLoader plugin. This plugin automatically captures log entries and loads them into a StarRocks table. * Setup: You must download the 
auditloader.zip
, configure the target database/table in 
plugin.conf
, and install it via the 
INSTALL PLUGIN
command. * Usage: Once installed, you can query the audit table (e.g., 
SELECT * FROM starrocks_monitor.cluster_audit_log
) to analyze query performance, user behavior, and access patterns without leaving the SQL interface. 3. CelerData Cloud (Managed Service) If you are using CelerData Cloud, audit information is also available through the management console: * Query History: Provides a searchable UI to filter queries by user, duration, status, and time range. * Admin/Audit View: CelerData Cloud clusters often include an administrative view that extends standard history into a full audit trail for compliance and security monitoring. For more details on configuring these logs, you can refer to the StarRocks FE Configuration or the AuditLoader documentation. References • administration/management/FE_configuration.md • administration/management/logs.md • administration/management/audit_loader.md
r
@Rocky Explain these resource group parameters to me 
spill_mem_limit_threshold
parameter for the rg and setup 
query_mem_limit
and 
enable_spill
r
These three parameters work together to manage how StarRocks handles memory-intensive queries by offloading intermediate data to disk (spilling) instead of failing with a "Memory Exceed Limit" error. 1. 
enable_spill
(Session Variable) This is the "master switch" for the spilling feature. * Purpose: Determines whether the execution engine is allowed to spill intermediate results (from Sort, Aggregate, or Join operators) to disk. * Default: 
false
. * Usage: You must set this to 
true
to enable any spilling behavior.
Copy code
sql
    SET enable_spill = true;
* Note: You also need to configure 
spill_local_storage_dir
in your 
be.conf
to provide a disk path for the spilled data. 2. 
query_mem_limit
(Session Variable) This defines the maximum memory a single query can consume on each Backend (BE) node. * Without Spilling: If a query's memory usage hits this limit, the query is killed immediately. * With Spilling: When 
enable_spill
is 
true
, this variable acts as a trigger point. By default, StarRocks will start spilling data to disk when a query reaches 80% of its 
query_mem_limit
. * Usage:
Copy code
sql
    -- Set limit to 8GB
    SET query_mem_limit = 8589934592;
3. 
spill_mem_limit_threshold
(Resource Group Parameter) This is a more granular control used specifically within Resource Groups to trigger spilling based on the group's collective memory usage. * Purpose: It defines a threshold percentage (0.0 to 1.0) of the resource group's allocated memory. * Trigger Logic: If Resource Groups are enabled, spilling is triggered for a query if either of these conditions is met: 1. Individual Query: The query usage > 80% of its 
query_mem_limit
. 2. Resource Group Total: The total memory used by all queries in the group > 
(BE Total Mem) _ (RG mem_limit) _ (spill_mem_limit_threshold)
. * Default: 
1.0
(meaning spilling starts when the resource group's memory limit is fully reached). * Example Setup:
Copy code
sql
    CREATE RESOURCE GROUP big_query_group
    WITH (
        "mem_limit" = "0.5",               -- Group can use 50% of BE memory
        "spill_mem_limit_threshold" = "0.8" -- Start spilling when group hits 80% of its 50% share
    );
Summary Table Parameter | Level | Description 
enable_spill
| Session/Global | Enables the ability to offload RAM to disk. 
query_mem_limit
| Session/Global | The hard cap for one query; triggers spill at 80% of this value. 
spill_mem_limit_threshold
| Resource Group | Multiplier that triggers spilling based on the entire group's memory usage. Pro Tip: If you are running large ETL jobs or complex joins that frequently fail with memory errors, enable 
SET enable_spill = true
and ensure your 
query_mem_limit
is high enough to allow the query to start, but low enough that spilling kicks in before the BE node runs out of physical RAM. References • sql-reference/System_variable.md • administration/management/resource_management/Query_management.md • administration/management/resource_management/Memory_management.md
r
@Rocky Will queries be dropped if they cross threshold, if 
spill_mem_limit_threshold
and 
enable_spill
is set (but not query_mem_limit set? In what scenarios will queries be killed?
r
If you have 
enable_spill = true
and have configured a 
spill_mem_limit_threshold
for a Resource Group (RG), queries will not be dropped simply because they cross that threshold. Instead, crossing the threshold triggers spilling (writing intermediate data to disk) to keep memory usage under control. 1. Will queries be dropped if they cross the threshold? No. The 
spill_mem_limit_threshold
is a "trigger" for spilling, not a "kill" signal. * Behavior: When the resource group's collective memory usage hits 
(BE Memory) _ (RG mem_limit) _ (spill_mem_limit_threshold)
, StarRocks will begin moving revocable memory (from sort, join, or aggregate operators) to the 
spill_local_storage_dir
. * If 
query_mem_limit
is not set: StarRocks defaults 
query_mem_limit
to 
0
(which essentially means "unlimited" per query, though it remains capped by the overall BE memory limit or the Resource Group's 
mem_limit
). The query will continue to run as long as it can successfully spill enough data to stay within the physical memory limits of the node. ────────── 2. In what scenarios will queries be killed? Even with spilling enabled, a query can still be killed (terminated) in the following scenarios: A. Out of Memory (OOM) for "Unspillable" Data Spilling only works for specific operators (Sort, Aggregate, Hash Join, and CTE). If a query uses excessive memory for operations that cannot spill, it will be killed once it hits the memory limit. This includes: * Memory used for expression evaluation. * Metadata and plan fragment structures. * Operators that don't support spilling yet (depending on your StarRocks version). B. Reaching the BE Process Hard Limit If the entire BE node hits its process-wide memory limit (configured via 
mem_limit
in 
be.conf
, default 90%), the system's OOM killer or StarRocks' internal GC will kill the most memory-intensive queries to prevent the BE process from crashing. C. Spill Disk is Full If the query needs to spill more data but the designated 
spill_local_storage_dir
is full (and you haven't enabled 
enable_spill_to_remote_storage
), the query will fail with an I/O error or be terminated because it cannot proceed without more memory or disk space. D. Query Timeout Spilling to disk is significantly slower than in-memory processing (often 10x or more). A query that might have finished in 30 seconds in-memory might take 10 minutes when spilling. If it exceeds the 
query_timeout
(default is usually 300s), it will be killed. _ _Tip: Always increase 
query_timeout
if you expect heavy spilling.* E. Hard Resource Group Limits If you have configured a Resource Group and the total memory usage exceeds the group's 
mem_limit
and the query can no longer reduce its footprint via spilling (e.g., all spillable data is already on disk), the query will be killed to protect the memory allocated to other resource groups. Summary Event | Action Hits 
spill_mem_limit_threshold
| Spills to disk (Query continues) Hits 80% of 
query_mem_limit
| Spills to disk (Query continues) Memory usage > Hard Limit (after spilling) | Query Killed (OOM) Execution time > 
query_timeout
| Query Killed (Timeout) Spill Disk Space exhausted | Query Killed (I/O Error) References • administration/management/resource_management/spill_to_disk.md • administration/management/resource_management/resource_group.md • sql-reference/System_variable.md • administration/management/resource_management/Memory_management.md References
r
@Rocky I am trying to read the audit log in mysql and get this error, help ELECT * FROM starrocks_monitor.cluster_audit_log; ERROR 5501 (3F000): Getting analyzing error. Detail message: Unknown database 'starrocks_monitor'.
r
The error 
Unknown database 'starrocks_monitor'
occurs because StarRocks does not create the audit log database or table automatically. By default, audit logs are only stored in the local file 
fe.audit.log
. To query audit logs via SQL, you must manually set up the AuditLoader plugin. Follow these steps to resolve the error: 1. Create the Database and Table You must first create the database and table where the logs will be stored. You can name them whatever you like (the plugin will point to these names), but common practice is to use 
starrocks_monitor
or 
starrocks_audit_db__
.
Copy code
sql
CREATE DATABASE starrocks_monitor;

CREATE TABLE starrocks_monitor.cluster_audit_log (
  `queryId` VARCHAR(64) COMMENT "Unique ID of the query",
  `timestamp` DATETIME NOT NULL COMMENT "Query start time",
  `queryType` VARCHAR(12) COMMENT "Query type (query, edit, load, etc.)",
  `clientIp` VARCHAR(32) COMMENT "Client IP",
  `user` VARCHAR(64) COMMENT "User name",
  `authorizedUser` VARCHAR(64) COMMENT "Authorized user name",
  `resourceGroup` VARCHAR(64) COMMENT "Resource group name",
  `catalog` VARCHAR(64) COMMENT "Catalog name",
  `db` VARCHAR(64) COMMENT "Database name",
  `state` VARCHAR(8) COMMENT "Query state (EOF, ERR, OK)",
  `errorCode` VARCHAR(512) COMMENT "Error code",
  `queryTime` BIGINT COMMENT "Query latency (ms)",
  `scanBytes` BIGINT COMMENT "Total bytes scanned",
  `scanRows` BIGINT COMMENT "Total rows scanned",
  `returnRows` BIGINT COMMENT "Total rows returned",
  `cpuCostNs` BIGINT COMMENT "CPU cost (ns)",
  `memCostBytes` BIGINT COMMENT "Memory cost (bytes)",
  `stmtId` INT COMMENT "Statement ID",
  `isQuery` TINYINT COMMENT "Is it a query (1) or not (0)",
  `feIp` VARCHAR(32) COMMENT "FE IP",
  `stmt` STRING COMMENT "SQL statement",
  `digest` VARCHAR(32) COMMENT "SQL digest",
  `planCpuCosts` DOUBLE COMMENT "Plan CPU costs",
  `planMemCosts` DOUBLE COMMENT "Plan memory costs"
) ENGINE=OLAP
DUPLICATE KEY(`queryId`, `timestamp`, `queryType`)
COMMENT "Audit log table"
DISTRIBUTED BY HASH(`queryId`) BUCKETS 3
PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-30",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "3"
);
2. Install/Configure the AuditLoader Plugin If you haven't installed the plugin yet: 1. Download the 
auditloader.zip
from the official StarRocks resources. 2. Edit 
plugin.conf
: Inside the zip, update the 
database
and 
table
fields to match what you created above (
starrocks_monitor
and 
cluster_audit_log
), and provide your cluster 
user
and 
password
. 3. Install the plugin:
Copy code
sql
    INSTALL PLUGIN FROM "/absolute/path/to/auditloader.zip";
3. Verify Check if the plugin is running:
Copy code
sql
SHOW PLUGINS;
After the plugin is installed, it will take about 60 seconds (the default batch interval) to start loading the logs from the file into your new table. You should then be able to run your 
SELECT
query successfully. Note: If you are using CelerData Cloud, the audit logs are often pre-configured or available via the "Query History" UI in the console, so you may not need to manually install this plugin. References • administration/management/audit_loader.md • administration/management/logs.md References • administration/management/audit_loader.md
r
@Rocky We had a CN go OOM recently, I'm trying to figure where this could have happened - how can i trace this down?
r
Tracing an Out of Memory (OOM) event on a Compute Node (CN) involves correlating system-level events with StarRocks' internal logs and audit data. 1. Confirm the OOM at the OS level First, verify if the Linux kernel actually killed the process. Look for "Out of memory" or "Killed process" messages around the time of the crash.
Copy code
bash
sudo dmesg -T | grep -i oom
# OR
sudo grep -Ei 'killed process|oom.kill' /var/log/messages
* What to look for: Note the exact timestamp and the process name (
starrocks_be
or 
cn
). If the kernel killed it, the node likely exceeded its physical RAM or the 
mem_limit
in 
be.conf
. 2. Search for "Large Allocations" in CN Logs StarRocks logs when a query attempts a massive memory allocation that might cause instability. Check the 
<http://cn.INFO|cn.INFO>
(or 
<http://be.INFO|be.INFO>
) log for the 
tcmalloc: large alloc
message.
Copy code
bash
grep "large alloc" <http://cn.INFO|cn.INFO> | tail -n 20
* Why it matters: These logs usually include a 
query_id
. If you find one right before the crash, that query is your primary suspect. 3. Check for "Memory Limit Exceeded" Errors Even if the process didn't crash immediately, StarRocks often logs which query triggered the memory safety limits.
Copy code
bash
grep -E "Memory limit exceeded|Mem usage has exceed the limit" cn.WARNING
* Extract the Query ID: Look for a string like 
query_id=749a263d-4c31-11ee-8261-00163e013620
. 4. Correlate with the Audit Log Since you have the audit log set up (from your previous question), use the 
query_id
or the timestamp of the crash to find the specific SQL. If you have a `query_id`:
Copy code
sql
SELECT user, stmt, memCostBytes, queryTime 
FROM starrocks_monitor.cluster_audit_log 
WHERE queryId = 'your-query-id-here';
If you only have a timestamp (e.g., 2024-05-20 14:05):
Copy code
sql
SELECT queryId, user, stmt, memCostBytes, queryTime 
FROM starrocks_monitor.cluster_audit_log 
WHERE timestamp BETWEEN '2024-05-20 14:00:00' AND '2024-05-20 14:10:00'
ORDER BY memCostBytes DESC 
LIMIT 10;
5. Inspect the 
cn.out
file When a CN/BE process crashes, it often dumps a stack trace or a final error message into the 
.out
file (located in the 
log/
directory).
Copy code
bash
tail -n 100 cn.out
* What to look for: If you see a C++ stack trace, it might indicate a bug or a specific operator (like a Join or Sort) that ran out of memory. 6. Review Memory Tracker Snapshots In newer versions (v3.x+), StarRocks often prints a "MemTracker" snapshot to the 
<http://cn.INFO|cn.INFO>
log when it detects it is nearing an OOM state. * Search for 
Memory Tracker Summary
in the logs. * It will break down memory usage by category: 
QueryPool
, 
Load
, 
Metadata
, etc. * If 
QueryPool
is high, it's a user query. If 
Metadata
is high, you might have too many tablets or partitions. Summary Checklist 1. Time: Find the exact second of the crash from 
dmesg
. 2. Identity: Find the 
query_id
in 
<http://cn.INFO|cn.INFO>
or 
cn.WARNING
around that time. 3. SQL: Query your 
cluster_audit_log
with that ID to see the SQL and how much memory it claimed. 4. Fix: Once identified, you can either optimize the SQL, set a stricter 
query_mem_limit
, or enable 
enable_spill
. References • administration/management/logs.md • faq/operation_maintenance_faq.md • faq/Sql_faq.md • administration/management/resource_management/Memory_management.md References • administration/management/logs.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md
r
@Rocky all of this is in celerdata, how do i run those there
r
In CelerData, your troubleshooting workflow is much simpler because most of the log correlation is handled in the CelerData Cloud Console. You generally don't need to use CLI tools like 
grep
on individual nodes. 1. Identify the Time & Node in "Monitoring" Go to the Monitoring tab in your CelerData console. * Look for a sudden drop in the Memory Usage chart or a "heartbeat lost" event for a specific Compute Node (CN). * Note the exact timestamp when the memory peaked or when the node restarted. 2. Find the Query in "Query History" Instead of querying an audit table via SQL, use the built-in Query History page: * Filter by Status: Look for queries with a 
FAILED
or 
KILLED
status around the OOM timestamp. * Sort by Memory: Click the Memory column to sort by the highest memory consumers. This is the fastest way to find the "killer" query. * Check "Peak Memory": The console will show you exactly how much memory that specific query was using when it failed. 3. Analyze the "Query Profile" Click on the 
Query ID
of the suspect query to open the Query Profile (DAG). * Look at the Memory Breakdown per operator. * CelerData highlights which specific operator (e.g., 
Hash Join
or 
Local Exchange
) was consuming the most RAM. This tells you if you need to optimize a join, add a filter, or enable spilling for that specific SQL. 4. Accessing Raw Logs (If needed) If the UI doesn't provide enough detail (e.g., you suspect a system-level leak rather than a user query): * BYOC (AWS/GCP/Azure): CelerData typically exports logs to your own cloud storage (S3/GCS) or logging service (CloudWatch/Stackdriver). Check your cluster configuration to see where these are directed. * Enterprise (On-Prem): If you are running CelerData Enterprise on your own VMs, follow the standard StarRocks log paths: 
log/cn.INFO
and 
log/cn.out
. Summary for CelerData Cloud For this info... | Go here in the CelerData Console Which query was it? | Query History (Sort by Memory) Which node crashed? | Monitoring (Node Memory charts) Why did it use so much? | Query Profile (Operator-level memory view) Was spilling active? | Query Profile (Look for 
Spill
metrics in the plan) Recommendation: If you see a query consistently hitting OOM, check the Query Profile. If the memory is heavily used by a 
Hash Join
, ensure you are joining on the correct keys or try enabling 
enable_spill = true
for that specific session to see if it completes. completes the query without crashing the CN.
r
@Rocky Go to the Monitoring tab in your CelerData console. Cannot find this
r
In CelerData Cloud, the Monitoring and Query History tools are located inside the specific cluster's detail page. If you are looking at the main organization-level dashboard, you won't see them. Follow these steps to find them: 1. Select your Cluster 1. In the left-hand navigation bar, click on Clusters. 2. In the list of clusters, click on the Name of the cluster that experienced the OOM event. 2. Locate the Tabs Once you are inside the cluster details view, look at the top menu bar (just below the cluster name). You should see the following tabs: * Overview: Basic health and CCU usage. * Query History: This is where you can find the high-memory queries. * Monitoring (sometimes labeled as Metrics or Observability): This is where the CPU and Memory charts for each node (CN/FE) are located. * SQL Editor: Where you run your manual queries. 3. What to do in "Query History" Since you are investigating an OOM, the Query History tab is actually more useful than the Monitoring tab: 1. Go to Query History. 2. Click the Status filter and select 
Failed
. 3. Click the Memory column header to sort by the highest memory usage. 4. Look for queries that failed around the time of the crash. 5. Click the Query ID to see the Query Profile. In the profile, CelerData will show you exactly which part of the SQL (e.g., a specific Join or Aggregation) hit the memory limit. 4. What to do in "Monitoring" If you want to see the memory trend: 1. Go to the Monitoring tab. 2. Look for the Memory Usage chart. 3. Select the time range of the incident. You should see a spike on one or more nodes followed by a drop (when the node restarted). Note: If you are using an Elastic Cluster, you might also check the Warehouses tab if you have multiple warehouses, as monitoring can be segmented by warehouse.
Open in Slack
PreviousNext
Powered by