new launch! introducing postgresql compatibility for amazon aurora
TRANSCRIPT
![Page 1: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/1.jpg)
© 2016, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Mark Porter, General Manager
Kevin Jernigan, Senior Product Manager
Amazon Aurora (PostgreSQL)
Amazon RDS for PostgreSQL
December 1, 2016
NEW LAUNCH!
Introducing PostgreSQL Compatibility
for Amazon Aurora
DAT206
![Page 2: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/2.jpg)
Agenda
Why did we build Amazon Aurora?
How is Amazon Aurora doing?
Why add PostgreSQL compatibility?
Durability and Availability Architecture
Performance Results vs. PostgreSQL
Performance Architecture
Announcing Performance Insights
Feature Roadmap
Preview Information & Questions
+
![Page 3: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/3.jpg)
Traditional relational databases are hard to scale
Multiple layers of
functionality all in a
monolithic stack
SQL
Transactions
Caching
Logging
Storage
![Page 4: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/4.jpg)
Traditional approaches to scale databases
Each architecture is limited by the monolithic mindset
SQL
Transactions
Caching
Logging
SQL
Transactions
Caching
Logging
Application Application
SQL
Transactions
Caching
Logging
SQL
Transactions
Caching
Logging
Storage
Application
Storage Storage
SQL
Transactions
Caching
Logging
Storage
SQL
Transactions
Caching
Logging
Storage
![Page 5: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/5.jpg)
Reimagining the relational database
What if you were inventing the database today?
You would break apart the stack
You would build something that:
Can scale out…
Is self-healing…
Leverages distributed services…
![Page 6: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/6.jpg)
A service-oriented architecture applied to the database
Move the logging and storage layer into a
multitenant, scale-out, database-optimized
storage service
Integrate with other AWS services like
Amazon EC2, Amazon VPC, Amazon
DynamoDB, Amazon SWF, and Amazon
Route 53 for control & monitoring
Make it a managed service – using Amazon
RDS. Takes care of management and
administrative functions.
Amazon
DynamoDB
Amazon SWF
Amazon Route 53
Logging + Storage
SQL
Transactions
Caching
Amazon S3
1
2
3
Amazon RDS
![Page 7: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/7.jpg)
Cloud-optimized relational database
Performance and availability of
commercial databases
Simplicity and cost-effectiveness of
open source databases,
with MySQL compatibility
What is Amazon Aurora?
![Page 8: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/8.jpg)
How is Amazon Aurora doing?
![Page 9: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/9.jpg)
“Our first tests of Aurora were difficult to believe because the performance
increase was substantial… Aurora made our migration from traditional
colocation to AWS easier because the storage was fully managed and
replication was extremely fast.”
- Mark Smallcombe, CTO
“…if you're using Aurora, you should think about using read replicas because
the replica lag is really a game changer compared to regular MySQL.”
- Advait Shinde, CTO and co-founder
“Amazon Aurora was able to satisfy all of our scale requirements with no
degradation in performance. With Alfresco on Amazon Aurora we scaled to 1
billion documents with a throughput of 3 million per hour, which is 10 times
faster than our MySQL environment!"
- John Newton, Founder and CTO of Alfresco”
"After 8 months of production, Aurora has been nothing short of impressive…
We love that so far Aurora has delivered the necessary performance without
any of the operational overhead of running MySQL.”
– Chris Broglie, Architect
![Page 10: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/10.jpg)
Amazon Aurora –
Higher Performance at Lower Cost
• Fewer instances needed
• Smaller instances can be used
• No need to pre-provision storage
• No additional storage for read replicas
Safe.com lowered their AWS database bill by 40% by switching
from sharded MySQL to a single Amazon Aurora instance.
Double Down Interactive (gaming) lowered their bill by 67%
while also achieving better latencies (most queries ran faster)
and lower CPU utilization.
![Page 11: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/11.jpg)
So what’s next?
![Page 12: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/12.jpg)
In 2014, we launched Amazon Aurora with MySQL compatibility.
Now, we are adding PostgreSQL compatibility.
Customers can now choose how to use Amazon’s
cloud-optimized relational database, with the performance and
availability of commercial databases and the simplicity and cost-
effectiveness of open source databases.
Making Amazon Aurora Better
![Page 13: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/13.jpg)
Start With the Customer – Why Add PostgreSQL?
![Page 14: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/14.jpg)
Start With the Customer – Why Add PostgreSQL?
![Page 15: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/15.jpg)
Customer Migration Scenarios to Amazon Aurora
Migrate from Amazon EC2 or on-premises
Migrate from Amazon RDS for PostgreSQL
Migrate Oracle and SQL Server applications
Build new applications
![Page 16: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/16.jpg)
Open source database
In active development for 20 years
Owned by a foundation, not a single company
Permissive innovation-friendly open source license
PostgreSQL Fast Facts
Open Source Initiative
![Page 17: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/17.jpg)
High performance out of the box
Object-oriented and ANSI-SQL:2008 compatible
Most geospatial features of any open-source database
Supports stored procedures in 12 languages (Java, Perl,
Python, Ruby, Tcl, C/C++, its own Oracle-like PL/pgSQL,
etc.)
PostgreSQL Fast Facts
![Page 18: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/18.jpg)
Most Oracle-compatible open-source database
Highest AWS Schema Conversion Tool automatic
conversion rates are from Oracle to PostgreSQL
PostgreSQL Fast Facts
![Page 19: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/19.jpg)
What does PostgreSQL compatibility mean?
PostgreSQL 9.6 + Amazon Aurora cloud-optimized storage
Performance: Up to 2x+ better performance than PostgreSQL alone
Availability: failover time of < 30 seconds
Durability: 6 copies across 3 Availability Zones
Read Replicas: single-digit millisecond lag times on up to 15 replicas
Amazon Aurora Storage
![Page 20: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/20.jpg)
What does PostgreSQL compatibility mean?
Cloud-native security and encryption
AWS Key Management Service (KMS) and AWS
Identity and Access Management (IAM)
Easy to manage with Amazon RDS
Easy to load and unload
AWS Database Migration Service and AWS Schema
Conversion Tool
Fully compatible with PostgreSQL, now and for the
foreseeable future
Not a compatibility layer – native PostgreSQL
implementation
AWS DMS
Amazon RDS
PostgreSQL
![Page 21: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/21.jpg)
Amazon Aurora with PostgreSQL Compatibility
Durability & Availability
![Page 22: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/22.jpg)
Scale-out, distributed, log structured storage
Master Replica Replica Replica
Availability Zone 1
Shared Storage Volume – Transaction Aware
Primary
Database
Node
Read
Replica /
Secondary
Node
Read
Replica /
Secondary
Node
Read
Replica /
Secondary
Node
Availability Zone 2 Availability Zone 3
AWS Region
Storage
Monitoring
Database and
Instance
Monitoring
![Page 23: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/23.jpg)
Amazon Aurora Storage Engine Overview
Data is replicated 6 times across 3 Availability Zones
Continuous backup to Amazon S3
(built for 11 9s durability)
Continuous monitoring of nodes and disks for repair
10GB segments as unit of repair or hotspot rebalance
Quorum system for read/write; latency tolerant
Quorum membership changes do not stall writes
Storage volume automatically grows up to 64 TB
AZ 1 AZ 2 AZ 3
Amazon S3
Database
Node
Storage
Node
Storage
Node
Storage
Node
Storage
Node
Storage
Node
Storage
Node
Storage
Monitoring
![Page 24: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/24.jpg)
What can fail?
Segment failures (disks)
Node failures (machines)
AZ failures (network or datacenter)
Optimizations
4 out of 6 write quorum
3 out of 6 read quorum
Peer-to-peer replication for repairs
SQL
Transaction
AZ 1 AZ 2 AZ 3
Caching
SQL
Transaction
AZ 1 AZ 2 AZ 3
Caching
Amazon Aurora Storage Engine Fault-tolerance
![Page 25: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/25.jpg)
Amazon Aurora Replicas
AvailabilityFailing database nodes are automatically
detected and replaced
Failing database processes are
automatically detected and recycled
Replicas are automatically promoted to
primary if needed (failover)
Customer specifiable fail-over order
AZ 1 AZ 3AZ 2
Primary
NodePrimary
Node
Primary
Database
Node
Primary
NodePrimary
NodeRead
Replica
Primary
NodePrimary
NodeRead
Replica
Database
and
Instance
Monitoring
PerformanceCustomer applications can scale out read traffic
across read replicas
Read balancing across read replicas
![Page 26: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/26.jpg)
Amazon Aurora Continuous Backup
Segment snapshot Log records
Recovery point
Segment 1
Segment 2
Segment 3
Time
• Take periodic snapshot of each segment in parallel; stream the logs to Amazon S3
• Backup happens continuously without performance or availability impact
• At restore, retrieve the appropriate segment snapshots and log streams to storage nodes
• Apply log streams to segment snapshots in parallel and asynchronously
![Page 27: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/27.jpg)
Traditional databases
Have to replay logs since the last
checkpoint
Typically 5 minutes between checkpoints
Single-threaded in MySQL and
PostgreSQL; requires a large number of
disk accesses
Amazon Aurora
No replay at startup because storage system
is transaction-aware
Underlying storage replays log records
continuously, whether in recovery or not
Coalescing is parallel, distributed, and
asynchronous
Checkpointed Data Log
Crash at T0 requires
a re-application of the
SQL in the log since
last checkpoint
T0 T0
Crash at T0 will result in logs being applied to
each segment on demand, in parallel,
asynchronously
Amazon Aurora Instant Crash Recovery
![Page 28: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/28.jpg)
Faster, more predictable failover with Amazon Aurora
AppRunningFailure Detection DNS Propagation
Recovery
DatabaseFailure
Amazon RDS for PostgreSQL is good: failover times of ~60 seconds
Replica-Aware App Running
Failure Detection DNS Propagation
Recovery
Database
Failure
Amazon Aurora is better: failover times < 30 seconds
1 5 - 2 0 s e c 3 - 1 0 s e c
App
Running
![Page 29: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/29.jpg)
Amazon Aurora with PostgreSQL Compatibility
Performance vs. PostgreSQL
![Page 30: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/30.jpg)
PostgreSQL
Benchmark System Configurations
Amazon Aurora
AZ 1
EBS EBS EBS
45,000 total IOPS
AZ 1 AZ 2 AZ 3
Amazon S3
m4.16xlarge
database
instance
Storage
Node
Storage
Node
Storage
Node
Storage
Node
Storage
Node
Storage
Node
c4.8xlarge
client driver
m4.16xlarge
database
instance
c4.8xlarge
client driver
ext4 filesystem
m4.16xlarge (64 VCPU, 256GiB), c4.8xlarge (36 VCPU, 60GiB)
![Page 31: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/31.jpg)
Amazon Aurora is >=2x Faster on PgBench
pgbench “tpcb-like” workload, scale 2000 (30GiB). All configurations run for 60 minutes
![Page 32: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/32.jpg)
Amazon Aurora is 2x-3x Faster on SysBench
Amazon Aurora delivers 2x the absolute peak of PostgreSQL and 3x
PostgreSQL performance at high client counts
SysBench oltp(write-only) workload with 30 GB database with 250 tables and 400,000 initial rows per table
![Page 33: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/33.jpg)
Amazon Aurora: Over 120,000 Writes/Sec
OLTP test statistics:
queries performed:
read: 0
write: 432772903
other:(begin + commit) 216366749
total: 649139652
transactions: 108163671 (30044.73 per sec.)
read/write requests: 432772903 (120211.75 per sec.)
other operations: 216366749 (60100.40 per sec.)
ignored errors: 39407 (10.95 per sec.)
reconnects: 0 (0.00 per sec.)
sysbench write-only 10GB workload with 250 tables and 25,000 initial rows per table. 10-minute warmup, 3,076 clients
Ignored errors are key constraint errors, designed into sysbench
Sustained sysbench throughput over 120K writes/sec
![Page 34: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/34.jpg)
Amazon Aurora Loads Data 3x Faster
Database initialization is three times faster than PostgreSQL using the
standard PgBench benchmark
Command: pgbench -i -s 2000 –F 90
![Page 35: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/35.jpg)
Amazon Aurora Gives >2x Faster Response Times
Response time under heavy write load >2x faster than PostgreSQL
(and >10x more consistent)
SysBench oltp(write-only) 23GiB workload with 250 tables and 300,000 initial rows per table. 10-minute warmup.
![Page 36: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/36.jpg)
Amazon Aurora Has More Consistent Throughput
While running at load, performance is more than three times
more consistent than PostgreSQL
PgBench “tpcb-like” workload at scale 2000. Amazon Aurora was run with 1280 clients. PostgreSQL was run with
512 clients (the concurrency at which it delivered the best overall throughput)
![Page 37: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/37.jpg)
Amazon Aurora is 3x Faster at Large Scale
Scales from 1.5x to 3x faster as database grows from 10 GiB to 100 GiB
SysBench oltp(write-only) – 10GiB with 250 tables & 150,000 rows and 100GiB with 250 tables & 1,500,000 rows
75,666
27,491
112,390
82,714
0
20,000
40,000
60,000
80,000
100,000
120,000
10GB 100GB
write
s /
sec
SysBench Test Size
SysBench write-only
PostgreSQL Amazon Aurora
![Page 38: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/38.jpg)
Amazon Aurora Delivers up to 85x Faster Recovery
SysBench oltp(write-only) 10GiB workload with 250 tables & 150,000 rows
Writes per Second 69,620
Writes per Second 32,765
Writes per Second 16,075
Writes per Second 92,415
Recovery Time (seconds) 102.0
Recovery Time (seconds) 52.0
Recovery Time (seconds) 13.0
Recovery Time (seconds) 1.2
0 20 40 60 80 100 120 140
0 20,000 40,000 60,000 80,000
PostgreSQL12.5GB
Checkpoint
PostgreSQL8.3GB
Checkpoint
PostgreSQL2.1GB
Checkpoint
Amazon AuroraNo Checkpoints
Recovery Time in Seconds
Writes Per Second
Crash Recovery Time - SysBench 10GB Write Workload
Transaction-aware storage system recovers almost instantly
![Page 39: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/39.jpg)
Amazon Aurora with PostgreSQL Compatibility
Performance By The Numbers
Measurement Result
PgBench >= 2x faster
SysBench 2x-3x faster
Data Loading 3x faster
Response Time >2x faster
Throughput Jitter >3x more consistent
Throughput at Scale 3x faster
Recovery Speed Up to 85x faster
![Page 40: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/40.jpg)
Amazon Aurora with PostgreSQL Compatibility
Performance Architecture
![Page 41: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/41.jpg)
Do fewer IOs
Minimize network packets
Offload the database engine
DO LESS WORK
Process asynchronously
Reduce latency path
Use lock-free data structures
Batch operations together
BE MORE EFFICIENT
How Does Amazon Aurora Achieve High Performance?
DATABASES ARE ALL ABOUT I/O
NETWORK-ATTACHED STORAGE IS ALL ABOUT PACKETS/SECOND
HIGH-THROUGHPUT PROCESSING NEEDS CPU AND MEMORY OPTIMIZATIONS
![Page 42: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/42.jpg)
Write IO Traffic in Amazon RDS for PostgreSQL
WAL DATA COMMIT LOG & FILES
RDS FOR POSTGRESQL WITH MULTI-AZ
EBS mirrorEBS mirror
AZ 1 AZ 2
Amazon S3
EBSAmazon Elastic
Block Store (EBS)
Primary
Database
Node
Standby
Database
Node
1
2
3
4
5
Issue write to Amazon EBS, EBS issues to mirror,
acknowledge when both done
Stage write to standby instance
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
OBSERVATIONS
T Y P E O F W R I T E
Write IO Traffic in Amazon RDS for PostgreSQL
![Page 43: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/43.jpg)
Write IO Traffic in an Amazon Aurora Database Node
AZ 1 AZ 3
Primary
Database
Node
Amazon S3
AZ 2
Read
Replica /
Secondary
Node
AMAZON AURORA
ASYNC
4/6 QUORUM
DISTRIBUTED
WRITES
DATAAMAZON AURORA + WAL LOG COMMIT LOG & FILES
IO FLOW
Only write WAL 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
2x or better PostgreSQL Community Edition performance on
write-only or mixed read-write workloads
PERFORMANCE
Boxcar log records – fully ordered by LSN
Shuffle to appropriate segments – partially ordered
Boxcar to storage nodes and issue writes
WAL
T Y P E O F W R I T E
Read
Replica /
Secondary
Node
![Page 44: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/44.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 GROWTHDurable LSN at head-node
COMMIT QUEUEPending 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 45: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/45.jpg)
Write IO Traffic in an Amazon Aurora Storage Node
LOG RECORDS
Primary
Database
Node
INCOMING QUEUE
STORAGE NODE
AMAZON 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 GOSSIPPeer
Storage
Nodes
All steps are asynchronous
Only steps 1 and 2 are in foreground latency path
Input queue is far smaller than PostgreSQL
Favors latency-sensitive operations
Uses disk space to buffer against spikes in activity
OBSERVATIONS
IO FLOW
① Receive record and add to in-memory queue
② Persist record and acknowledge
③ 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 Amazon
S3
⑦ Periodically garbage collect old versions
⑧ Periodically validate CRC codes on blocks
![Page 46: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/46.jpg)
IO traffic in Aurora Replicas
PAGE CACHE
UPDATE
Aurora Master
30% Read
70% Write
Aurora Replica
100% New Reads
Shared Multi-AZ Storage
PostgreSQL Master
30% Read
70% Write
PostgreSQL Replica
30% New Reads
70% Write
SINGLE-THREADED
WAL APPLY
Data Volume Data Volume
Physical: Ship redo (WAL) to Replica
Write workload similar on both instances
Independent storage
Physical: Ship redo (WAL) from Master to Replica
Replica shares storage. No writes performed
Cached pages have redo applied
Advance read view when all commits seen
POSTGRESQL READ SCALING AMAZON AURORA READ SCALING
![Page 47: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/47.jpg)
Applications Restart Faster With Survivable Caches
Cache normally lives inside the
operating system database process–
and goes away when/if that database
dies
Aurora moves the cache out of the
database process
Cache remains warm in the event of a
database restart
Lets the database resume fully loaded
operations much faster
Cache lives outside the database
process and remains warm across
database restarts
SQL
Transactions
Caching
SQL
Transactions
Caching
SQL
Transactions
Caching
RUNNING CRASH AND RESTART RUNNING
![Page 48: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/48.jpg)
Database Cloning
Create a copy of a database without
doubling storage costs
• Creation of a clone is instantaneous since
it doesn’t require deep copy
• Data copy happens only on write – when
original and cloned volume data differ
Typical use cases:
• Clone a production DB to run tests
• Reorganize a database
• Save a point in time copy for analysis
without impacting production system.
Production database
Clone Clone
CloneDev/test
applications
Benchmarks
Production applications
Production applications
![Page 49: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/49.jpg)
How Does Cloning Work?
Page
1
Page
2
Page
3
Page
4
Source Database Cloned database
Both databases reference same pages on the shared
distributed storage system
Page
1
Page
2
Page
3
Page
4
Shared storage volume
Page
1
Page
3
Page
2
Page
4
![Page 50: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/50.jpg)
How Does Cloning Work?
Page
1
Page
2
Page
3
Page
4
Page
5
Page
1
Page
3
Page
5
Page
2
Page
4
Page
6
Page
1
Page
2
Page
3
Page
4
Page
5
Page
6
As databases diverge, new pages are added appropriately to each
database while still referencing pages common to both databases
Page
2
Page
3
Page
5
Source Database Cloned database
Shared storage volume
![Page 51: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/51.jpg)
What is online point in time restore?
Online point in time restore is a quick way to bring the database to a particular point in
time without having to restore from backups
• Rewinding the database to quickly recover from unintentional DML/DDL operations
• Rewind multiple times to determine the desired point in time in the database state. For
example - quickly iterate over schema changes without having to restore multiple times
t0 t1 t2
t0 t1
t2
t3 t4
t3
t4
Rewind to t1
Rewind to t3
Invisible Invisible
![Page 52: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/52.jpg)
Amazon Aurora with PostgreSQL Compatibility
Announcing Performance Insights
![Page 53: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/53.jpg)
Performance Insights
Intuitive tuning
for managed cloud databases
![Page 54: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/54.jpg)
First Step: Enhanced MonitoringReleased 2016
O/S Metrics
Process & thread List
Up to 1 second granularity
![Page 55: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/55.jpg)
Next Step: Performance Insights
Database Engine
Performance Tuning
![Page 56: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/56.jpg)
Beyond Database Load
• Lock detection
• Execution plans
• API access
• Included with RDS
• 35 days data retention
• Support for all RDS database engines in 2017
![Page 57: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/57.jpg)
Amazon Aurora with PostgreSQL Compatibility
Roadmap
![Page 58: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/58.jpg)
High Performance
Easyto Operate & Compatible
High Availability
Secureby Design
Amazon Aurora with PostgreSQL Compatibility –
Launch Roadmap
2x or faster than PostgreSQL
Up to 64 TB of storage per instance
Write jitter reduction
Near synchronous replicas
Reader endpoint
Enhanced OS monitoring
Performance Insights
Push button migration
Auto-scaling storage
Continuous backup and PITR
Easy provisioning / patching
All PostgreSQL features
All RDS for PostgreSQL extensions
AWS DMS supported inbound
Failover in less than 30 seconds
Customer specifiable failover order
Up to 15 readable failover targets
Instant crash recovery
Survivable buffer cache
X-region snapshot copy
Encryption at rest (AWS KMS)
Encryption in transit (SSL)
Amazon VPC by default
Row Level Security
![Page 59: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/59.jpg)
Amazon
Aurora
Available
Durable
The Amazon Aurora Database Family
AWS DMS Amazon RDS
AWS IAM,
KMS & VPC
Amazon
S3
Convenient
Compatible
Automatic
Failover
Read
Replicas
X
6 CopiesHigh
Performance
& Scale
Secure
Encryption at rest
and in transit
Enterprise
Performance
64TB
Storage
PostgreSQL
MySQL
![Page 60: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/60.jpg)
QuestionsTimeline
We are taking signups for the preview now
We plan to release in general availability in 2017
How do I sign up for the preview?
http://aws.amazon.com/rds/aurora
FAQs
https://aws.amazon.com/rds/aurora/faqs/#postgresql
Mark Porter, General Manager ([email protected])
Kevin Jernigan, Senior Product Manager ([email protected])
Amazon RDS for PostgreSQL
Amazon Aurora
![Page 61: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/61.jpg)
Remember to complete
your evaluations!
![Page 62: NEW LAUNCH! Introducing PostgreSQL compatibility for Amazon Aurora](https://reader034.vdocuments.us/reader034/viewer/2022051521/586fc80f1a28aba24c8b62f9/html5/thumbnails/62.jpg)
Thank you!