Deep Dive on Amazon Aurora

1 .Deep Dive on Amazon Aurora Kevin Jernigan kmj@Amazon.com Principal Product Manager, Amazon Aurora Amazon Web Services 

2 .

3 .Agenda • Aurora overview • Performance improvements • Availability improvements • Recent innovations © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

4 .“Intuit invests significantly to own and operate high-end commercial databases underpinning our business. Until now, there just wasn’t a real alternative to obtain the reliability and performance our customers need. Amazon Aurora is a game- changer for us: providing the performance and availability features that rival expensive on-premises databases and SANs at a significantly lower price point. The RDS management capabilities on top of Amazon Aurora will allow us to focus our resources and energy on what matters most – building great applications and delighting our customers.” Troy Otillio, Director, Public Cloud, Intuit © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

5 .Wh at is Amazon Au ro ra? Databas e reimag in ed for the cloud  Speed and availability of high-end commercial databases  Simplicity and cost-effectiveness of open source databases  Drop-in compatibility with MySQL and PostgreSQL  Simple pay as you go pricing Delivered as a managed service © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

6 .Re-imagining the relational database 1 Scale-out, distributed architecture 2 Service-oriented architecture leveraging AWS services 3 Automate administrative tasks – fully managed service © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

7 . Scale-out, distributed architecture Availability Availability Availability ▪ Purpose-built log-structured distributed Zone 1 Zone 2 Zone 3 storage system designed for databases SQL SQL SQL ▪ Storage volume is striped across hundreds Transactions Transactions Transactions of storage nodes distributed over 3 Master Replica Caching Caching Caching different availability zones ▪ Six copies of data, two copies in each availability zone to protect against AZ+1 Shared storage volume failures ▪ Plan to apply same principles to other layers of the stack Storage nodes with SSDs © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

8 .L e v e r a g i n g AW S s e r v i c e s Lambda Invoke Lambda functions from stored procedures/triggers S3 Load data from/ Select into S3, store snapshots and backups in S3 IAM Use IAM roles to manage database access control CloudWatch Upload systems metrics and database logs © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

9 .Au tomate ad ministrative tasks Automatic fail-over Schema design Backup & recovery Query construction You Isolation & security Query optimization Industry compliance AWS Push-button scaling Automated patching Advanced monitoring Routine maintenance Takes care of your time-consuming database management tasks, freeing you to focus on your applications and business © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

10 . Aurora customer adoption Fastest growing service in AWS history Aurora is used by ¾ of the top 100 AWS customers © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

11 .Who is moving to Aurora and why? ▪ Higher performance – up to 5x Customers using ▪ Better availability and durability open source engines ▪ Reduces cost – up to 60% ▪ Easy migration; no application change ▪ One tenth of the cost; no licenses Customers using ▪ Integration with cloud ecosystem Commercial engines ▪ Comparable performance and availability ▪ Migration tooling and services © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

12 . A MAZON A URORA I S FA S T … 5x faster than MySQL; 3x faster than PostgreSQL © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

13 . Aurora MySQL performance WRITE PERFORMANCE READ PERFORMANCE 700000 250000 600000 200000 500000 150000 400000 300000 100000 200000 50000 100000 0 0 MySQL SysBench results; R4.16XL: 64cores / 488 GB RAM Aurora MySQL 5.6 Aurora read write throughput compared to MySQL 5.6 based on industry standard benchmarks. © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

14 . Aurora PostgreSQL performance While running pgbench at load, throughput is 3x more consistent than PostgreSQL pgbench throughput over time, 150 GiB, 1024 clients 45000 40000 35000 30000 Throughput, tps 25000 20000 15000 10000 5000 0 10 15 20 25 30 35 40 45 50 55 60 Aurora PostgreSQL Minutes PostgreSQL (single-AZ) © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

15 .How did we achieve this? DO LESS WORK BE MORE EFFICIENT Do fewer IOs Process asynchronously Minimize network packets Reduce latency path Cache prior results Use lock-free data structures Offload the database engine Batch operations together DATABASES ARE ALL ABOUT I/O NETWORK-ATTACHED STORAGE IS ALL ABOUT PACKETS/SECOND HIGH-THROUGHPUT PROCESSING IS ALL ABOUT CONTEXT SWITCHES © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

16 .Aurora I/O profile MYSQL WITH REPLICA AMAZON AURORA AZ 1 3 AZ 2 AZ 1 AZ 2 AZ 3 Primary Replica Primary Replica Replica Instance Instance Instance Instance Instance ASYNC 1 4 4/6 QUORUM Amazon Elastic Block Store (EBS) EBS 2 5 DISTRIBUT ED WRITES EBS mirror EBS mirror Amazon S3 Amazon S3 MySQL I/O profile for 30 min Sysbench run Aurora IO profile for 30 min Sysbench run 780K transactions 27,378K transactions 35X MORE 7,388K I/Os per million txns (excludes mirroring, standby) 0.95 I/Os per transaction (6X amplification) 7.7X LESS Average 7.4 I/Os per transaction T Y PE OF W R I T E LOG BINLOG DATA © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. DOUBLE-WRITE FRM FILES 

