The MySQL Ecosystem @ GitHub 2015

by Sam Lambert

Slide 1

Slide 1 text

the best way to build and ship software The MySQL Ecosystem @ GitHub

Slide 2

Slide 2 text

the best way to build and ship software Sam Lambert Director of Technology ! github.com/samlambert " twitter.com/isamlambert # samlambert.com 2 !

Slide 3

Slide 3 text

" 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 #

Slide 4

Slide 4 text

the best way to build and ship software GitHub Code hosting and collaboration 4 $

Slide 5

Slide 5 text

" 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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

the best way to build and ship software Slave 7 (

Slide 8

Slide 8 text

the best way to build and ship software Replica/Secondary 8 )

Slide 9

Slide 9 text

the best way to build and ship software What is MySQL’s role at GitHub? 9 ?

Slide 10

Slide 10 text

" 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

Slide 11

Slide 11 text

the best way to build and ship software What is the Ecosystem? 11 ?

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

the best way to build and ship software Why is the Ecosystem so important? 13 ?

Slide 14

Slide 14 text

the best way to build and ship software MySQL is ﬁne on its own 14 *

Slide 15

Slide 15 text

the best way to build and ship software We can (mostly) trust MySQL 15 +

Slide 16

Slide 16 text

the best way to build and ship software The GitHub Stack 16 ,

Slide 17

Slide 17 text

" 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 % /

Slide 18

Slide 18 text

the best way to build and ship software SELECT DATE_SUB(NOW(), INTERVAL 2 YEAR); 18 0

Slide 19

Slide 19 text

the best way to build and ship software Small number of MySQL hosts 19 ,

Slide 20

Slide 20 text

the best way to build and ship software Majority of queries served from one host 20 1

Slide 21

Slide 21 text

the best way to build and ship software Replicas only used for backups and failover 21 1

Slide 22

Slide 22 text

the best way to build and ship software Old hardware 22 1

Slide 23

Slide 23 text

the best way to build and ship software Unscalable 23 2

Slide 24

Slide 24 text

the best way to build and ship software Contention everywhere 24 2

Slide 25

Slide 25 text

the best way to build and ship software Traﬃc spikes caused query response times to go up 25 2

Slide 26

Slide 26 text

the best way to build and ship software Time for a change! 26 3

Slide 27

Slide 27 text

the best way to build and ship software Need to move data centers 27 4

Slide 28

Slide 28 text

the best way to build and ship software A chance to update hardware 28 4

Slide 29

Slide 29 text

" the best way to build and ship software 29 5 , HARDWARE NETWORKING • More hosts • Faster CPUs • SSDs • 10gb New Datacenter

Slide 30

Slide 30 text

the best way to build and ship software A chance to tune conﬁg 30 4

Slide 31

Slide 31 text

the best way to build and ship software Time to functionally shard 31 6

Slide 32

Slide 32 text

" the best way to build and ship software 32 7 • Partition by function • Split features out • Isolate tables as a whole • App speciﬁc • Milage may vary Functional Sharding

Slide 33

Slide 33 text

the best way to build and ship software Large volume single table 33 8

Slide 34

Slide 34 text

the best way to build and ship software Constantly growing 34 8

Slide 35

Slide 35 text

the best way to build and ship software No joins - app refactor 35 9

Slide 36

Slide 36 text

the best way to build and ship software Regression testing is essential 36 :

Slide 37

Slide 37 text

the best way to build and ship software Replaying queries from live 37 ;

Slide 38

Slide 38 text

the best way to build and ship software Long benchmarks +4 hours 38 5

Slide 39

Slide 39 text

the best way to build and ship software Go Live 39 <

Slide 40

Slide 40 text

the best way to build and ship software Maintenance window 40 '

Slide 41

Slide 41 text

the best way to build and ship software 13 minutes 41 3

Slide 42

Slide 42 text

" the best way to build and ship software Results! 42 MySQL app mean time

Slide 43

Slide 43 text

the best way to build and ship software Tooling 43 '

Slide 44

Slide 44 text

the best way to build and ship software Lots to mention 44 '

Slide 45

Slide 45 text

the best way to build and ship software Haystack 45 =

Slide 46

Slide 46 text

the best way to build and ship software Haystack 46 =

Slide 47

Slide 47 text

the best way to build and ship software Haystack 47 =

Slide 48

Slide 48 text

the best way to build and ship software Haystack 48 =

Slide 49

Slide 49 text

the best way to build and ship software Haystack 49 =

Slide 50

Slide 50 text

the best way to build and ship software Haystack 50 =

Slide 51

Slide 51 text

the best way to build and ship software Slow queries 51 9

