Redshift protect against running out of disk space due to select queries

0

Hi,

When issuing queries on a large table Redshift risks running out of disk space. What are best practices to limit queries to a reasonable disk usage? This is vital to daily operation and an essential security feature if Redshift is used in conjunction with external services.

After running out of disk space (160GB total, 80GB used) I vacuumed the table and reduced the size from around 80 GB to 1 GB, but I still experience significant spikes in disk usage with simple queries. Obviously there has to be a way to prevent users from killing the database by issuing a few select queries and I just don't know about it, so I would greatly appreciate your advice.

This example query uses up to 10 GB of disk space for more than a minute:

explain select * from my_schema.mytable order by created_time limit 1;
------------------------------------------------------------------------------------------------------------------------------------------------
XN Limit (cost=1000005105905.92..1000005105905.92 rows=1 width=4527)
-> XN Merge (cost=1000005105905.92..1000005199888.98 rows=37593224 width=4527)
Merge Key: created_time
-> XN Network (cost=1000005105905.92..1000005199888.98 rows=37593224 width=4527)
Send to leader
-> XN Sort (cost=1000005105905.92..1000005199888.98 rows=37593224 width=4527)
Sort Key: created_time
-> XN Seq Scan on event (cost=0.00..375932.24 rows=37593224 width=4527)
------------------------------------------------------------------------------------------------------------------------------------------------

[EDIT] I limited the query via WLM to only be able to spill 1G to disk, but it does not abort the query even though it takes up way more disk space. The configuration works as expected otherwise - when I limit the time the query can take it aborts as expected. My guess is that it does not consider the way the query takes up disk space as spilling memory to disk. Please confirm if that is correct.

Cheers, Johannes M.

질문됨 일 년 전303회 조회
1개 답변
1

What is usually the cause of a CPU spike like what you're describing is if you are loading into a table without any compression settings. The default setting for COPY is that COMPUPDATE is ON. What happens is that Redshift will take the incoming rows, run them through every compression setting we have and return the the appropriate (smallest) compression.

To fix the issue, it's best to make sure that compression is applied to the target table of the COPY statement. Run Analyze Compression command if necessary to figure out what the compression should be and manually apply it to the DDL. For temporary tables LZO can be an excellent choice to choose because it's faster to encode on these transient tables than say ZSTD. Just to be sure also set COMPUPDATE OFF in the COPY statement.

AWS_Guy
답변함 일 년 전
  • Oh sorry, maybe I accidentally wrote that it is a CPU spike somwhere. The spike is in the disk size not CPU :) I am quite happy with the current compression rate as it is, but thanks for your recommendation! I will be sure to keep it in mind.

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