The MySQL Ecosystem @ GitHub 2015

A talk given at Percona Live 2015

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 !