Performance Tuning in AWS Redshift

Introduction to Redshift:

Amazon Redshift is a hosted data warehouse product which is part of the larger cloud computing platform, Amazon Web Services (AWS). Redshift differs from Amazon’s other hosted database offering Amazon RDS by being able to handle analytics workloads on large scale datasets stored by a column-oriented DBMS principle.

To know more about Redshift follow this link.

Redshift System Architecture and its component:

Redshift architecture is a cluster formation. It has two or more compute nodes and is coordinated through a leader node. All client applications are communicated with clusters through its leader node.

Components

Leader node

The leader node in an Amazon Redshift Cluster manages all external and internal communication.

It is responsible for preparing query execution plans whenever a query is submitted to the cluster.

The leader node distributes a query load to the compute node only when the query involves accessing data stored on the compute nodes. Otherwise, the query is executed on the leader node itself.

There are some functions in redshift which are always executed in a leader node. Basically, the leader node distributes the SQL query to the compute node, and if a query references a user-created table or system table, it is executed in a compute node.

If a query references a catalog table (tables with prefix “PG” like PG_Table_def which executes on leader itself.

Then the following are the functions which are executed in leader node:

Current_schema()

An example query for the leader node:

Select * from PG_table_DEF where schema_name = current_Schema() limit 1;

Compute Node:

The compute node executes the actual query and returns the results to the leader node.

There are two types of compute nodes.

Dense storage- This node allows you to create large data warehouses using HDD for low price.

Dense compute- this node allows you to create high performance data warehouses using SSDs.

Node Slices:

The compute node consists of slices. Each slice has the compute node’s memory and disk storage to execute the query operations. The leader node’s job is to assign a query to the slices for execution. Once the query is assigned, it works in parallel to generate the query results.

Data is distributed to slice based on distribution style and distribution key of table.

Massively Parallel Processing:

Redshift allows massively parallel processing for complex queries and large data sets. Multiple compute nodes executes the same query on portions of data to increase the parallel run.

Columnar Data Storage:

Columnar storage reduces the number of disk I/O requests and minimizes the amount of data loaded into the memory to execute a query. Reduction in I/O speeds up query execution and loading less data means redshift can perform more in-memory processing.

Redshift uses sort keys to sort columns and filter out chunks of data while executing queries.

Data Compression:

Data compression is one of the important factors in ensuring query performance. It reduces storage and enables loading of large amounts of data in the memory fast. Redshift can use compression encoding depending on the column data type.

Types of ways to tune the redshift:

Sort and distribution key.
Scenario: vacuum slowness and report query slowness.
Sort Key:
When creating a table, you can define the sort key for one or more columns. Data will load into the table based on the sort key column. Sort key has three types.

• Single Sort Key
• Compound Sort Key
• Interleaved Sort Key

Single Sort Key: It behaves as normal sort key.
Example
Create table customer:
(Customer_id Interger not null,
Customer_name varchar(40) not null sortkey,
)distkey(Customer_id);

Query 1: select * from Customer where customer_id=1;

In this example it sorts based on customer_id, It fetch only one block.

Query 2: select * from Cutomer where customer_name=’Alex’;

It will sort based on the customer name ‘Alex’. It fetches data from three blocks.
Difference between compound and interleaved:

Compound Sort Key:

Compound sort keys, are made up of all the columns that are listed in the sort key definition during the creation of the table, and the compound sort key will behave based on the order that they are listed. The order is important, as the performance decreases when queries depend on the secondary sort columns.

When you define a compound sort key, make sure to put the first column in the list as the most frequently used column in your queries.
Compound sort keys work best in situations where the query’s filter applies conditions, which use a prefix of the sort keys.

Interleaved Sort Key:

Contrary to compound sort keys, interleaved sort keys put an equal weight to each of the included columns in the sort key definition. If there’s no dominant column in your queries, then your performance of query will improve by creating an interleaved sort key.

Note: If your query uses restrictive predicates on secondary sort columns, interleaved sort keys might significantly improve query performance, also interleaved sort keys are more efficient with huge data tables.

Choosing Sort Keys:

• If recent data is queried the most frequently, specify the timestamp column as the leading column for the sort key. Eg. Date
• If you do frequent range filtering or equality filtering on one column, specify that column as the sort key.
• If you frequently join a table, specify the join column as both the sort key and the distribution key.

Improves Performance:

A sort key improves performance:-

1. By reducing disk I/O by skipping over blocks when filtering data using a query where clause.
2. By reducing the need to physically sort data for ORDER BY or GROUP BY operations.
3. By facilitating a MERGE JOIN – the fastest of the three join methods supported by redshift.

Types of Distribution Key:

Amazon redshift uses the MSSP technique, and it automatically distributes data and query across the nodes available in cluster. There are three types of distribution available in redshift.

• Even
• Key
• All

Even Distribution: This is a default one, like a round-robin method, it will distribute equal amounts of data across slices. This key will suit when we are not using the table in joins.
Key Distribution: In key distribution, rows are distributed based on the values in one column or we can say like data is distributed across the slices, and the leader node matches the designated column.
All Distribution: If we specify All Distribution style during table creation, then the leader node distributes the copy of tables to every node in the cluster. The loading process will take longer if the table is distributed on ALL style.

Choosing Distribution Key:

• If the table (e.g. fact table) is highly de-normalized and no JOIN is required, choose the EVEN style.
• Choose ALL style for small tables that do not often change.
• It is beneficial to select a KEY distribution if a table is used in JOINS. Also, consider the other joining tables and their distribution style.

Compressing the Tables Using Redshift Encodings

The major reason why we are going for compression is to reduce the size of the table.

How it works? Let’s consider an example if we are having a user_table with column user_id,user_name,location,phone_no.

Eg: When we are applying column encoding while creating a table in redshift. At the backend it will create a sample table with primary key for the encoded column. When we are using join operations in our query, it will join the with sample table and replace the column value with primary ID and this reduces the table size and increases the performance too.

Where we Should Not Use Column Encoding – Join and Where Clause.

We should not apply the column encoding for joining columns and for filter columns.

To know more about compression and encoding types, follow this link.