http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Is there a way to keep a record of...
# questions-and-troubleshooting
m
@Rocky Is there a way to keep a record of query history in my primary key table? I want to identify and profile queries that cause cpu spikes
r
Yes, there is a way to keep a record of query history in a StarRocks table to help you identify and profile queries causing CPU spikes. 1. Persistent Query History: AuditLoader StarRocks provides a plugin called AuditLoader that automatically parses the FE audit logs (
fe.audit.log
) and loads them into a StarRocks table. This is the recommended way to maintain a long-term, searchable record of query performance. * Setup: You create a database (e.g., 
starrocks_audit_db*
) and a table (e.g., 
starrocks_audit_tbl*
). * Metrics: The table includes specific columns for resource tracking: * `cpuCostNs`: CPU time consumed by the query (in nanoseconds). * `memCostBytes`: Memory consumed. * `queryTime`: Total execution time (in milliseconds). * `stmt`: The actual SQL statement. * Table Type: While the default instructions often use a Duplicate Key table (ideal for logs), you can define it as a Primary Key table using 
queryId
as the primary key if you want to ensure uniqueness and efficient lookups by ID. 2. Identifying CPU Spikes Once AuditLoader is running, you can find the most CPU-intensive queries using a simple SQL query:
Copy code
sql
SELECT 
    user,
    queryId,
    cpuCostNs / 1e9 AS cpu_seconds,
    queryTime,
    stmt
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE timestamp >= NOW() - INTERVAL 1 DAY
ORDER BY cpuCostNs DESC
LIMIT 10;
3. Profiling Specific Queries Once you have identified a 
queryId
from your history table that caused a CPU spike, you can view its detailed execution profile to see which operator (e.g., a large Join or Scan) was responsible: * View Profile List: 
SHOW PROFILELIST;
(lists recent historical queries). * Analyze Profile:
Copy code
sql
    -- Available in v3.2+
    ANALYZE PROFILE FROM '<query_id>';
This command provides a text-based breakdown of where time and CPU were spent in the execution tree. 4. Real-time Identification If you are currently experiencing a CPU spike and want to see what is running right now, use the 
PROC
system: * Current FE: 
SHOW PROC '/current_queries';
* Global (All FEs, v3.4+): 
SHOW PROC '/global_current_queries';
These commands show 
CPUTime
for active queries, allowing you to kill problematic ones immediately using 
KILL QUERY <query_id>;
. Note: To ensure detailed profiles are captured for your history, make sure the FE configuration 
enable_collect_query_detail_info
is set to 
true
. References • sql-reference/information_schema/tasks.md • sql-reference/information_schema/warehouse_queries.md • sql-reference/information_schema/task_runs.md • data_source/block_cache_warmup.md • using_starrocks/running_queries.md
m
@Rocky Since I have not enabled this audit loader yet, I want to just journalctl or cat my fe.audit.log. I know the approximate timestamp the cpu spike occurred. How can I check this by ssh into the machine to view logs
r
Since you haven't enabled the AuditLoader plugin yet, you can find the necessary information by inspecting the 
fe.audit.log
file directly on your FE (Frontend) machine. 1. Locate the Log File By default, the audit log is located in the 
log
directory of your StarRocks installation: * Path: 
${STARROCKS_HOME}/log/fe.audit.log
* If you are unsure of the home directory, you can check the process environment: 
ps aux | grep StarRocksFE
. 2. Identify the Log Format StarRocks uses a pipe-separated (
|
) format by default. Each entry contains a timestamp at the beginning and various 
key=value
pairs. The field you are looking for is 
cpucostns
 (CPU cost in nanoseconds). 3. Searching for CPU Spikes If you know the approximate time (e.g., June 2nd around 14:00), use the following commands to find the heaviest queries: Option A: Find the Top 10 CPU-consuming queries in a specific hour This command greps for the timestamp, extracts the CPU cost and the SQL statement, and sorts them numerically.
