YesSQL, Process and Tooling at Scale

Transcript

1. YesSQL,
   Process and Tooling
   at Scale
   Rocio Delgado
   Universe 2016
   

   View Slide

2. whoami?
   @rokkzy
   @rocio
   !
   

   View Slide

3. FIN
   

   View Slide

4. FIN
   

   View Slide

5. View Slide

6. One system fits all?
   !
   

   View Slide

7. One system fits all
   !
   

   View Slide

8. " # $
   16+ M Users 125+ M Issues 38+ M Repos
   %
   78+ M Pull Requests*
   * Since 2010
   

   View Slide

9. Team Growth
   

   View Slide

10. Team Growth
    

    View Slide

11. !
    Performance as a
    Feature
    

    View Slide

12. Why care about performance?
    

    View Slide

13. " & '
    Everybody’s
    responsibility
    Process &
    Tooling
    Metrics
    Performance Culture
    

    View Slide

14. https://www.flickr.com/photos/wocintechchat/
    

    View Slide

15. " & '
    Everybody’s
    responsibility
    Process &
    Tooling
    Metrics
    Performance Culture
    " & '
    Everybody’s
    responsibility
    Process &
    Tooling
    Metrics
    

    View Slide

16. View Slide

17. View Slide

18. Process & Tooling
    

    View Slide

19. Process & Tooling
    

    View Slide

20. Deploying code
    

    View Slide

21. +
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    ( ( ( ( ( (
    

    View Slide

22. View Slide

23. GitHub’s Online Schema
    Transmogrifier
    Transfiguration
    Transformer
    Thingy
    

    View Slide

24. ✴ Triggerless
    ✴ Lightweight
    ✴ Pauseable
    ✴ Dynamically controllable
    ✴ Auditable
    ✴ Testable
    ✴ Trustable
    

    View Slide

25. Process & Tooling
    

    View Slide

26. Process & Tooling
    

    View Slide

27. Process & Tooling
    

    View Slide

28. Process & Tooling
    

    View Slide

29. Process & Tooling
    

    View Slide

30. View Slide

31. A process alone won’t solve all the problems.
    

    View Slide

32. https://www.flickr.com/photos/wocintechchat/
    Communication
    

    View Slide

33. Collaboration
    

    View Slide

34. Tooling
    

    View Slide

35. Speaking of tools…
    

    View Slide

36. Process & Tooling - Peek
    

    View Slide

37. Process & Tooling - Peek
    

    View Slide

38. Finding N+1’s
    

    View Slide

39. Process & Tooling - Haystack
    

    View Slide

40. Performance Culture
    " & '
    Everybody’s
    responsibility
    Process &
    Tooling
    Metrics
    

    View Slide

41. Measure all the things
    

    View Slide

42. Measure all the things
    

    View Slide

43. Measure all the things
    

    View Slide

44. Putting it all together…
    

    View Slide

45. View Slide

46. VividCortex
    

    View Slide

47. Cloning the table to staging
    

    View Slide

48. Testing a new index and open PR
    

    View Slide

49. Migration is approved
    

    View Slide

50. Schedule migration
    

    View Slide

51. Run migration
    

    View Slide

52. Ship it
    

    View Slide

53. .explain
    

    View Slide

54. Profit
    

    View Slide

55. Verify
    

    View Slide

56. No more needles
    

    View Slide

57. ! Using Science
    

    View Slide

58. Scientist
    https://github.com/github/scientist
    

    View Slide

59. Performance test between 2 indexes
    

    View Slide

60. Performance Culture
    " & '
    Everyone’s
    responsibility
    Process &
    Tooling
    Metrics
    

    View Slide

61. !
    MySQL as primary data
    store
    

    View Slide

62. MySQL
    ✴ Predictability
    ✴ Scalability
    ✴ High performance
    ✴ High availability
    ✴ Operational experience
    ✴ Optimized for fast reads
    

    View Slide

63. K,V table in MySQL
    

    View Slide

64. Storing users dismissal notices in K,V
    

    View Slide

65. Storing users dismissal notices in K,V
    

    View Slide

66. Checking for presence
    

    View Slide

67. SQL queries on KV table per second
    

    View Slide

68. !
    What IS a good index
    strategy?
    

    View Slide

69. ✴Build indexes for performance critical queries
    ✴Build index order that benefits more queries
    ✴SELECT * FROM issues WHERE user_id = 2 AND repository_id = 2;
    ✴SELECT * FROM issues WHERE user_id > 2 AND repository_id= 2;
    ✴INDEX ON (repository_id, user_id) is best
    ✴Prefer to extend an existing index rather than create a new one
    ✴Favor multi column indexes
    

    View Slide

70. !
    What’s NOT a good index
    strategy?
    

    View Slide

71. Lack of indexes
    

    View Slide

72. Unnecessary indexes
    ✴ Indexes require space, the more you have the
    bigger the table.
    ✴ Write operations will be slower.
    

    View Slide

73. Finding unused indexes
    

    View Slide

74. !
    What’s coming next in
    MySQL?
    

    View Slide

75. You had me at emoji
    

    View Slide

76. ! It’s work in progress
    

    View Slide

77. Hubot https://hubot.github.com
    gh-ost https://github.com/github/gh-ost
    Haystack http://githubengineering.com/exception-monitoring-and-response
    Peek https://github.com/peek/peek
    Scientist https://github.com/github/scientist
    Graphite http://graphite.wikidot.com
    

    View Slide

78. FIN
    @rokkzy
    @rocio
    !
    Gracias!
    

    View Slide