@Rocky we are planning to push query profile for each query to clickhouse for internal usecase for tracking query performance, resource usage and cost. can you suggest on how this can be implemented. suggest if there are any better approaches.

To implement query performance and resource usage tracking in StarRocks, you have several options depending on how deep you want the "profile" to be (summary vs. full execution plan). 1. The Log-Based Approach (Recommended for External Sinks) StarRocks generates two main logs that are ideal for ingestion into external systems like ClickHouse: *

fe.audit.log

: Records every query with high-level metrics: Execution time, CPU time, Memory usage, Scanned bytes/rows, and the SQL statement itself. * Optimization: Set

audit_log_json_format=true

in

fe.conf

to make parsing easier for external log collectors. *

fe.profile.log

: Introduced for more granular performance analysis. It records the

QueryDetail

object in JSON format, which includes detailed resource consumption metrics like

cpuCostNs

,

memCostBytes

, and

spillBytes

. * Configuration: Enable this by setting

enable_profile_log = true

in your FE configuration. Implementation Strategy: Use a log collection agent (e.g., Filebeat, Logstash, or Vector) to tail these JSON files and push them directly to your destination. This is the most non-intrusive method and ensures query performance isn't impacted by the export process. 2. Audit Loader Plugin (Internal Store & Forward) StarRocks provides an Audit Loader plugin. By default, it parses the audit log and loads it into a StarRocks table:

starrocks_audit_db*.starrocks_audit_tbl*

. * Better Approach? If your goal is internal tracking, storing this data within StarRocks itself using the Audit Loader is often more efficient. You can then use StarRocks' own visualization tools (or a BI tool connected to StarRocks) to monitor performance. * Syncing to ClickHouse: If you still need the data in ClickHouse, you could use a

SELECT ... INTO OUTFILE

or a simple script to move data from the

starrocks_audit_tbl__

to your target. 3. Querying System Tables For real-time monitoring of currently running or recent queries, you can query StarRocks' built-in metadata: * `information_schema.exec_queries`: Shows currently executing queries and their resource usage. * `information_schema.loads`: For tracking data ingestion performance. Comparison & Suggestions Approach | Detail Level | Overhead | Best For

fe.profile.log

+ Vector/Filebeat | Very High (JSON) | Low | Granular performance/cost analysis in external DB. Audit Loader (Internal) | High (Relational) | Minimal | Self-contained monitoring within StarRocks.

fe.audit.log

| Medium | Low | General auditing and latency tracking. Recommendation: If you strictly need the data in an external system,

fe.profile.log

(JSON) combined with a log-shipper like Vector is the cleanest implementation. It provides the exact resource metrics (

cpuCostNs

,

memCostBytes

) you need for cost calculation without adding load to the FE's database engine. For cost tracking specifically, pay close attention to the

cpuCostNs

and

scanBytes

fields in the profile log, as these are the primary drivers of resource consumption in StarRocks. References • administration/management/logs.md • faq/operation_maintenance_faq.md • knowledge/usage/best-practice.md • administration/management/FE_configuration.md • best_practices/audit_log_resource_group.md • developers/trace-tools/query_trace_profile.md

@Rocky can audit loader store query profile for each query?

The Audit Loader plugin is specifically designed to parse and load the

fe.audit.log

. While it can store many performance metrics, it generally does not store the full Query Profile (the execution tree/operator-level breakdown). Here is a breakdown of what you can get via the Audit Loader versus what you need for a full profile: 1. What Audit Loader Can Store The Audit Loader reads from the

fe.audit.log

and populates a table (

starrocks_audit_tbl__

) with summary metrics for every query. This includes: * Resource Usage:

cpuCostNs

,

memCostBytes

,

scanBytes

,

scanRows

. * Execution Info:

queryTime

,

returnRows

,

state

(EOF/ERR),

errorCode

. * Metadata:

queryId

,

user

,

clientIp

,

stmt

(the SQL). * Planning Costs:

planCpuCosts

,

planMemCosts

. If your goal for "tracking query performance and cost" is satisfied by knowing how much CPU/Memory/Data-Scanned each query used, the Audit Loader is sufficient and very convenient. 2. What it Cannot Store The full Query Profile (the detailed breakdown of every Fragment and Operator, e.g., how long a specific

OLAP_SCAN_NODE

took vs a

HASH_JOIN_NODE

) is not part of the standard audit log. Therefore, the Audit Loader cannot load it into a table by default. 3. The Better Approach for Full Profiles:

fe.profile.log

Starting from StarRocks v3.2.5, there is a dedicated log for detailed profiles:

fe.profile.log

. * Content: It stores the

QueryDetail

object in JSON format for every query. This JSON contains the granular metrics you see in the StarRocks Manager or via

SHOW QUERY PROFILE

. * Implementation: 1. Enable it in `fe.conf`:

enable_profile_log = true

and

enable_collect_query_detail_info = true

. 2. Instead of the Audit Loader plugin, use a log-shipper like Vector or Filebeat. 3. Configure the shipper to tail

fe.profile.log

, parse the JSON, and push it directly to ClickHouse. Summary Recommendation * If you want summary metrics (Cost/Usage): Use the Audit Loader. It's built-in and easy to query within StarRocks. You can then pull this table into ClickHouse if needed. * If you want the deep Execution Profile: Use

fe.profile.log

+ Vector/Filebeat to push JSON data to ClickHouse. The Audit Loader is not the right tool for full operator-level profiles. References • administration/management/audit_loader.md • administration/management/logs.md • administration/management/FE_configuration.md References • administration/management/logs.md • administration/management/FE_configuration.md