Athena - Query exhausted resources at this scale factor

Hi there,

As you may already know Athena is a serverless technology which make use of shared resources. Athena uses Presto as a query engine which runs different stages of a query in memory. For a small number of queries and for certain operators, Presto brings all the data into a single nodes memory and may fail because it cannot spill pages to disk when memory is exhausted. This is not a resource-related issue, but more related to how specific operators cannot handle large amounts of data. This error is transient in nature, so if you submit the same query again, it might be successful. However, if you keep getting the same error consistently consider the following suggestions:

• If you are running your query in Athena1 consider running it on Athena2 which has a better performance than Athena1.
• Optimize the query as mentioned in Performance Tuning Best Practices for Athena.
• try using exponential backoff method and rerun the query.
• if the queries are being submitted at top of the hour, try to run the queries at a different time.
• You can also use the EMR cluster to run your query using Hive or Presto. This option will give you a flexibility to tune memory parameters as per your requirements.

I hope this helps.

If your data is in CSV format anot columnar format like Parquet or ORC, Athena may have to read the entire contents of the file and that can sometimes exhaust its shared resources for you during some queries.