Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

Time:2022-5-14

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

Amazon RedshiftIt is a fast and popular fully managed cloud data warehouse that allows you to use standard Amazon SQL to process EB level data in data warehouses, operational databases and data lakes. It provides different node types to adapt to various workloads; You can choose from Amazon redshift RA3, Amazon redshift DC2 and Amazon redshift DS2 according to your needs. Amazon redshift RA3 is the latest instance type. It allows you to independently expand computing and storage and pay for it. It also supports advanced functions such as cross cluster data sharing and cross availability cluster relocation. For more information about node count and type recommendations when upgrading, seeUpgrade to Amazon redshift RA3 node type

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

To learn more about the latest technology release and practical innovation of Amazon cloud technology, please pay attention to the 2021 Amazon cloud technology China summit! Click the picture to sign up ~

BowenExpand the cloud data warehouse and reduce costs with the New Amazon redshift RA3 node with managed storage: compare Amazon redshift RA3 with Amazon redshift DS2 instance typesThe advantages of migrating from Amazon redshift DS2 to Amazon redshift RA3 are introduced in detail. After understanding the advantages of Amazon redshift RA3, many of our existing Amazon redshift DC2 customers have migrated to Amazon redshift RA3 after successfully evaluating their performance. However, this assessment is mainly done manually, which requires you to replicate the workload to assess performance on the new node.

You can use Amazon redshift simple replay tool to conduct what if analysis and evaluate the performance of workload in different scenarios.For example, you can use this tool to benchmark the actual workload, evaluate new functions or evaluate different cluster configurations on new instance types such as Amazon redshift RA3. It also includes enhanced support for replaying data ingestion and export pipelines using copy and unload statements. To start and replay your workload fromAmazon redshift GitHub repositoryDownload the tool.

In this article,We will describe the steps to automatically evaluate Amazon redshift RA3 instances through the Amazon redshift simple replay utility.If you run production workloads in Amazon redshift using the older generation Amazon redshift DS2 and Amazon redshift DC2 node types, you can use this solution to automatically extract workload logs from the source production cluster and replay them in an isolated environment, so that you can directly and seamlessly compare the two Amazon redshift clusters.

precondition

This solution usesAmazon CloudFormationAutomatically preset all necessary resources in your Amazon cloud technology account. For details, seeGetting started with Amazon cloudformation

As a prerequisite for this solution, you need to complete the following steps. You may need administrator access to your Amazon cloud account to perform these steps and then deploy this solution.

  1. Enable audit logging in the source Amazon redshift cluster in the Amazon redshift console, and then specify the location of the Amazon Simple Storage Service (Amazon S3) bucket to save the log file. For details, see database audit log records.
  2. Set parameter group enable_ user_ activity_ Change logging to true. For details, see Managing parameter groups using the console.
  3. Restart the cluster.
  4. Create an Amazon Elastic Compute Cloud (Amazon EC2) key pair in the Amazon cloud technology account that plans to deploy the Amazon cloudformation template. For details, see creating key pairs using Amazon EC2.

Solution overview

The solution includes two Amazon cloudformation templates for performing extraction and replay of workloads.You can deploy these two templates in the same account hosting Amazon redshift cluster, which is our recommended method. Alternatively, you can run the extraction template in the production account and the replay template in the independent development account to perform this evaluation, as shown in the figure below.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

https://github.com/awslabs/am…

This process usesAmazon Step Functions 

AndAmazon Lambda To orchestrate end-to-end workflows for extraction and playback. The first template deploys the extraction application in the source account. This will extract the audit log from the Amazon S3 bucket for Amazon redshift audit logging. It stores it in a new Amazon S3 bucket created for the replay extract process. It also creates a manual snapshot of your cluster and authorizes the replay account to restore the snapshot.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

The second template deploys the replay application in the development account (if you choose not to run replay in the source account). It uses Amazon S3 files from the extraction application and generates an automatic evaluation summary for playback.

End to end workflow: extraction process

The extraction process starts when you enter the time interval at which you want to run this process. It automatically extracts audit logs from the source cluster and stores them in the New Amazon S3 bucket of the account. It will also deploy a size of M5 Amazon EC2 instance of large (with simple replay utility installed). The following figure shows the solution architecture.

The following figure shows the Amazon step functions state machine used to extract the process.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

The state machine performs the following steps to extract the source cluster metadata into the Amazon S3 bucket:

  1. Wait for the source cluster to be available.
  2. Create a manual snapshot of the source cluster using the identifier RA3 migration evaluation snapshot – connected to the cluster identifier string.
  3. The snapshot authorizes the target account where you plan to run the replay process.
  4. Upload the source cluster configuration parameters to the extraction Amazon S3 bucket.
  5. Run the extraction process to get logs from the source cluster and put them into the Amazon S3 bucket.

