Deep Dive on Amazon Aurora with PostgreSQL Compatibility

下载 6

知可追

发布于

1865

人观看

#信息技术

Amazon Aurora与PostgreSQL兼容是一种关系型数据库服务，它将高端商业数据库的速度和可用性与开源数据库的简单性和成本效益相结合。我们回顾了这些功能，以了解有助于提高可伸缩性、可用性和耐久性的体系结构差异。我们还深入研究了服务的功能，并回顾了最新的可用功能。最后，我们将介绍迁移到亚马逊极光的技术。
关于作者
Jim Mlodgenski
Principal database engineer at Amazon Web Services, Co-Chair - PGConf US, Director - United States PostgreSQL, Co-organizer of Philly PUG & NYC PUG, former CTO at OpenSCG

展开查看详情

1 . May, 2019 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

2 . Open source engines Commercial engines RDS platform • Automatic fail-over • Isolation & security • Advanced monitoring • Backup & recovery • Industry compliance • Routine maintenance • X-region replication • Automated patching • Push-button scaling 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. Image credit: © 2018, By Mackphillips Amazon Web Services,-Inc. Own or work, CC BY-SA its Affiliates. 4.0, https://commons.wikimedia.org/w/index.php?curid=55946550 All rights reserved.

3 . CLIENTS Postgres 9.6/10/11 — same extensions RDS Backup/Recovery - PITR Aurora PostgreSQL PostgreSQL High Availability & Durability 11 - Soon Secure – IAM Auth Read Replicas Cross Region Snapshots Aurora EBS Scale Compute – Online Scale Storage Storage Outbound Logical Replication Cross Region Replication 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

4 .Log-Based Storage 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

5 . A B C DE Queued Work Queued Work Durability 6 5 6 0 3 2 4 0 2 3 5 0 2 1 4 0 0 1 4 Tracking A B C D E Log Buffer Storage Storage PostgreSQL Aurora PostgreSQL 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

6 . update t set y = 6; update t set y = 6; t-v1 t-v1 t-v1 t-v1 t-v2 t-v2 t-v2 t-v2 Block in t-v3 Block in t-v3 t-v3 Memory Full Memory Block t-v3 checkpoint no WAL checkpoint 4K Aurora = 8K Storage 4K no FPW datafile archive PostgreSQL Aurora 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

7 . Aurora RW Peer Coalesce Storage Nodes 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

8 . 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

9 . Insert Workload—PostgreSQL 9.6 30,000 25,000 20,000 Inserts Per Second 15,000 10,000 5,000 BASE 16GB Max WAL Aurora PostgreSQL 0 1 31 61 91 121 151 181 211 241 271 Minutes 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

10 . Update Workload—PostgreSQL 9.6 20,000 17,158 BASE 16GB Max WAL Aurora PostgreSQL TPS (2 Updates per Transaction) 15,000 10,000 4,871 5,000 3,729 - 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

11 . RECOVERY TIME FROM CRASH UNDER LOAD 160 Recovery Time in Seconds (less is better) 140 As PostgreSQL 30 GiB Redo 120 throughput goes up, so Recovered in 123 does log size and crash seconds 100 recovery time 80 Amazon Aurora has no redo. Recovered in 3 seconds while 60 10 GiB Redo maintaining significantly greater Recovered in 50 throughput. 40 seconds 3 GiB Redo 20 Recovered in 19 seconds 0 0 20,000 40,000 60,000 80,000 100,000 120,000 140,000 Bubble size represents redo log, which must be recovered Writes / Second (more is better) 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

12 .Base Architecture 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

13 . Availability zone 1 Availability zone 2 Availability zone 3 Application Application Application RO RO RW Async RW RO RO RO Async Invalidation RO Invalidation Write log & Update & Update Read records blocks Storage Aurora Automatic Scalable to 64TB 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

14 .Durability—4 of 6 Quorum 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

15 . Region Availability zone 1 COMMIT Availability zone 2 Availability zone 3 EBS EBS EBS 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

16 . 140 High Concurrency Sync Write Test 123 120 100 2 Node (4 copy) 3 Node (6 Copy) Latency (ms) 80 60 40 28 31 21 20 10 12 6 7 0 50 90 99.9 99.99 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. Percentile © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

17 . Region Availability zone 1 COMMIT Availability zone 2 Aurora Aurora Availability zone 3 Storage Storage Aurora Aurora Aurora Aurora Storage Storage Storage Storage 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

18 . sysbench response time (p95), 30 GiB, 1024 clients 600.00 PostgreSQL (Single AZ, No Backup) Amazon Aurora (Three AZs, Continuous Backup) 500.00 400.00 response time, ms 300.00 200.00 100.00 0.00 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 minute 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

19 .Replicas and Clones 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

20 . update PostgreSQL PostgreSQL RW RO EBS Snapshot EBS 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

21 . update in update memory Aurora Aurora RW RO Aurora Storage 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

22 . RW RO accounts accounts tellers tellers PostgreSQL branches Async Replication branches history and apply history RW RO accounts accounts Aurora tellers PostgreSQL branches Async Replication history and memory update 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

23 . pgbench RW 8K tps on Primary – RO 200k tps on replica backfill pgbench_history 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

24 . pgbench RW 8K tps on Primary – RO 200k tps on replica backfill pgbench_history 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

25 . Availability zone 1 Availability zone 2 Availability zone 3 Reporting Application Application Application RO RW Clone RW Write log Read records blocks Storage Aurora Primary Storage Clone Storage 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

26 . PGBench RW Scale 10K - Target Rate 20K TPS 25000 20000 Transactions Per Second (TPS) 15000 Main Database Clone Database 10000 5000 0 0 3 6 9 12 15 18 21 24 27 30 33 36 39 42 45 48 51 54 57 60 63 66 69 72 75 78 Minutes 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

27 .Replication 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

28 . PostgreSQL instance 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

29 .Caching 2019, Amazon © 2018, Amazon Web Web Services, Services, Inc. or or its Affiliates. Allrights Affiliates. All rightsreserved. reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

0点赞

1收藏

6下载