Tuning Trino for Interactive-Scale Data
Shopify's data scientists rely on Trino for quick access to a data lake that now absorbs 15 Gbps and over 300 million rows per second. Keeping query latency under five seconds at that scale required more than just adding hardware. It demanded a systematic look at how the query engine was configured, how queries were routed, and where CPU time was actually going.
Trino itself is a distributed SQL engine designed to query large datasets across heterogeneous sources. It is not a database—it has no storage component—but it excels at joining and aggregating data from many systems via standard SQL. Shopify runs two primary clusters: a Scheduled cluster for reports from interactive analytics apps on fixed schedules, and an Adhoc cluster for on-demand queries, including those from the experiments (A/B testing) platform. A fork of Lyft's Trino Gateway routes queries to the appropriate cluster by inspecting HTTP headers, and all clusters run on Kubernetes (GKE) for easy scaling and blue-green deployments.
The performance target was ambitious: P95 query latency under five seconds, down from a baseline where roughly five percent of queries ran between one and five minutes. To diagnose the bottlenecks, the team analyzed query volumes, the most frequently queried datasets, CPU wall-time consumption, resource-heavy datasets, and failure scenarios.
Root Causes of Slowdowns
The initial error analysis revealed a resource-related error rate of about 0.35 percent—acceptable given the load profile—but pinpointing the queries that timed out or degraded cluster performance was harder. The team built a Trino Query Replicator to recreate past load conditions, which allowed deeper inspection of error classes. That analysis narrowed the problems down to five areas:
- Storage type: compressed JSON messages from Kafka were particularly slow to process.
- Cluster classes: the adhoc cluster was being used for everything, not just scheduled or truly ad hoc work.
- CPU and memory allocation: both coordinator and workers needed to scale with query volume and data size.
- JVM settings: virtual machine options needed tuning.
- Dataset statistics: better statistics would enable Trino's cost-based query optimization.
Scaling up hardware first—doubling worker pods to 61 cores and 220GB memory and adding more workers—did not yield stable results. So the team turned to the query logs, stack traces, the Trino codebase, and direct consultation with Trino's creators. Four actionable changes emerged.
Separating Workloads
The original two-cluster setup created friction: experiment queries added unpredictable load to the adhoc cluster, making user query times inconsistent, while user queries disrupted experiment runtimes. The fix was a dedicated cluster just for experiments queries, with Trino Gateway routing based on a specific HTTP header. This separated the two workloads cleanly and improved predictability for both groups of users.
Shopify also built tooling that lets users spin up ephemeral clusters for temporary heavy-duty processing or investigations with a single command. An Airflow job tears these down automatically after a defined TTL.
Reducing Coordinators Lock Contention
When conventional scaling wasn't enough, the team examined what happened when the cluster overloaded. Datadog metrics showed queued work mounting while no queries or splits were dispatched. The coordinator was running but stopped emitting metrics for minutes at a time. CPU load on the coordinator looked normal, so the team captured stack traces and found the real issue: lock contention on the Internal Resource Group object, driven by all the active queries and tasks.
The solution was to set hardConcurrencyLimit to 60 in the root resource group. This caps the number of running parallel queries, directly reducing contention on the coordinator. It's a balancing act: allow enough queries to fully utilize the cluster, but cap the count to prevent coordinator lock contention.
JVM Recompilation Settings
With lock contention reduced, cluster throughput was still lower than expected. Datadog showed one worker pegged at 100% CPU while most others were idle. Profiling with jvisualvm while the issue occurred revealed that almost all CPU time went to one of two things:
- GCM AES decryption of data coming from GCS.
- JSON deserialization of that data.
These workers were processing the same datasets as everyone else, so the CPU discrepancy was puzzling. After trial and error, the team found that specific JVM options prevented the situation. These options were later incorporated into the recommended JVM settings in a subsequent Trino release, and the discussion is documented in the Trino GitHub repository. The root cause was a condition where the JVM stopped compiling certain methods, forcing them to run in the interpreter—dramatically slower than compiled code.
After applying the JVM settings, worker CPU usage aligned nicely with no single worker hitting the 100% long tail.
Capping Splits Per Stage Per Worker
While investigating query performance, the team came across a query in the Trino Web UI that revealed a serious problem. That single query had approximately 29,000 running splits, despite the cluster having only 18,000 available worker threads. Datadog graphs showed a maximum of 18,000 concurrent running splits, so the Web UI count was likely an artifact. Still, testing showed that a single query could monopolize the entire cluster, starving all other queries.
Searching Slack and forum archives turned up an undocumented configuration option: task.max-drivers-per-task. This setting caps the maximum number of splits that can be scheduled per stage, per query, per worker. Setting it to 16 limited the problematic query to roughly 7,200 active splits, freeing the cluster to handle other work concurrently.
The Outcome and Roadmap
Even without optimizing storage, Shopify reduced interactive query execution latency to 30 seconds through a combination of cluster node sizing, cluster class changes, Trino configuration updates, and JVM tuning. The charts below show how these changes affected query distribution and execution times.
Query latency shifted across the distribution: more queries landed in the zero-to-five-second bucket, and, critically, the heaviest queries no longer ran for extended periods. As of this post, the P95 query execution time is below 30 seconds. This was achieved by separating clusters to lower the number of concurrent queries, applying the recommended JVM recompilation settings, and capping the maximum number of drivers per query task.
Infrastructure work alone was not the end of the story. Shopify still sees room for improvement as it pushes to make Trino its primary interactive query engine. The team has identified several future initiatives:
- Transitioning storage for better performance (JSON to Parquet).
- Implementing an Alluxio cache layer.
- Building load profiling tooling.
- Improving statistics so the Trino query optimizer can select better execution strategies.
- Enhancing Shopify Trino Conductor with improved UI and infrastructure, plus introducing weighted query routing.