Extracting the Amazon cloudformation template automatically starts the first iteration of the extraction process, but you can submit a template with start_ Time and end_ Time enter the state machine of the parameter to re run the process at any time, as shown in the following code:

{“input”: {“start_time”: “<<Extract_Start_Time>>”, “end_time”: “<<Extract_End_Time>>”}}

Will start_ Date and end_ Replace the value of date with the actual date in iso-8601 format (for example, 2021-03-05t12:30:00 + 00:00). The following screenshot shows the state machine run input.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

You need to deploy the Amazon cloudformation template for the extraction process in the same account hosting the source cluster. This template requires the following parameters:

  • Sourceredshiftclusterendpoint – non Amazon redshift RA3 source cluster endpoint, including port number and database name.
  • Accountidforreplay – if you plan to run the replay process in another account, please enter the 12 digit Amazon cloud technology account ID in this parameter. If you are running the extraction and playback process in the same account, enter n / A.
  • Simplereplaystarttime – the start date of the first iteration of the extraction process you want to run from the source cluster, in iso-8601 format (for example, 2021-01-20t21:41:16 + 00:00). You can change it later in the input JSON that extracts the state machine.
  • Simplereplayendtime – the end date and time you want to extract from the source cluster and replay in the target Amazon redshift RA3 cluster, in iso-8601 format. You can change it later in the input JSON that extracts the state machine. Please make sure that the difference between the start time and the end time does not exceed 24 hours.
  • Extractsystemtables – this is an optional step (provided you extract the source cluster system tables for reference). We recommend setting this parameter to no because it adds the Amazon identity and access management (IAM) role to the source cluster to unload system tables from the source cluster.
  • Enduseriamrolename – the name of the existing Amazon Iam role of the end user who may be running the extract replay assessment. You can use this parameter to allow non administrator users to run the extract replay state machine without any other permissions on Amazon cloud technology resources. If you do not want to provide any end-user rights, enter n / A.
  • Ec2instanceami – Amazon AMI of Amazon EC2 instance based on Amazon Linux 2. We recommend that you keep the default Amazon AMI for this parameter unless it is required for compliance requirements.
  • Amazon Identity and Access Management
    http://aws.amazon.com/iam

After deploying the template, navigate to the output tab of the template, which lists some relevant parameters required for replay process deployment.

End to end workflow: replay process

The second part of this solution is,Use the Amazon cloudformation template to deploy the replay process in the same account running the extraction process or other accounts in the same region

This process presets two Amazon redshift clusters: one is a replica cluster (the configuration is exactly the same as the source cluster), and the other is a target cluster with Amazon redshift RA3 configuration. The two instances of ecm5 in the cluster are extracted and replayed simultaneously. Since the replay process retains the time interval between queries and transactions to simulate the exact workload from the source cluster, the time spent by this process is the same as the start you provided when running the extraction process_ Time and end_ The duration between time is roughly the same. The following figure shows the architecture of the solution.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

The following figure shows the Amazon step functions state machine used to replay the process.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

The state machine performs the following steps to replay the workload extracted from the Amazon S3 bucket:

  1. Update the Amazon redshift parameter group to the same configuration as the source cluster parameter group, which is saved in the Amazon S3 bucket as part of the extraction process.
  2. If the replica and target cluster do not exist, start their cluster creation process in parallel. The replica cluster will be created with exactly the same configuration as the source cluster, while the target cluster will be created with Amazon redshift RA3 configuration (if the source cluster is compatible with the elastic adjustment of Amazon redshift RA3 configuration specified when you deploy Amazon cloudformation template). If the target Amazon redshift RA3 configuration is incompatible with elastic adjustment, it will create the target cluster with the same configuration as the replica cluster.
  3. If the target cluster was created using a non Amazon redshift RA3 configuration due to incompatibility with elastic adjustment in the previous step, it will perform classic adjustment for the cluster when the cluster is available.
  4. If the target cluster or replica cluster is suspended, it will resume the cluster.
  5. If the target cluster or replica cluster is available and any recovery operations (if applicable) for the cluster have been completed, it runs Amazon SQL script and sets some Amazon redshift objects in the common architecture of the cluster to perform automatic performance comparison between clusters.
  6. After the setting process of the target cluster and replica cluster is completed, it will run the replay process in both clusters at the same time, so as to run all Amazon SQL extracted from the source cluster, while maintaining the same transaction sequence and time interval as the source cluster.
  7. After the replay process is completed, it will unload the query statistics from the replica cluster and load them into the target Amazon redshift RA3 cluster, so as to realize the direct performance comparison between the environments in Amazon redshift RA3 cluster.

