http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> "SHOW PROC '/transactions/_statisti...
# questions-and-troubleshooting
r
@Rocky "SHOW PROC '/transactions/_statistics_/running';" shows many trunscations stuck in COMMITTED state. They are likely related to statistics.predicate_columns table. Two questions: 1. How to find out the exact table those transactions are running agains 2. Is it safe to truncate statistics.predicate_columns table to clean up stuck transactions?
r
To find the table associated with stuck transactions in the 
_statistics_
database and address issues with the 
predicate_columns
table, follow these steps: 1. How to find the exact table for a transaction When you run 
SHOW PROC '/transactions/_statistics_/running';
, you will see a list of transactions. To map a transaction to a specific table: * Check the Label: The 
Label
column in the output often contains identifying information. For internal statistics tasks, the label typically follows a pattern like 
analyze_<table_id>_...
or includes the table ID. * Query Transaction Info: You can get more details about a specific transaction ID using:
Copy code
sql
    SHOW TRANSACTION FROM _statistics_ WHERE id = <transaction_id>\G
This may show the specific table or partitions involved in the 
Label
or 
Attachments
fields. * Map Table ID to Name: If you find a 
TableId
in the label, use the following to find the name:
Copy code
sql
    SELECT TABLE_NAME, TABLE_SCHEMA
    FROM information_schema.tables
    WHERE TABLE_ID = <table_id>;
2. Safety of truncating 
_statistics_.predicate_columns
 Yes, it is generally safe to truncate the 
_statistics_.predicate_columns
table. * Purpose: This table (introduced in v3.5) acts as a metadata log that records which columns are frequently used in filters (
WHERE
, 
JOIN
, 
GROUP BY
). The Cost-Based Optimizer (CBO) uses this to decide which columns need statistics collection. * Impact of Truncating: If you truncate it, StarRocks will simply lose the history of which columns were used as predicates. It will begin re-collecting this information as new queries are executed. * Cleaning Stuck Transactions: Truncating the table will clear the data, but transactions already in the COMMITTED state are in the "Publish Version" phase. They have already written data and are waiting for the FE to publish the new version. Truncating the table might not immediately "kill" the transaction, but it prevents new ones from queuing up if the table has become a bottleneck. Recommendations for "Stuck" Transactions If transactions are stuck in 
COMMITTED
for a long time: 1. Check BE Publish Version: This state usually means the FE is waiting for Backends (BEs) to acknowledge the version update. Check the FE leader's 
fe.log
for "publish version timeout" or "slow to publish version" messages. 2. Internal Statistics Configuration: If the high volume of transactions against 
_statistics_
is causing cluster-wide performance issues (e.g., version count overhead), you can temporarily disable predicate column collection:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("enable_collect_predicate_columns" = "false");
3. FE Restart: Transactions stuck in 
COMMITTED
that never transition to 
VISIBLE
are sometimes the result of a metadata inconsistency. If they persist for hours despite low system load, an FE restart is often required to clear the clear the transaction manager's state. References • loading/SQL_transaction.md • knowledge/trouble_shooting/fe_deadlock_problems.md
r
@Rocky How to find table name based on TransactionId?
Copy code
SHOW TRANSACTION FROM _statistics_ WHERE id = <transaction_id>
doesn't show such information
r
To find the exact table name from a 
TransactionId
when 
SHOW TRANSACTION
doesn't provide it (which is common for internal statistics tasks), you can use the following steps to map the ID through the system metadata views. 1. Find the Table Name using System Views You can join the 
be_txns
view with 
partitions_meta
(available since v3.1) to find the table associated with a specific transaction. Run this query (replacing 
<your_txn_id>
):
Copy code
sql
SELECT 
    p.DB_NAME, 
    p.TABLE_NAME, 
    p.PARTITION_NAME,
    t.TXN_ID
FROM information_schema.be_txns t
JOIN information_schema.partitions_meta p ON t.PARTITION_ID = p.PARTITION_ID
WHERE t.TXN_ID = <your_txn_id>
LIMIT 1;
If you don't have 
partitions_meta
, use the Table ID path: 1. Find the 
TABLET_ID
for the transaction:
Copy code
sql
   SELECT TABLET_ID FROM information_schema.be_txns WHERE TXN_ID = <your_txn_id> LIMIT 1;
2. Find the 
TABLE_ID
from that tablet:
Copy code
sql
   SELECT TABLE_ID FROM information_schema.be_tablets WHERE TABLET_ID = <tablet_id> LIMIT 1;
3. Get the name from the configuration:
Copy code
sql
   SELECT TABLE_SCHEMA, TABLE_NAME
   FROM information_schema.tables_config
   WHERE TABLE_ID = <table_id>;