Slide 52

Slide 52 text

the best way to build and ship software Slow queries 52 9

Slide 53

Slide 53 text

the best way to build and ship software Haystack 53 =

Slide 54

Slide 54 text

the best way to build and ship software Haystack is awesome 54 =

Slide 55

Slide 55 text

the best way to build and ship software Failbotd 55 >

Slide 56

Slide 56 text

" the best way to build and ship software 56 > • C daemon • Takes exceptions from the app • Queues them • Posts to Haystack Failbotd

Slide 57

Slide 57 text

the best way to build and ship software Staﬀ toolbar 57 ?

Slide 58

Slide 58 text

" the best way to build and ship software Staﬀ toolbar 58

Slide 59

Slide 59 text

the best way to build and ship software 59

Slide 60

Slide 60 text

the best way to build and ship software Chat 60 @

Slide 61

Slide 61 text

the best way to build and ship software ~137 remote employees 61 A

Slide 62

Slide 62 text

the best way to build and ship software 100+ chat rooms 62 A

Slide 63

Slide 63 text

the best way to build and ship software Hubot 63 "

Slide 64

Slide 64 text

the best way to build and ship software Who is Hubot? 64 "

Slide 65

Slide 65 text

" 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

Slide 66

Slide 66 text

the best way to build and ship software 66

Slide 67

Slide 67 text

the best way to build and ship software Open Source 67 "

Slide 68

Slide 68 text

the best way to build and ship software CoﬀeeScript 68 "

Slide 69

Slide 69 text

the best way to build and ship software hubot.github.com 69 "

Slide 70

Slide 70 text

the best way to build and ship software github.com/hubot-scripts 70 "

Slide 71

Slide 71 text

the best way to build and ship software Super easy to set up 71 "

Slide 72

Slide 72 text

" the best way to build and ship software Deploy to Heroku 72

Slide 73

Slide 73 text

the best way to build and ship software github.com/cobyism/hubot-heroku-slack 73 "

Slide 74

Slide 74 text

the best way to build and ship software What can hubot do? 74 "

Slide 75

Slide 75 text

the best way to build and ship software 850+ commands 75 "

Slide 76

Slide 76 text

the best way to build and ship software Nearly everything 76 "

Slide 77

Slide 77 text

the best way to build and ship software Part of shipping is adding ChatOps 77 "

Slide 78

Slide 78 text

" the best way to build and ship software /hq me 78

Slide 79

Slide 79 text

the best way to build and ship software ChatOps is core to our culture 79 "

Slide 80

Slide 80 text

" 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>

Slide 81

Slide 81 text

" the best way to build and ship software Invite your friends 81

Slide 82

Slide 82 text

the best way to build and ship software It’s all about context 82 "

Slide 83

Slide 83 text

the best way to build and ship software Context is implicit 83 "

Slide 84

Slide 84 text

the best way to build and ship software Incident responce 84 "

Slide 85

Slide 85 text

the best way to build and ship software DDoS 85 "

Slide 86

Slide 86 text

the best way to build and ship software /mitigation enable 86 "

Slide 87

Slide 87 text

the best way to build and ship software /mysql 87 "

Slide 88

Slide 88 text

" 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

Slide 89

Slide 89 text

" the best way to build and ship software /mysql explain 89

Slide 90

Slide 90 text

the best way to build and ship software Learn together 90 "

Slide 91

Slide 91 text

the best way to build and ship software github.com/samlambert/hubot-mysql-chatops 91

Slide 92

Slide 92 text

the best way to build and ship software Collaborative workspace 92 "

Slide 93

Slide 93 text

" the best way to build and ship software /mysql backup 93

Slide 94

Slide 94 text

" the best way to build and ship software /mysql clone 94

Slide 95

Slide 95 text

the best way to build and ship software Killed queries 95 "

Slide 96

Slide 96 text

" the best way to build and ship software Killed queries 96

Slide 97

Slide 97 text

the best way to build and ship software User access 97 "

Slide 98

Slide 98 text

" the best way to build and ship software User access 98

Slide 99

Slide 99 text

the best way to build and ship software Graphme 99 B

Slide 100

Slide 100 text

" the best way to build and ship software Graphme 100

Slide 101

Slide 101 text

" the best way to build and ship software /graph me 101

Slide 102

Slide 102 text

the best way to build and ship software Amen 102 C

Slide 103

Slide 103 text

" the best way to build and ship software 103 C • Monitoring app • Set up backends • Easily gather metrics • Pushed to Graphme Amen

Slide 104

Slide 104 text

the best way to build and ship software We have these tools. Let’s use them 104 '