The Amazon cloudformation template for the replay process automatically starts the first iteration of the replay process, but you can rerun the process at any time by submitting a state machine without any parameters. This template requires the following parameters:

  • Sourceaccountnumber – the source account number that runs the extraction process. You can find it on the output tab of the extract stack.
  • Sourceaccountsimplereplays3bucket – extracts the Amazon S3 bucket, created by the extraction template (found on the stack Output tab).
  • Sourceredshiftclusterendpoint – non Amazon redshift RA3 source cluster endpoint, including port number and database name (available on the stack Output tab).
  • Sourceredshift clusterkmskeyarn – Amazon key management service (KMS) key Amazon ARN (Amazon resource name) (if your source Amazon redshift cluster is encrypted) (available on the stack Output tab). If the source cluster is encrypted, you need to run extraction and playback in the same account.
  • Sourceredshiftclustermaster username – the user name associated with the primary user account of the source cluster (found on the stack Output tab).
  • Sourceredshiftclusterprimarydatabase – the name of the primary database in the source cluster where you want to replay the workload. Amazon redshift will automatically create a default database named dev, which may not be your primary database. Enter the correct values based on your deployment. If you have multiple databases, you need to run extract and replay on one database at a time.
  • Targetredshiftclusternodetype – Amazon redshift RA3 node type to preset. Upgrade to Amazon 3 according to the recommended node type and redshift node type.
  • Targetredshiftclusternumberofnodes – the number of compute nodes in the cluster.
  • Enduseriamrolename – the name of the existing Amazon Iam role of the end user who may be running the extract replay assessment. You can use this parameter to allow non administrator users to run the extract replay state machine without any other permissions on Amazon cloud technology resources. If you do not want to provide any end-user rights, enter n / A
  • Grants3readonlyaccesstoredshift – if you deploy the extraction and replay process in the same account, you can enter yes for this parameter, which grants Amazon s3readonlyaccess to the Amazon redshift target and replica cluster to replay the replica statement of Amazon redshift in the account. Otherwise, you need to manually copy the files and adjust the copy in the latest extraction folder of the extraction Amazon S3 bucket_ replacement. CSV file, and you need to replay the config / replay.csv file of Amazon S3 bucket Set the copy of the file parameter to true in the yaml statement.
  • VPC – an existing Amazon virtual private cloud (Amazon VPC) where you want to deploy clusters and Amazon EC2 instances.
  • Subnetid – the existing subnet within Amazon VPC where you deploy the cluster and Amazon EC2 instance.
  • Keypairname – allows SSH to connect to an existing key pair that replays Amazon EC2 instances.
  • Onpremisescidr – the IP range (CIDR notation) used to access the existing infrastructure of the target cluster and replica cluster from Amazon SQL clients. If unsure, please enter the CIDR address of the company desktop. For example, if the IP address of the desktop is 10.156.87.45, enter 10.156.87.45/32.
  • Ec2instancetype – Amazon EC2 instance type that hosts the code base of the simple replay utility. If the data size in the cluster is less than 1 TB, you can use the large instance type. We recommend using larger instance types for larger workloads to prevent Amazon EC2 instances from becoming a bottleneck when obtaining query results from the cluster.
  • Ec2instancevolumegib – Amazon EC2 instance volume size in gib. We recommend keeping it at 30 gib or more.
  • Ec2instanceami – Amazon AMI of Amazon EC2 instance based on Amazon Linux 2. Do not change this parameter unless you need to meet compliance requirements.
  • Amazon Key Management Service (KMS)
    https://aws.amazon.com/kms/
  • Upgrade to RA3 node type
    https://docs.aws.amazon.com/r…
  • Amazon Virtual Private Cloud
    http://aws.amazon.com/vpc

Access and security

To deploy this solution using Amazon cloudformation,You need to have administrator access to the Amazon cloud technology account that plans to deploy the extraction and playback process。 Both templates provide the input parameter enduseriamrolename, which you can use to allow non administrator users to run processes without any extensive permissions on system resources.

Amazon cloudformation template uses security best practices to preset all necessary resources based on the principle of minimum permission, and hosts all resources in the account VPC. Amazon EC2 instance and Amazon redshift cluster share the same security group. SSH access to Amazon EC2 instance is not allowed. Access to Amazon redshift target cluster and replica cluster is controlled by Amazon cloudformation template parameter onpremisescidr. You need to provide this parameter to allow local users to connect to the new cluster using Amazon SQL client on Amazon redshift port.