17 .IO t raff ic in Au ro ra (sto rage n o d e ) STORAGE NODE IO FLOW 7 ① Receive record and add to in-memory queue LOG RECORDS INCOMING QUEUE 1 ② Persist record and ACK Primary ACK GC ③ Organize records and identify gaps in log Instance 2 UPDATE ④ Gossip with peers to fill in holes QUEUE COALESCE DATA ⑤ Coalesce log records into new data block versions BLOCKS SCRUB 8 5 ⑥ Periodically stage log and new block versions to S3 SORT GROUP 3 ⑦ Periodically garbage collect old versions Peer ⑧ Periodically validate CRC codes on blocks PEER TO PEER GOSSIP HOT Storage LOG Nodes 4 POINT IN TIME OBSERVATIONS SNAPSHOT All steps are asynchronous 6 Only steps 1 and 2 are in foreground latency path Input queue is 46X less than MySQL (unamplified, per node) S3 BACKUP Favor latency-sensitive operations Use disk space to buffer against spikes in activity © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

18 .Performance enhancements in Aurora ► Adaptive thread pool ► Fast B-Tree inserts DML throughput ► Smart thread scheduler ► Z-order spatial indexes ► Asynchronous group commit ► Smart read-node selector ► Latch-free lock manager ► Logical read ahead ► Lock compression ► NUMA aware scheduler ► Latch-free read views ► Highly concurrent catalog ► Hash joins ► Asynchronous key prefetch Query execution ► Batched scans ► Instant schema update ► High-performance auditing DDL and Ops ► Faster index build © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

19 . W H AT A B O U T AVA I L A B I L I T Y ? “Performance only matters if your database is up” © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

20 .6-way replicated storage Sur vive s catastro ph ic failure s Six copies across three availability zones 4 out 6 write quorum; 3 out of 6 read quorum Peer-to-peer replication for repairs Volume striped across hundreds of storage nodes AZ 1 AZ 2 AZ 3 AZ 1 AZ 2 AZ 3 SQL SQL Transaction Transaction Caching Caching Read availability Read and write availability © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

21 .Up to 15 promotable read replicas Reader end-point Read Read Read Master Replica Replica Replica Shared distributed storage volume ► Up to 15 promotable read replicas across multiple availability zones ► Re-do log based replication leads to low replica lag – typically < 10ms ► Reader end-point with load balancing and auto-scaling © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

22 .Availability enhancements in Aurora ► Instant crash recovery Unplanned unavailability ► Survivable cache ► Fast failover, incl. driver support Planned unavailability ► Zero-downtime patching Business continuity ► Continuous automated backup planning ► Point-in-Time-Restore ► Backtrack ► Cross-region read replicas 

23 .Instant crash recovery TRADITIONAL DATABASE AMAZON AURORA Have to replay logs since the last checkpoint Underlying storage replays redo records on demand as part of a disk read Typically 5 minutes between checkpoints Parallel, distributed, asynchronous Single-threaded in MySQL; requires a large number of disk accesses No replay for startup Crash at T0 will result in redo logs being applied to each segment on demand, in parallel, asynchronously Crash at T0 requires a re-application of the SQL in the redo log since last checkpoint Checkpointed Data Redo Log T0 T0 © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

24 .Database fail-over time 0 - 5s – 30% of fail-overs 5 - 10s – 40% of fail-overs 35,00% 50,00% 30,00% 45,00% 40,00% 25,00% 35,00% 20,00% 30,00% 15,00% 25,00% 20,00% 10,00% 15,00% 5,00% 10,00% 0,00% 5,00% 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 0,00% 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 10 - 20s – 25% of fail-overs 20 - 30s – 5% of fail-overs 60% 20% 50% 40% 15% 30% 10% 20% 5% 10% 0% 0% 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 1 3 5 7 9 11 13 15 17 19 21 23 25 ©27 29Amazon 2018, 31 33Web35 Services, Inc. or its affiliates. All rights reserved. 

25 . Zero downtime patching Net App Before ZDP state state Old DB Engine Net App state state New DB User sessions terminate Engine Storage Service during patching With ZDP Old DB Application Engine Networking state state New DB User sessions remain Engine Storage Service active through patching © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

26 .R E C E N T I N N O VAT I O N S © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

27 .Fast d atabase clo n ing BENCHMARKS Clone database without copying data ▪ Creation of a clone is nearly instantaneous DEV/TEST ▪ Data copy happens only on write – when original APPLICATIONS CLONE and cloned volume data differ Example use cases ▪ Clone a production DB to run tests CLONE CLONE ▪ Reorganize a database PRODUCTION PRODUCTION ▪ Save a point in time snapshot for analysis without APPLICATIONS APPLICATIONS impacting production system. PRODUCTION DATABASE © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

28 . Database backtrack Invisible t4 Invisible t2 t3 Rewind to t3 t0 t1 Rewind to t1 t0 t1 t2 t3 t4 Backtrack brings the database to a point in time without requiring restore from backups • Backtracking from an unintentional DML or DDL operation • Backtrack is not destructive. You can backtrack multiple times to find the right point in time © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. 

29 .Ho w d o e s b acktrack wo rk? SEGMENT LOG SNAPSHOT RECORDS SEGMENT 1 SEGMENT 2 SEGMENT 3 RECOVERY TIME POINT We keep periodic snapshot of each segment; we also preserve the redo logs For backtrack, we identify the appropriate segment snapshots Apply log streams to segment snapshots in parallel and asynchronously © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.