elasticsearch error: slowlog threshold exceeded - how to find the slow queries

elasticsearch error: slowlog threshold exceeded - how to find the slow queries

TL;DR

The slowlog only logs what exceeds the thresholds you set, and the defaults log almost nothing. Set index.search.slowlog.threshold.query.warn to a real value, reproduce the slowness, then read the slowlog for the query body and took time. The query DSL in the log is your EXPLAIN.

elasticsearch: slowlog threshold exceeded (query took longer than threshold)

Steps

1. Set a threshold that captures the problem: PUT [index]/_settings with index.search.slowlog.threshold.query.warn: 5s (start loose, tighten later). Expected: The setting is acknowledged.

2. Reproduce the slow period or wait for it; the slowlog writes to the dedicated slowlog file, not the main log. Expected: Entries appear with took times and the full query body.

3. Read the entries: each has the index, shard, took millis, and the query DSL. Sort by took to find the worst offenders. Expected: You have the exact slow queries.

4. Profile the worst one with the Profile API to see which clause costs the time (usually a wildcard, a deep aggregation, or a script). Expected: The profile names the expensive clause.

5. Fix the clause: rewrite wildcards, add the right fielddata or keyword mapping, or precompute the aggregation. Then tighten the threshold to catch the next tier. Expected: The slowlog goes quiet at the new threshold.

When to use

• the slowlog threshold exceeded warning appears
• you need the actual slow query bodies
• search latency regressed and you need per-query data

When not to use

• no slowlog entries exist (the threshold never fired; lower it)
• the slowness is indexing, not search (use the indexing slowlog instead)
• the whole cluster is slow (check node stats and GC before blaming queries)

Tool compatibility

• Elasticsearch 7.x/8.x
• index-level slowlog settings

Variant phrasings

• "elasticsearch slowlog threshold exceeded"
• "find slow queries elasticsearch slowlog"
• "elasticsearch search slowlog enable"

Why it happens

Elasticsearch does not log slow queries by default at useful thresholds; the defaults are set so high that only disasters log. The slowlog is opt-in evidence: you set the threshold, the cluster records what crosses it with full query bodies, and that becomes the basis for optimization.

Edge cases

• the slowlog logs the rewritten query, which can differ from what the client sent; both are useful
• thresholds are per index; a template-level setting is needed for time-series indices
• very low thresholds on a busy cluster generate enormous log volume; tighten gradually

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Zmhd5zg3qYYPRw-pDluxIw