TechPrufer
Get started
Shopify
Shopify logoShopify · Feature trail

Trino Query Execution Performance Tooling

1 post · Jul 2021 – Nov 2022

What this trail covers

Shopify has evolved its approach to managing Trino query execution performance by developing custom verification, benchmarking, and profiling tooling. This framework, built as a lightweight Python library, standardizes testing procedures, extracts undocumented knowledge into code, and minimizes the risk of changes at scale. It supports use cases like replaying past queries, replicating real-time traffic, and utilizing standardized benchmarking frameworks like TPC-DS. The tooling integrates with Datadog for metrics extraction and analysis. The team also addressed issues with JVM settings, CPU and memory allocation, and cluster classes by creating workload-specific clusters and implementing lock contention limits.

All Shopify trails
  1. Reducing BigQuery Costs: How We Fixed A $1 Million Query - ShopifyThis post details the investigation and resolution of a BigQuery query that was projected to cost nearly $1 million USD per month. The primary solution involved implementing table clustering on two feature columns identified in the query's WHERE clause, which reduced the billed data scanned from 75 GB per query to 108.3 MB per query, a reduction of over 150x. Additional tips for reducing BigQuery costs include avoiding SELECT*, partitioning tables, and using table preview options instead of running queries for exploration.
  2. Shopify's Path to a Faster Trino Query Execution: Custom Verification, Benchmarking, and Profiling Tooling - ShopifyThis post details the development of a custom Python-based framework for verifying, benchmarking, and profiling Trino query execution. The framework includes a core library for Trino query orchestration and interfaces for PyTest. For verification, it enables running queries on candidate clusters and comparing results against control clusters, with marks for different test types (correctness, performance). For benchmarking, it utilizes TPC-DS queries across various scale factors to evaluate performance under standardized conditions. The tooling aims to reduce toil by formalizing undocumented upgrade checklists and providing a structured approach to testing changes in a complex, forked Trino environment.
  3. Shopify's Path to a Faster Trino Query Execution: Infrastructure - ShopifyThis post details the challenges and solutions for improving Trino query execution performance at Shopify, focusing on issues related to JVM settings, CPU and memory allocation, and cluster classes. It describes the creation of workload-specific clusters (scheduled, ad-hoc, and experimental) and the implementation of a `hardConcurrencyLimit` of 60 on the root resource group to reduce coordinator lock contention. The post also highlights the importance of analyzing query execution logs, stack traces, and Datadog metrics to identify and resolve performance bottlenecks, including the use of a Trino Query Replicator to recreate past performance degradation scenarios.