Copy code
bash
# Replace '2024-06-02 14:' with your specific date and hour
grep "2024-06-02 14:" fe.audit.log | \
awk -F'|' '{
    cpu=0; stmt=""; 
    for(i=1;i<=NF;i++) {
        if($i ~ /cpucostns=/) {split($i, a, "="); cpu=a[2]}
        if($i ~ /stmt=/) {split($i, b, "="); stmt=b[2]}
    }
    print cpu/1000000000 "s", stmt
}' | sort -rn | head -n 10
Option B: Search for extremely long-running queries If the CPU spike was caused by a long-running query, you might also look at 
queryTime
(in milliseconds):
Copy code
bash
grep "2024-06-02 14:" fe.audit.log | grep -o "queryTime=[0-9]*" | sort -rn | head -n 10
4. What to look for in the logs When you find a suspicious line, look for these specific keys: * 
cpucostns
: The total CPU time spent across all fragments for that query. * 
memCostBytes
: Total memory used. * 
scanBytes
/ 
scanRows
: If these are extremely high, the query is likely scanning too much data due to missing predicates or poor partitioning. * 
stmt
: The SQL query text itself. 5. If using JSON format If your cluster has 
audit_log_json_format=true
enabled (available in v3.2.7+), the file will be in JSON format. You can use 
jq
to parse it:
Copy code
bash
grep "2024-06-02 14:" fe.audit.log | jq -r 'select(.cpucostns > 1000000000) | [.cpucostns, .stmt] | @tsv' | sort -rn
Note: If you are running multiple FEs, you may need to check the 
fe.audit.log
on all FE nodes, as queries are logged on the specific FE that received the client connection. References • administration/management/logs.md • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • administration/management/audit_loader.md • Table design/StarRocks_table_design.md • Table design/StarRocks_table_design.md References • administration/management/logs.md • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md
m
@Rocky My fe.audit.log is empty. The only files with information is fe.log, fe.out, fe.warn.log and proc_profile
r
If your 
fe.audit.log
is empty, it usually means the audit logging module is either disabled in your configuration or the logs are being rotated/deleted prematurely. However, since you have 
proc_profile
, you actually have access to a more advanced diagnostic tool. Here is how you can investigate your CPU spikes using the files you found: 1. Check 
proc_profile
(Best for FE CPU Spikes) The 
proc_profile
directory is a feature introduced in StarRocks v3.2+ that automatically captures FE (Frontend) performance profiles. This is highly useful if the CPU spike is happening on the FE node itself. * Location: Look for files in 
${STARROCKS_HOME}/log/proc_profile/
. * What they are: These are typically 
.tgz
or 
.html
files containing Flame Graphs. * How to use: 1. Find the file with a timestamp matching your CPU spike. 2. Download the 
.html
file and open it in a web browser. 3. The Flame Graph will show you exactly which Java threads and methods were consuming CPU during that window. 2. Fix the empty 
fe.audit.log
 To identify specific SQL queries causing future spikes, you must ensure audit logging is enabled. * Check current config: Connect via MySQL and run:
Copy code
sql
    ADMIN SHOW FRONTEND CONFIG LIKE '%audit%';
* Enable it if missing: Ensure 
audit_log_modules
includes 
query
. If it's empty, you can set it dynamically:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("audit_log_modules" = "query,slow_query");
Note: Check your 
fe.conf
file to make sure these aren't being overridden on restart. 3. Check 
fe.log
and 
fe.warn.log
 Since these files have information, you can search them for clues: * Look for Slow Queries: Even without the audit log, slow queries or large metadata operations often leave traces in 
fe.log
.
Copy code
bash
    grep -i "slow query" fe.log
