Amazon Aurora - Amazon AWS

Parameter GroupとMaintenance WindowもDBウラスタと各. ノ ドそれAれに存在する. • フェイルオ バが発生しても常にマスタを参 する. エンドポイントがウラスタ毎に1つ ...
29MB Sizes 1 Downloads 292 Views
.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Amazon RDS • • • • S3 • © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Amazon Aurora • re:Invent 2014

RDS

• Amazon 1 –



© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

OSS

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Amazon Aurora • Amazon Aurora – RDS SQL Server

RDS

1

MySQL / MariaDB / PostgreSQL / Oracle / MS

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

vCPU

Mem

Hourly Price

db.t2.small

1

2

$0.063

db.t2.medium

2

4

$0.125



db.r3.large

2

15.25

$0.35

db.r3.xlarge

4

30.5

$0.70

• •

db.r3.2xlarge

8

61

$1.40

db.r3.4xlarge

16

122

$2.80

db.r3.8xlarge

32

244

$5.60

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• • IO •

: $0.120/GB/ : $0.240/100

vCPU

Mem

Hourly Price

db.r4.large

2

15.25

$0.35

db.r4.xlarge

4

30.5

$0.70

db.r4.2xlarge

8

61

$1.40

db.r4.4xlarge

16

122

$2.80

db.r4.8xlarge

32

244

$5.60

db.r4.16xlarge

64

488

$11.20

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.



.4

4 5

• • IO •

: $0.120/GB/ : $0.240/100

1

7

.

/

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

6

5

Amazon Aurora •

MySQL5.6/5.6

• •

10GB 3AZ –



6

S3

VPC –



2

64TB

Security Group

Amazon Aurora

NACL

99.99%

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

SQL Transactions Caching Logging

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

1

Application

SQL

SQL

Transactions

Transactions

Caching

Caching

Logging

Logging

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

W

• – –

7

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

S 0

W 7

7 917

A

M



Q

• • • •

M

W A

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

S L

, , np

A

x

L

C

BMQ

E

, ae cM Asl E Qi M y uABM tDS

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

z

D

D

o D

E

BMLE

B

D mr

x ,

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.



B 3 F3 C3 3 E3

Data Plane

SQL Transactions



%. 2 %. 2 , E3

. 9 %, DW 3 W

Caching

Logging + Storage

• %. 2 S3 WB

a A

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Control Plane

Amazon S3

Amazon DynamoDB

Amazon SWF

Amazon Route 53

• DB DB



• •

survivable cache = DB

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

+

SQL

SQL

SQL

Transactions

Transactions

Transactions

Caching

Caching

Caching

Aurora •

SSD – – –

10GB

AZ 1

64TB

Peer-to-peer gossip

AZ 2

SQL Transactions

Caching

• – –





3AZ 6 2 •

1

AZ

3

3

Log structured Storage – –

redo log Log page

Data page

Amazon S3 © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

AZ 3

• •

)( N G 6 •

b P

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

) G

) () L

L )

S (

S

466

4 1 /

47

(

)

Ot Ot Ot

)

26-7526

-7

8 D N DGKL G A

