amazon rds deep dive
TRANSCRIPT
![Page 1: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/1.jpg)
© 2016, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Deep Dive on Amazon RDS Julien Simon, Principal Technical Evangelist, AWS
[email protected] - @julsimon
Kristján Thorvaldsson, Director of WOW labs, WOW Air [email protected] - @kristjanth
![Page 2: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/2.jpg)
Agenda
• Introduction
• Case study: WOW air
• Scaling on Amazon RDS • High availability with Amazon RDS • Migrating to Amazon RDS
![Page 3: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/3.jpg)
No infrastructure management
Scale up/down Cost-effective
Instant provisioning
Application compatibility
Amazon Relational Database Service (Amazon RDS)
![Page 4: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/4.jpg)
Amazon RDS engines
Commercial Open source Amazon Aurora
![Page 5: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/5.jpg)
Amazon Aurora vs. MySQL Feature RDS Aurora RDS MySQL
Number of replicas Up to 15 Up to 5
Replica5on type Asynchronous (milliseconds) Asynchronous (seconds)
Replica5on performance impact on primary
Low High
Replica can act as failover target Yes (no data loss) Yes (poten5ally minutes of loss)
Storage Up to 64 TB, auto growth Up to 6 TB, specify storage limit
Automated failover Yes, to replica Yes, to standby
User-‐defined replica5on delay No Yes
Replica support for different data or schema vs. primary
No Yes
Cross-‐region replica5on No Yes
Data cache survives Yes No
![Page 6: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/6.jpg)
Trade-offs with a managed service
Fully managed host and OS • No access to the database host operating system • Limited ability to modify configuration that is managed on the
host operating system • No functions that rely on configuration from the host OS
Fully managed storage • Max storage limits
• SQL Server — 4 TB • MySQL, MariaDB, PostgreSQL, Oracle — 6 TB • Aurora — 64 TB
• Growing your database is a process
![Page 7: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/7.jpg)
Selected RDS customers
![Page 8: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/8.jpg)
Selected Amazon Aurora customers
![Page 9: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/9.jpg)
© 2016, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
WOW air “up” in the cloud Kristján Torvaldsson, Director of WOW labs, WOW Air
[email protected] - @kristjanth
![Page 10: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/10.jpg)
A little bit about WOW air
Low-cost airline founded in 2011 First flight in May 2012 Awarded Air Operator’s certificate (AOC) in October 2013 Served its one-millionth guest in December 2014
![Page 11: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/11.jpg)
A little bit about WOW air (cont’d)
Over 27 destinations in Europe and North America 750,000 guests and 6 aircrafts in 2015 By the end of 2016, 1.8 million guests and 11 aircrafts
![Page 12: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/12.jpg)
Why Amazon Web Services?
Three successful sales campaigns thanks to AWS scalability • “US East”: Boston BOS and Baltimore BWI • “Maple Leaf”: Montreal YUL and Toronto YYZ • “US West”: Los Angeles LAX and San Francisco SFO
![Page 13: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/13.jpg)
“US East” campaign
Internet Booking Engine (IBE) moved to Amazon Web Services (AWS) Amazon EC2 instances set up to host: • 20 Web Servers (MS IIS) • 3 Databases (MS SQL) • 1 In-memory DB Server (Couchbase)
![Page 14: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/14.jpg)
Operation “Maple Leaf”
Content Management System (CMS) moved to Amazon EC2 instances CMS database set up on Amazon RDS MySQL with Multi-AZ deployment Total success or … ?
![Page 15: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/15.jpg)
Operation “Maple Leaf” (cont‘d)
Problems with too many DB connections from web application Ended up bumping up to larger RDS instances… twice
First bump was done with Amazon EC2 instances turned off Second bump was done with Amazon EC2 instances live
![Page 16: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/16.jpg)
“US West” campaign - Cross region replication CMS set up on Amazon EC2 auto-scaling groups in 2 regions Master Database replicated to read replicas in 2 regions (AZ: a) Read replicas replicated to second level of read replicas (AZ: b and c)
![Page 17: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/17.jpg)
Why Amazon RDS?
Multiple database engines to choose from Fully managed, with optional automatic upgrades Redundancy with Multi-AZ deployment Cross region replication functionality (MySQL) Snapshot feature for backups and restore
![Page 18: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/18.jpg)
Database replication project
Replicate (in real-time) the Oracle Database of our Inventory System hosted by our partner: • Partner wanted to use SymmetricDS as the replication tool
• Size of data to replicate was unknown
• Partner had previously set up ongoing Oracle to MS SQL replication for another client (in “just” 4 weeks)
![Page 19: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/19.jpg)
Database replication project cont‘d
Our simple solution was up and running in 4 hours: • Target database was selected to be Amazon Aurora
• Database storage grows on the fly
• No “nasty” conversion of data needed
![Page 20: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/20.jpg)
Future RDS projects
• Region replication for our new CMS Database with Amazon RDS for PostgreSQL
• Region Replication for the Database on
our Electronic Flight Bag (EFB) system with Amazon RDS for PostgreSQL
![Page 21: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/21.jpg)
More information
WOW air‘s website: https://wowair.com About WOW air: http://wowair.us/the-wow-experience/our-story AWS Case Study on WOW air: https://aws.amazon.com/solutions/case-studies/wow-air
![Page 22: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/22.jpg)
Scaling on RDS
![Page 23: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/23.jpg)
Read Replicas
Bring data close to your customer’s applications in different regions Relieve pressure on your master node for supporting reads and writes. Promote a Read Replica to a master for faster recovery in the event of disaster
![Page 24: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/24.jpg)
Read Replicas
Within a region • MySQL • MariaDB • PostgreSQL • Aurora Cross-region • MySQL • MariaDB
![Page 25: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/25.jpg)
Read Replicas – Oracle and Microsoft SQL Server
Oracle • GoldenGate • Third-party replication products • Snapshots SQL Server • Third-party replication products • Snapshots
![Page 26: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/26.jpg)
Scaling manually Console
![Page 27: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/27.jpg)
Scaling on a schedule – CLI or AWS Lambda
import boto3
client=boto3.client('rds')
def lambda_handler(event, context):
response=client.modify_db_instance(DBInstanceIdentifier='sg-cli-test',
DBInstanceClass='db.m4.xlarge',
ApplyImmediately=True)
print response
#Scale down at 8:00 PM on Friday
0 20 * * 5 /home/ec2-user/scripts/scale_down_rds.sh
#Scale up at 4:00 AM on Monday
0 4 * * 1 /home/ec2-user/scripts/
scale_up_rds.sh
aws rds modify-db-instance --db-instance-identifier sg-cli-test --db-instance-class db.m4.large --apply-immediately
![Page 28: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/28.jpg)
Scaling on demand – Cloudwatch & AWS Lambda
import boto3
import json
client=boto3.client('rds')
def lambda_handler(event, context):
message = event['Records'][0]['Sns']['Message']
parsed_message=json.loads(message)
db_instance=parsed_message['Trigger']['Dimensions'][0]['value']
print 'DB Instance: ' + db_instance
response=client.modify_db_instance(DBInstanceIdentifier=db_instance,
DBInstanceClass='db.m4.large',
ApplyImmediately=True)
print response
SNS Lambda Cloudwatch RDS
![Page 29: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/29.jpg)
Scaling – Single AZ With single AZ deployment, the master takes an outage
dbinstancename.1234567890.us-west-2.rds.amazonaws.com:3006
![Page 30: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/30.jpg)
Scaling – Multi-AZ With Multi-AZ, the standby gets upgraded first
dbinstancename.1234567890.us-west-2.rds.amazonaws.com:3006
![Page 31: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/31.jpg)
High availability
![Page 32: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/32.jpg)
Minimal deployment – Single AZ
Availability Zone
AWS Region
10.1.0.0/16
10.1.1.0/24
Amazon Elastic Block Store Volume
![Page 33: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/33.jpg)
High availability – Multi-AZ
Availability Zone A
AWS Region
10.1.0.0/16
10.1.1.0/24
Availability Zone B
10.1.2.0/24
Replicated storage
Same instance type as master
![Page 34: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/34.jpg)
High availability – Amazon Aurora storage
• Automatically grows up to 64 TB • Automatically replicates across
3 AZs with 2 copies in each AZ • can lose 2 copies
without affecting writes • can lose 3 copies
without affecting reads • Continuously monitors nodes and
disks for repair • 10 GB SSD segments as unit of
repair or hotspot rebalance • Continuously backed up to
Amazon S3
AZ 1 AZ 2 AZ 3
Amazon S3
![Page 35: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/35.jpg)
High availability – Amazon Aurora nodes
• Up to 15 secondary nodes • No log replay, resulting in minimal
replica lag (10 to 20 ms) • Failing database nodes and
processes are automatically detected and replaced
• Secondary nodes automatically promoted on persistent outage, no single point of failure
• Cache survives database restart
AZ 1 AZ 3 AZ 2
Primary Node Primary Node Primary Node
Primary Node Primary Node Secondary Node
Primary Node Primary Node Secondary Node
![Page 36: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/36.jpg)
Simulating Amazon Aurora failures
ALTER SYSTEM CRASH [ INSTANCE | DISPATCHER | NODE ]; ALTER SYSTEM SIMULATE percentage_of_failure PERCENT • READ REPLICA FAILURE [ TO ALL | TO "replica name" ] • DISK FAILURE [ IN DISK index | NODE index ] • DISK CONGESTION BETWEEN minimum AND maximum
MILLISECONDS [ IN DISK index | NODE index ] FOR INTERVAL quantity [ YEAR | QUARTER | MONTH | WEEK| DAY | HOUR | MINUTE | SECOND ];
![Page 37: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/37.jpg)
Failover – MySQL vs Aurora
App Running Failure Detection DNS Propagation
Recovery Recovery
DB Failure
MySQL
App Running
Failure Detection DNS Propagation
Recovery
DB Failure
Aurora with MariaDB driver
1 5 - 3 0 s e c
5 - 2 0 s e c
1 5 - 3 0 s e c
Driver benefits
![Page 38: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/38.jpg)
Tips to improve recovery time with MySQL
• DO NOT use the IP address to connect to RDS! • Set a low TTL on your own CNAME (beware if you use
Java) • Avoid large number of tables :
• No more than 1000 tables using Standard Storage • No more than 10,000 tables using Provisioned IOPS
• Avoid tables in your database growing too large • Make sure you have enough IOPS • Avoid large transactions • Use RDS Events to be notified
![Page 39: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/39.jpg)
Migrating onto RDS
![Page 40: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/40.jpg)
ü Move data to the same or different database engine
ü Keep your apps running during the migration
ü Start your first migration in 10 minutes or less
ü Replicate within, to, or from Amazon EC2 or RDS
AWS Database Migration Service
![Page 41: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/41.jpg)
Customer premises
Application Users
AWS
Internet
VPN
Start a replication instance Connect to source and target database Select tables, schemas, or databases
Let the AWS Database Migration Service create tables, load data, and keep them in sync Switch applications over to the target at your convenience
Keep your apps running during the migration
![Page 42: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/42.jpg)
Migrate from Oracle and SQL Server
Move your tables, views, stored procedures, and data manipulation language (DML) to MySQL, MariaDB, and Amazon Aurora
Highlight where manual edits are needed AWS Schema
Conversion Tool
![Page 43: Amazon RDS Deep Dive](https://reader033.vdocuments.us/reader033/viewer/2022042513/58758be51a28ab901c8b5d5d/html5/thumbnails/43.jpg)
Thank You!