The MySQL Ecosystem @ GitHub 2015

Sam Lambert

April 15, 2015

Tweet Share

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 !