RDSã§ã¯ç¾å¨MySQL / MariaDB / PostgreSQL / Oracle / MS. SQL Serverãé¸æå¯è½ .... 2. L g d bS. L g d bS. 3. 3. /
.
© 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 1 5
• • IO •
: $0.120/GB/ : $0.240/100
7
.
/
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
6
5
Amazon Aurora • MySQL5.6 •
10GB
• 3AZ –
2
64TB
6
S3
• VPC –
Security Group NACL
• Amazon Aurora
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 %. 2 %. 2 , E3
. 9 %, DW 3 W
Data Plane
SQL Transactions 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 2 •
6 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
)
-3
8
-7 4
-
,47-3
-
i
,
8
78
7
28
17 47 8726 26 25 6 8 17
( , -3 8
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
(/
T T -3 T g p
f O
T Ot TPuOl rOoy p i TPuOl y p PuOl e Ol y p - -
7 7 8
8AA KLH CA GH AK
A
)
26-7526
-7
8D N DGKL G A
(
B
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 D
m S
• •
A
0
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
a a 1 n
o6
0
aBG
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 C 3
• • •
1
T T Q
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
1
1
BB B
4I
• • •
1
C CD 4D 6 - D B
/B 4B CD4 6
3
A 64 CD4 6
1
1 / 2
3
3
L g d bS
6
v
BB B
wpRt l C v le RrokO
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
TLb
i
L
d
c
g bS
l
L
4I
ag
1
nNm
nNm Psu f e L
LZ
E BD D4 4D4
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
(
LFI L F MN BD
0. d Z h S 0. T / ./– b y rr y Z h y rr y e0. d Z h
N F MN
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.
IFLLKL
0.
0.
, 4
IFLLKL
r -I RK
(
582 .3758 © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved. 2
BG
( IF OND ( 4 38
/- -
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 / 9
32; f –
/E
IRNLGF LPTUFPGI
5
/ D 1 ( A; A9
26 60A 32 B 6 3
3
–y
rleo Z a Z kleod
)6. 15
32;
–y hm wi f
L
mc we –
)1
,7
n dh u w t ( h–t
8;5
06 8;5
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
3
2/ /
9
w
p-) 7 6 ;T
OLP UI XT0IPGK WSLUI QPNX WQSMNQF
6
i
zw
zw C FORNL LGFULQP
/OFYQP
n
8 e
50 FUF TIU
.6
2;A083 B 6 3
4 9 4683
h
s w
/ 55
CB
D
// / O
/ I
/ / O
KS
A
MP T
/ /
I
P RMP
IA WNF
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
/ / P EML A
/
C
• – – –
•
5
Application (-
V5S E L 32 o C 6 -) 5
--
•
5SA
n
E ML M
SQL Transactions
a
32
• •
5
W
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
Commit (T8)
LSN 50
C o mmit ( T 7)
L SN 47
C o mmit ( T 6)
L SN 41
C o mmit ( T 5)
L SN 34
C o mmit ( T 4)
L SN 30
C o mmit ( T 3)
L SN 22
Commit (T2)
LSN 12
Commit (T1)
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% 30.00% 25.00% 20.00% 15.00% 10.00% 5.00% 0.00%
-
50.00% 40.00% 30.00% 20.00% 10.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 BM
1 M
MtGe wG D tG
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 o
c
h i a c Ubg a c
w
ag
Ubg )
)
I ) / ag
) i
d
d (
/
u © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
e g
)
x I d U
O
U
t
g
g rn g
lm x
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.
:
• – – –
• I
;
' ;
–
H
:
; ;
O
AG
d
:
aF nblF K ei
NRR
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
D
;
:
WB ;:; V _L W SND 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 p MySQL Cp
L
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 c
IS 3
3
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
Q
yc u
Mfr w a
• – – e
uo
C
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
U
i h
SP
Q 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
• Query Cache
buffer / connection /
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
S M pDr c S M h Mld o
• Q •
R
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 g S rs
A m Db •
•
yS
0
O
im Db L
oceocPC A pP nDb oT nim
o
nim Db C –
•
B
P
o
E
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
o
u
TPCni
SR VP
EM
L HQP A oT
Db
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 1 Writer
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
IP 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
•
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.
•
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 O N 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
•
U
d a
•
D
/
• • •
J
y
• •
il UC
/ / D M
U e
/ / P
/ Pr
lc
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
l
m
M IR
x
UC
/
/ l M
Jwu
l l
p
owu
pM r
t Pns
EJAB lc
D
l
D
• • •
• • •
P d eDu DP P sD h D t
B
C c
D kD i
d
ae Dv d ae nm lDkr a o
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
//
B
p C D
//
C
//
•
sL •
.M
aW
m
A IE
•
lC
•
A 2
rL
.Cni
A kzSni s
•
•
M
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
-6.0 -
61 e
kr | r er a _ran tUe m er U
–
6
•
– i
q
ut
g
aor
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
r
0 ,
m
M q i D y
.- -
er *
er
s
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
)
(
• – 5
(1.10
– •
open SSL
•
– • • Open 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
o ( )2) /
)>= 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/CPU IO /CPU
Aurora
• Fast Insert – –
Primary key
LOAD DATA
( Global • 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 (Version 1.16 • equijoin hash join • •
) Aurora
R3.8xlarge 8.2x
– Batched Scan (Version 1.16 •
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
1.8x
)
Hash Joins Latency improvement factor vs. Batched Key Access (BKA) join algorithm Decision support benchmark, R3.8xlarge, cold buffer cache (lower improvement if all data cached) 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
-
Hash join used in queries 2, 3, 5, 7, 8, 9, 10, 11, 12, 15, 16, 17,18, 19, 21
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Batched scans 1
: • • • •
InnoDB
MySQL
Amazon Aurora InnoDB buffer pool
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
MySQL
2.00 1.80 1.60 1.40 1.20 1.00 0.80 0.60 0.40 0.20 -
Latency improvement factor vs. Batched Key Access (BKA) join algorithm Decision support benchmark, R3.8xlarge © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
.,
D:
-::
3
. / 1 / 1
3
/ 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
• – Amazon Aurora
•
PITR – Log Structured Storage – SOA
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
© 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.