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 ?
39. Thanks! Any questions? @mariuszgil mariusz@mariuszgil.pl