* Look for "Big Query" logs: StarRocks sometimes logs "Big Queries" (those exceeding memory/time thresholds) in the main log if configured. * Look for Connection Spikes: Search for 
connection
to see if a sudden influx of users caused the spike. 4. Investigate BE CPU Spikes (if logs are empty) If the CPU spike is on the Backend (BE) nodes rather than the FE, the FE logs might not show much. To see what the BEs are doing right now (or during a spike), you can use: * Top N Threads (v3.1+): You can run this via your SQL client to see which BE threads are using the most CPU:
Copy code
sql
    -- Replace <backend_id> with the ID from SHOW BACKENDS
    ADMIN EXECUTE ON <backend_id> 'System.print(ExecEnv.io_profile_and_get_topn_stats("all", 10, 3))';
Summary Recommendation 1. Immediate action: Open the latest 
.html
files in the 
proc_profile
directory to see if the FE was stuck in a specific task (like planning or metadata lock). 2. Correction: Ensure 
audit_log_modules
is set to 
query,slow_query
so that 
fe.audit.log
begins capturing SQL execution data. 3. AuditLoader: Once 
fe.audit.log
is populating, install the AuditLoader plugin to keep this history in a Primary Key table as originally planned earlier. References • administration/management/logs.md • administration/management/FE_configuration.md • administration/management/audit_loader.md
m
@Rocky My audit_modules already contain query and slow query. What else causing nothing to show up in fe.audit.log?
r
If your 
audit_log_modules
includes 
query
and 
slow_query
but 
fe.audit.log
is still empty, there are a few specific reasons why this might be happening: 1. Check the Built-in Audit Plugin StarRocks uses a built-in plugin called 
__builtin_AuditLogBuilder
to write these logs. If this plugin is disabled or failed to start, the log file will remain empty. * Run this command: 
SHOW PLUGINS;
* What to look for: Ensure 
__builtin_AuditLogBuilder
is listed and its 
Status
is 
INSTALLED
. If it is not there or has an error, the logging subsystem is inactive. 2. Verify the Actual Log Directory It's possible that the audit logs are being written to a different location than you expect, especially if 
audit_log_dir
was customized. * Run this command: 
ADMIN SHOW FRONTEND CONFIG LIKE '%audit_log_dir%';
* Check the path: SSH into that specific directory and check for the file there. 3. Multiple FE Nodes If you have a cluster with multiple Frontends (FEs), each FE only logs the queries it personally handles. * If you are connected via a Load Balancer (like HAProxy or F5), your queries might be hitting FE-A while you are SSH'd into FE-B. * Check the 
fe.audit.log
on all FE nodes in your cluster. 4. Check Logback/Log4j2 Configuration StarRocks uses Log4j2 for logging. If someone modified the 
conf/log4j2.xml
file, the audit appender might have been commented out or its level set too high. * Check 
${STARROCKS_HOME}/conf/log4j2.xml
. * Look for the 
<RollingFile name="audit">
section. Ensure it isn't disabled and that the path matches where you are looking. 5. Disk Space or Permissions * Permissions: Ensure the user running the StarRocks process (often 
starrocks
) has write permissions to the log directory. * Disk Space: If the disk is 100% full, the FE may stop writing to logs to prevent a crash. Run 
df -h
to verify. 6. Configuration Refresh If you recently changed the 
audit_log_modules
setting using 
ADMIN SET CONFIG
, try restarting the FE. While many configs are mutable, some logging appenders require a restart to properly initialize if they were completely disabled at boot. Next Step Recommendation: Run 
SHOW PLUGINS;
first. If the builtin audit builder is active, and you have confirmed you are on the correct FE node, check the 
log4j2.xml
to see if the audit logging was manually suppressed at the file-level. References • administration/management/FE_configuration.md • administration/management/logs.md • administration/management/audit_loader.md • knowledge/usage/best-practice.md
m
@Rocky Would enable_collect_query_detail_info not work if I cant get fe.audit.log to work? Where cna I view the profiled queries
r
Yes, 
enable_collect_query_detail_info
will work even if your 
fe.audit.log
is empty. They are two separate systems: * 
fe.audit.log
: Driven by the Audit Plugin to record the SQL text and basic execution stats to a file. * 
enable_collect_query_detail_info
: An internal FE mechanism that collects structured JSON-like "Query Detail" objects (including profiles) and stores them in FE memory for APIs and the Web UI. 1. Where to view the data Once you enable it (
ADMIN SET FRONTEND CONFIG ("enable_collect_query_detail_info" = "true");
), you can view the profiled queries in three ways: A. The FE Web UI (Easiest) Navigate to the web interface of the FE you are connected to: * URL: 
http://<FE_IP>:<FE_HTTP_PORT>/query
(Default port is 
8030
). * This page lists recent queries. If you also have 
SET enable_profile = true;
enabled for your session, you can click on a Query ID to see a detailed visual profile and execution breakdown. B. The Query Detail API You can fetch the collected data as JSON via 
curl
. This is useful if you want to search through recent queries without a functional log file.
Copy code
bash
curl -u root:password "http://<FE_IP>:8030/api/query_detail?event_time=0"
* The 
event_time=0
parameter returns all cached records. * You can filter by user or specific timestamps using API parameters. C. Profile Logs (StarRocks v3.2.5+) If you are on a newer version, you can enable Profile Logging, which writes these detailed JSON objects to a separate file instead of the audit log. 1. Check if enabled: 
ADMIN SHOW FRONTEND CONFIG LIKE 'enable_profile_log';
2. Enable it: 
ADMIN SET FRONTEND CONFIG ("enable_profile_log" = "true");
3. View Logs: Look for files named 
profile.log
in your 
${STARROCKS_HOME}/log/
directory. These will contain the detailed JSON records that 
enable_collect_query_detail_info
produces. 2. View Running Queries (Real-time) If the CPU spike is happening right now, the most effective way to see what is consuming resources is via the 
proc
system:
Copy code
sql
-- View running queries on the current FE
SHOW PROC '/current_queries';

