Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up
for free
Database scaling patterns
Mariusz Gil
April 12, 2013
Programming
2
460
Database scaling patterns
Mariusz Gil
April 12, 2013
Tweet
Share
More Decks by Mariusz Gil
See All by Mariusz Gil
mariuszgil
1
210
mariuszgil
1
28
mariuszgil
0
150
mariuszgil
1
140
mariuszgil
1
290
mariuszgil
5
430
mariuszgil
0
280
mariuszgil
3
450
mariuszgil
8
510
Other Decks in Programming
See All in Programming
ken3ypa
0
160
adoranwodo
0
250
siketyan
1
120
shigeruoda
0
490
phoenixhawk
0
100
yshrsmz
1
460
nbkouhou
9
4.8k
azdaroth
0
150
yanagii
0
190
showwin
0
130
nbkouhou
1
1.3k
kyonmm
2
2.2k
Featured
See All Featured
3n
163
22k
jnunemaker
PRO
40
4.6k
eileencodes
113
25k
bermonpainter
342
26k
hursman
106
9.2k
notwaldorf
13
1.6k
wjessup
338
16k
destraynor
223
47k
zakiwarfel
88
3.3k
bkeepers
52
4.2k
myddelton
109
11k
reverentgeek
27
1.9k
Transcript
Database scaling patterns Mariusz Gil the we a way
ABOUT ME
EXPERIENCE from 1 to 100+ DB servers
EXPERIENCE from 1 to 100+ DB servers
What i it a ABOUT ?
DATABASE IS MAIN PROBLEM IN 99% OF WEB APPS
TYPICAL ISSUES? NORMALIZED TO xNF INEFFICIENT SCHEMA, BAD INDEXES AND
QUERIES, UNUSED SLOW-LOG
shared setup
!" # $ %% & ' !( !" # !" # shared server
cache all the things, wisely...
!" # $ %% & ' !( !" # !" # shared server
separated servers
!" # $ %% & ' !( !" # !" # DB server
scale up
scale up
denormalization
DB server
!" # $ %% & ' !( !" # !" # !" # $ %% & ' !( !" # !" # DB server
DB server
!" # $ %% & ' !( !" # !" # DB server !" # $ %% & ' !( !" # ) !" # )
DB server DB server
!" # $ %% & ' !( !" # ) !" # ) # $ %% & ' !( !" # !" # ID SENDER_ID RECEIVER_ID ... 1 10 1 2 20 2 3 10 1 4 20 3 5 10 1 6 20 4 7 10 1 USER_ID MESSAGE_ID 1 1 1 3 1 5 1 7 2 2 3 4 4 6 USER_ID MESSAGE_ID 10 1 10 3 10 5 10 7 20 2 20 4 20 6
replication
MASTER-SLAVE scale reads, but not writes
DB master
!" # $ %% & ' !( !" # ) !" # ) DB slave !" # $ %% & ' !( !" # ) !" # ) DB slave !" # $ %% & ' !( !" # ) !" # ) ... INSERT... UPDATE... DELETE... SELECT... SELECT...
MASTER-MASTER just for HA, not for scaling
DB master
!" # $ %% & ' !( !" # ) !" # ) DB master !" # $ %% & ' !( !" # ) !" # ) INSERT... UPDATE... DELETE... SELECT...
sharding
DB shard $ %% &
' !( !" # ) !" # ) DB shard !" # $ %% & ' !( !" # ) !" # ) DB shard !" # $ %% & ' !( !" # ) !" # ) INSERT... UPDATE... DELETE... SELECT... INSERT... UPDATE... DELETE... SELECT... INSERT... UPDATE... DELETE... SELECT...
Sharding i HARD !
Resharding i HARDER !
JOINS FORGET ABOUT IT data could be splitted into many
shards
ALTERS THINK TWICE ABOUT IT altering many shards could be
a nice challenge
fault tolerant multi-master sharding
SCALE WHEN NECESSARY NOT FOR FUN
Alternative TOOLS ?
None
None
None
None
None
Thanks! Any questions? @mariuszgil mariusz@mariuszgil.pl