performance and cost with amazon aurora - meetupfiles.meetup.com/1744630/aurora oct 21...
TRANSCRIPT
![Page 1: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/1.jpg)
© 2015, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
October 21, 2015
Performance and Cost with Amazon Aurora
John Loughlin, Solutions Architect, AWS
![Page 2: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/2.jpg)
Agenda
• What it is and why we built it. • Performance • Availability • Scale • Cost
![Page 3: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/3.jpg)
Why we built Amazon Aurora
![Page 4: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/4.jpg)
Current DB architectures are monolithic
Multiple layers of functionality all on a single box
SQL
Transactions
Caching
Logging
![Page 5: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/5.jpg)
Current DB architectures are monolithic
Even when you scale it out, you’re still replicating the same stack
SQL
Transactions
Caching
Logging
SQL
Transactions
Caching
Logging
Application
![Page 6: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/6.jpg)
SQL
Transactions
Caching
Logging
SQL
Transactions
Caching
Logging
Application Even when you scale it out, you’re still replicating the same stack
Current DB architectures are monolithic
![Page 7: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/7.jpg)
SQL
Transactions
Caching
Logging
SQL
Transactions
Caching
Logging
Storage
Application Even when you scale it out, you’re still replicating the same stack
Current DB architectures are monolithic
![Page 8: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/8.jpg)
This is a problem. For cost. For flexibility. And for availability.
![Page 9: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/9.jpg)
What if you were inventing the database today?
You wouldn’t design it the way we did in 1970. At least not entirely. You’d build something that can scale out, that is self-healing, and that leverages existing AWS services.
Reimagining the relational database
![Page 10: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/10.jpg)
Speed and availability of high-end commercial databases Simplicity and cost-effectiveness of open source databases Drop-in compatibility with MySQL Simple pay as you go pricing
Delivered as a managed service
Relational databases reimagined for the cloud
![Page 11: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/11.jpg)
Moved the logging and storage layer into a multi-tenant, scale-out database-optimized storage service Integrated with other AWS services like Amazon EC2, Amazon VPC, Amazon DynamoDB, Amazon SWF, and Amazon Route 53 for control plane operations Integrated with Amazon S3 for continuous backup with 99.999999999% durability
Control Plane Data Plane
Amazon DynamoDB
Amazon SWF
Amazon Route 53
Logging + Storage
SQL
Transactions
Caching
Amazon S3
A service-oriented architecture applied to the database
![Page 12: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/12.jpg)
Amazon Aurora saves you money
![Page 13: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/13.jpg)
Simple pricing No licenses No lock-in Pay only for what you use
Discounts 44% with a 1-year RI 63% with a 3-year RI
vCPU Mem Hourly Price
db.r3.large 2 15.25 $0.29
db.r3.xlarge 4 30.5 $0.58
db.r3.2xlarge 8 61 $1.16
db.r3.4xlarge 16 122 $2.32
db.r3.8xlarge 32 244 $4.64
• Storage consumed, up to 64 TB, is $0.10 / GB-month • I/Os consumed are billed at $0.20 per million I/O • Prices are for Virginia
Enterprise grade, open-source pricing
![Page 14: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/14.jpg)
Cost of ownership: Aurora vs. MySQL MySQL configuration hourly cost
Primary r3.8XL
Standby r3.8XL
Replica r3.8XL
Replica R3.8XL
Storage 6 TB / 10 K PIOP
Storage 6 TB / 10 K PIOP
Storage 6 TB / 5 K PIOP
Storage 6 TB / 5 K PIOP
$3.78/hr
$3.78/hr
$3.78/hr $3.78/hr
$2,42/hr
$2,42/hr $2,42/hr
Instance cost: $15.12 / hr Storage cost: $8.30 / hr Total cost: $23.42 / hr
$2,42/hr
![Page 15: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/15.jpg)
Cost of ownership: Aurora vs. MySQL Aurora configuration hourly cost
Instance cost: $13.92 / hr Storage cost: $4.43 / hr Total cost: $18.35 / hr
Primary r3.8XL
Replica r3.8XL
Replica R3.8XL
Storage / 6 TB
$4.64 / hr $4.64 / hr $4.64 / hr
$4.43 / hr
21.6% Savings
§ No idle standby instance
§ Single shared storage volume
§ No POIPs – pay for use I/O
§ Reduction in overall IOP
![Page 16: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/16.jpg)
Cost of ownership: Aurora vs. MySQL Further opportunity for saving
Instance cost: $6.96 / hr Storage cost: $4.43 / hr Total cost: $11.39 / hr
51.3% Savings
Primary r3.8XL
Replica r3.8XL
Replica r3.8XL
Storage / 6TB
$2.32 / hr $2.32 / hr $2.32 / hr
$4.43 / hr
r3.4XL r3.4XL r3.4XL
§ Use smaller instance size
§ Pay-as-you-go storage
![Page 17: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/17.jpg)
Aurora Works with Your Existing Apps
![Page 18: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/18.jpg)
Query and Monitoring SI and Consulting Data Integration
“It is great to see Amazon Aurora remains MySQL compatible; MariaDB connectors work with Aurora seamlessly. Today, customers can take MariaDB Enterprise with MariaDB MaxScale drivers and connect to Aurora, MariaDB, or MySQL without worrying about compatibility. We look forward to working with the Aurora team in the future to further accelerate innovation within the MySQL ecosystem.” - Roger Levy, VP Products, MariaDB
Business Intelligence
Establishing our ecosystem
![Page 19: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/19.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 AWS EC2 or RDS
AWS Database Migration Service
![Page 20: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/20.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 21: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/21.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 22: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/22.jpg)
1 Establish baseline a) MySQL dump/import
b) RDS MySQL to Aurora DB
snapshot migration
2 Catch-up changes a) Binlog replication
b) Tungsten Replicator
Aurora MySQL
2 - Replication
1 - Baseline
Achieving near zero downtime migration to Aurora
![Page 23: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/23.jpg)
Migrate your RDS Instance using the AWS Console
![Page 24: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/24.jpg)
Amazon Aurora Is Easy to Use
![Page 25: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/25.jpg)
Create a database in minutes Automated patching Push-button scale compute Continuous backups to Amazon S3 Automatic failure detection and failover
Amazon RDS
Simplify database management
![Page 26: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/26.jpg)
Read replicas are available as failover targets—no data loss Instantly create user snapshots—no performance impact Continuous, incremental backups to Amazon S3 Automatic storage scaling up to 64 TB—no performance or availability impact Automatic restriping, mirror repair, hot spot management
Simplify storage management
![Page 27: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/27.jpg)
Backup and recovery, data load and unload
Performance tuning
5%
25%
20%
40%
5% 5%
Scripting and coding
Security planning
Installing, upgrading, patching, and migrating
Documentation, licensing, and training
Databases are hard to manage
![Page 28: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/28.jpg)
Hosting your databases on premises
you Power, HVAC, net
Rack & stack
Server maintenance
OS patches
DB s/w patches
Database backups
Scaling High availability
DB s/w installs
OS installation
App optimization
![Page 29: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/29.jpg)
Hosting your databases in Amazon EC2
Power, HVAC, net
Rack & stack
Server maintenance
OS installation
OS patches
DB s/w patches
Database backups
Scaling
High availability
DB s/w installs
App optimization
you
![Page 30: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/30.jpg)
If you choose a managed DB service
App optimization
Power, HVAC, net
Rack & stack
Server maintenance
OS patches
DB s/w patches
Database backups
High availability
DB s/w installs
OS installation
Scaling
you
![Page 31: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/31.jpg)
Amazon Aurora Is Highly Available
![Page 32: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/32.jpg)
Highly available by default • 6-way replication across 3 AZs • 4 of 6 write quorum
• Automatic fallback to 3 of 4 if an Availability Zone (AZ) is unavailable
• 3 of 6 read quorum
SSD, scale-out, multi-tenant storage • Seamless storage scalability • Up to 64 TB database size • Only pay for what you use
Log-structured storage • Many small segments, each with their own redo logs • Log pages used to generate data pages • Eliminates chatter between database and storage
SQL
Transactions
AZ 1 AZ 2 AZ 3
Caching
Amazon S3
Aurora storage
![Page 33: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/33.jpg)
Lose two copies or an AZ failure without read or write availability impact
Lose three copies without read availability impact
Automatic detection, replication, and repair
SQL
Transaction
AZ 1 AZ 2 AZ 3
Caching
SQL
Transaction
AZ 1 AZ 2 AZ 3
Caching
Read and write availability Read availability
Self-healing, fault-tolerant
![Page 34: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/34.jpg)
Traditional databases Have to replay logs since the last checkpoint Single-threaded in MySQL; requires a large number of disk accesses
Amazon Aurora Underlying storage replays redo records on demand as part of a disk read Parallel, distributed, asynchronous
Checkpointed Data Redo Log
Crash at T0 requires a re-application of the SQL in the redo log since last checkpoint
T0 T0
Crash at T0 will result in redo logs being applied to each segment on demand, in parallel, asynchronously
Instant crash recovery
![Page 35: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/35.jpg)
We moved the cache out of the database process Cache remains warm in the event of a database restart Lets you resume fully loaded operations much faster Instant crash recovery + survivable cache = quick and easy recovery from DB failures
SQL Transactions
Caching
SQL
Transactions
Caching
SQL Transactions
Caching
Caching process is outside the DB process and remains warm across a database restart
Survivable caches
![Page 36: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/36.jpg)
Aurora replicas can be promoted instantly
Page cache invalidation
Aurora Master
30% Read
70% Write
Aurora Replica
100% New Reads
Shared Multi-AZ Storage
MySQL Master
30% Read
70% Write
MySQL Replica
30% New Reads
70% Write
Single-threaded binlog apply
Data Volume Data Volume
MySQL read scaling Replicas must replay logs Replicas place additional load on master Replica lag can grow indefinitely Failover results in data loss
![Page 37: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/37.jpg)
To cause the failure of a component at the database node: ALTER SYSTEM CRASH [{INSTANCE | DISPATCHER | NODE}] To simulate the failure of disks: ALTER SYSTEM SIMULATE percent_failure DISK failure_type IN [DISK index | NODE index] FOR INTERVAL interval To simulate the failure of networking: ALTER SYSTEM SIMULATE percent_failure NETWORK failure_type [TO {ALL | read_replica | availability_zone}] FOR INTERVAL interval To simulate the failure of an Aurora Replica: ALTER SYSTEM SIMULATE percentage_of_failure PERCENT READ REPLICA FAILURE [TO ALL | TO "replica name"] FOR INTERVAL interval
Simulate failures using SQL
![Page 38: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/38.jpg)
Amazon Aurora Is Fast
![Page 39: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/39.jpg)
4 client machines with 1,000 connections each
WRITE PERFORMANCE READ PERFORMANCE
Single cl ient machine with 1,600 connections
MySQL SysBench
R3.8XL with 32 cores and 244 GB RAM
SQL benchmark results
![Page 40: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/40.jpg)
Reproducing these results
h t t p s : / / d 0 . a w s s t a t i c . c o m / p r o d u c t - m a r k e t i n g / A u r o r a / R D S _ A u r o r a _ P e r f o r m a n c e _ A s s e s s m e n t _ B e n c h m a r k i n g _ v 1 - 2 . p d f
AMAZON AURORA
R3.8XLARGE
R3.8XLARGE
R3.8XLARGE
R3.8XLARGE
R3.8XLARGE
• Create an Amazon VPC (or use an existing one).
• Create four EC2 R3.8XL client instances to run the SysBench client. All four should be in the same AZ.
• Enable enhanced networking on your clients.
• Tune your Linux settings (see whitepaper).
• Install Sysbench version 0.5.
• Launch a r3.8xlarge Amazon Aurora DB instance in the same VPC and AZ as your clients.
• Start your benchmark!
1
2
3
4
5
6
7
![Page 41: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/41.jpg)
Beyond benchmarks
If only real-world applications saw benchmark performance DISTORTIONS IN THE REAL WORLD
Requests contend with each other Metadata rarely fits in data dictionary cache Data rarely fits in buffer cache Production databases need to run HA
![Page 42: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/42.jpg)
Scaling user connections
SysBench OLTP workload 250 tables
Connections Amazon Aurora RDS MySQL
30K IOPS (single AZ)
50 40,000 10,000
500 71,000 21,000
5,000 110,000 13,000
8x U P TO
FA S T E R
![Page 43: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/43.jpg)
Scaling table count
Tables Amazon Aurora
MySQL I2.8XL
local SSD
MySQL I2.8XL
RAM disk
RDS MySQL
30K IOPS (single AZ)
10 60,000 18,000 22,000 25,000
100 66,000 19,000 24,000 23,000
1,000 64,000 7,000 18,000 8,000
10,000 54,000 4,000 8,000 5,000
SysBench write-only workload 1,000 connections Default settings
11x U P TO
FA S T E R
![Page 44: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/44.jpg)
Scaling data set size
DB Size Amazon Aurora RDS MySQL
30K IOPS (single AZ)
1GB 107,000 8,400
10GB 107,000 2,400
100GB 101,000 1,500
1TB 26,000 1,200
67x U P TO
FA S T E R
SYSBENCH WRITE-ONLY
DB Size Amazon Aurora RDS MySQL
30K IOPS (single AZ)
80GB 12,582 585
800GB 9,406 69
CLOUDHARMONY TPC-C
136x U P TO
FA S T E R
![Page 45: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/45.jpg)
Running with read replicas
Updates per second Amazon Aurora
RDS MySQL 30K IOPS (single AZ)
1,000 2.62 ms 0 s
2,000 3.42 ms 1 s
5,000 3.94 ms 60 s
10,000 5.38 ms 300 s
SysBench write-only workload 250 tables
500x U P TO
L O W E R L A G
![Page 46: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/46.jpg)
Do fewer IOs
Minimize network packets
Cache prior results
Offload the database engine
DO LESS WORK
Process asynchronously
Reduce latency path
Use lock-free data structures
Batch operations together
BE MORE EFFICIENT
How do we achieve these results?
DATABASES ARE ALL ABOUT I/O
NETWORK-ATTACHED STORAGE IS ALL ABOUT PACKETS/SECOND
HIGH-THROUGHPUT PROCESSING DOES NOT ALLOW CONTEXT SWITCHES
![Page 47: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/47.jpg)
IO traffic in RDS MySQL
BINLOG DATA DOUBLE-WRITE LOG FRM FILES
T Y P E O F W R I T E
MYSQL WITH STANDBY
Issue write to EBS – EBS issues to mirror, ack when both done Stage write to standby instance using DRBD Issue write to EBS on standby instance
IO FLOW
Steps 1, 3, 5 are sequential and synchronous This amplifies both latency and jitter Many types of writes for each user operation Have to write data blocks twice to avoid torn writes
OBSERVATIONS
780K transactions 7,388K I/Os per million txns (excludes mirroring, standby) Average 7.4 I/Os per transaction
PERFORMANCE
30 minute SysBench write-only workload, 100 GB data set, RDS SingleAZ, 30K PIOPS
EBS mirror EBS mirror
AZ 1 AZ 2
Amazon S3
EBS Amazon Elastic
Block Store (EBS)
Primary instance
Standby instance
1
2
3
4
5
![Page 48: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/48.jpg)
IO traffic in Aurora (database)
AZ 1 AZ 3
Primary instance
Amazon S3
AZ 2
Replica instance
AMAZON AURORA
ASYNC 4/6 QUORUM
DISTRIBUTED WRITES
BINLOG DATA DOUBLE-WRITE LOG FRM FILES
T Y P E O F W R I T E
30 minute SysBench write-only workload, 100 GB data set
IO FLOW
Only write redo log records; all steps asynchronous No data block writes (checkpoint, cache replacement) 6X more log writes, but 9X less network traffic Tolerant of network and storage outlier latency
OBSERVATIONS
27,378K transactions 35X MORE 950K I/Os per 1M txns (6X amplification) 7.7X LESS
PERFORMANCE
Boxcar redo log records – fully ordered by LSN Shuffle to appropriate segments – partially ordered Boxcar to storage nodes and issue writes
![Page 49: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/49.jpg)
IO traffic in Aurora (storage node)
LOG RECORDS
Primary instance
INCOMING QUEUE
STORAGE NODE
S3 BACKUP
1
2
3
4
5
6
7
8 UPDATE QUEUE
ACK
HOT LOG
DATA BLOCKS
POINT IN TIME SNAPSHOT
GC
SCRUB COALESCE
SORT GROUP
PEER-TO-PEER GOSSIP Peer storage nodes
All steps are asynchronous Only steps 1 and 2 are in foreground latency path Input queue is 46X less than MySQL (unamplified, per node) Favor latency-sensitive operations Use disk space to buffer against spikes in activity
OBSERVATIONS
IO FLOW
① Receive record and add to in-memory queue ② Persist record and ACK ③ Organize records and identify gaps in log ④ Gossip with peers to fill in holes ⑤ Coalesce log records into new data block versions ⑥ Periodically stage log and new block versions to S3 ⑦ Periodically garbage collect old versions ⑧ Periodically validate CRC codes on blocks
![Page 50: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/50.jpg)
Asynchronous group commits
Read
Write
Commit
Read
Read
T1
Commit (T1)
Commit (T2)
Commit (T3)
LSN 10
LSN 12
LSN 22
LSN 50
LSN 30
LSN 34
LSN 41
LSN 47
LSN 20
LSN 49
Commit (T4)
Commit (T5)
Commit (T6)
Commit (T7)
Commit (T8)
LSN GROWTH Durable LSN at head-node
COMMIT QUEUE Pending commits in LSN order
TIME
GROUP COMMIT
TRANSACTIONS
Read
Write
Commit
Read
Read
T1
Read
Write
Commit
Read
Read
Tn
TRADITIONAL APPROACH AMAZON AURORA Maintain a buffer of log records to write out to disk
Issue write when buffer full or time out waiting for writes
First writer has latency penalty when write rate is low
Request I/O with first write, fill buffer till write picked up
Individual write durable when 4 of 6 storage nodes ACK
Advance DB durable point up to earliest pending ACK
![Page 51: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/51.jpg)
Re-entrant connections multiplexed to active threads
Kernel-space epoll() inserts into latch-free event queue
Dynamically size threads pool
Gracefully handles 5,000+ concurrent client sessions on r3.8xl
Standard MySQL – one thread per connection
Doesn’t scale with connection count
MySQL EE – connections assigned to thread group
Requires careful stall threshold tuning
CLI
EN
T C
ON
NE
CTI
ON
CLI
EN
T C
ON
NE
CTI
ON
LATCH FREE TASK QUEUE
epol
l()
MYSQL THREAD MODEL AURORA THREAD MODEL
Adaptive thread pool
![Page 52: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/52.jpg)
IO traffic in Aurora (read replica)
PAGE CACHE UPDATE
Aurora master
30% Read
70% Write
Aurora replica
100% New Reads
Shared Multi-AZ Storage
MySQL master
30% Read
70% Write
MySQL replica
30% New Reads
70% Write
SINGLE-THREADED BINLOG APPLY
Data volume Data volume
Logical: Ship SQL statements to replica.
Write workload similar on both instances.
Independent storage.
Can result in data drift between master and replica.
Physical: Ship redo from master to replica.
Replica shares storage. No writes performed.
Cached pages have redo applied.
Advance read view when all commits seen.
MYSQL READ SCALING AMAZON AURORA READ SCALING
![Page 53: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/53.jpg)
Improvements over the past few months
Write batch size tuning Asynchronous send for read/write I/Os Purge thread performance Bulk insert performance
BATCH OPERATIONS
Failover time reductions Malloc reduction System call reductions Undo slot caching patterns Cooperative log apply
OTHER Binlog and distributed transactions Lock compression Read-ahead
CUSTOMER FEEDBACK
Hot row contention Dictionary statistics Mini-transaction commit code path Query cache read/write conflicts Dictionary system mutex
LOCK CONTENTION
![Page 54: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/54.jpg)
Getting started aws.amazon.com/rds/aurora aws.amazon.com/rds/aurora/getting-started
![Page 55: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/55.jpg)
Questions?
aws.amazon.com/rds/aurora
![Page 56: Performance and Cost with Amazon Aurora - Meetupfiles.meetup.com/1744630/Aurora Oct 21 Meetup.pdf · 2015-10-22 · Performance and Cost with Amazon Aurora John Loughlin, Solutions](https://reader033.vdocuments.us/reader033/viewer/2022041918/5e6a9994f0347950916493b1/html5/thumbnails/56.jpg)
Thank you!
aws.amazon.com/rds/aurora