The MySQL Ecosystem @ GitHub 2015

The MySQL Ecosystem @ GitHub 2015

A talk given at Percona Live 2015

Be9caeb9d4ef9944d151af909063ed6e?s=128

Sam Lambert

April 15, 2015
Tweet

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 !