YesSQL, Process and Tooling at Scale

YesSQL, Process and Tooling at Scale

SQL databases have been a primary data storage for more than four decades and at GitHub that hasn't been the exception, but scaling applications also means scaling teams. We not only need to make sure that the application can support the load, but also that engineers can keep building performant and usable features.

This talk will dig into how GitHub creates a culture of performance to keep delivering a product that is fast and highly available.

D09fad3e36ae6841f54820be0e907f7a?s=128

Rocio Delgado

September 14, 2016
Tweet

Transcript

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

  2. whoami? @rokkzy @rocio !

  3. FIN

  4. FIN

  5. None
  6. One system fits all? !

  7. One system fits all !

  8. " # $ 16+ M Users 125+ M Issues 38+

    M Repos % 78+ M Pull Requests* * Since 2010
  9. Team Growth

  10. Team Growth

  11. ! Performance as a Feature

  12. Why care about performance?

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

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

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

    Culture " & ' Everybody’s responsibility Process & Tooling Metrics
  16. None
  17. None
  18. Process & Tooling

  19. Process & Tooling

  20. Deploying code

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

    ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (
  22. None
  23. GitHub’s Online Schema Transmogrifier Transfiguration Transformer Thingy

  24. ✴ Triggerless ✴ Lightweight ✴ Pauseable ✴ Dynamically controllable ✴

    Auditable ✴ Testable ✴ Trustable
  25. Process & Tooling

  26. Process & Tooling

  27. Process & Tooling

  28. Process & Tooling

  29. Process & Tooling

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

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

  33. Collaboration

  34. Tooling

  35. Speaking of tools…

  36. Process & Tooling - Peek

  37. Process & Tooling - Peek

  38. Finding N+1’s

  39. Process & Tooling - Haystack

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

    Metrics
  41. Measure all the things

  42. Measure all the things

  43. Measure all the things

  44. Putting it all together…

  45. None
  46. VividCortex

  47. Cloning the table to staging

  48. Testing a new index and open PR

  49. Migration is approved

  50. Schedule migration

  51. Run migration

  52. Ship it

  53. .explain

  54. Profit

  55. Verify

  56. No more needles

  57. ! Using Science

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

  59. Performance test between 2 indexes

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

    Metrics
  61. ! MySQL as primary data store

  62. MySQL ✴ Predictability ✴ Scalability ✴ High performance ✴ High

    availability ✴ Operational experience ✴ Optimized for fast reads
  63. K,V table in MySQL

  64. Storing users dismissal notices in K,V

  65. Storing users dismissal notices in K,V

  66. Checking for presence

  67. SQL queries on KV table per second

  68. ! What IS a good index strategy?

  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
  70. ! What’s NOT a good index strategy?

  71. Lack of indexes

  72. Unnecessary indexes ✴ Indexes require space, the more you have

    the bigger the table. ✴ Write operations will be slower.
  73. Finding unused indexes

  74. ! What’s coming next in MySQL?

  75. You had me at emoji

  76. ! It’s work in progress

  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
  78. FIN @rokkzy @rocio ! Gracias!