AWS Webcast - Amazon RDS for MySQL: Best Practices and Migration

55 %
45 %
Information about AWS Webcast - Amazon RDS for MySQL: Best Practices and Migration
Technology

Published on February 25, 2014

Author: AmazonWebServices

Source: slideshare.net

Description

Amazon RDS makes it easy to set up, operate, and scale, relational databases in the cloud. Amazon RDS for MySQL supports applications that require up to tens of thousands of IOPS, and allows you to scale on demand without administrative complexity.

In this webinar, we will discuss best practices for getting the most out of Amazon RDS for MySQL, as well as techniques for migrating data to and from the service.

Amazon RDS for MySQL: Best Practices and Data Migration © 2011 Amazon.com, Inc. and its affiliates. All rights reserved. May not be copied, modified or distributed in whole or in part without the express consent of Amazon.com, Inc.

Multi-AZ deployments cname update DNS Primary Update AZ1 Physical Synchronous Replication AZ2 New! Less than 2 minutes for failover

Read Replicas Sync Replication Async Replication

Logical Failover – Replicas of Replicas Sync Replication Async Replication

Things to consider – Read Replicas • MySQL replication is single-threaded • MySQL 5.1 and 5.5 – Replication may stop after crash recovery on the master – sync_binlog = 0 by default – Includes Multi-AZ failover – mysql.rds_next_master_log if potential missing info. is acceptable. • MySQL 5.6 – Crash safe slaves – sync_binlog = 1 by default – Less performance impact but more replica reliability

Backup and Restore Amazon S3 Log 1 Log 2 AZ1 Log 5 AZ2

Cross Region – Snapshot Copy Amazon S3 Amazon S3 AZ1 AZ3 Region 1 Region 2

Cross Region – Read Replicas AZ1 Region 1 AZ3 Region 2

Provisioned IOPS - Scale db.m2.4xlarge - 130GB Data - Partial Random Read Workload 20,000 17,500 15,000 333GB - Regular 12,500 T P 10,000 S 7,500 5,000 2,500 0 333GB - 1000 PIOPS 300GB - 3000 PIOPS

Provisioned IOPS - Latency db.m2.4xlarge - 130GB Data - Partial Random Read Workload - Max Rate 9% Percentage in Latency Bucket 8% 7.69% 7.89% 7% 6% 333GB - Regular 333GB - 1000 PIOPS 300GB - 3000 PIOPS 5% 4% 3.83% 3% 2% 1.38% 1% 0.42% 0.00% 0.01% 0.00% 0.00% 0% 3-20 ms 20-500ms >500ms

Provisioned IOPS - Scale db.m2.4xlarge - 130GB Data - Partial Random Read Workload 20,000 17,500 15,000 333GB - Regular 333GB - 1000 PIOPS 12,500 T P 10,000 S 7,500 5,000 2,500 0 Target = 5000 300GB - 3000 PIOPS

Provisioned IOPS– Latency @ 5000 TPS db.m2.4xlarge - 130GB Data - Partial Random Read Workload - 5000 TPS 9% Percentage in Latency Bucket 8% 7.89% 7% 6% 5% 333GB - Regular 333GB - 1000 PIOPS 300GB - 3000 PIOPS 4% 3% 2% 1.17% 1% 0.35% 0.27% 0.00% 0.00% 0.04% 0.00% 0.00% 0% 3-20 ms 20-500ms >500ms

Provisioned IOPS– 10,000 IOPS db.m2.4xlarge - 130GB Data - Partial Random Read Workload 40,000 35,000 30,000 25,000 T P 20,000 S 15,000 10,000 5,000 0 333GB - Regular 300GB - 3000 PIOPS 333GB - 1000 PIOPS 1000GB-10000 PIOPS

What Limit? – Amazon CloudWatch Metrics