(

B A

-3

8

-7 4

-

,47-3

-

7 7 8

8AA KLH CA GH AK

8

7 8

7

28

17 47 8726 26 25 6 8 17

( , -3 8

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

(/

f O

T Ot TPuOl rOoy p i TPuOl y p PuOl e Ol y p - -

i

,

T T -3 T g p

1

m p m p psf O ram

USQ d i c t O 5N 4 MG IEDBDA n ay R up O m

O IA GH A



4

m

D

S

• •

A

0

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

a a 1

o6

0

aBG

n

L S B



6 4

r

• • –

d

oa

• –

• © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

S

3

t3

e c S

T

S ug

• – – –

• • •

3

S

3

3 3

3

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

3

C T C T 3 Q

• • •

1

2

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

A3 A

L

1

2

S

Amazon Aurora 3

3

1D4 CD4 6

/B 4B CD4 6

/ 2

1

1

BB B

• • •

1

C CD 4D 6 - D B

/B 4B CD4 6

3

A 64 CD4 6

1

1

4I

3

3

d

L

g bS

6

v

BB B

wpRt l C v le RrokO

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

d

L

c

g bS

L

l

TLb

i

4I

ag

1

nNm

nNm

Psu f e L

LZ E

D4 4D4

BD

E

B

MySQL Master

MySQL Replica

Aurora Master

70% Write

70% Write

30% Read

30% New Reads

30% Read

Data Volume

Data Volume

70% Write

Binlog

MySQL read scaling • • •

binlog / relay log



© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

PAGE CACHE UPDATE

Aurora Replica

100% New Reads

Shared Multi-AZ Storage



0 – – –

21 5323 D f MD

RS

mAo 0 L (- 23 - )AD M a

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

M R A

MD

Q

A n

(

-

0. d Z h S 0. T / ./– b y rr y Z h y rr y e0. d Z h

N F MN

LFI L F MN BD

A BD

z ( w u w a dln r w s ekm d Uc Z hyr Wo Z h o id y zt

) -I RK 0 MNFB . KBG NKLD 0.

0.

0.

0.

IFLLKL

, 4 IFLLKL

r -I RK

582

(

( IF OND ( 4 38

.3758

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved. 2

BG

/- -

tw tw tw

M.D BE PLFND K

/8 .50

3 0

n (,,4 3 8M n ) 3 8M

PKLG K

2.

N MDN

1 1350

y

. ) )

C 1 )7

A )ECBC

FI

)

/E

. /E

SLOFSX LPTUFPGI

F

/E

/ 9

32; f –

IRNLGF LPTUFPGI

5

/ D 1 ( A; A9

26 60A 32 B 6 3

–y

32;

–y hm wi f

L

mc we –

3

)1

OLP UI

6

8;5

06 8;5

3

2/ /

9

n dh u w t ( h–t

i

w

zw

zw C FORNL LGFULQP

/OFYQP

n

8 e

)6. 15

,7

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

rleo Z a Z kleod

p-) 7 6 ;T

XT0IPGK WSLUI QPNX WQSMNQF

50 FUF TIU

.6

2;A083 B 6 3

4 9 4683

h

s w

/ 55

CB

D

// / O

/ I

/ / O KS

/ /

I

A

MP T RMP

P

IA

/ /

WNF P EML A

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

/

C

• – – –



5

Application (-

V5S E L 32 o -) 5

--



5SA

W

SQL

n

E ML M

6

Transactions

a

32

• •

5

C

P

Caching

5 m 5 a

Storage

K 5 Amazon S3

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.



Q

A – –

M

D

B L

A S

L B A

A E

MySQL Client

Thread

Aurora Client

Thread Client

Thread Thread

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

B

Thread Thread

Client

Thread Thread

Thread Pool

• Standard MySQL –

CLIENT CONNECTION

AURORA THREAD MODEL

1







• MySQL EE –





© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

LATCH FREE TASK QUEUE

epoll()

CLIENT CONNECTION

MYSQL THREAD MODEL



Kernel

r3.8xl

epoll()

Amazon Aurora 5,000

TRANSACTIONS T1 Read Read

T1

Tn

Read Read

Read Read

Write Read

Write Read

Write Read

Commit

Commit

Commit

TIME

C o mmi t ( T 8 )

LSN 50

Commit ( T7)

LSN 47

Commit ( T6)

LSN 41

Commit ( T5)

LSN 34

Commit ( T4)

LSN 30

Commit ( T3)

LSN 22

C o mmi t ( T 2 )

LSN 12

C o mmi t ( T 1 )

LSN 10

LSN 49 GROUP COMMIT

LSN 20

COMMIT QUEUE

LSN GROWTH

Pending commits in LSN order

Durable LSN at head-node

AMAZON AURORA I/O

6

4

DB Durable © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

ACK

ACK

MYSQL DB failure

Failure detection

Recovery

Recovery

AURORA WITH MARIADB DRIVER DB failure

Failure detection

15-20 sec

DNS propagation Recovery

3-20 sec © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

App running

DNS propagation

App running

0

-

0%

35.00%

-

50.00%

30.00%

40.00%

25.00% 20.00%

30.00%

15.00%

20.00%

10.00% 5.00%

10.00%

0.00% 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35

0

0.00% 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35

-

0

60% 50%

20%

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

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

1

3

5

7

9 11 13 15 17 19 21 23 25 27 29 31 33 35



P

– e

1

1 )