-- View running queries across all FEs (v3.4+)
SHOW PROC '/global_current_queries';
Summary Comparison Feature | Target File | Purpose Audit Log | 
fe.audit.log
| Records every SQL statement for auditing/security. Query Detail | 
profile.log
(or Memory/API) | Records deep performance metrics (CPU, Memory, Scan rows). Note on Performance: Be cautious with 
enable_collect_query_detail_info = true
in extremely high-concurrency environments (thousands of queries per second), as collecting these details adds a small amount of overhead to the FE. For troubleshooting a CPU spike, it is perfectly safe to enable. References • administration/management/FE_configuration.md • administration/http_interface/query_detail.md • using_starrocks/running_queries.md • sql-reference/System_variable.md • administration/management/logs.md References • administration/http_interface/query_detail.md • using_starrocks/running_queries.md • sql-reference/information_schema/loads.md
m
@Rocky Is there no table I can view the profiled queries? Can only fetch via API?
r
There is no built-in, persistent SQL table (like 
information_schema.query_history
) that automatically stores query profiles indefinitely. However, you can interact with profiles and query history using SQL in two ways: 1. The "Pseudo-Table" Commands (SQL-based viewing) StarRocks provides SQL commands that return tabular results for query analysis. These do not require a physical table to exist: * 
SHOW PROFILELIST
: This is the closest SQL equivalent to a history table. It lists recently finished and running queries along with their Query IDs and execution times.
Copy code
sql
    SHOW PROFILELIST;
    -- Or limit the results
    SHOW PROFILELIST LIMIT 10;
* 
ANALYZE PROFILE
: Once you have a Query ID from the list above, you can "query" its performance breakdown directly:
Copy code
sql
    ANALYZE PROFILE FROM 'a40456b2-8428-11ee-8d02-6a32f8c68848';
* 
SHOW PROC '/current_queries'
: To see what is running right now in a table format (including CPU and Memory usage), use this:
Copy code
sql
    SHOW PROC '/current_queries';
    -- In v3.4+, use: SHOW PROC '/global_current_queries';
