Redshift COPY command CPU spikes

Question

A customer is running into the CPU spikes (it goes to almost 100%) on all compute nodes, while running COPY command to load data into Redshift cluster from S3. They are running a cluster of 2 ds2.xlarge nodes which gives total of 4 slices to work in parallel. 
After noticing the spike, they added a third node to the cluster, but still seeing the same behavior.

The files being copied are csv, in the compressed GZIP format.

Anyone seen this issue before? Any pointers will be much helpful, thanks!

Accepted Answer

While proper splitting of files is very important and highly recommended, it shouldn't cause a CPU spike across the cluster.

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][1] 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][2] 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.

[1]: http://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-load.html#copy-compupdate
  [2]: http://docs.aws.amazon.com/redshift/latest/dg/r_ANALYZE_COMPRESSION.html

Redshift COPY command CPU spikes

관련 콘텐츠