Amazon Aurora - Amazon AWS

.

© 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.


Amazon Aurora • re:Invent 2014

RDS

• Amazon 1 –

•


OSS


Amazon Aurora • Amazon Aurora – RDS SQL Server

RDS

1

MySQL / MariaDB / PostgreSQL / Oracle / MS


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


• • 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


•

.4

4 5

• • IO •

: $0.120/GB/ : $0.240/100

1

7

.

/


6

5

Amazon Aurora •

MySQL5.6/5.6

• •

10GB 3AZ –

•

6

S3

VPC –

•

2

64TB

Security Group

Amazon Aurora

NACL

99.99%



SQL Transactions Caching Logging


1

Application

SQL

SQL

Transactions

Transactions

Caching

Caching

Logging

Logging



W

• – –

7


S 0

W 7

7 917

A

M

•

Q

• • • •

M

W A


S L

, , np

A

x

L

C

BMQ

E

, ae cM Asl E Qi M y uABM tDS


z

D

D

o D

E

BMLE

B

D mr

x ,


•

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


Control Plane

Amazon S3

Amazon DynamoDB

Amazon SWF

Amazon Route 53

• DB DB

•

• •

survivable cache = DB


+

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


) 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


(/

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


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


3

C T C T 3 Q

• • •

1

2


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


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

•


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


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


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


/

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


•

Q

A – –

M

D

B L

A S

L B A

A E

MySQL Client

Thread

Aurora Client

Thread Client

Thread Thread


B

Thread Thread

Client

Thread Thread

Thread Pool

• Standard MySQL –

CLIENT CONNECTION

AURORA THREAD MODEL

1

•

•

•

• MySQL EE –

•

•


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


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


P

m )

( )

R

G

1 M

MtGe wG D tG

BM r

R P

d

P R

:

• • – –

LG:Q


RS

D

LG

A C B C BPM:LG

/

• – – –

• – – –

• – –

• • •

/


• – – –


• •

.

.

.

.

.

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 | +-----------------+---------------------------------------+--------------------------------------------------------+



U h i a c o Ubg c a c w

ag I ) /

Ubg )

)


ag

) i

d

e g

O

U

d lm / ( x I d x ) U u

t

g rn

g g

g


• M – – M

D

LM -

1 LM

• –

-

R

1


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

•

•


ME

N CN

R

N A

• C A


• – – –

:

;

' ;

:

H

:

; ;

;

:

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

NRR


D

E

ei

Segment snapshot

Log records

Segment 1 Segment 2 Segment 3

Recovery point

g l

e l


n

3

Time

S

h S

a dg

n

0 •

•

gd

L

p

MySQL Cp

t

ce gd


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


u

T k

r

IS 3

3

c

P

5 I A A

: •

"

I

• " K

"

" K C;E ;AF

D

AF FH

• – –


K

; K

"

E;C

K

E; K

A

:

" ;

K

K

K

K


DO

O

DI

C F N

.

.

/

A 6 5

7 6 LE


MO

/

P

DO

O

DI

QR

F

N

• ML yc Q u

U SP

Mfr w a

Q

• – – e

uo

C


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


/

2

VPC subnet Availability Zone A

• RDS for MySQL

buffer / connection /

• Query Cache ON – ON – Write heavy


Aurora OFF

• innodb_change_buffering – Default: NONE – – Secondary index

1-2 change buffer

Aurora •


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


• – Amazon Aurora

• –


•

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/


B

G

•

5 – Amazon Aurora

– Amazon Aurora

– Amazon Aurora


SSD

5

• CPU / Memory –


MySQL CPU

• – CPU – – Disk IO

• – Aurora – –


?

• Aurora MySQL

• Aurora –


MySQL


RDS for MySQL •

Amazon Aurora – RDS for MySQL – RDS for MySQL 5.6


Amazon Aurora

• RDS for MySQL

Parameter Group

–

: max_connection / innodb_buffer_pool_size / query_cache_*

–

EBS

• –


• Amazon Aurora InnoDB – MyISAM

•


MyISAM InnoDB

• MyISAM –

6TB

• MyISAM –


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;


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

Aurora

– Aurora


S

• Q

R

S M pDr c S M h Mld o

inMm

• M

h


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


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



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.


•

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


Writer

failback

Reader Endpoint

•

VPC subnet

VPC subnet

A Reader

• R

Reader Reader 1 A Writer failback

• Reader VPC subnet Availability Zone A


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


MySQL

/ : / /3 chg

•

• –

w ohm

A n K

/ /3 L T k lp

t

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


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

•


(MIXED

)

DB

• time_zone – RDS MySQL, RDS MariaDB


• Amazon Aurora –

Reader • • •

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


Reader

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


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.


• Amazon Aurora – –

• – –


CPU Query Cache

•

DB 1 – – –



•

S -

-

•

•

•


:

:

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


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


M

( •

•

A N

X B MS I O

I

B

BE DB SP T

•

•

)

LR

BW TCG

Y Q


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


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_


=

dig =

A % / = 0/ = = /

c

muS

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

so gh N = =/S N S Q

t -

y so v L

d


1LO1

B

I

E D

2

3


V T

0

BV R

SWB

4 4

1N

B

•

)

–

• – –

• –

•

$

(

(

.

14 14 .

14

t u

bc A

l

ndB

k 0 4 .


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


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 .


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


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


> 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


/ 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


//

C

//

•

sL •

.M

aW

m

A IE

•

lC

•

A 2

.Cni

A kzSni s

•

•

M rL

5

A TV )

2 (


4 14

L 0

Sni

K r oS (

e t M g

4

K L uEKD

-'


;

-

• – Amazon Aurora ST_Crosses

ST_Distance

GEOMETRY ST_Contains, spatial query

•

– Amazon Aurora • MySQL5.7


dimensionally ordered space-filling curve 2

D

• – –

/


:

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

/


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


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


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


F P

,

•

•

,3

E C


,3 LC

GC

SGW A

)

(

• – 5

(1.10

– •

SSL

•

– • • SSL


ZDP

)

D

B

D


• 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


CPUCreditBalance

• secondary index – db.r3.8xlarge –


/ 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


• Hot row contention improvement –

hot

CPU – error 188

• Improved out-of-memory handling – Amazon Aurora

OOM

• Smart read selector – – SysBench

Write


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 ;


• 1

S

– –

Ffr ND

Oi )>= 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* /


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.


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


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


u c

L hag -

ol ErtDn> S W C vol

i e E

•


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


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


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


#BUDIFETDBOT

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


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


§ § § §

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



Amazon Aurora •

Amazon

RDBMS

– MySQL5.6/5.7

• – Amazon Aurora

•

PITR – Log Structured Storage – SOA


• 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


: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 :
