CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftamazon-redshift-spectrum
Minh Triet
Minh Triet

Reputation: 1250

Getting a "Disk Full" error from Redshift Spectrum

I am facing the problem of frequent Disk Full error on Redshift Spectrum, as a result, I have to repeatedly scale up the cluster. It seems that the caching would be deleted.

Ideally, I would like the scaling up to keep the caching, and finding a way to know how much disk space would be needed in a query.

Is there any document out there that talks about the caching of Redshift Spectrum, or they are using the same mechanism to Redshift?

EDIT: As requested by Jon Scott, I am updating my question

SELECT p.postcode,
         SUM(p.like_count),
         COUNT(l.id)
FROM post AS p
INNER JOIN likes AS l
    ON l.postcode = p.postcode
GROUP BY 1;

The total of zipped data on S3 is about 1.8 TB. Athena took 10 minutes, scanned 700 GBs and told me Query exhausted resources at this scale factor

EDIT 2: I used a 16 TB SSD cluster.

Upvotes: 0

Views: 1464

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

You did not mention the size of the Redshift cluster you are using but the simple answer is to use a larger Redshift cluster (more nodes) or use a larger node type (more disk per node).

The issue is occurring because Redshift Spectrum is not able to push the full join execution down to the Spectrum layer. A majority of the data is being returned to the Redshift cluster simply to execute the join.

You could also restructure the query so that more work can be pushed down to Spectrum, in this case by doing the grouping and counting before joining. This will be most effective if the total number of rows output from each subquery is significantly fewer than the rows that would be returned for the join otherwise.

SELECT p.postcode
     , p.like_count
     , l.like_ids
FROM (--Summarize post data
      SELECT p.postcode
           , SUM(p.like_count)
      FROM post AS p 
      GROUP BY 1
     ) AS p
INNER JOIN (--Summarize likes data
            SELECT l.postcode
                 , COUNT(l.id) like_ids
            FROM likes AS l 
            GROUP BY 1
          ) AS l
    -- Join pre-summarized data only
    ON l.postcode = p.postcode
;

Upvotes: 1

Related Questions