2. Creating a Real Table via "AuditLoader" If you want a standard StarRocks table that you can query with 
SELECT
, 
WHERE
, and 
GROUP BY
, you must use the AuditLoader plugin. * How it works: It reads the data from your 
fe.audit.log
and automatically performs a 
Stream Load
into a StarRocks table you define (e.g., 
starrocks_audit_db*.starrocks_audit_tbl*
). * Catch-22: Since your 
fe.audit.log
is currently empty, AuditLoader will have nothing to load. You must first resolve why the 
fe.audit.log
is empty (check 
SHOW PLUGINS;
to ensure 
__builtin_AuditLogBuilder
is 
INSTALLED
) before this table-based approach will work. 3. Why isn't there an 
information_schema
table? StarRocks keeps query profiles and "Query Detail" info in FE memory to avoid the disk I/O overhead of writing every single query's full performance metrics to a system table. This is why the primary ways to access it are the memory-based API (Web UI) or the transient SQL commands like 
SHOW PROFILELIST
. Summary Recommendation 1. For historical analysis: Fix your 
fe.audit.log
and install AuditLoader. This gives you a permanent, searchable SQL table of all queries. 2. For immediate troubleshooting: Use 
SHOW PROFILELIST
to find the Query ID of your CPU spike, then 
ANALYZE PROFILE
to see the details. References • best_practices/query_tuning/query_profile_text_based_analysis.md • administration/management/audit_loader.md • using_starrocks/running_queries.md • administration/http_interface/query_detail.md References • administration/http_interface/query_detail.md • using_starrocks/running_queries.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky Show profilelist truncates the query responsible. How do I view full query.
r
To view the full query text when it is truncated in 
SHOW PROFILELIST
, you should use the Query ID from that list to fetch the complete details using one of the following methods: 1. Using 
ANALYZE PROFILE
(Best for SQL) Once you have the 
QueryId
from 
SHOW PROFILELIST
, run this command to see a text-based breakdown of the query. The "Summary" section at the top typically contains the full SQL statement.
Copy code
sql
-- Replace <Query_ID> with the ID from SHOW PROFILELIST
ANALYZE PROFILE FROM '<Query_ID>';
2. Using 
get_query_profile
 This function returns the entire profile as a string. You can use it to see the full SQL text and the detailed execution plan. Use 
\G
in the MySQL client to make it readable:
Copy code
sql
SELECT get_query_profile('<Query_ID>')\G
3. For Currently Running Queries If the query is still running and you want to see the full text without truncation, use the 
FULL
keyword with the process list command:
Copy code
sql
SHOW FULL PROCESSLIST;
* 
SHOW PROCESSLIST
truncates after 100 characters. * 
SHOW FULL PROCESSLIST
displays the entire SQL statement. 4. Via the FE Web UI The Web UI is often the easiest place to read long, complex queries. 1. Go to 
http://<FE_IP>:8030/query
(the Queries page). 2. Find your query in the list. 3. Click the Query ID or the Profile link. The full SQL will be displayed at the top of the detail page. Why is it truncated? 
SHOW PROFILELIST
and 
SHOW PROCESSLIST
are designed to provide a quick summary of many queries. To prevent the terminal from being flooded with multi-megabyte SQL strings, StarRocks truncates the display by default. You must explicitly "drill down" into a specific query ID to see the full text. References • sql-reference/sql-statements/cluster-management/plan_profile/SHOW_PROFILELIST.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • best_practices/query_tuning/query_profile_text_based_analysis.md • best_practices/query_tuning/query_profile_overview.md References • sql-reference/sql-statements/cluster-management/plan_profile/SHOW_PROFILELIST.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • best_practices/query_tuning/query_profile_text_based_analysis.md • using_starrocks/running_queries.md
Open in Slack
PreviousNext
Powered by