Redshift CPU utilisation very high on running COPY JOB

Hello

There may be several reasons for the CPU usage to grow. Here are a few steps to help you diagnose the issue:

Troubleshooting High CPU During Redshift COPY Operations

High CPU usage can occur during Amazon Redshift COPY processes because these are CPU-intensive, primarily due to several pre-ingestion steps:

• The COPY command analyzes compression, scans, sorts, and aggregates the data before insertion.
• By default, these tasks execute sequentially, potentially causing the CPU to reach maximum capacity.
• Performance may worsen as the volume of data grows.

To identify specific causes:

• Use Redshift Advisor's suggested SQL queries to check for overhead from compression analysis.
• Monitor node-level CPUUtilization metrics in CloudWatch.
• Run queries to diagnose performance bottlenecks.
• Look for data skew or distribution issues leading to uneven node workloads:

•     [(https://docs.aws.amazon.com/redshift/latest/dg/advisor-recommendations.html)] 
  

•     [(https://aws.amazon.com/blogs/big-data/enhance-data-ingestion-performance-in-amazon-redshift-with-concurrent-inserts/)].
  

•     This repost topic:  high-cpu-usage-of-one-redshift-node-not-leader-how-understand-what-is-causing-this-imbalance
  

Improving COPY Performance To enhance efficiency and lower CPU load during COPY:

1. Bypass compression analysis:

• Explicitly set column ENCODE options when creating tables.
• Add COMPUPDATE OFF to COPY commands.
• Automate encoding using AWSColumnEncodingUtility from GitHub.

2. Optimize file management:

• Break large data uploads into smaller batches.
• Use multiple files for loading (preferably as many as the Redshift slices).
• Compress files before upload and aim for file sizes between 1MB–1GB.
• For bulk loads, leverage ManifestGenerator or similar tools.

3. Leverage concurrent insert functionality:

• Ensure your cluster is on patch 187 or later to take advantage of concurrent inserts.
• Note potential deadlock risks with multiple simultaneous COPY operations

•    [(https://docs.aws.amazon.com/redshift/latest/dg/advisor-recommendations.html)] 
  

•     This repost topic : improve-copy-ingestion-performance-for-large-data-loads-on-amazon-redshift
  

•     [(https://aws.amazon.com/blogs/big-data/enhance-data-ingestion-performance-in-amazon-redshift-with-concurrent-inserts/].
  

Monitoring and Diagnostic Queries

Find tables with missing statistics:

SELECT substring(trim(plannode),1,100) AS plannode, count(*) 
FROM stl_explain 
WHERE plannode LIKE '%missing statistics%' 
GROUP BY plannode 
ORDER BY 2 DESC;

Identify data skew or unsorted rows:

-- See source for full query
SELECT trim(pgn.nspname) AS schema, 
trim(a.name) AS table, id AS tableid,
-- Additional columns omitted for brevity
FROM stv_tbl_perm a
-- Joins omitted for brevity
WHERE mbytes IS NOT NULL 
ORDER BY mbytes DESC;

Find longest running queries:

-- See source for full query
SELECT trim(database) AS db, count(query) AS n_qry,
-- Additional columns omitted for brevity
FROM (SELECT userid, label, stl_query.query,
-- Subquery omitted for brevity
GROUP BY database, label, qry_md5, aborted
ORDER BY total DESC LIMIT 50;

[[repost article on diagnosing queries](https://repost.aws/articles/ARxvwkh0OWTfiUFXdT-qnjwg/redshift-diagnostics-queries-to-identify-the-performance-bottleneck]

[repost article on cpu usage]

Hope this helps!