c 1 A G



• c

uRp

iW d P

a Gon

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

P

m )

( )

R

G

1 M

MtGe wG D tG

BM r

R P

d

P R

:

• • – –

LG:Q

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

RS

D

LG

A C B C BPM:LG

/

• – – –

• – – –

• – –

• • •

/

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• – – –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• •

.

.

.

.

.

mysql> SELECT SERVER_ID, REPLICA_LAG_IN_MILLISECONDS, SESSION_ID FROM INFORMATION_SCHEMA.REPLICA_HOST_STATUS; +-----------------+-----------------------------------------------------+-----------------------------------------+ | SERVER_ID | REPLICA_LAG_IN_MILLISECONDS | SESSION_ID | +-----------------+----------------------------------------------------+-------------------------------------------+ | demo-db01 | 18.458999633789062 | 62c35a1c-2f61-11e5-96de-06be620fb7bd | | demo-db02 | 0 | MASTER_SESSION_ID | | demo-db03 | 19.39299964904785 | 6194b000-2f61-11e5-9bf6-12715c13435b | +-----------------+---------------------------------------+--------------------------------------------------------+

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

U h i a c o Ubg c a c w

ag I ) /

Ubg )

)

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

ag

) i

d

e g

O

U

d lm / ( x I d x ) U u

t

g rn

g g

g

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• M – – M

D

LM -

1 LM

• –

-

R

1

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

S 5

5

M

D Q

R

A

Q

Z

• •

)

E

A E WR

Writer

Reader © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

E

