@Rocky what are the ways to check query history, long running queries, and other helpful aspects to investigate previously run queries?

To investigate query history and monitor long-running queries in StarRocks, you can use several built-in tools ranging from real-time monitoring to historical log analysis. 1. Check Currently Running (Long-Running) Queries If you need to see what is consuming resources right now, use these commands: *

SHOW PROC '/current_queries'

: Displays queries currently running on the FE node you are connected to. *

SHOW PROC '/global_current_queries'

(v3.4+): Shows queries running across all FE nodes in the cluster. This is the most comprehensive way to find "top talkers" cluster-wide. _ _Useful columns:*

ScanBytes

,

ScanRows

,

MemoryUsage

,

CPUTime

, and

ExecTime

. *

SHOW RUNNING QUERIES

: Specifically useful if you have Query Queues enabled. It shows if a query is

RUNNING

or

PENDING

(waiting in a queue) and how long it has been waiting. *

SHOW PROCESSLIST

: A standard MySQL-compatible command to see active connections and the SQL they are executing. 2. Check Query History StarRocks provides two primary ways to look at previously executed queries: *

SHOW PROFILELIST

: This command lists recently completed queries (Finished, Error, or Running for >10s). * It provides a

QueryId

which is essential for deep-dive analysis. * Example:

SHOW PROFILELIST LIMIT 10;

* Audit Log (

fe.audit.log

): The source of truth for all historical queries. * Located at

fe/log/fe.audit.log

. * It records the User, Client IP, StartTime, EndTime, Query ID, and the full SQL statement. * It also includes performance metrics like

QueryTime

,

ScanBytes

, and

ReturnRows

. _ _Tip:* Many users create an External Table pointing to these logs so they can query their query history using SQL. 3. Deep-Dive Investigation (Why was it slow?) Once you identify a slow

QueryId

from the history or running list, you can analyze its execution plan in detail: *

ANALYZE PROFILE FROM '<Query_ID>'

: This generates a detailed, text-based report of the query execution. * It highlights nodes with high CPU/Memory usage. * For running queries, it shows progress icons (🚀 for running, ✅ for finished). *

EXPLAIN ANALYZE <SQL>

: If you want to re-run a query and get a performance breakdown immediately, prefix your query with this command. It executes the query and returns the profile. 4. How to Stop a Long-Running Query If you find a query that is "runaway" or hung, you can terminate it using the

ConnectionId

found in

SHOW PROCESSLIST

or `current_queries`:

sql
-- First, find the ConnectionId
SHOW PROCESSLIST;

-- Then, kill the query
KILL CONNECTION <ConnectionId>;

5. Other Helpful Aspects * Resource Groups: If you notice specific users or applications are constantly running long queries, you can use Resource Groups to limit their CPU and Memory usage so they don't impact the rest of the cluster. * Metrics (Prometheus/Grafana): For high-level trends (e.g., "Is my average latency increasing?"), StarRocks exports metrics like

starrocks_fe_query_err_rate

and

starrocks_fe_query_latency

. References • using_starrocks/running_queries.md • best_practices/query_tuning/query_profile_text_based_analysis.md • using_starrocks/async_mv/materialized_view_task_run_details.md

@Rocky right now i did this SET enable_profile = true; SET GLOBAL enable_profile = true; What will the implications be and can you point me to some references around here?

Hello! I'm Rocky. You’ve enabled the query profiling feature both for your current session and globally for all future sessions. Here is a breakdown of what that means and the implications for your StarRocks cluster. 1. Difference Between the Two Commands *

SET enable_profile = true;

: This applies only to your current connection (session). Any queries you run in this specific window will generate a profile. *

SET GLOBAL enable_profile = true;

: This sets the default for all new connections made to the cluster from this point forward. It does not affect other sessions that are already active. 2. What Happens Now? When this is enabled, the Backends (BEs) will collect detailed execution metrics for every single query (e.g., CPU time, memory usage per operator, data distribution) and send them to the Leader Frontend (FE). 3. Implications to Consider Pros: * Performance Tuning: You get deep visibility into query execution, making it much easier to identify bottlenecks (e.g., "Why is this join slow?"). * Debugging: If a query behaves unexpectedly, the profile provides the ground truth of how data was processed. Cons/Risks: * Performance Overhead: Collecting and sending profile data creates additional network and CPU overhead. In high-concurrency environments (high QPS), this can noticeably degrade performance. * FE Memory Pressure: The FE stores these profiles in memory. By default, it keeps the last 100 profiles. If your queries are very complex, storing them globally for every single user can increase FE memory consumption. * Leader FE Dependency: Profiles are only submitted to the Leader FE. If you are connected to a Follower FE and try to view the profile, you may not see it unless you check the Leader's web UI. 4. Better Alternative:

big_query_profile_threshold

If your goal is to catch "slow" queries without the overhead of profiling every tiny metadata request or fast lookup, StarRocks recommends leaving

enable_profile

at

false

globally and using this instead:

sql
-- Only generate profiles for queries taking longer than 5 seconds
SET GLOBAL big_query_profile_threshold = '5s';

This is much safer for production environments. 5. Where to View the Profiles Once enabled, you can view the profiles in the FE Web UI: * URL:

http://<FE_IP>:<FE_HTTP_PORT>/query

(Default port is 8030) * Alternatively, you can use the command

get profile 'query_id'

if you have the query ID. 6. References For more details, you can check these sections in the official documentation: * Query Profile Overview & Best Practices * System Variables (enable_profile) Recommendation: If this is a production environment with high traffic, I suggest turning the GLOBAL setting off (

SET GLOBAL enable_profile = false;

) and using the

big_query_profile_threshold

approach instead! References • sql-reference/System_variable.md • faq/Others.md • best_practices/query_tuning/query_profile_overview.md • administration/management/proc_profile.md • administration/management/FE_configuration.md