Save 37% off PRO during our Black Friday Sale! »

Database scaling patterns

Database scaling patterns

34be88398f623c109b61d23e8215bd23?s=128

Mariusz Gil

April 12, 2013
Tweet

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