Common challenges when scaling Trino workloads

The primary benefits of using Amazon S3 with Trino are S3's ability to scale for large data volumes and S3's cost effectiveness. But when you query large data volumes, a collection of related performance issues can happen on occasion. These can result from how data is stored, or by configuration settings that restrict good performance, or from other reasons. When these issues occur, there are effective steps you can take to avoid or mitigate them.

This section begins with a list of general optimizations you can implement to increase query performance on large data volumes. Following that, common issues are detailed and mitigations are provided for each one.

This topic is sourced from the following conference presentation: Accelerate performance at scale: Best practices for Trino with Amazon S3.

Optimizing data layout for large data sets

Performance bottlenecks aren't infrequent when you're querying large data sets. But there are best practices you can implement to give yourself a head start when you use Trino to query data in Amazon S3. These include the following:

Common challenges when you query large data sets

This section lists a collection of common issues that can occur when you accumulate a large data set in Amazon S3 and query it with Trino. Each section shows you ways to solve the issue or reduce its impact on queries. Each of the issues described in the following sections has been reproduced and tested, using a Hive connector.

Large data scans

When your query has to scan large data sets, it can lead to issues like slow query performance and higher storage cost. Large data volumes can result from fast data growth or planning that doesn't result in moving legacy data within an appropriate time frame. This can lead to slower queries.

To mitigate performance hits from scanning large data sets, we recommend that you utilize partitioning and bucketing:

To illustrate how mitigation can work for large data scans, assume you store and query data that has records with a state attribute, which can be assigned to California or Alaska, and this state attribute is one of your query conditions. You can improve query performance by storing data for each state in a separate S3 bucket, or partitioning your data based on the state, using an S3 prefix. This partitioning and bucketing can also cause performance improvement if you base it on an additional column, like a date attribute, for example.

Using various S3 storage types

Generally, you choose storage types based on performance, data access, resiliency, and cost requirements for your workloads. There can be trade offs between cost and performance. It's important to choose the appropriate Amazon S3 storage class that matches your data access patterns. There are two main access patterns:

Using compaction

You can also use Iceberg automatic compaction, if you use Iceberg tables, which results in more optimal file sizes, to increase query efficiency. For more information, see AWS Glue Data Catalog now supports automatic compaction of Apache Iceberg tables.

HTTP slow-down errors

This occurs when the request rate exceeds a pre-configured threshold on an Amazon S3 prefix. The HTTP error that occurs most commonly when this state is reached is the following: Error 503: Please reduce your request rate. The source for this issue can be rooted in the presence of a large number of small files, because of the number of splits that must be created in order to read the data. There are a couple ways to mitigate the issue:

For more information about how Trino determines the number of splits in a set of data to query, see Performance tuning configuration properties in the Hive connector documentation.

Difficulty querying small files

Querying many small files can result in heavy I/O overhead, because of a high number of GET and LIST requests, and subsequently affect query performance negatively. Optimizing file size can improve query performance. There are a few ways to do this:

Take note that Iceberg has a feature available to merge small files into larger files which is automatic compaction. It works with files managed with the AWS Glue Data Catalog. For more information, see AWS Glue Data Catalog now supports automatic compaction of Apache Iceberg tables.

Queries that include data that isn't needed

It's common for data to grow, which makes it imperative to track your data-access patterns and to move data appropriately as it ages or becomes irrelevant. This is because as data grows, query performance can degrade over time, mainly because of the sheer volume of data to scan when a query runs. Amazon S3 and other services offer guidance for data-lifecycle migration, which shows strategies for moving data to different storage locations as it becomes cold. There is also a storage cost benefit to doing this.

In addition to data migration, you can use other strategies like removing source data that isn't relevant to the queries you're running. This can take some work, as it might mean changing your source-data schema. But its positive result is to reduce data volume and result in faster queries. For more information, see Managing the lifecycle of objects.