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.

Transcript

1. YesSQL, Process and Tooling at Scale Rocio Delgado Universe 2016
2. whoami? @rokkzy @rocio !
3. FIN
4. FIN
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
18. Process & Tooling
19. Process & Tooling
20. Deploying code
21. + ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (
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
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…
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!