Bottlenecks and Scaling Throughput • • • • EBS Optimized (0.5 -1Gbit) db.m1.large,db.m1.xlarge,m2.2xlarge,m2.4xlarge db.cr1.8xlarge – 10Gbit Regular + non optimized – shared DB Instance Class IOPS • • • • db.t1.micro – db.cr1.8xlarge 1-88 ECU 0.6 – 244 GB RAM Memory to ECU Ratio • 1.7 to 2.8 (exclude micro) Regular or 1,000-30,000 Storage • Ratio 3:1 to 10:1 5GB - 100GB - 3TB DB Engine • • Block Size 8 – 16K Ability to utilize resources

Read/Write Benchmark 130GB Data - Partial Random 90R/10W Workload 10K IOPS 80,000 70,000 cpu 95% r/s 6800 w/s 2600 rMB/s 104 60,000 TPS 50,000 40,000 30,000 20,000 10,000 cpu 85% r/s 3450 w/s 600 rMB/s 52 cpu 100% r/s 6800 w/s 950 rMB/s 104 42,000 cpu 40% r/s 0 w/s 3200 rMB/s 0 47,000 13,200 5,850 - db.m1.large db.m1.xlarge db.m2.4xlarge db.cr1.8xlarge

Bottlenecks and Scaling – Read Replicas

Read/Write Benchmark – Using RR 130GB Data - Partial Random 90R/10W Workload – 10K IOPS 120,000 100,000 TPS 80,000 126,000 60,000 40,000 42,000 20,000 5,850 47,000 db.m2.4xlarge db.cr1.8xlarge 13,200 - db.m1.large db.m1.xlarge cr1 + 3 x 4xlarge

Things to consider – Provisioned IOPS • Use Provisioned IOPS optimized instances – M1.L, M3.XL, M2.2XL (500Mbps) – M1.XL, M3.2XL, M2.4XL (1000Mbps) – CR1.8XL (>1000 Mbps) • Understand Channel Bandwidth – Full duplex – 1000 Mbps ~ 100MBps (with protocol overhead) or – 100 MBps ~ 6250 16KB IOPS

Things to consider – Provisioned IOPS • Max realizable IOPS – Workload dependent – 1:1 R/W -> Max realizable IOPS ~12.5K 16KB IOPS for M2.4XL – 1:1 R/W -> 20K 16KB IOPS for CR1.8XL • Provisioning more than Max can help lower latency • IO Size – IO Sizes <= 16KB is same – IO Sizes > 16KB consumes more IO – 6250 16KB IOPS = 3125 32KB IOPS

Things to consider – Provisioned IOPS • Not able to realize IOPS provisioned? – – – – Using Provisioned IOPS optimized instances? Running automated backups, snapshots, scale storage? Reviewed Queue Depth? Database contention? Locking? Deadlocks?

Other Best Practices • Storage Engine – Avoid MyISAM – not transactional • Cloudwatch alarms – CPU, Memory, Storage, Latency, Replica Lag • SMS/email notifications – Failover, Replication status • Number of Tables – Not more than 1000 tables (standard) and 10,000 tables (PIOPS)

RDS Pre-Migration Steps • • • • • Stop applications accessing the DB Take a snapshot Disable backups Use Single-AZ instances Configure security for cross-DB traffic

Importing from a MySQL DB Instance Application DB Application mysqldump Staging area Load data scp Tsunami UDP Staging server Replication AWS Region

Create a DB Instance for MySQL and EC2 Create DB instance for MySQL using AWS Management Console or CLI PROMPT>rds-create-db-instance mydbinstance -s 1024 -c db.m3.2xlarge -e MySQL - u <masterawsuser> -p <secretpassword> --backup-retention-period 3 Create Amazon EC2 (Staging server) using AWS Management Console or CLI aws ec2 run-instances --image-id ami-xxxxxxxx --count 1 --instance-type m3.2xlarge --key-name MyKeyPair --security-groups MySecurityGroup Create replication user on the master mysql> GRANT SELECT,REPLICATION USER,REPLICATION CLIENT ON *.* TO repluser@‘<RDS Endpoint>' IDENTIFIED BY ‘<password>';

