@Rocky All my logs have been configured to go to the system journal instead of individual log files. Does this mean the query audit plugin that takes data from fe.audit.log will not work? I want to profile all historical queries

The AuditLoader plugin will still work even if your logs are redirected to the system journal. While some documentation states that AuditLoader "reads from the file," this is a simplification of how the modern StarRocks plugin architecture works. Here is the technical breakdown: 1. In-Memory Event Capture StarRocks uses a plugin-based audit system. When a query completes, the Frontend (FE) generates an internal

AuditEvent

object. This object is simultaneously passed to all active audit plugins: *

__builtin_AuditLogBuilder

: This built-in plugin is responsible for writing the data to the physical

fe.audit.log

file. *

AuditLoader

: When you install this plugin, it receives the exact same

AuditEvent

objects directly in memory from the FE. It does not need to tail the file on disk. 2. How it Handles Data Once AuditLoader receives the events in memory: * It batches them in its own internal buffer. * It periodically triggers a Stream Load (using HTTP PUT) to send that batch into the StarRocks table you configured (e.g.,

starrocks_audit_tbl__

). * This process is independent of the Log4j2 configuration or the system journal. 3. Important Considerations * Historical Data: The plugin can only capture and load queries that occur after the plugin is installed and started. It cannot "reach back" into your system journal to find queries from the past. * Profiling Existing Logs: If you want to profile queries that have already happened and are currently only in your system journal, you will need to manually export them (e.g., using

journalctl

) and use a standard Stream Load to import them into your audit table. * Configuration: Ensure that

enable_audit_sql

is set to

true

(default) in your FE configuration so that the SQL text is actually included in the events. Summary: Redirecting file logs to the system journal (stdout) does not break the AuditLoader plugin because it consumes data via the internal FE plugin API, not by reading the log files. References • administration/management/audit_loader.md • knowledge/usage/best-practice.md

@Rocky Since I am hosting the FE node on an ec2 instance, do I scp the auditloader.zip to the instance or just curl download from within?

You can use either method to get the file onto the EC2 instance, but there is a critical step in between: you must modify the configuration inside the zip file before you install it. Because you need to edit

plugin.conf

with your specific database credentials, the process usually looks like this: Recommended Workflow 1. Download and Edit: You can

curl

the zip directly on your EC2 instance or download it locally.

bash
    curl -O <https://releases.starrocks.io/resources/auditloader.zip>

2. Modify Configuration: * Unzip the package:

unzip auditloader.zip

* Edit `plugin.conf`: Set your

user

,

password

, and

frontend_host_port

(usually

127.0.0.1:8030

). * Re-zip the files:

zip -q -m -r auditloader.zip auditloader.jar plugin.conf plugin.properties

3. Distribute to ALL FEs: * If you have multiple FE nodes, you must

scp

this modified

auditloader.zip

to the exact same absolute path on every FE instance. * Note: Do not delete this file after installation; StarRocks needs it to remain at that path. Installation Command Once the file is on your FE node(s), run the installation command from your SQL client (e.g., MySQL shell):

sql
-- Use the absolute path where you placed the file on the EC2 instance
INSTALL PLUGIN FROM "/home/ec2-user/auditloader.zip";

Alternative: Internal HTTP Server If you have many FEs and don't want to

scp

to each one, you can host your modified

auditloader.zip

on an internal S3 bucket (with a signed URL) or an internal Nginx server and install via URL:

sql
INSTALL PLUGIN FROM "<http://internal-server/auditloader.zip>" 
PROPERTIES("md5sum" = "your_file_md5_here");

Summary:

scp

is usually the simplest method for EC2 once you have modified the

plugin.conf

locally or on one of the instances. Just ensure the path is identical across all FE nodes. References • administration/management/audit_loader.md References • administration/management/audit_loader.md • knowledge/usage/best-practice.md

@Rocky SHould I be using the root user and password?

While you can use the

root

user, it is generally not recommended for security best practices. Since the

plugin.conf

file stores credentials on the file system, using

root

gives the plugin (and anyone with access to the FE nodes) full administrative control over your cluster. The better approach is to create a dedicated user with "least privilege" access. Recommended Steps 1. Create a dedicated user: Create a user specifically for auditing (e.g.,

audit_admin

).

sql
    CREATE USER 'audit_admin'@'%' IDENTIFIED BY 'your_password';

2. Grant only the necessary permissions: The plugin only needs to be able to perform Stream Loads into the audit table.

sql
    -- Grant INSERT privilege on the audit table
    GRANT INSERT ON TABLE starrocks_audit_db*.starrocks_audit_tbl* TO USER 'audit_admin'@'%';

3. Use these credentials in `plugin.conf`: Update your

plugin.conf

with the new user:

conf
    user=audit_admin
    password=your_password

Extra Security: Password Encryption If you are worried about storing the password in plaintext in

plugin.conf

, StarRocks allows you to encrypt it: 1. Choose a secret key (up to 16 bytes). 2. Encrypt the password in StarRocks:

