skills.rest
ivanshamaev
ivanshamaevCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

trino-memory-and-spill-tuning

Tune Trino memory properties and JVM heap settings to resolve OOM query failures.

14|1|Updated May 5, 2026
Search Tags:#query optimization#trino#memory tuning#spill to disk#jvm heap#oom diagnostics#fault tolerant execution
One-click install
npx skills add https://github.com/ivanshamaev/de-agent-skills --skill trino-memory-and-spill-tuning
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: trino-memory-and-spill-tuning
Source: https://github.com/ivanshamaev/de-agent-skills/tree/main/group_skills/trino_group_skills/trino_memory_and_spill_tuning
Command: npx skills add https://github.com/ivanshamaev/de-agent-skills --skill trino-memory-and-spill-tuning

SYSTEM DOCUMENTATION & REQUIREMENTS

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.”

Frequently Asked Questions about trino-memory-and-spill-tuning

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
Why does my Trino query fail with OutOfMemoryError during a large hash join?▼

Trino query OOMs during large hash joins occur when worker JVM memory limits are exceeded. Tuning query.max-memory-per-node alongside JVM -Xmx and memory.heap-headroom-per-node resolves these failures by constraining memory allocation properly.

How do I configure Trino memory limits and JVM heap for a 64GB worker?▼

Configuring Trino memory limits involves setting query.max-memory, query.max-total-memory, and memory.heap-headroom-per-node to fit within the JVM -Xmx heap allocation. An 80% RAM approach for JVM heap sizing on a 64GB worker ensures sufficient reserved memory for Trino operations.

When should I enable spill to disk for Trino ETL batch queries?▼

Spill to disk should be enabled for Trino ETL batch queries when memory-intensive operators like global sorts, window functions, or high-cardinality aggregations cause OOMs. Configuring spill paths, thresholds, and compression reduces memory pressure during shuffles.

Does Trino fault-tolerant execution with TASK retry mode prevent worker crashes?▼

Trino fault-tolerant execution with TASK retry mode prevents mid-execution failures in long-running batch workloads. Using exchange manager backends like filesystem or S3-style storage allows queries to recover from worker crashes without restarting completely.

What is the best way to diagnose Trino worker OOM and capture heap dumps?▼

Diagnosing Trino worker OOM involves enabling fast crash on OutOfMemoryError and capturing JVM heap dumps for investigation. Reviewing memory-intensive operator patterns like HashJoin build-side blowups identifies the root cause of memory limit exceedance.

Can I tune Trino exchange and sink buffers to reduce memory pressure?▼

Tuning Trino exchange and sink buffers reduces memory pressure during shuffles by limiting buffer sizes. Combined with spill-to-disk configuration and targeted fixes for global OrderBy or DISTINCT operations, this prevents workers from exceeding memory limits.

Related Skills

Explore More Data & Analytics Skills→
ivanshamaevivanshamaev
14

starrocks-memory-tuning

Tune StarRocks memory limits, spill settings, and pool configurations to resolve query OOM and BE memory pressure.

Community
Advanced
ivanshamaevivanshamaev
14

trino-production-readiness-review

Audit Trino cluster configurations against a production readiness checklist.

Community
Advanced
sandeshbagmaresandeshbagmare

python-resilience

Add retry logic, exponential backoff, and timeouts to Python applications.

Community
Intermediate
unoplatformunoplatform
9

uno-toolkit-resource-extensions

Attach a ResourceDictionary to a single control for per-control styling overrides.

Official
Basic