Access rights to all resources are usedAmazon Iam role control, such roles grant appropriate permissions to Amazon redshift, Amazon lambda, Amazon step functions and Amazon EC2. Grant read-write access to the Amazon S3 bucket created by the extraction process to the Amazon cloud technology account used to replay the process, so that it can read and update the configuration from the bucket.

Evaluate RA3 performance

After the first run of the replay state machine, you should be able to view the Amazon redshift RA3 target and non Amazon redshift RA3 replica clusters on the Amazon redshift console of the Amazon cloud technology account where the replay template is deployed. Each iteration of replay will automatically populate the following tables and views in the public architecture of the target Amazon redshift RA3 cluster, so that you can directly compare the performance between clusters:

  • source_ target_ Comparison – provides a comparative summary of the time taken by the two clusters to replay the workload. It provides total grouped by Amazon redshift queue and user name_ query_ time_ saved_ The seconds column, which can be very useful in your final assessment.
  • source_ target_ comparison_ Raw – provides a detailed comparison of the time taken by two clusters for each query.
  • replica_ cluster_ query_ Stats – store query level metrics for replays running on replica clusters.
  • target_ cluster_ query_ Stats – stored query level indicators for replay running on Amazon redshift RA3 cluster.
  • source_ cluster_ query_ Stats – query level indicator in the storage source cluster. This table may be empty because it relies on STL log views in the source cluster, which remain for only 2-5 days. For details, see STL view for logging.
  • detailed_ query_ Stats — populate query_ The stats table also provides the logic we use to populate these statistics from the STL log view.
  • STL view for logging
    https://docs.amazonaws.cn/en_…

Cost and schedule considerations

Running this template in your Amazon cloud technology account will have some cost impact, because it will preset a new Amazon redshift cluster and three Amazon EC2 instances, if you don’t have oneReserved Instances , these instances may be charged as on-demand instances. After completing the evaluation, we recommend deleting the Amazon cloudformation stack. This will delete all associated resources except the two Amazon S3 buckets used for extraction and playback. It is not recommended that we use it when the cluster is suspended. For details, seeAmazon RedshiftPricing # and Amazon EC2price.

limit

Simple replay and this automated process have some known limitations:

  • If there is a lag in transferring the audit log to Amazon S3, the extraction process may fail. In this case, you need to choose a different time interval from the past to rerun the extracted state machine.
  • There is no guarantee that related Amazon SQL queries across connections will run in the original order.
  • If the target cluster does not have access to the external table, the Amazon redshift spectrum query will not be replayed.
  • Queries with bind variables are not replayed.
  • Playback using JDBC is not supported.
  • A large number of concurrent acquisitions may put pressure on Amazon EC2 clients. For these cases, a larger Amazon EC2 instance may be required.
  • The audit log may contain Amazon SQL that is not committed to the production cluster. These Amazon SQL will be replayed.

You can minimize the impact of these restrictions by comparing replicas with target clusters (rather than directly with the production Amazon redshift cluster).

conclusion

Compared with previous instances, the Amazon redshift RA3 instance provides many additional advantages. If you try to migrate to the Amazon redshift RA3 instance type but are worried about the evaluation work, you can easily and seamlessly perform this evaluation and successfully migrate to the Amazon redshift RA3 node with the help of Amazon redshift simple replay utility.

If you are satisfied with the performance of Amazon redshift RA3 instance type, you can adjust the production cluster and move it to Amazon redshift RA3 platform. The time taken to adjust the production cluster is similar to the time taken to create and test the Amazon redshift RA3 cluster, depending on whether elastic adjustment or traditional adjustment is used. We recommend that you create a manual snapshot before performing a tuning operation on the production cluster.

Author of this article

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

Manash Deb

Amazon cloud technology

Senior analyst Solution Architect

For more than 15 years, it has been committed to building end-to-end data-driven solutions using different database and data warehouse technologies. Like to learn new technologies and solve, automate and simplify customer problems through Amazon cloud technology’s easy-to-use cloud data solutions.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility

Sinath Madabushi

Amazon Redshift

Senior Database Engineer

He has worked in various fields of data warehouse, business intelligence and database technology for more than 16 years. Is an avid big data enthusiast who works with customers around the world to meet their data warehouse needs.

Simplify Amazon redshift RA3 Migration assessment using Amazon redshift simple replay utility