http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Why does a single node deployment s...
# questions-and-troubleshooting
j
@Rocky Why does a single node deployment still fail to return profile data with us inlining/using query hints for these variables
Copy code
SET SESSION enable_profile = true;
SET SESSION big_query_profile_threshold = 0;
SET SESSION pipeline_profile_level = 1;
SET SESSION runtime_profile_report_interval = 0;
SET SESSION enable_query_cache = false;
, running the query, and running 
get_query_profile(last_query_id())
(also with the same hints)?
r
The issue you're experiencing—where 
get_query_profile(last_query_id())
returns empty data despite your settings—is most likely caused by the value you've assigned to 
runtime_profile_report_interval
or a misunderstanding of how 
last_query_id()
behaves with certain clients. 1. 
runtime_profile_report_interval
Must Be a Positive Integer The most likely culprit is 
SET SESSION runtime_profile_report_interval = 0;
. * The Problem: According to StarRocks documentation, this variable requires a positive integer (the default is 
10
). * The Result: Setting this to 
0
can cause the reporting mechanism (which sends profile data from the Backend/BE to the Frontend/FE) to fail or never trigger. If the FE never receives the data from the BE, 
get_query_profile
will return an empty result. * Fix: Set this to a positive value like 
5
or 
10
. 2. 
big_query_profile_threshold = 0
Behavior In several StarRocks versions, a value of 
0
for 
big_query_profile_threshold
is interpreted as disabling the profile capture for queries (unless 
enable_profile
is explicitly 
true
for that specific query). * The Risk: Even with 
enable_profile = true
, some versions treat an explicit 
0
threshold as a "disable" flag. * Fix: Use a very small positive threshold like 
'100ms'
or 
'1s'
, or simply omit this variable if you are already using 
enable_profile = true
. Note that from v3.3.11 and v3.4 onwards, the default value was changed from 
0
to 
30s
to avoid this confusion. 3. 
last_query_id()
and Client Interference If you are running these commands in a SQL IDE (like DBeaver, DataGrip, or Navicat), the IDE often executes "hidden" metadata queries in the background (e.g., 
SELECT DATABASE()
, 
SHOW TABLES
). * The Problem: 
last_query_id()
returns the ID of the very last statement executed in the session. If your IDE ran a background heartbeat or metadata query between your actual query and your 
get_query_profile
call, 
last_query_id()
will return the ID of that background query, which likely has no profile data. * Fix: Manually verify the Query ID by running 
SHOW PROFILELIST;
first. Copy the specific ID for your query and pass it as a string: 
SELECT get_query_profile('your-query-id-here');
. 4. 
get_query_profile
Session Requirement The 
get_query_profile
function itself requires 
enable_profile
to be 
true
during its own execution. If you are using hints, ensure they are formatted correctly:
Copy code
sql
SELECT /*+ set_var(enable_profile=true) */ get_query_profile('your-query-id');
Recommended Troubleshooting Steps Try running this sequence exactly as shown: 1. Set variables with valid ranges:
Copy code
sql
    SET SESSION enable_profile = true;
    SET SESSION big_query_profile_threshold = '10ms'; -- Use a string/duration
    SET SESSION pipeline_profile_level = 1;
    SET SESSION runtime_profile_report_interval = 5; -- Must be positive
2. Run your target query. 3. Check the ID manually:
Copy code
sql
    SHOW PROFILELIST LIMIT 5;
4. Retrieve the profile using the literal ID from the list above:
Copy code
sql
    SELECT get_query_profile('<ID_FROM_STEP_3>');
