trino-memory-and-spill-tuning

What problem does it solve?

This skill helps you diagnose and fix Trino queries that fail due to memory limits or JVM OutOfMemoryError by tuning both Trino memory management and spill-to-disk behavior.

Core Features & Use Cases

• Memory limit sizing for nodes and queries: Set query.max-memory, query.max-total-memory, query.max-memory-per-node, and memory.heap-headroom-per-node so Trino’s reserved memory fits within JVM -Xmx.
• JVM heap and OOM diagnostics: Configure JVM heap sizing (including an 80% RAM approach), enable fast crash on OOM, and capture heap dumps for investigation.
• Spill-to-disk and exchange buffering: Enable spill with proper paths, thresholds, and compression, plus tune exchange/sink buffers to reduce memory pressure during shuffles.
• Fault-tolerant execution strategy (TASK retry mode): Use exchange manager backends (filesystem/S3-style) for long-running batch workloads that may fail mid-execution.
• Operator-specific fixes: Apply targeted remedies for memory-intensive patterns like HashJoin build-side blowups, global Sort/OrderBy, high-cardinality Aggregations, Window buffering, and DISTINCT/COUNT DISTINCT behavior.
• Use case: During a data engineering ETL run, a large JOIN+aggregation query intermittently crashes workers; apply memory sizing + spill tuning to prevent OOM while keeping performance stable.

Quick Start

Tell the agent to “Diagnose my Trino query OOMs and propose exact values for query.max-memory/query.max-total-memory/query.max-memory-per-node, memory.heap-headroom-per-node, and spill-to-disk settings for a 64GB worker with dedicated SSDs.”