Query exhausted resources at this scale factor

Question

I was running SQL query on Amazon Athena. And I got the following error couple of times:

Query exhausted resources at this scale factor

This query ran against the "test1" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: *************

Answer 1

Without seeing the query it's hard to say for sure what the problem is, but it's very likely that it is due to an internal issue in Athena that has to do with sorting of large intermediary result sets.

The version of Presto that Athena uses does not have support for sorting datasets that are too big to fit in memory. It used to be the same for aggregations too, but that has been fixed by the Athena team.

The issue most often occurs when you have very wide tables, i.e. many columns, or columns with a lot of data. Each individual row can represent a big chunk of memory, and if a node runs out of memory while trying to sort its chunk the query aborts with the "query exhausted resources at this scale factor" error.

If this matches your situation the only way around is unfortunately to limit the number of columns, or eliminate the sorting. Sometimes you can rearrange the query to do the sorting at a different stage to make the memory pressure on the sorting stage lower.