────────── Reference Links: • get_query_profile Documentation • Query Profile Overview &amp; Variable Ranges • System Variables - runtime_profile_report_interval References • best_practices/query_tuning/query_profile_overview.md • sql-reference/System_variable.md • knowledge/usage/best-practice.md
j
@Rocky Does SHOW PROFILELIST have a consistent view for the cluster or is dependant on what FE I am hitting?
r
No, 
SHOW PROFILELIST
and the 
get_query_profile()
function do not have a consistent cluster-wide view; they are dependent on the specific Frontend (FE) node you are connected to. Each FE node maintains its own local cache of query profiles for the queries it coordinated. Because StarRocks queries are distributed but coordinated by a single FE, the "session" and its associated profile data reside on that specific FE. Why this matters for your setup: 1. FE-Local Caching: When you execute a query, the Backend (BE) nodes report the profile data back to the FE node that initiated the query. Other FE nodes in the cluster (Followers or Observers) do not receive this data and will not show the query in their 
SHOW PROFILELIST
. 2. Load Balancers: If you are connecting to your StarRocks cluster through a load balancer or a proxy (like Nginx, HAProxy, or a Kubernetes Service), your session might be routed to FE-A for the query execution, but your subsequent 
SHOW PROFILELIST
or 
get_query_profile()
call might be routed to FE-B. If this happens, FE-B will return an empty list or "Query ID not found." 3. The Leader Exception: While general SQL query profiles are local to the coordinating FE, certain system-level profiles (like those for Routine Load or specific background tasks) are often centralized and submitted only to the Leader FE. Troubleshooting & Recommendations: * Direct Connection: For debugging, bypass your load balancer and connect directly to the IP of a single FE. Ensure you run both the query and the profile retrieval statement on that same connection. * Web UI: If you use the StarRocks Web UI (
http://<fe_ip>:<fe_http_port>
), remember that the "Queries" tab only displays profiles handled by that specific FE. You may need to check the Web UI of each FE to find your query if you aren't sure which one handled it. * Sticky Sessions: If you must use a load balancer, ensure it is configured with session persistence (sticky sessions) so that your SQL client remains on the same FE for the duration of your analysis. Summary: To see the profile, you must run the retrieval command on the same FE node that executed the query. ────────── Reference Links: • SHOW PROFILELIST Documentation • Query Profile Overview • FE High Availability and Load Balancing References • sql-reference/sql-statements/cluster-management/plan_profile/SHOW_PROFILELIST.md • best_practices/query_tuning/query_profile_overview.md • faq/Others.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_FRONTENDS.md References • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • faq/Others.md • administration/management/logs.md • deployment/deploy_manually.md • best_practices/query_tuning/query_profile_overview.md
j
@Rocky After querying with the right hints for profiling it looks like 
SHOW PROFILELIST
is returning stale data for the same connection. I can see the query immediately if I run ``SHOW PROFILELIST`` in a terminal, while polling for even a long time from the connection that made the query keeps returning the stale profile list
r
The behavior you're describing—where a separate terminal sees the query profile immediately but your active connection returns stale data—is almost always caused by how your SQL client (IDE) manages the session, rather than a StarRocks server-side failure. Here are the primary reasons for this "stale" behavior: 1. 
last_query_id()
Pollution (Most Likely) If you are using a SQL IDE (like DBeaver, DataGrip, or Navicat), the IDE often executes background metadata queries to refresh its UI (e.g., 
SELECT DATABASE()
, 
SHOW TABLES
, or heartbeats). * The Problem: 
last_query_id()
returns the ID of the absolute last statement sent over that connection. If your IDE sent a "hidden" query between your execution and your 
get_query_profile
call, 
last_query_id()
will return the ID of that metadata query. * The Symptom: 
get_query_profile()
returns empty or "stale" data because you are unintentionally asking for the profile of a heartbeat query that doesn't have profiling enabled. * The Fix: Do not rely on 
last_query_id()
in an IDE. Instead, run your query, then run 
SHOW PROFILELIST;
. Manually copy the UUID for your specific statement and run:
Copy code
sql
    SELECT get_query_profile('manual-uuid-here');
