The MySQL Ecosystem at GitHub

A talk given at Percona Live 2015 by Sam Lambert.

GitHub and English.

Transcript

1. the best way to build and ship software The MySQL Ecosystem @ GitHub
2. the best way to build and ship software Sam Lambert Director of Technology ! github.com/samlambert " twitter.com/isamlambert # samlambert.com 2 !
3. " the best way to build and ship software “ ” THE HUMAN BRAIN IS A WONDERFUL THING. IT STARTS WORKING THE MOMENT YOU ARE BORN, AND NEVER STOPS UNTIL YOU STAND UP TO SPEAK IN PUBLIC. - GEORGE JESSEL 3 #
4. the best way to build and ship software GitHub Code hosting and collaboration 4 $
5. " the best way to build and ship software 3.333 M 6.667 M 10 M 13.333 M 16.667 M 20 M 2015 What is GitHub? 5 DEVELOPERS ARE WORKING TOGETHER ON PROJECTS ON GITHUB RIGHT NOW 8 MILLION 21 MILLION
6. " the best way to build and ship software % & ' 6 What is GitHub? 130+ TB Git data 239 GitHubbers 100 Engineers
7. the best way to build and ship software Slave 7 (
8. the best way to build and ship software Replica/Secondary 8 )
9. the best way to build and ship software What is MySQL’s role at GitHub? 9 ?
10. " the best way to build and ship software What is MySQL’s role at GitHub? 10 janky mysql redis elasticsearch jenkins memcached pages nginx github-fs git-daemon ernicorn gitmon slumlord SVN analytics-app unicorn nginx github-babel babeld gitauth github-fe nginx unicorn github-api nginx unicorn gpanel unicorn resqued timerd alambic-fe alambic
11. the best way to build and ship software What is the Ecosystem? 11 ?
12. the best way to build and ship software What is the Ecosystem? 12 " mysqld everything else
13. the best way to build and ship software Why is the Ecosystem so important? 13 ?
14. the best way to build and ship software MySQL is fine on its own 14 *
15. the best way to build and ship software We can (mostly) trust MySQL 15 +
16. the best way to build and ship software The GitHub Stack 16 ,
17. " the best way to build and ship software 17 The GitHub Stack WEB BACKEND DATABASES Ruby on Rails Powerful application framework. Rapid development. - Javascript UI goodness . C Powers a number of backend applications . Puppet Provisioning . MySQL Main source of truth Elasticsearch Indexes all the code, as well as issues and pull requests. / Redis Resque, cache data. / Git Obviously % /
18. the best way to build and ship software SELECT DATE_SUB(NOW(), INTERVAL 2 YEAR); 18 0
19. the best way to build and ship software Small number of MySQL hosts 19 ,
20. the best way to build and ship software Majority of queries served from one host 20 1
21. the best way to build and ship software Replicas only used for backups and failover 21 1
22. the best way to build and ship software Old hardware 22 1
23. the best way to build and ship software Unscalable 23 2
24. the best way to build and ship software Contention everywhere 24 2
25. the best way to build and ship software Traffic spikes caused query response times to go up 25 2
26. the best way to build and ship software Time for a change! 26 3
27. the best way to build and ship software Need to move data centers 27 4
28. the best way to build and ship software A chance to update hardware 28 4
29. " the best way to build and ship software 29 5 , HARDWARE NETWORKING • More hosts • Faster CPUs • SSDs • 10gb New Datacenter
30. the best way to build and ship software A chance to tune config 30 4
31. the best way to build and ship software Time to functionally shard 31 6
32. " the best way to build and ship software 32 7 • Partition by function • Split features out • Isolate tables as a whole • App specific • Milage may vary Functional Sharding
33. the best way to build and ship software Large volume single table 33 8
34. the best way to build and ship software Constantly growing 34 8
35. the best way to build and ship software No joins - app refactor 35 9
36. the best way to build and ship software Regression testing is essential 36 :
37. the best way to build and ship software Replaying queries from live 37 ;
38. the best way to build and ship software Long benchmarks +4 hours 38 5
39. the best way to build and ship software Go Live 39 <
40. the best way to build and ship software Maintenance window 40 '
41. the best way to build and ship software 13 minutes 41 3
42. " the best way to build and ship software Results! 42 MySQL app mean time
43. the best way to build and ship software Tooling 43 '
44. the best way to build and ship software Lots to mention 44 '
45. the best way to build and ship software Haystack 45 =
46. the best way to build and ship software Haystack 46 =
47. the best way to build and ship software Haystack 47 =
48. the best way to build and ship software Haystack 48 =
49. the best way to build and ship software Haystack 49 =
50. the best way to build and ship software Haystack 50 =
51. the best way to build and ship software Slow queries 51 9
52. the best way to build and ship software Slow queries 52 9
53. the best way to build and ship software Haystack 53 =
54. the best way to build and ship software Haystack is awesome 54 =
55. the best way to build and ship software Failbotd 55 >
56. " the best way to build and ship software 56 > • C daemon • Takes exceptions from the app • Queues them • Posts to Haystack Failbotd
57. the best way to build and ship software Staff toolbar 57 ?
58. " the best way to build and ship software Staff toolbar 58
59. the best way to build and ship software 59
60. the best way to build and ship software Chat 60 @
61. the best way to build and ship software ~137 remote employees 61 A
62. the best way to build and ship software 100+ chat rooms 62 A
63. the best way to build and ship software Hubot 63 "
64. the best way to build and ship software Who is Hubot? 64 "
65. " the best way to build and ship software “ ” GITHUB, INC., WROTE THE FIRST VERSION OF HUBOT TO AUTOMATE OUR COMPANY CHAT ROOM. HUBOT KNEW HOW TO DEPLOY THE SITE, AUTOMATE A LOT OF TASKS, AND BE A SOURCE OF FUN IN THE COMPANY. EVENTUALLY HE GREW TO BECOME A FORMIDABLE FORCE IN GITHUB. BUT HE LED A PRIVATE, MESSY LIFE. SO WE REWROTE HIM. Who is Hubot? 65
66. the best way to build and ship software 66
67. the best way to build and ship software Open Source 67 "
68. the best way to build and ship software CoffeeScript 68 "
69. the best way to build and ship software hubot.github.com 69 "
70. the best way to build and ship software github.com/hubot-scripts 70 "
71. the best way to build and ship software Super easy to set up 71 "
72. " the best way to build and ship software Deploy to Heroku 72
73. the best way to build and ship software github.com/cobyism/hubot-heroku-slack 73 "
74. the best way to build and ship software What can hubot do? 74 "
75. the best way to build and ship software 850+ commands 75 "
76. the best way to build and ship software Nearly everything 76 "
77. the best way to build and ship software Part of shipping is adding ChatOps 77 "
78. " the best way to build and ship software /hq me 78
79. the best way to build and ship software ChatOps is core to our culture 79 "
80. " the best way to build and ship software You are on your own 80 ➜ ~ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.23 Homebrew Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
81. " the best way to build and ship software Invite your friends 81
82. the best way to build and ship software It’s all about context 82 "
83. the best way to build and ship software Context is implicit 83 "
84. the best way to build and ship software Incident responce 84 "
85. the best way to build and ship software DDoS 85 "
86. the best way to build and ship software /mitigation enable 86 "
87. the best way to build and ship software /mysql 87 "
88. " the best way to build and ship software /mysql 88 /mysql analyze analyze a table /mysql archive start or stop archiving on a MySQL cluster /mysql backup backup a table /mysql backup-­‐list list backups for a table /mysql clone clone a table to the MySQL staging environment /mysql cluster show cluster topology /mysql drop safely drops a mysql table /mysql drop-­‐all drops all eligible tables /mysql drop-­‐check checks if a table is safe to drop /mysql drop-­‐list lists the tables that are ready to be dropped /mysql drop-­‐pending lists the tables that are pending dropping /mysql dummy-­‐drop drops a table from production by renaming it /mysql engine shows output of show engine innodb status on a given host /mysql explain explain a query /mysql gather gathers MySQL diagnostic information /mysql log-­‐migration-­‐version insert the version of a migration into production /mysql inno list queries /mysql index-­‐stats Prints out index stats for the specified table /mysql kill kills queries matching supplied criteria /mysql kills shows victims of pt-­‐kill on given date /mysql maintenance begin and end the maintenance of a mysql node /mysql master-­‐swap returns the binlog position to start replication from on a new master /mysql mutexes prints a list of mutex events for a MySQL host /mysql nibble deletes all rows in a table /mysql nibble-­‐all nibble all non nibbled migration tables /mysql panic stops the delayed replica to prevent propagation of data loss /mysql pk-­‐pct shows the % of the primary key range that is used /mysql pool saves or restores the innodb buffer pool of a host 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
89. " the best way to build and ship software /mysql explain 89
90. the best way to build and ship software Learn together 90 "
91. the best way to build and ship software github.com/samlambert/hubot-mysql-chatops 91
92. the best way to build and ship software Collaborative workspace 92 "
93. " the best way to build and ship software /mysql backup 93
94. " the best way to build and ship software /mysql clone 94
95. the best way to build and ship software Killed queries 95 "
96. " the best way to build and ship software Killed queries 96
97. the best way to build and ship software User access 97 "
98. " the best way to build and ship software User access 98
99. the best way to build and ship software Graphme 99 B
100. " the best way to build and ship software Graphme 100
101. " the best way to build and ship software /graph me 101
102. the best way to build and ship software Amen 102 C
103. " the best way to build and ship software 103 C • Monitoring app • Set up backends • Easily gather metrics • Pushed to Graphme Amen
104. the best way to build and ship software We have these tools. Let’s use them 104 '
105. the best way to build and ship software It’s about the app 105 -
106. the best way to build and ship software Let’s talk strategy 106 D
107. the best way to build and ship software All the hardware 107 ,
108. " the best way to build and ship software Single host 108
109. the best way to build and ship software 109 primary replica replica replica github.com nginx unicorn
110. the best way to build and ship software Doesn’t scale 110 1
111. the best way to build and ship software 111 primary github.com nginx unicorn haproxy Read Proxy replica replica replica
112. the best way to build and ship software Use existing patterns 112 1
113. the best way to build and ship software Tuning Haproxy 113 5
114. the best way to build and ship software App failover logic 114 5
115. the best way to build and ship software /mysqlproxy 115 "
116. " the best way to build and ship software /mysqlproxy status 116
117. the best way to build and ship software /mysqlproxy weight 117 "
118. the best way to build and ship software /mysqlproxy disable 118 "
119. the best way to build and ship software /mysqlproxy enable 119 "
120. the best way to build and ship software App refactor 120 -
121. the best way to build and ship software Send all GET requests to a replica 121 -
122. the best way to build and ship software Stick to master for a second after POST 122 -
123. the best way to build and ship software How do you refactor these changes? 123 .
124. the best way to build and ship software Some GETs make writes 124 .
125. the best way to build and ship software Set up write alert connection 125 -
126. the best way to build and ship software Write alerts go to Haystack 126 -
127. the best way to build and ship software Get the stack trace 127 -
128. the best way to build and ship software Gradual iteration over the codebase 128 -
129. " the best way to build and ship software Change 129
130. the best way to build and ship software Gitauth 130 -
131. " the best way to build and ship software Single host 131
132. " the best way to build and ship software Master 132
133. the best way to build and ship software Now we are sensitive to delay 133 E
134. " the best way to build and ship software Delay!! 134
135. the best way to build and ship software What is happening? 135 ?
136. " the best way to build and ship software Delay!! 136
137. the best way to build and ship software Ok, so deletes are happening 137 F
138. the best way to build and ship software Attack on two fronts 138 G
139. the best way to build and ship software Performance schema 139 5
140. " the best way to build and ship software Amen - IO waits by index 140 @client.query "SELECT object_name AS table_name,\ LOWER(index_name) as index_name,\ count_fetch AS select_count,\ count_insert AS insert_count,\ count_update AS update_count,\ count_delete AS delete_count\ FROM performance_schema.table_io_waits_summary_by_index_usage\ WHERE object_schema = 'github_production'\ AND object_name NOT REGEXP '^_.'", (err, results) => if err log 'error', err else prefix = "#{metric}.table_stats.waits" for row in results @gauge "#{prefix}.#{row.table_name}.#{row.index_name}.select.count", parseInt(row.select_count) @gauge "#{prefix}.#{row.table_name}.#{row.index_name}.insert.count", parseInt(row.insert_count) @gauge "#{prefix}.#{row.table_name}.#{row.index_name}.update.count", parseInt(row.update_count) @gauge "#{prefix}.#{row.table_name}.#{row.index_name}.delete.count", parseInt(row.delete_count) 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
141. " the best way to build and ship software Graphme 141
142. the best way to build and ship software Haystack 142 =
143. the best way to build and ship software Affected rows bucket 143 H
144. the best way to build and ship software Haystack 144 =
145. " the best way to build and ship software Haystack 145
146. " the best way to build and ship software Graphme 146
147. the best way to build and ship software Too many affected rows 147 9
148. " the best way to build and ship software Contributions 148
149. the best way to build and ship software Dependent destroy 149 F
150. the best way to build and ship software Problem found 150 I
151. the best way to build and ship software Throttler 151 J
152. " the best way to build and ship software Github::Throttler 152 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
153. " the best way to build and ship software Github::Throttler 153 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
154. the best way to build and ship software Problem solved 154 )
155. the best way to build and ship software We are hiring!! 155 &
156. the best way to build and ship software git.io/vv8yi 156 &
157. " the best way to build and ship software 157 K • GTIDs • MySQL 5.7 • Cluster aware monitoring • Failover improvements • So much more Roadmap
158. the best way to build and ship software Questions? 158 ?
159. the best way to build and ship software Sam Lambert Director of Technology ! github.com/samlambert " twitter.com/isamlambert # samlambert.com 159 !