Configure the Master Database Record the “File” and the “Position” values. $ mysql -h localhost -u root -p mysql> show master statusG *************************** 1. row *************************** File: mysql-bin.000023 Position: 107 Binlog_Do_DB: mytest Binlog_Ignore_DB: 1 row in set (0.00 sec)

Importing from a MySQL DB Instance

Upload Files to Amazon EC2 using UDP • Tar and compress MySQL dump file preparation to ship to Amazon EC2 staging server. • Update the Amazon EC2 security group to allow UDP connection from the server where the dump file is being created to your new MySQL client server. • On the Amazon EC2 staging instance, untar the tar.tgz file.

Configure the Amazon RDS database Create the database mysql> create database bench; Import the database that you previously exported from the master database Mysql> load data local infile '/reinvent/tables/customer_address.txt' into table customer_address fields terminated by ','; Mysql> load data local infile '/reinvent/tables/customer.txt' into table customer fields terminated by ','; Configure the slave DB instance for MySQL, and start the slave server mysql> call mysql.rds_set_external_master(‘<master server>',3306,‘<replicationuser>',‘<password>','mysql-bin.000013',107,0); mysql> call mysql.rds_start_replication;

Make Amazon RDS instance the Master Switch over to the RDS instance – Stop the service/application that is pointing at the Master Database – Once all changes have been applied to New RDS Database. Stop replication with “call mysql.rds_stop_replication” – Point the service/application at the New RDS Database. – Once Migration is complete. “call mysql. rds_reset_external_master”

RDS Post-migration Steps • • • • • Turn on backups Turn on multi-az Tighten down security Set up alarms for key metrics Turn on notifications for Database Events

References • Data Import Guide for MySQL • Best practices/operational guidelines • Using Amazon RDS Notifications

Questions? © 2011 Amazon.com, Inc. and its affiliates. All rights reserved. May not be copied, modified or distributed in whole or in part without the express consent of Amazon.com, Inc.

Thank You © 2011 Amazon.com, Inc. and its affiliates. All rights reserved. May not be copied, modified or distributed in whole or in part without the express consent of Amazon.com, Inc.

Add a comment

Related presentations

Related pages

AWS Webcast - Amazon RDS for MySQL: Best Practices and ...

Amazon RDS for MySQL supports ... AWS Webcast - Amazon RDS for MySQL: Best ... Amazon RDS for MySQL: Best Practices and Migration ...
Read more

Amazon RDS for MySQL: Best Practices and Migration - YouTube

Amazon RDS for MySQL: Best Practices and Migration ... Amazon RDS for MySQL: Best Practices and ... AWS Webcast - Amazon RDS for Oracle: Best ...
Read more

Best Practices for Amazon RDS - AWS Documentation

This section summarizes best practices for working with Amazon RDS. ... An Amazon RDS performance best practice ... Best Practices for Working with MySQL ...
Read more

Amazon RDS for MySQL Best Practices and - Youtube ...

58min 46s AWS Webcast - Amazon RDS for MySQL: Best Practices and Migration
Read more

AWS Webcast - Amazon RDS for MySQL: Best Practices for ...

AWS Webcast - Amazon RDS for MySQL: Best Practices for Performance and Data ... Web Content Management Migration - Best practices and planning for ...
Read more

Migrate On-Premises MySQL Data to Amazon RDS (and back ...

AWS Database Migration Service Migrate ... the process of migrating data to Amazon RDS running MySQL, ... best to think of replication as ...
Read more

Amazon RDS for MySQL: Best Practices and Migration - Documents

Share Amazon RDS for MySQL: Best Practices and Migration. ... AWS Webcast - Amazon RDS for MySQL: Best Practices and Migration. AWS Webcast ...
Read more

Importing and Exporting SQL Server Data - Amazon ...

Importing Data from a MySQL or MariaDB DB to an Amazon RDS MySQL or MariaDB ... Amazon RDS for SQL Server ... we recommend the following best practices:
Read more