2. Client-Side Result Caching Many SQL IDEs cache the results of 
SHOW
statements to improve performance. * The Problem: When you rerun 
SHOW PROFILELIST;
in the same window, your IDE may be showing you the cached result set from your first execution instead of sending a new request to StarRocks. * The Fix: Look for a "Refresh" or "Invalidate Cache" button in your IDE's results grid, or open a completely new SQL editor tab. This explains why a "fresh" terminal works while the "poll" in the existing tab does not. 3. Session "Stickiness" and the Leader FE Even in a single-node deployment, StarRocks differentiates between the Leader FE and other roles. If your deployment has multiple FE processes (even on one machine), certain profile metadata is synchronized to the Leader. * If your connection was established before a leader change or is hitting a different internal port, there might be a synchronization lag. * The Fix: Run the 
SYNC;
command in your session before polling. This forces the FE to synchronize its metadata state:
Copy code
sql
    SYNC;
    SHOW PROFILELIST;
4. Query Duration vs. Visibility * Short Queries: Queries that finish in milliseconds appear in 
SHOW PROFILELIST
only after they have fully completed and the BE has reported the profile back to the FE. * Long Queries: Queries running for more than 10 seconds appear in the list with a 
Running
state. * If your query is very fast, but your BE-to-FE reporting is slightly delayed (controlled by 
runtime_profile_report_interval
), there may be a 1-2 second window where the query has finished but the profile isn't "registered" in the FE's list yet. Recommended Test To rule out client interference, run this exact block in a single execution script:
Copy code
sql
/*+ set_var(enable_profile=true, big_query_profile_threshold=0, runtime_profile_report_interval=5) */
SELECT count(*) FROM your_table; -- Your actual query

-- Immediately check the list without using last_query_id()
SHOW PROFILELIST LIMIT 5;
If your query appears here but not in your manual polling, your SQL IDE is likely "polluting" the session with background queries between your steps. ────────── References: • SHOW PROFILELIST Statement • get_query_profile Function • SYNC Statement References
• sql-reference/sql-statements/cluster-management/plan_profile/SHOW_PROFILELIST.md • sql-reference/sql-functions/utility-functions/get_query_profile.md • best_practices/query_tuning/query_profile_text_based_analysis.md • best_practices/query_tuning/query_profile_overview.md • sql-reference/sql-statements/cluster-management/nodes_processes/SYNC.md • using_starrocks/caching/query_cache.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • data_source/catalog/maxcompute_catalog.md • administration/http_interface/query_detail.md • faq/operation_maintenance_faq.md • faq/Others.md • administration/management/logs.md • deployment/deploy_manually.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_FRONTENDS.md • sql-reference/System_variable.md • knowledge/usage/best-practice.md • best_practices/query_tuning/query_profile_operator_metrics.md
j
@Rocky does 
SYNC
get it's own query id? Will it pollute last_query_id?
r
Yes, 
SYNC
will pollute 
last_query_id()
. In StarRocks, almost every statement sent to the Frontend (FE)—including utility commands like 
SYNC
, 
SHOW
, and `DESCRIBE`—is assigned a unique Query ID. Since 
last_query_id()
returns the ID of the absolute last statement executed in your current session, running 
SYNC
immediately after your query will cause 
last_query_id()
to return the ID for the 
SYNC
command itself. Why this breaks your profiling: 1. Non-Profilable Command: 
SYNC
is a metadata/coordination command. It does not generate a query plan or execution profile. 2. Empty Result: When you run 
get_query_profile(last_query_id())
after a 
SYNC
, you are effectively asking for the profile of the 
SYNC
statement. Because 
SYNC
has no profile data, the function returns an empty result or an error. Does 
SYNC
actually help? In your case, you likely do not need 
SYNC
. * 
SYNC
is used to ensure that Session B can see the results of a write from Session A immediately. * StarRocks already guarantees "Read-Your-Writes" consistency within the same session. Since your query and your profile check are in the same connection, the FE already knows about the query it just coordinated. Correct Workflow in an IDE To avoid "pollution" from 
SYNC
, IDE heartbeats, or metadata queries, use the manual ID method: 1. Run your query with the profiling hints. 2. Run 
SHOW PROFILELIST;
 to see the list of recent queries. 3. Find your specific query in the list (look at the 
Sql
column to match your query). 4. Copy the 
QueryId
 (a UUID like 
5a2b...
) and run:
Copy code
sql
    SELECT get_query_profile('5a2b-your-manual-id-here');
