Query Logger

Overview

• Which queries are the slowest or the most expensive right now?
• Which query patterns (templates) dominate load?
• Which queries are scanning too much data, hitting limits, or erroring?
• How has a specific query’s latency or resource usage changed over time?

How it’s enabled

Browse query history in the Data Portal

What gets captured

• It took 1 second or longer (timeUsedMs >= 1000).
• It failed with an error.

• Queries against system_query_log itself, such as the ones you run while browsing query history. This stops the log from filling up with its own lookups.
• StarTree health checks and uptime probes. These are identified by the client that sends them, not by the SQL, so your own queries are never excluded by accident.
• Queries you opt out individually. Prepend SET skipQueryLog=true; to a statement you don’t want recorded, such as a high-frequency health check of your own:
  SET skipQueryLog=true;
  SELECT * FROM myTable LIMIT 1;
  
  This only affects logging. The query still runs and returns results normally.

Configuration

curl -X POST \
  'http://localhost:9000/cluster/configs' \
  -H 'Content-Type: application/json' \
  -d '{
    "querylog.capture.threshold.latency.ms": "500"
  }'

curl -X DELETE 'http://localhost:9000/cluster/configs/querylog.capture.threshold.latency.ms'

Capture settings

Table settings

The data: key fields

* queryHash is populated only when query fingerprinting is enabled for your cluster; otherwise it may be empty. Ask StarTree Ops if you plan to rely on it.

Sample queries

All examples filter to the last 24 hours using logTimestampMs. Adjust the window as needed. Always keep a time filter on these queries (see Limitations & caveats).

SELECT logTimestampMs, tableName, timeUsedMs, numDocsScanned, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
ORDER BY timeUsedMs DESC
LIMIT 10

SELECT logTimestampMs, tableName, timeUsedMs,
       offlineTotalCpuTimeNs + realtimeTotalCpuTimeNs AS totalCpuNs, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
ORDER BY totalCpuNs DESC
LIMIT 10

SELECT queryHash,
       COUNT(*) AS executions,
       AVG(timeUsedMs) AS avgLatencyMs,
       MAX(timeUsedMs) AS maxLatencyMs,
       AVG(numEntriesScannedInFilter) AS avgFilterScan,
       ANY_VALUE(query) AS sampleQuery
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
GROUP BY queryHash
ORDER BY avgLatencyMs DESC
LIMIT 20

SELECT logTimestampMs, tableName, numEntriesScannedInFilter, numDocsScanned, timeUsedMs, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
  AND numEntriesScannedInFilter > 1000000
ORDER BY numEntriesScannedInFilter DESC
LIMIT 25

SELECT logTimestampMs, tableName, numExceptions, exceptions, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
  AND numExceptions > 0
ORDER BY logTimestampMs DESC
LIMIT 50

SELECT logTimestampMs, tableName, numGroupsLimitReached, groupsTrimmed,
       maxRowsInJoinReached, timeUsedMs, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
  AND (numGroupsLimitReached = true OR groupsTrimmed = true OR maxRowsInJoinReached = true)
ORDER BY logTimestampMs DESC
LIMIT 50

SELECT tableName, COUNT(*) AS queries,
       AVG(timeUsedMs) AS avgMs, PERCENTILETDIGEST(timeUsedMs, 99) AS p99Ms
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
GROUP BY tableName
ORDER BY queries DESC

How the data is kept tidy

• Automatic segment compaction. The pipeline lands data as many small segments (one every few minutes per broker). A background merge/rollup job runs on a schedule and compacts those small segments into fewer, larger ones, keeping the total segment count bounded as the table grows. No action is required on your part.
• Automatic retention. Data older than the configured retention window is dropped automatically, so the table stays within its storage budget.

Limitations & caveats

⚠️ It consumes storage on your cluster. system_query_log is a real Pinot table — its segments occupy disk on your servers. StarTree keeps it bounded with an automatic retention window (older data is dropped, 7 days by default) and a storage quota (300 GiB by default), and the background merge job (above) keeps the segment count in check, so the footprint stays finite. When the table reaches its quota, newly captured queries stop landing until retention frees space; existing history is not deleted early. High-QPS clusters, or clusters that capture every query, generate more log data; see Table settings and talk to StarTree Support if you need a longer window or expect large volume.

⚠️ Your analysis queries run on the same cluster. Queries against system_query_log execute on the same brokers and servers serving your production traffic and compete for the same resources. Always filter by logTimestampMs to a bounded window and avoid unbounded SELECT * scans, so debugging doesn’t add load to an already-busy cluster.

• Near-real-time, not instant. Logged queries appear after background segment generation runs — expect a short delay (roughly 10–15 minutes) before a just-executed query is queryable. It’s for analysis and debugging, not live monitoring.
• Long fields are truncated. query (4096 characters by default, see querylog.max.query.length), exceptions, serverStats, and traceInfo have maximum lengths; extremely large values are cut off.
• One row per captured query. By default only slow and failed queries are stored (see What gets captured), so counts from this table are not total query volume unless capture is set to every query.
• Best-effort capture. Under extreme broker load, or during an error storm where many queries qualify at once, some records may be dropped rather than slow down query serving.