Tweet
Tweet

Slide 1

Slide 1 text

Database scaling patterns Mariusz Gil the we a way

Slide 2

Slide 2 text

ABOUT ME

Slide 3

Slide 3 text

EXPERIENCE from 1 to 100+ DB servers

Slide 4

Slide 4 text

EXPERIENCE from 1 to 100+ DB servers

Slide 5

Slide 5 text

What i it a ABOUT ?

Slide 6

Slide 6 text

DATABASE IS MAIN PROBLEM IN 99% OF WEB APPS

Slide 7

Slide 7 text

TYPICAL ISSUES? NORMALIZED TO xNF INEFFICIENT SCHEMA, BAD INDEXES AND QUERIES, UNUSED SLOW-LOG

Slide 8

Slide 8 text

shared setup

Slide 9

Slide 9 text

          !"  #   $    %% & ' !(     !"  #    !"  # shared server

Slide 10

Slide 10 text

cache all the things, wisely...

Slide 11

Slide 11 text

          !"  #   $    %% & ' !(     !"  #    !"  # shared server

Slide 12

Slide 12 text

separated servers

Slide 13

Slide 13 text

          !"  #   $    %% & ' !(     !"  #    !"  # DB server

Slide 14

Slide 14 text

scale up

Slide 15

Slide 15 text

scale up

Slide 16

Slide 16 text

denormalization

Slide 17

Slide 17 text

DB server           !"  #   $    %% & ' !(     !"  #    !"  #           !"  #   $    %% & ' !(     !"  #    !"  # DB server

Slide 18

Slide 18 text

DB server           !"  #   $    %% & ' !(     !"  #    !"  # DB server           !"  #   $    %% & ' !(     !"  #   )     !"  #     ) 

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

replication

Slide 21

Slide 21 text

MASTER-SLAVE scale reads, but not writes

Slide 22

Slide 22 text

DB master           !"  #   $    %% & ' !(     !"  #   )     !"  #     )  DB slave           !"  #   $    %% & ' !(     !"  #   )     !"  #     )  DB slave           !"  #   $    %% & ' !(     !"  #   )     !"  #     )  ... INSERT... UPDATE... DELETE... SELECT... SELECT...

Slide 23

Slide 23 text

MASTER-MASTER just for HA, not for scaling

Slide 24

Slide 24 text

DB master           !"  #   $    %% & ' !(     !"  #   )     !"  #     )  DB master           !"  #   $    %% & ' !(     !"  #   )     !"  #     )  INSERT... UPDATE... DELETE... SELECT...

Slide 25

Slide 25 text

sharding

Slide 26

Slide 26 text

DB shard   $    %% & ' !(     !"  #   )     !"  #     )  DB shard           !"  #   $    %% & ' !(     !"  #   )     !"  #     )  DB shard           !"  #   $    %% & ' !(     !"  #   )     !"  #     )  INSERT... UPDATE... DELETE... SELECT... INSERT... UPDATE... DELETE... SELECT... INSERT... UPDATE... DELETE... SELECT...

Slide 27

Slide 27 text

Sharding i HARD !

Slide 28

Slide 28 text

Resharding i HARDER !

Slide 29

Slide 29 text

JOINS FORGET ABOUT IT data could be splitted into many shards

Slide 30

Slide 30 text

ALTERS THINK TWICE ABOUT IT altering many shards could be a nice challenge

Slide 31

Slide 31 text

fault tolerant multi-master sharding

Slide 32

Slide 32 text

SCALE WHEN NECESSARY NOT FOR FUN

Slide 33

Slide 33 text

Alternative TOOLS ?

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

No content

Slide 36

Slide 36 text

No content

Slide 37

Slide 37 text

No content

Slide 38

Slide 38 text

No content

Slide 39

Slide 39 text

Thanks! Any questions? @mariuszgil [email protected]