Database scaling patterns

Transcript

1. Database scaling patterns Mariusz Gil the we a way

2. ABOUT ME

3. EXPERIENCE from 1 to 100+ DB servers

4. EXPERIENCE from 1 to 100+ DB servers

5. What i it a ABOUT ?

6. DATABASE IS MAIN PROBLEM IN 99% OF WEB APPS

7. TYPICAL ISSUES? NORMALIZED TO xNF INEFFICIENT SCHEMA, BAD INDEXES AND

   QUERIES, UNUSED SLOW-LOG

8. shared setup

9.          

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

10. cache all the things, wisely...

11.          

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

12. separated servers

13.          

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

14. scale up

15. scale up

16. denormalization

17. DB server        

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

18. DB server        

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

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

20. replication

21. MASTER-SLAVE scale reads, but not writes

22. DB master        

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

23. MASTER-MASTER just for HA, not for scaling

24. DB master        

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

25. sharding

26. DB shard   $    %% &

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

27. Sharding i HARD !

28. Resharding i HARDER !

29. JOINS FORGET ABOUT IT data could be splitted into many

    shards

30. ALTERS THINK TWICE ABOUT IT altering many shards could be

    a nice challenge

31. fault tolerant multi-master sharding

32. SCALE WHEN NECESSARY NOT FOR FUN

33. Alternative TOOLS ?

34. None
35. None
36. None
37. None
38. None

39. Thanks! Any questions? @mariuszgil mariusz@mariuszgil.pl