2. Is it safe to truncate 
_statistics_.predicate_columns
? Yes, it is safe. This table is used by the Cost-Based Optimizer (CBO) to track which columns are frequently used in filters. This helps the CBO prioritize statistics collection for those specific columns. * Impact of Truncating: You lose the history of "which columns have been queried lately." The next time a query runs, StarRocks will simply re-log the columns it sees. * Why it's stuck: Transactions stuck in 
COMMITTED
for this table usually indicate a "Publish Version" bottleneck. Because many small queries update this table simultaneously, the FE may struggle to publish new versions fast enough. * Prevention: If this table frequently causes issues, you can disable the automatic collection of these predicates:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("enable_collect_predicate_columns" = "false");
Alternatively, you can increase the threshold for when this collection happens via 
statistic_auto_collect_predicate_columns_threshold
. Summary of stuck "COMMITTED" transactions If the transactions remain stuck in 
COMMITTED
for more than a few minutes: 1. Check FE Logs: Search for 
publish version timeout
. This usually means the BEs are busy or there is network congestion. 2. Clear Transactions: Truncating the table 
_statistics_.predicate_columns
will stop new transactions, but if you need to clear the current stuck ones, you may need to wait for the timeout or restart the FE leader if they are blocked the metadata state becomes inconsistent state persists. References • sql-reference/information_schema/tables_config.md • sql-reference/information_schema/tables.md • sql-reference/information_schema/be_configs.md • sql-reference/information_schema/partitions_meta.md • sql-reference/information_schema/table_constraints.md • knowledge/admin_and_operation/shared_data_compaction.md
r
@Rocky this is an example of the output of SHOW PROC '/transactions/datarails_analytic/running' command: [ { "TransactionId": "21375541", "Label": "delete_019e7c91-5401-71eb-ad96-1889d1db975d", "Coordinator": "FE: kube-starrocks-fe-2.kube-starrocks-fe-search.starrocks.svc.cluster.local", "TransactionStatus": "COMMITTED", "LoadJobSourceType": "INSERT_STREAMING", "PrepareTime": "2026-05-31 054559", "PreparedTime": "2026-05-31 054559", "CommitTime": "2026-05-31 054559", "PublishTime": null, "FinishTime": null, "Reason": "", "ErrorReplicasCount": "0", "ListenerId": "[-1, 49163640]", "TimeoutMs": "14400000", "PreparedTimeoutMs": "86400000", "ErrMsg": "" } ] This command:
Copy code
SELECT
    p.DB_NAME,
    p.TABLE_NAME,
    p.PARTITION_NAME,
    t.TXN_ID
FROM information_schema.be_txns t
JOIN information_schema.partitions_meta p ON t.PARTITION_ID = p.PARTITION_ID
WHERE t.TXN_ID = 21375541
LIMIT 1;
returns nothing
r
To find the table name for a transaction stuck in 
COMMITTED
status when the standard 
be_txns
query returns nothing, you can use the following methods: 1. Check the 
ListenerId
 In your 
SHOW PROC
output, the 
ListenerId
field is 
[-1, 49163640]
. In many transaction types (like those generated by 
INSERT
, 
DELETE
, or 
UPDATE
), the second value in that array is often the Table ID. Run this query to see if that ID matches a table in your database:
Copy code
sql
SELECT TABLE_NAME, TABLE_SCHEMA 
FROM information_schema.tables 
WHERE TABLE_ID = 49163640;
_(Note: If your version of StarRocks doesn't have 
TABLE_ID
in 
information_schema.tables
, try 
information_schema.tables_config
.)_ 2. Query 
information_schema.loads
 Even though the label starts with 
delete_
, some DML operations are tracked in the load metadata view. Search by the specific label provided in your output:
Copy code
sql
SELECT DB_NAME, TABLE_NAME, STATE, TYPE 
FROM information_schema.loads 
WHERE LABEL = 'delete_019e7c91-5401-71eb-ad96-1889d1db975d';
3. Check FE Leader Logs Since the transaction is in the 
COMMITTED
state, the Front-End (FE) has finished the execution phase and is currently in the Publish Version phase. The mapping is held in the FE's memory. You can find the table name by searching the FE leader's 
fe.log
for the `TransactionId`:
Copy code
bash
grep "21375541" fe.log
Look for lines containing 
committing transaction
or 
publish version
. These lines usually explicitly mention the 
TableId
or 
TableName
. 4. Why 
be_txns
returned nothing The 
information_schema.be_txns
view shows transactions currently being processed by the Back-End (BE) data-service. When a transaction reaches 
COMMITTED
, the BE has already finished writing the data and reported success to the FE. At this point, the transaction is often removed from the active 
be_txns
list because the BE is no longer "running" the logic; it is simply waiting for the FE to broadcast the new version number. Summary: Is it the Statistics Table? Regarding your first question: if the table name found via the methods above is 
_statistics_.predicate_columns
, then: * Yes, it is safe to truncate it. This table is just a log of used columns for the optimizer. * Transactions stuck in 
COMMITTED
 on this table usually mean the FE is overwhelmed by the high frequency of small updates to this table (each query can trigger one). * To stop the flood, set: 
ADMIN SET FRONTEND CONFIG ("enable_collect_predicate_columns" = "false");
References • sql-reference/information_schema/be_txns.md • sql-reference/information_schema/be_cloud_native_compactions.md • sql-reference/information_schema/be_compactions.md • sql-reference/information_schema/be_threads.md • sql-reference/information_schema/be_tablets.md • administration/management/BE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md
2 Views
Open in Slack
PreviousNext
Powered by