Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

The MySQL Ecosystem @ GitHub 2015

The MySQL Ecosystem @ GitHub 2015

A talk given at Percona Live 2015

Sam Lambert

April 15, 2015
Tweet

More Decks by Sam Lambert

Other Decks in Technology

Transcript

  1. the best way to build and ship software Sam Lambert

    Director of Technology ! github.com/samlambert " twitter.com/isamlambert # samlambert.com 2 !
  2. " 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 #
  3. " 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
  4. " the best way to build and ship software %

    & ' 6 What is GitHub? 130+ TB Git data 239 GitHubbers 100 Engineers
  5. the best way to build and ship software What is

    MySQL’s role at GitHub? 9 ?
  6. " 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
  7. the best way to build and ship software What is

    the Ecosystem? 12 " mysqld everything else
  8. the best way to build and ship software Why is

    the Ecosystem so important? 13 ?
  9. " 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 % /
  10. the best way to build and ship software Majority of

    queries served from one host 20 1
  11. the best way to build and ship software Replicas only

    used for backups and failover 21 1
  12. the best way to build and ship software Traffic spikes

    caused query response times to go up 25 2
  13. " the best way to build and ship software 29

    5 , HARDWARE NETWORKING • More hosts • Faster CPUs • SSDs • 10gb New Datacenter
  14. " 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
  15. " the best way to build and ship software 56

    > • C daemon • Takes exceptions from the app • Queues them • Posts to Haystack Failbotd
  16. " 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
  17. the best way to build and ship software Part of

    shipping is adding ChatOps 77 "
  18. " 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>
  19. " 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
  20. " the best way to build and ship software 103

    C • Monitoring app • Set up backends • Easily gather metrics • Pushed to Graphme Amen
  21. the best way to build and ship software We have

    these tools. Let’s use them 104 '
  22. the best way to build and ship software 109 primary

    replica replica replica github.com nginx unicorn
  23. the best way to build and ship software 111 primary

    github.com nginx unicorn haproxy Read Proxy replica replica replica
  24. the best way to build and ship software Send all

    GET requests to a replica 121 -
  25. the best way to build and ship software Stick to

    master for a second after POST 122 -
  26. the best way to build and ship software How do

    you refactor these changes? 123 .
  27. the best way to build and ship software Set up

    write alert connection 125 -
  28. the best way to build and ship software Now we

    are sensitive to delay 133 E
  29. " 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
  30. " 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
  31. " 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
  32. " the best way to build and ship software 157

    K • GTIDs • MySQL 5.7 • Cluster aware monitoring • Failover improvements • So much more Roadmap
  33. the best way to build and ship software Sam Lambert

    Director of Technology ! github.com/samlambert " twitter.com/isamlambert # samlambert.com 159 !