RedShift-Performance Tuning In Few Clicks

Sadagopan.K.V

Performance Tuning Levels

● Table Design ● Query● INFRA level

(Clusters)● Data migration

Performance Tuning - Redshift

Figure out the requirement

Table Design● Felicitous distribution style

○ ALL

○ KEY

○ EVEN

Use DISTSTYLE ALL only when the table is not big.

● Tag the correct DISTKEY○ Helps in data distribution.

○ Plays a key role in self joins

Table Design-Contd● SORTKEY

○ Use the option of multiple sort keys

○ Date,timestamp,count etc.

○ Correctly sorted tables will easy work for self joins

● Primary Key & Foreign key○ Used by query planner

○ Helps in faster self joins

Querying

● Allergic to SELECT * ○ Reduces Scan time & I/O

○ Easy query plan

● EXPLAIN command to view the query plan

Querying-contd● Query Queues

○ Avoid execution bottlenecks

○ High response

● Don’t use Cross-joins unless absolutely necessary ○ reduces nested loops & faster the

execution

Querying-contd● Use CASE expression

○ similar to if/then/else○ Avoids multiple selects

INFRA Level● Nodes , Processors & Slices

○ More Nodes , More Performance

○ Yeah its more costier !!

● Node Types○ Dense Nodes

○ Compute Nodes

INFRA Level● Choose the right Disk

○ SSD/HDD

○ Data & Clustering

○ Less Data High Performance- SSD

○ More Data - HDD

● WLM queue (Workload Management)

○ Clear concurrency & memory management

○ Allocate memory for user groups for query execution

Data Level

● Enable Data compression ○ Reduces storage & I/O in turn increase

query performance

○ compression for table columns

● Update System Stats ○ updating system stats (Analyse for every

data update)

○ Ease query plan

Data Level● Clean up the data before

loading.○ EMR ○ Structured data

● Remove NON ASCII ○ Supports 4 byte

UTF-8

Questions

Thank you