sql
    SELECT TO_BASE64(AES_ENCRYPT('your_password', 'your_secret_key'));

3. In

plugin.conf

, set: *

password

= the base64 string from step 2. *

secret_key

= your secret key. Summary: Avoid using

root

. Create a limited user with only

INSERT

permissions on the audit table to keep your cluster secure. References • administration/management/audit_loader.md • administration/user_privs/authorization/user_privs.md References • administration/management/audit_loader.md • administration/user_privs/authorization/ranger_plugin.md • knowledge/usage/best-practice.md • administration/user_privs/authorization/user_privs.md

@Rocky CPU time consumed by the query (nanoseconds) (planCpuCosts). This isnt actually query time right? It is the sum of cpu time across all vCPUs? SO if I have 12 nodes and 32vCPU per node, per CPU time is planCpuCosts/12/32?

It sounds like you are looking at the cumulative CPU time metric (often labeled as

CpuCostNs

or

cpucostns

in the audit logs). To answer your questions: 1. Is this the "Query Time"? No. This is not the wall-clock time (latency) that you see in your SQL client. * Query Time (Latency): The wall-clock duration from the start to the end of the query. * CPU Time (

cpuCostNs

): The actual sum of time the CPU cores were actively working on your query. If a query runs for 1 second of wall-clock time but utilizes 8 cores fully for that entire second, the

cpuCostNs

will be approximately 8 seconds (8,000,000,000 ns). 2. Is it the sum across all vCPUs? Yes. It is the total CPU time consumed across all threads on all Backend (BE) nodes involved in the query. StarRocks is a massively parallel processing (MPP) engine, so a single query typically runs many "pipeline drivers" (threads) simultaneously across your 12 nodes. 3. Is "Per CPU time" =

planCpuCosts / 12 / 32

? Technically, your formula calculates the average CPU usage per core across your entire cluster for that query. While this is a valid way to look at "resource density," it isn't a standard performance metric. Here is how to better interpret that number: * Total Resource Footprint:

cpuCostNs / 1e9

gives you the total CPU-seconds. If this number is very high, the query is "heavy" regardless of how fast it finished. * Parallelism Degree: If you divide

cpuCostNs

by the

QueryTime

(converted to nanoseconds), you get the average number of cores used during the execution. _ _Example:* If

cpuCostNs

is 10s and

QueryTime

is 1s, the query used 10 cores on average. * Cluster Capacity: Your calculation (

cpuCostNs / (12 _ 32 _ 1e9)

) tells you what percentage of your total cluster capacity was "occupied" by that query for its duration. A Note on "Plan Costs" vs. "CPU Costs" Be careful with the naming: *

cpuCostNs

(Actual): Found in

fe.audit.log

. This is the actual measured time spent by CPUs. *

plan_cpu_costs

(Estimated): This is a unitless value generated by the Optimizer (Planner) before the query even starts. It is used for the Query Queue (resource management) to guess how heavy a query might be. If you are profiling historical queries to see what actually happened, you should ignore "plan costs" and look specifically at

cpuCostNs

. Summary: Your understanding is correct—it is a cumulative sum. Using

cpuCostNs

is the best way to identify "CPU-hog" queries that might be efficient in wall-clock time (due to high parallelism) but are actually draining your cluster resources. References • administration/management/logs.md • best_practices/audit_log_resource_group.md • best_practices/query_tuning/query_profile_operator_metrics.md References • best_practices/query_tuning/query_profile_operator_metrics.md • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • best_practices/query_tuning/query_planning.md • sql-reference/sql-statements/cluster-management/plan_profile/EXPLAIN.md

@Rocky Once I install the plugin from the zip file, I can delete the zip file right?

No, you should not delete the zip file. According to the StarRocks documentation, the

auditloader.zip

package must remain at the specified path even after the installation is complete. Why you must keep it: * Source Reference: When you run

INSTALL PLUGIN FROM "/path/to/auditloader.zip"

, StarRocks points to that specific file as the source. If you run

SHOW PLUGINS

, you will see the

Sources

column still points to that absolute path. * FE Restarts: If an FE (Frontend) node restarts or if you add new FE nodes to the cluster, they may need to reference that zip file to reload the plugin. * Documentation Warning: The official guide specifically notes: "the auditloader.zip needs to be persisted in the path after the installation is performed, and the source files should not be deleted after installation." Recommendation: Place the zip file in a permanent directory (e.g., within the StarRocks installation folder or a dedicated plugins directory) on all FE nodes at the exact same absolute path before running the

INSTALL

command. If you ever need to change the configuration (like the password you just set up), you will need to

UNINSTALL

the plugin, update the zip file, and

INSTALL

it again. References • administration/management/audit_loader.md • sql-reference/sql-statements/cluster-management/plugin/INSTALL_PLUGIN.md References • administration/management/audit_loader.md • sql-reference/sql-statements/cluster-management/plugin/INSTALL_PLUGIN.md

@Rocky What do I need to do if I want to edit the configs? Use different user and password for the audit loader?