Summary of Pollution Statement Type | Updates 
last_query_id()
? | Has a Profile? 
SELECT / INSERT
| Yes | Yes (if enabled) 
SHOW PROFILELIST
| Yes | No 
SYNC
| Yes | No 
SET
| Yes (usually) | No IDE background queries | Yes | No Recommendation: Stop using 
SYNC
and 
last_query_id()
if you are working in a GUI or running multiple utility commands. Use 
SHOW PROFILELIST
to grab the ID manually to ensure you are looking at the correct execution data for the correct statement. References • sql-reference/sql-statements/cluster-management/nodes_processes/SYNC.md • using_starrocks/Materialized_view-single_table.md • using_starrocks/running_queries.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • administration/management/resource_management/query_queues.md
j
@Rocky same for "SET transaction_read_only = '#{reset_value}'?
r
Yes, the statement 
SET transaction_read_only = ...
will pollute 
last_query_id()
. In StarRocks, almost every command sent to the Frontend (FE)—including 
SET
, 
SHOW
, 
USE
, and `EXPLAIN`—is processed as a statement and assigned a unique Query ID. Since 
last_query_id()
strictly returns the ID of the most recently executed statement in your session, any "hidden" 
SET
command sent by your driver or IDE will overwrite the ID of the actual query you are trying to profile. Why this is happening Many database drivers (especially in environments like Ruby on Rails, Spring/Java, or Python SQLAlchemy) and SQL IDEs (like DBeaver or DataGrip) automatically issue "housekeeping" commands. * Transaction Resets: When a connection is returned to a pool or a query finishes, the client may send 
SET transaction_read_only = 0
or 
SET autocommit = 1
to ensure the next user starts with a clean state. * The Result: If your workflow is: 1. 
SELECT ...
(Query ID: 
A
) 2. 
SET transaction_read_only = ...
(Query ID: 
B
- sent automatically by client) 3. 
SELECT last_query_id();
-> Returns 
B
. 4. 
SELECT get_query_profile(last_query_id());
-> Returns empty/error because Query 
B
has no profile. Does 
SET
appear in 
SHOW PROFILELIST
? Typically, no. 
SHOW PROFILELIST
only tracks statements that generate a distributed execution plan (like 
SELECT
, 
INSERT
, 
UPDATE
, 
DELETE
). Utility commands like 
SET
or 
SHOW
do not generate a runtime profile, so they won't appear in that list. However, they do still update the session's "last executed ID" state. How to solve this Because you cannot always control what your IDE or driver sends in the background, you should avoid 
last_query_id()
for profiling in these environments. Instead: 1. Use 
SHOW PROFILELIST
manually: Run your query, then immediately run 
SHOW PROFILELIST;
. Find the 
QueryId
that matches your specific SQL string and copy it. 2. Hardcode the ID:
Copy code
sql
    -- Use the UUID you copied from the list
    SELECT get_query_profile('5a2b8d68-bbec-11ec-af65-00163e1e238f');
3. Use the Web UI: Navigate to 
http://<fe_ip>:<fe_http_port>/query
. This shows the history for that FE regardless of session pollution, allowing you to click and view the profile for any recent query. 4. Use 
SET_VAR
Hints: Instead of running a separate 
SET
command before your query (which adds more statements to the history), include your settings inside the query itself:
Copy code
sql
    SELECT /_+ SET_VAR(enable_profile=true) _/ * FROM table;
Summary: Any statement, including 
SET transaction_read_only
, updates the 
last_query_id()
. If your client is injecting these automatically, 
last_query_id()
becomes unreliable for manual debugging. References • sql-reference/sql-functions/utility-functions/last_query_id.md • sql-reference/System_variable.md • sql-reference/sql-statements/cluster-management/plan_profile/SHOW_PROFILELIST.md • administration/management/FE_configuration.md • best_practices/query_tuning/query_profile_overview.md References • sql-reference/sql-functions/utility-functions/last_query_id.md • sql-reference/System_variable.md • administration/management/FE_configuration.md
Open in Slack
PreviousNext
Powered by