Hello Good morning. Our servers intermittently stop responding and return 502 Gateway Timeout errors. The issue resolves temporarily after a restart, but it recurs once the system is under load. Given that our dataset is extremely large (around 300 billion records across all tables), my suspicion is that one or more expensive queries are causing the system to hang. Observations & Context: • I want to identify which queries are running for a long time or consuming excessive resources. • Running

EXPLAIN "query"

manually in the UI isn’t feasible since we have hundreds of queries. • After a restart, the servers function normally until the issue reappears. • My main doubt is that certain queries are significantly expensive and triggering these outages. Request: If anyone has experience or additional context on how to efficiently identify long-running or expensive queries (without manually running

EXPLAIN

for each one), please share your insights.

So you have grafana metrics setup? You can use that to correlate the time and look at queries in the log around that time

sure thanks

@Mayank is there a query log in Pinot? I’ve not been able to find one, is there something exposed via Grafana that exposes the logs?

Yes both broker and server log query related stats

@Mayank sorry, I meant a raw query log not just the metrics in grafana. I.e how do I find the actual sql query to correlate with performance issues in grafana

You need to do the following: • From grafana check when the degradation starts. • From broker log, you need to check when do the queries start taking longer, and inspect the queries. Check for ones that did lot of work (scanning, grouping, distinct/count, percentile, MSQE heavy joins) • It is not straight forward to pinpoint to one query automatically, because an expensive query can degrade other queries as well.

Sorry, even more dumb basic question, where do I find the broker query log? I literally have no clue where to see the raw sql text and a timestamp

Ah ok, in the broker pod, there's a directory for log