Slide 105

Slide 105 text

the best way to build and ship software It’s about the app 105 -

Slide 106

Slide 106 text

the best way to build and ship software Let’s talk strategy 106 D

Slide 107

Slide 107 text

the best way to build and ship software All the hardware 107 ,

Slide 108

Slide 108 text

" the best way to build and ship software Single host 108

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

the best way to build and ship software Doesn’t scale 110 1

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

the best way to build and ship software Use existing patterns 112 1

Slide 113

Slide 113 text

the best way to build and ship software Tuning Haproxy 113 5

Slide 114

Slide 114 text

the best way to build and ship software App failover logic 114 5

Slide 115

Slide 115 text

the best way to build and ship software /mysqlproxy 115 "

Slide 116

Slide 116 text

" the best way to build and ship software /mysqlproxy status 116

Slide 117

Slide 117 text

the best way to build and ship software /mysqlproxy weight 117 "

Slide 118

Slide 118 text

the best way to build and ship software /mysqlproxy disable 118 "

Slide 119

Slide 119 text

the best way to build and ship software /mysqlproxy enable 119 "

Slide 120

Slide 120 text

the best way to build and ship software App refactor 120 -

Slide 121

Slide 121 text

the best way to build and ship software Send all GET requests to a replica 121 -

Slide 122

Slide 122 text

the best way to build and ship software Stick to master for a second after POST 122 -

Slide 123

Slide 123 text

the best way to build and ship software How do you refactor these changes? 123 .

Slide 124

Slide 124 text

the best way to build and ship software Some GETs make writes 124 .

Slide 125

Slide 125 text

the best way to build and ship software Set up write alert connection 125 -

Slide 126

Slide 126 text

the best way to build and ship software Write alerts go to Haystack 126 -

Slide 127

Slide 127 text

the best way to build and ship software Get the stack trace 127 -

Slide 128

Slide 128 text

the best way to build and ship software Gradual iteration over the codebase 128 -

Slide 129

Slide 129 text

" the best way to build and ship software Change 129

Slide 130

Slide 130 text

the best way to build and ship software Gitauth 130 -

Slide 131

Slide 131 text

" the best way to build and ship software Single host 131

Slide 132

Slide 132 text

" the best way to build and ship software Master 132

Slide 133

Slide 133 text

the best way to build and ship software Now we are sensitive to delay 133 E

Slide 134

Slide 134 text

" the best way to build and ship software Delay!! 134

Slide 135

Slide 135 text

the best way to build and ship software What is happening? 135 ?

Slide 136

Slide 136 text

" the best way to build and ship software Delay!! 136

Slide 137

Slide 137 text

the best way to build and ship software Ok, so deletes are happening 137 F

Slide 138

Slide 138 text

the best way to build and ship software Attack on two fronts 138 G

Slide 139

Slide 139 text

the best way to build and ship software Performance schema 139 5

Slide 140

Slide 140 text

" 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

Slide 141

Slide 141 text

" the best way to build and ship software Graphme 141

Slide 142

Slide 142 text

the best way to build and ship software Haystack 142 =

Slide 143

Slide 143 text

the best way to build and ship software Aﬀected rows bucket 143 H

Slide 144

Slide 144 text

the best way to build and ship software Haystack 144 =

Slide 145

Slide 145 text

" the best way to build and ship software Haystack 145

Slide 146

Slide 146 text

" the best way to build and ship software Graphme 146

Slide 147

Slide 147 text

the best way to build and ship software Too many aﬀected rows 147 9

Slide 148

Slide 148 text

" the best way to build and ship software Contributions 148

Slide 149

Slide 149 text

the best way to build and ship software Dependent destroy 149 F

Slide 150

Slide 150 text

the best way to build and ship software Problem found 150 I

Slide 151

Slide 151 text

the best way to build and ship software Throttler 151 J

Slide 152

Slide 152 text

" 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

Slide 153

Slide 153 text

" 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

Slide 154

Slide 154 text

the best way to build and ship software Problem solved 154 )

Slide 155

Slide 155 text

the best way to build and ship software We are hiring!! 155 &

Slide 156

Slide 156 text

the best way to build and ship software git.io/vv8yi 156 &

Slide 157

Slide 157 text

" the best way to build and ship software 157 K • GTIDs • MySQL 5.7 • Cluster aware monitoring • Failover improvements • So much more Roadmap

Slide 158

Slide 158 text

the best way to build and ship software Questions? 158 ?

Slide 159

Slide 159 text

the best way to build and ship software Sam Lambert Director of Technology ! github.com/samlambert " twitter.com/isamlambert # samlambert.com 159 !