I've been conducting a deep-dive performance analysis of our Sumo Logic implementation for the past quarter, specifically focusing on query execution times across our multi-terabyte daily ingest. I'm hitting a consistent and critical roadblock: complex analytical queries on large datasets (e.g., multi-day JOINs across logs, metrics, and traces for a root-cause analysis) are timing out well before completion. The `Search job was canceled due to timeout` message is becoming a daily frustration.
Our typical offending query pattern involves aggregations and transformations over 24-72 hour windows. We're not just doing simple `count by`. An example of the structure that frequently fails:
```sql
_sourceCategory=app/prod*
| parse "trace_id=*," as trace_id
| join (dataset=metrics _metric=app_latency_ms) on trace_id
| timeslice 1h
| max(_value) as max_latency, pct(_value, 95) as p95_latency by _timeslice, service_name
| fields _timeslice, service_name, max_latency, p95_latency
```
The query planner seems to choke once the intermediate result set exceeds a certain, undocumented, in-memory threshold. I've attempted the standard mitigations:
* Increasing the `receiptTimeout` in the Search API calls.
* Aggressively filtering data early with explicit `where` clauses.
* Breaking the query into chained, smaller search jobs via the API.
The chained job approach is operationally burdensome and negates the value of an interactive analytics platform. My hypothesis is that we're encountering a fundamental limitation of Sumo's query execution engine's memory management or a partition pruning inefficiency.
I want to gather concrete data points from the community before engaging with support, as their responses often lean towards generic "optimize your query" advice. My specific questions are:
* At what approximate daily ingest volume (in GB/day) did you begin to encounter persistent timeout issues for analytical (not monitoring) queries?
* Have you identified any specific query operators (`join`, `transpose`, `lookup`) that are disproportionately expensive in Sumo's implementation compared to other platforms?
* What, if any, workarounds have proven sustainably effective? Are we forced to move to a summarized/rollup data model for all historical analysis?
* Is this a known scaling limit of the "Classic" query mode, and is the "New Search Experience" (NSE) architected to handle these large working sets more gracefully?
The business implication is that our ability to perform historical trend analysis and retrospective security investigations is becoming severely constrained. I'm interested in architectural insights, not just query syntax tips.
You've hit the classic wall where a managed service's query planner meets actual complexity. Increasing the receiptTimeout is like trying to fix a burst pipe with more water pressure.
The real culprit in your example is that JOIN, especially across datasets. Sumo's join is a memory-bound, broadcast join under the hood. When you're dealing with multi-day spans and a high cardinality key like `trace_id`, you're asking it to build a hash table in memory that can easily blow past their undocumented per-node limits. The planner doesn't "choke," it just hits a hard, pre-configured memory ceiling and dies.
Forget the standard mitigations. You need to avoid that pattern entirely. Pre-aggregate your metrics into a timeslice before the join, or better yet, move this kind of correlated analysis out of the interactive query layer. I batch-process these trace-to-metric joins into a separate rollup index using a scheduled search, then query the much smaller result. It's an extra hop, but it's the only way to make it reliable on their infrastructure without begging for a custom capacity increase.
You've zeroed in on the core issue with that JOIN pattern. The memory-bound broadcast join explanation is correct.
A practical step you can take immediately is to break your analysis into sequential, smaller queries that materialize intermediate results. Instead of trying to join raw metrics against raw logs across days in one go, run a query to pre-aggregate your metrics data by `trace_id` and `_timeslice` first and save it to a scheduled view or a more compact lookup. Then your main query joins against that summarized dataset. This drastically reduces the cardinality and memory pressure.
Have you looked at the relative data volumes on each side of your join? If the metrics side is significantly smaller, you might get further with the join, but for the scale you're describing, a decoupled approach is often the only path forward.