(

)(

• R P

CN

ME CN

N





© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

ME

N CN

R

N A

• C A

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• – – –

:

;

' ;

:

H

:

; ;

;

:

• I O aF WB ;:; V AG nblF K _L W SND d ei –

NRR

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

D

E

ei

Segment snapshot

Log records

Segment 1 Segment 2 Segment 3

Recovery point

g l

e l

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

n

3

Time

S

h S

a dg

n

0 •



gd

L

p

MySQL Cp

t

ce gd

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

L p

D



Disk read T redo log

T k



nr

L

a i

D

L nr

o a o h

p h 0

p

R

• –

• •

5 ze

i

3

3

5

ndA

Bp R R R

R

ma P o

t

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

u

T k

r

IS 3

3

c

P

5 I A A

: •

"

I

• " K

"

" K C;E ;AF

D

AF FH

• – –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

K

; K

"

E;C

K

E; K

A

:

" ;

K

K

K

K

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

DO

O

DI

C F N

.

.

/

A 6 5

7 6 LE

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

MO

/

P

DO

O

DI

QR

F

N

• ML yc Q u

U SP

Mfr w a

Q

• – – e

uo

C

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

i h

t

C

v

oC

NU A

• –

W

N







NU

U DS t

R S

f L e

M

S PM

N S

f

VPC subnet

R A NoyM

Q a S i S P r u 1 C LS S

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

/

2

VPC subnet Availability Zone A

• RDS for MySQL

buffer / connection /

• Query Cache ON – ON – Write heavy

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Aurora OFF

• innodb_change_buffering – Default: NONE – – Secondary index

1-2 change buffer

Aurora •

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• thread_handling – Default: multiple-connections-per-thread – – multiple-connections-per-thread, no-threads, one-thread-perconnection, dynamically-loaded – Client Aurora

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• – Amazon Aurora

• –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.



5 – re:Invent 5 (r3.8xlarge + NW db.r3.8xlarge Aurora

• TPC-C 5

Sysbench kernel

db.r3.8xlarge

4 )

2.5

– https://code.launchpad.net/~percona-dev/perconatools/tpccmysql © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• FAQ A AMI –

G

A

http://aws.amazon.com/rds/aurora/faqs/

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

B

G



5 – Amazon Aurora

– Amazon Aurora

– Amazon Aurora

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

SSD

5

• CPU / Memory –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

MySQL CPU

• – CPU – – Disk IO

• – Aurora – –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

?

• Aurora MySQL

• Aurora –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

MySQL

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

RDS for MySQL •

Amazon Aurora – RDS for MySQL – RDS for MySQL 5.6

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Amazon Aurora

• RDS for MySQL

Parameter Group



: max_connection / innodb_buffer_pool_size / query_cache_*



EBS

• –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• Amazon Aurora InnoDB – MyISAM



© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

MyISAM InnoDB

• MyISAM –

6TB

• MyISAM –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

3TB

MySQL • MySQL5.6

Amazon Aurora

• mysql > CALL mysql.rds_set_external_master (DB Hostname or IP address', 3306,’user', ‘password', ’Binlog', position, 0); mysql > CALL mysql.rds_start_replication;

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

MySQL • RDS for MySQL MySQL on EC2 MySQL Amazon Aurora –

Aurora

– Aurora

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

S

• Q

R

S M pDr c S M h Mld o

inMm

• M

h

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

D Q

R

e

D h

A ld

ah

L

Application Read Write

RDS MySQL5.6 (Master) Aurora © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Aurora (Writer)

Aurora (Reader)

T

• –



C

lh

nim C –



g

A m Db •



B

yS

im L

S

rs

nim Db

o

0

Db O

Db

oceocPC A pP nDb oT

P

o L

E

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

u

TPCni

SR VP

EM

HQP A

oT

o

CdDc UTgloa T A A Q O t

C A

W

b o

S

Data Export and Import guide • Data Export and Import guide – https://d0.awsstatic.com/productmarketing/Aurora/Aurora_Export_Import_Best_Practices_v13.pdf

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Aurora Driver • MariaDB Connector/J 1.2.0 – https://mariadb.com/kb/en/mariadb/mariadb-connector-j-120release-notes/ – Aurora • https://mariadb.com/kb/en/mariadb/about-mariadb-connector-j/ • https://mariadb.com/kb/en/mariadb/failover-and-high-availability-withmariadb-connector-j/#specifics-for-amazon-aurora

– 2015.09 Amazon Linux

• – Fast failover – Auto node discovery © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

rpm

https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/ © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.



Amazon Aurora cluster – –

Reader Failover Round Robin



Load Balancing –



Reader Higher Availability –



HAproxy

Reader Reader

• DB Aurora Availability Zone Availability Zone

• – –

DNS failover Reader Writer

IP 1

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Writer

failback

Reader Endpoint



VPC subnet

VPC subnet

A Reader

• R

Reader Reader 1 A Writer failback

• Reader VPC subnet Availability Zone A

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

VPC subnet Availability Zone B

• Percona Xtrabackup

Amazon EC2

Amazon Aurora – mysqldump

20



S3 –

Management Console CLI tools AWS Import/Export Snowball S3

• MySQL

Amazon Aurora

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

MySQL

/ : / /3 chg



• –

w ohm

A n K

/ /3 L T k lp

t

: / / / : : / A / : A / : s R z DUQ

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

S G w K o lp

L A r

: A tU

d / :

-:

c i

/

e

aM .

u

yU



Writer • •

Reader

API

• DR

DB

• • •

binlog_format



© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

(MIXED

)

DB

• time_zone – RDS MySQL, RDS MariaDB

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• Amazon Aurora –

Reader • • •

Reader Reader : Tier 0 > Tier 1 > … > Tier 15 Reader – Writer –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Reader

Cluster View • Amazon Aurora Cluster – Cluster – : Cluster Snapshot Instance View

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Cluster Cluster Cluster View

DB

• Amazon Aurora – select AURORA_VERSION(); – select @@aurora_version; – show variables like '%version’; – forum

• Lock Manager – show global status where variable_name in (‘aurora_lockmgr_memory_used’); – select * from INFORMATION_SCHEMA.GLOBAL_STATUS where variable_name in ( 'aurora_lockmgr_memory_used’); © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• Amazon Aurora – –

• – –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

CPU Query Cache



DB 1 – – –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.



S -

-







© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

:

:

a-

-

:

: :

• –

:

:

: : SX

R e



eO Q LQK Y



LQD PGv

aGs

DAED XDs

C UX

M

lD

Y

– eMKSX TGro BW T SX IUMG VI N – ro

Gu

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

GY N



T R S a

M



TR aQ A E B D O aT aT ETR NX I CGTR WPa



TR



TR

aQ GN R S aE DUXS a r G A OaT aTEou L aQ

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

M

( •



A N

X B MS I O

I

B

BE DB SP T





)

LR

BW TCG

Y Q

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

I C

AG

I DB U Y

0 0



3/

D H 7 H

– o lu – 2. – ADI:7 H



I D

– 5 d



rcw –

HH

o

l N K

Ta

rg v

hf

/D :

L 3/ l

CH

rcw

jy

/D : 2 N

B MDC DB

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Q

W :

u

hf p

rg

me U

/D :1DC 2 I D

S D B C

v P

s ni

L 3/

tz C

d H

l C



vw = =/ - ' M N – – – – –

= = = = =

P Q ea f LO

= = = = =

/ / / / /

l b r

1 1 1 1 1

= = / 1 1 1 1 1 1

/% / 1 / / 1 / 1

p

n_

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

=

dig =

A % / = 0/ = = /

c

muS

/ 1 == % % = / % = / %

so gh N = =/S N S Q

t -

y so v L

d

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

1LO1

B

I

E D

2

3

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

V T

0

BV R

SWB

4 4

1N

B



)



• – –

• –



$

(

(

.

14 14 .

14

t u

bc A

l

ndB

k 0 4 .

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

bc

go

bc

bc ndA

D

b

l

he r

$

i wA

a

B

l l

T

bc B

B

• –

02 •

0 2 ki c

02

t



02

– • •



02 •

02

0 5

T 2



02 •

T M T

02

u WR

0 5

02

2

aA

D

( cl g T D

0 5 2 0

0

t

t

S

2

r

0

a

2 0 5 neC

2

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

0 102

)

02

ib

2 0 5 neC

102 02 2 0 102 02 B [A 0 5 102 02 0 102 02 b S gCdhi ]w W tA b

Be

0 5

t

st oA 102

02

D

TA 102

ki 02

c

]

W

b

• –

r

S

B

A S



– – •

– – –

– –

t

5 r Q

2

5

5

t

5

r o5 S D

k

B

uL

tS

5

S B

S

B7

5 Q D r

S .

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

y

B 2 A

Q

B

6 QA

D

y

S

7

t5ar

M QB7

5c .

7

2

5

7

S QB7 B7

B 7

7

r

t r S

S .

5



6S: 5* , (

y r c( 6S: 5* c 4: 7 MOJJILN MJ ND D ? Mf L N ? IO M 1 TI 1OLIL b m utosb c 6S: 5 * ,gie( b d dz n s

– – –



ba j pʼ w b

– – –



b

bsxt q tvfm





*

i

)

ʼ slt pʼ wc

ʻb

dz n

sa

ʻ

*bsxt q tv hlt c

; D AIL 1OLIL 6S: 5 ( DM OLIL SM . N D AIL 1OLIL 6S: 5 I ND O M NI = OLIL ; D LMDI AIL 1OLIL 6S: 5 ( DM * , ( . N D LMDI AIL 1OLIL 6S: 5 I ND O M NI = * ; ? A ON J L N L LIOJ AIL 1OLIL 6S: 5 ( DM ? A O N OLIL SM * , . N ? A ON J L N L LIOJ AIL 1OLIL 6S: 5 I ND O M NI = ? A O N OLIL *

- NNJM- AILO M QM

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

TI

I

MJ 0

2/*)()



c – – – –

;

,J E

( (

.

-B Ae m p bMwPr quxR

A: E BB

oc

EK

;

tP t Pk _MlP y u d P c U Mkh g nR10 qj s g wuko g g wuko Joinc (jPwfz fR ) Join (equi-join) ox k fo : http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.BestPractices.html#Aurora.BestP ractices.AKP Version1.15 w i x aR ba Aurora

• •

@K @K

. K akh iP

batched_key_access: ON mrr_cost_based: OFF

)) ))

A: =B A

BA EBE BA B @

E

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

> K E ;'

E

;

. K

;'BA ; > K

'BA @EE

;. K E

B

; A:

'B



D

d

U • • • •

pM / r y • •

a

J

D

il UC

/ / D M

U e

/ / P

lc

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

/ Pr l

m

M

IR

x

UC

/

/ l M

Jwu

l l

p

owu t

Pns lc

EJAB l

D

• • •

• • •

P B d eDu C DP c P sD D h D t kD i

ae p Dv d ae C nm lDkr D a o

//

B

d

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

//

C

//



sL •

.M

aW

m

A IE



lC



A 2

.Cni

A kzSni s





M rL

5

A TV )

2 (

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

4 14

L 0

Sni

K r oS (

e t M g

4

K L uEKD

-'

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

;

-

• – Amazon Aurora ST_Crosses

ST_Distance

GEOMETRY ST_Contains, spatial query



– Amazon Aurora • MySQL5.7

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

dimensionally ordered space-filling curve 2

D

• – –

/

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

:

D





D

/ /

/

D D

:/ / B :

:

( B B

)

(

0 - / 0 - -

0 - 0

8 -

-

- 0

Query

Query

Create event string

DDL

DDL

Create event string

DML

DML

Create event string

DCL

Create event string

Connect

Create event string

Create event string

DCL Connect

8

Write to File

Latch-free queue

Write to File

Write to File

Write to File

MySQL 5.7



/-

-

8

/

Audit Off

95K

615K

6.47x

Audit On

33K

525K

15.9x

/

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Aurora

/ 8

8 5 8-0 8

-

- /

• – –

A

,

C

A

T QD

O

C a VYRiu tKvs LVU_OlpT B Knx



)C

)VU_OPTnx



)C

)VU_OPTnx



)C

)VU_OPTnx



) C VU_O

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

K dGS

EvsE rTnx VU_T QDe OVU_ ,( NM )

NM NM

Tnx

• –

--

l

– •

– i

-6.0 -

61

kr | r er a _ran tUe m er U 6

q

ut

g

aor

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

r

e

m M

0

,

.- -

er *

s

er q i D y

4 1

c

6

d

BB

v

0i

i s

x

q A

i

W

Query

Create event string

DDL

Create event string

DML

Create event string

DCL

Create event string

Connect

Create event string

C R

A ICL C

M

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

F P

,





,3

E C

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

,3 LC

GC

SGW A

)

(

• – 5

(1.10

– •

SSL



– • • SSL

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

ZDP

)

D

B

D

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• Replication improvements – Reader buffer cache write read

heavy

• Throughput improvements for workload with cached reads – Buffer cache

Amazon Aurora

read view – Sysbench Select only MySQL5.7 164K reads/sec

Amazon Aurora 625K reads/sec

• Throughput improvements for workload with hot row contention – Amazon Aurora – TPC-C © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

row page MySQL5.7

16

/

.

2



db.t2.medium / db.t2.small – – CPUCreditUsage

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

CPUCreditBalance

• secondary index – db.r3.8xlarge –

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

/ 75%

• Performance schema – Amazon Aurora Sysbench MySQL 60% Amazon Aurora MySQL 1/4 – db.r3.8xlarge Performance schema Sysbench 100K write/sec, 550K reads/sec

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• Hot row contention improvement –

hot

CPU – error 188

• Improved out-of-memory handling – Amazon Aurora

OOM

• Smart read selector – – SysBench

Write

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

27%

• Amazon Aurora – –

AWS Lambda (mysql.lambda_async)

Lambda • Version 1.16

– IAM Role

(lambda_sync

)

Aurora

• DELIMITER ;; CREATE PROCEDURE SNS_Publish_Message (IN subject VARCHAR(255), IN message TEXT) LANGUAGE SQL BEGIN CALL mysql.lambda_async(’Lambda ARN', CONCAT('{ "subject" : "', subject, '", "message" : "', message, '" }') ); END ;; DELIMITER ;

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• 1

S

– –

Ffr ND

Oi )>=<=3F

o ( )2) /

)AXMLo ( OT

) EI d T – ManifestFL cwa F em (Version 1.11 =< ( <> <> "=< (

( 3> ( 3> =< =<

" <> " <> <> <>

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

=< (

( (

"=< ( 3> 3>

<> <>

n

uvo

)

l

)

3 3

<> <> 3> 3>

( 3> ( 3> "( "(

" "

( (



lfik

N

a

IFDF:

e

nNk

– LOAD DATA FROM S3 x _Manifestmbcoa – 1mbco yp6GBUVtʼR _ • 25GBa _ OY N aexportT_us M exportT_ aU SW T_ra

* * 3 BEAD ,* 3* ,/ 3* ' ,/* 3* ,/ 3* ' /, * 3 / * ,3* /

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

L CL

,/3

3 , *

;I

SQL tʼSW C:B

EF

L

50+ system/OS metrics | sorted process list view | 1–60 sec granularity alarms on specific metrics | egress to Amazon CloudWatch Logs | integration with third-party tools © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

CPU Utilization User System Wait IRQ Idle

Processes

Memory

Num processes Num interruptible Num non-interruptible Num zombie

MemTotal MemFree Buffers Cached SwapCached Active Inactive SwapTotal SwapFree Dirty Writeback Mapped Slab

Process List Network Rx per declared ethn Tx per declared ethn

Process ID Process name VSS Res Mem % consumed CPU % used CPU time Parent ID

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Device IO TPS Blk_read Blk_wrtn read_kb read_IOs read_size write_kb write_IOs write_size avg_rw_size avg_queue_len

File System Free capacity Used % Used



-)



-) ds

( – –

Lm

K

D

Bo

Lb

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

u c

L hag -

ol ErtDn> S W C vol

i e E



© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Encryption at Rest • Key Management Service(KMS) – – –

Aurora Snapshot Snapshot

Aurora

• Disk • – © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

(KMS

)



Large dataset read performance –



IO

Aurora

Fast Insert –

Primary key

– • •



IO/CPU /CPU

( Global

LOAD DATA

aurora_fast_insert_cache_hits: aurora_fast_insert_cache_misses:

INSERT INTO ... SELECT )

cursor index

Parallel Read Ahead – –

B-Tree

Disk page Writer

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Reader

Lab mode • – DB –

aurora_lab_mode

• GA



• – Lock compression • • OOM

66%

– Fast DDL • nullable • http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Managing.h tml#AuroraMySQL.Managing.FastDDL

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Lab mode • – Hash Join • equijoin n tD Aurora Ub RcS hash join u l p so • b iFy p uD GL • r F R3.8xlargeSi i F h aQ b h u Fw 8.2x z AmM

– Batched Scan • Side . © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Fw 1.8x /

/

: ./

:

z

AmM . /

./ / : /

)BTI+PJOT

-BUFODZJNQSPWFNFOUGBDUPSWT#BUDIFE,FZ"DDFTT #," KPJOBMHPSJUIN %FDJTJPOTVQQPSUCFODINBSL 3YMBSHF DPMECVGGFSDBDIF MPXFSJNQSPWFNFOUJGBMMEBUBDBDIFE  9.00

8.22

8.00 7.00 6.00 5.00 4.00 3.00 2.00 1.00

Q

ue ry Q -1 ue ry Q -2 ue ry Q -3 ue ry Q -4 ue ry Q -5 ue ry Q -6 ue ry Q -7 ue ry Q -8 ue r Q y-9 ue ry Q -10 ue ry Q -11 ue ry Q -12 ue ry Q -13 ue ry Q -14 ue ry Q -15 ue ry Q -16 ue ry Q -17 ue ry Q -18 ue ry Q -19 ue ry Q -20 ue ry Q -21 ue ry -2 2

-

)BTIKPJOVTFEJORVFSJFT              

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

#BUDIFETDBOT

"NB[PO"VSPSB‫ذכ‬٦‫ٍؗإٔـ‬ٝծ؎ٝ ‫ٍؗإٔؿأؙحر‬ٝծ؎ٝ‫ٖأؙحر‬ٝ ‫ٍؗأآ‬ٝ‫׾‬遤ֲꥷ‫*ח‬OOP%# CVGGFSQPPM ַ‫ג׭הת׾ٕفة׵‬铣‫׫‬鴥‫׬‬

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

1.78X

Query-1 Query-2 Query-3 Query-4 Query-5 Query-6 Query-7 Query-8 Query-9 Query-10 Query-11 Query-12 Query-13 Query-14 Query-15 Query-16 Query-17 Query-18 Query-19 Query-20 Query-21 Query-22

.Z42-‫כד‬遤‫ח׾‬遤‫א׆‬鐰⣣‫׭׋׷ׅ‬ ؔ٦‫זֲ״ך♴⟃ָسحقغ‬椚歋‫ד‬㣐ֹֻ ‫׷ז‬ • ‫ؿ‬؋ٝ‫ّءؙ‬ٝ‫؝‬٦ٕ‫ך‬㟓⸇ • ٗ‫ثحٓהؙح‬ • ؕ٦‫زإٔا‬،‫زأٔה‬، • *OOP%#‫ה‬.Z42-꟦‫ؓؿד‬٦‫حو‬ ‫ךز‬㢌䳔

2.00 1.80 1.60 1.40 1.20 1.00 0.80 0.60 0.40 0.20 -

-BUFODZJNQSPWFNFOUGBDUPSWT#BUDIFE,FZ"DDFTT #," KPJOBMHPSJUIN%FDJTJPOTVQQPSUCFODINBSL  3YMBSHF

.,

D:

-::

3

. / 1

3

/ 1 / 1

§ § § §

ox

pxcn T i ae I II Q a w TN S a w a wex q S a w S px z a

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

§ § § §

tfi f e rm

-

: .

33 2:

2:

33 2:

2:

33 2:

2:

12 D

pxS w L dul b M M N S Te r pz qby M :3 : A pxT S 1 R s M S T 33 2: 3 : : 3 :3 3 S M N S

a

Aurora

On r3.large

MySQL 5.7

10GB table

0.27 sec

3,960 sec

1,600 sec

50GB table

0.25 sec

23,400 sec

5,040 sec

100GB table

0.26 sec

53,460 sec

9,720 sec

Aurora

On r3.8xlarge

MySQL 5.6

MySQL 5.6

MySQL 5.7

10GB table

0.06 sec

900 sec

1,080 sec

50GB table

0.08 sec

4,680 sec

5,040 sec

100GB table

0.15 sec

14,400 sec

9,720 sec

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

Amazon Aurora •

Amazon

RDBMS

– MySQL5.6/5.7

• – Amazon Aurora



PITR – Log Structured Storage – SOA

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

• M

L



• • •



A

1B 1:1E 1 D/ 0 1

:1E :1E D/ 0 1

12



GF A A A

1 1

D/ D/ 2D

:

A

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

:1E

1 121 1 121 :1E A 1

GF

1B 1:1E 1 D/ 0 1

:

12

:

. / 1

D/

0 0

0

A

0

A

1 1 1 121

:1E . / 1 1A :

© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.