To edit the configurations for the AuditLoader (such as changing the username, password, or target database), you must follow a cycle of uninstalling and reinstalling. There is currently no

ALTER PLUGIN

command to update these settings in-place. Here is the step-by-step process: 1. Uninstall the current plugin You need to remove the active plugin first so you can replace it with the new configuration.

sql
UNINSTALL PLUGIN AuditLoader;

2. Update the configuration file 1. Locate your

auditloader.zip

file on your machine. 2. Unzip it to access the internal files. 3. Edit

plugin.conf

: Change the

user

,

password

, or any other parameters (like

database

or

table

) as needed. 4. Re-zip the files: Bundle

auditloader.jar

,

plugin.conf

, and

plugin.properties

back into the zip package.

bash
    zip -q -m -r auditloader.zip auditloader.jar plugin.conf plugin.properties

3. Sync the file across FE nodes If you have multiple Frontend (FE) nodes, ensure the updated

auditloader.zip

is copied to the exact same absolute path on every FE node. If the paths differ, the installation will fail on some nodes. 4. Reinstall the plugin Run the install command using the absolute path to your updated zip file:

sql
INSTALL PLUGIN FROM "/your/absolute/path/auditloader.zip";

5. Verify the change Check if the plugin is active and running:

sql
SHOW PLUGINS;

If you don't see logs appearing in your audit table after a few minutes, check your

fe/log/fe.log

file. Search for the keyword

audit

to find any error messages related to the AuditLoader (such as authentication failures or "Access Denied" errors if the new user lacks permissions). Summary Checklist for the New User Since you are switching users, ensure the new user has: *

USAGE

on the cluster. *

INSERT

and

SELECT

privileges on the

starrocks_audit_db*.starrocks_audit_tbl*

table. References • administration/management/audit_loader.md • sql-reference/sql-statements/cluster-management/plugin/INSTALL_PLUGIN.md • sql-reference/sql-statements/cluster-management/plugin/UNINSTALL_PLUGIN.md References • administration/management/audit_loader.md • administration/management/FE_configuration.md

@Rocky How can I go about the password issue? I dont want to store the password in a zipped file within the ec2 instance. It is not proper procedure.

You are correct that storing a plaintext password in a file on the filesystem is not best practice. However, because the AuditLoader is a Java-based plugin that runs as a background thread within the Frontend (FE), it must have access to credentials to perform the internal Stream Load (via HTTP PUT) to the audit table. Since StarRocks does not currently support dynamic secret fetching (like a direct Vault or AWS Secrets Manager integration) for its plugins, you can use these two strategies to secure your setup: 1. Use the Built-in AES Encryption (Middle Ground) StarRocks allows you to store an encrypted version of the password in the

plugin.conf

. This prevents anyone who merely looks at the file from seeing your actual password. How to set it up: 1. Choose a

secret_key

(up to 16 bytes). 2. In StarRocks, generate the encrypted string for your

audit_admin

user:

sql
    -- Replace 'your_actual_password' and 'your_secret_key'
    SELECT TO_BASE64(AES_ENCRYPT('your_actual_password', 'your_secret_key'));

3. Update your

plugin.conf

with the output:

ini
    user=audit_admin
    # Put the base64 output here
    password=EncryptedStringFromAbove==
    # Put your 16-byte key here
    secret_key=your_secret_key

While the key is still in the file, this satisfies most automated security scanners and prevents casual "shoulder surfing." ────────── 2. Deployment-Time Injection (Proper Procedure) Since you are already using a configuration tool that connects to Vault (based on your HCL snippet), the standard "proper procedure" is to generate the zip file dynamically during your deployment pipeline rather than storing it in a repository. The Workflow: 1. Templates: Keep

auditloader.jar

,

plugin.properties

, and a template

plugin.conf.tmpl

in your deployment source. 2. Fetch & Replace: During your deployment (using Terraform, Ansible, or a custom script), fetch the

audit_admin

password from Vault. 3. Zip on the Fly: Inject the password into the

.conf

file, zip the three files into

auditloader.zip

, and copy it to your FE nodes. 4. Install: Run the

INSTALL PLUGIN

command. This ensures the password only exists on the EC2 instance's disk within the StarRocks directory, never in your source code or CI/CD logs. 3. Minimize the Blast Radius Because you have already scoped the

audit_admin

user to only have

INSERT

and

SELECT

on the specific

starrocks_audit_db__

database, even if that password were compromised from the EC2 instance, the attacker would have: * No access to your business data. * No ability to drop tables or change configurations. * No ability to access other databases in the cluster. Summary There is no way to avoid the password being "somewhere" on the node because the plugin acts as a client. The most secure path is a combination of Strategy 1 (Encryption) to hide the string from the filesystem and Strategy 2 (Provisioning) to ensure the secret is only injected at the moment of deployment. References • administration/management/audit_loader.md • administration/user_privs/authorization/user_privs.md References • administration/management/audit_loader.md • administration/management/FE_configuration.md