Slide 1

Slide 1 text

Successful MySQL Scalability Ronald Bradford 2010.08 www.RonaldBradford.com Friday, September 10, 2010

Slide 2

Slide 2 text

The one hour version Friday, September 10, 2010

Slide 3

Slide 3 text

Successful MySQL Scalability Necessary Principles System Architecture Data Availability Best Practices Being proactive OUTLINE Friday, September 10, 2010

Slide 4

Slide 4 text

Successful MySQL Scalability 1 1 Friday, September 10, 2010

Slide 5

Slide 5 text

Successful MySQL Scalability Integrated monitoring and instrumentation Action 1 Friday, September 10, 2010

Slide 6

Slide 6 text

Successful MySQL Scalability Question: How do you know when your server is down or not accessible? Answer: The users will let us know. EXAMPLE 1 ✘ Friday, September 10, 2010

Slide 7

Slide 7 text

Successful MySQL Scalability How 1 Friday, September 10, 2010

Slide 8

Slide 8 text

Successful MySQL Scalability Monitoring/Alerting Graphical Historical Necessary Generally missing/incomplete Useless for real-time analysis How 1 Friday, September 10, 2010

Slide 9

Slide 9 text

Successful MySQL Scalability Monitoring/Alerting Graphical Historical Necessary Generally missing/incomplete Useless for real-time analysis How 1 REACTIVE Friday, September 10, 2010

Slide 10

Slide 10 text

Successful MySQL Scalability How 1 Friday, September 10, 2010

Slide 11

Slide 11 text

Successful MySQL Scalability Dashboard The state of NOW Sampling at 1s/3s/5s e.g. 0.1% of throughput How 1 Friday, September 10, 2010

Slide 12

Slide 12 text

Successful MySQL Scalability Dashboard The state of NOW Sampling at 1s/3s/5s e.g. 0.1% of throughput How 1 PROACTIVE Friday, September 10, 2010

Slide 13

Slide 13 text

Successful MySQL Scalability How 1 Friday, September 10, 2010

Slide 14

Slide 14 text

Successful MySQL Scalability Instrumentation Important to business viability e.g. orders per minute Seamless implementation i.e. no code changes to view real-time extensible How 1 Friday, September 10, 2010

Slide 15

Slide 15 text

Successful MySQL Scalability Instrumentation Important to business viability e.g. orders per minute Seamless implementation i.e. no code changes to view real-time extensible How 1 PROACTIVE Friday, September 10, 2010

Slide 16

Slide 16 text

Successful MySQL Scalability Monitoring + Instrumentation = Preemptive Analysis How 1 Friday, September 10, 2010

Slide 17

Slide 17 text

Successful MySQL Scalability How 1 ✘ Friday, September 10, 2010

Slide 18

Slide 18 text

Successful MySQL Scalability How 1 ✔ < 1 second Friday, September 10, 2010

Slide 19

Slide 19 text

Successful MySQL Scalability Preemptive Analysis Fail early Faster user response Less physical resource impact WHY 1 Friday, September 10, 2010

Slide 20

Slide 20 text

Successful MySQL Scalability TIP 1 Friday, September 10, 2010

Slide 21

Slide 21 text

Successful MySQL Scalability Have a status website allow for comments (e.g. blog) TIP 1 Friday, September 10, 2010

Slide 22

Slide 22 text

Successful MySQL Scalability Have a status website allow for comments (e.g. blog) Have a public dashboard website TIP 1 Friday, September 10, 2010

Slide 23

Slide 23 text

Successful MySQL Scalability Have a status website allow for comments (e.g. blog) Have a public dashboard website Host them somewhere else! TIP 1 Friday, September 10, 2010

Slide 24

Slide 24 text

Successful MySQL Scalability 2 2 Friday, September 10, 2010

Slide 25

Slide 25 text

Successful MySQL Scalability Seamless automated server deployment Action 2 Friday, September 10, 2010

Slide 26

Slide 26 text

Successful MySQL Scalability Actual Client 100+ production database servers Full-time DBAs No automated software installation No single documented installation process No version control No auditing/verification Example 2 ✘ Friday, September 10, 2010

Slide 27

Slide 27 text

Successful MySQL Scalability Basics Version Control Automated Build & Release Integrated Monitoring How 2 Friday, September 10, 2010

Slide 28

Slide 28 text

Successful MySQL Scalability Intermediate Health Checks Runtime config file management Benchmarking How 2 Friday, September 10, 2010

Slide 29

Slide 29 text

Successful MySQL Scalability Advanced Server request queue Automated discovery Proactive scaling (up & down) How 2 Friday, September 10, 2010

Slide 30

Slide 30 text

Successful MySQL Scalability Consistent and timely provisioning Unattended installation with correct and reproducible configuration Automated discovery GOAL 2 ✔ Friday, September 10, 2010

Slide 31

Slide 31 text

Successful MySQL Scalability 3 3 Friday, September 10, 2010

Slide 32

Slide 32 text

Successful MySQL Scalability Disaster is inevitable Action 3 Friday, September 10, 2010

Slide 33

Slide 33 text

Successful MySQL Scalability Question: Have you ever performed a database recovery? Answer: No, why? Example 3 ✘ Friday, September 10, 2010

Slide 34

Slide 34 text

Successful MySQL Scalability Consultant: Do you know that your daily backups only recover the data up to that time( e.g. 1 am). You know you have lost all your sales and data changes since then. Customer: No, I didn’t know that. Example 3 ✘ Friday, September 10, 2010

Slide 35

Slide 35 text

Successful MySQL Scalability HOW 3 Friday, September 10, 2010

Slide 36

Slide 36 text

Successful MySQL Scalability Have a DR plan HOW 3 Friday, September 10, 2010

Slide 37

Slide 37 text

Successful MySQL Scalability Have a DR plan Documented HOW 3 Friday, September 10, 2010

Slide 38

Slide 38 text

Successful MySQL Scalability Have a DR plan Documented Tested HOW 3 Friday, September 10, 2010

Slide 39

Slide 39 text

Successful MySQL Scalability Have a DR plan Documented Tested Timed HOW 3 Friday, September 10, 2010

Slide 40

Slide 40 text

Successful MySQL Scalability Have a DR plan Documented Tested Timed Verified HOW 3 Friday, September 10, 2010

Slide 41

Slide 41 text

Successful MySQL Scalability HOW 3 Friday, September 10, 2010

Slide 42

Slide 42 text

Successful MySQL Scalability Test under production conditions HOW 3 Friday, September 10, 2010

Slide 43

Slide 43 text

Successful MySQL Scalability Test under production conditions System Load HOW 3 Friday, September 10, 2010

Slide 44

Slide 44 text

Successful MySQL Scalability Test under production conditions System Load Database Size HOW 3 Friday, September 10, 2010

Slide 45

Slide 45 text

Successful MySQL Scalability Test under production conditions System Load Database Size End to End HOW 3 Friday, September 10, 2010

Slide 46

Slide 46 text

Successful MySQL Scalability Know and practice for disaster Provide confidence to management Be as confident about your recovery as your scalability GOAL 3 ✔ Friday, September 10, 2010

Slide 47

Slide 47 text

Successful MySQL Scalability What does your website look like when it’s down? SIDEBAR! 3 Friday, September 10, 2010

Slide 48

Slide 48 text

Successful MySQL Scalability SIDEBAR! 3 Google Picasa ✘ Friday, September 10, 2010

Slide 49

Slide 49 text

Successful MySQL Scalability SIDEBAR! 3 Mozilla ✘ Friday, September 10, 2010

Slide 50

Slide 50 text

Successful MySQL Scalability SIDEBAR! 3 ✔ Friday, September 10, 2010

Slide 51

Slide 51 text

Successful MySQL Scalability SIDEBAR! 3 ✔ Friday, September 10, 2010

Slide 52

Slide 52 text

Successful MySQL Scalability 4 4 Friday, September 10, 2010

Slide 53

Slide 53 text

Successful MySQL Scalability Application Programming Interface Action 4 Friday, September 10, 2010

Slide 54

Slide 54 text

Successful MySQL Scalability Example 4 ✘ Friday, September 10, 2010

Slide 55

Slide 55 text

Successful MySQL Scalability Public API Example 4 ✘ Friday, September 10, 2010

Slide 56

Slide 56 text

Successful MySQL Scalability Public API Application web site Example 4 ✘ Friday, September 10, 2010

Slide 57

Slide 57 text

Successful MySQL Scalability Public API Application web site Batch processes Example 4 ✘ Friday, September 10, 2010

Slide 58

Slide 58 text

Successful MySQL Scalability Public API Application web site Batch processes 3 access paths to same data with different business rules Example 4 ✘ Friday, September 10, 2010

Slide 59

Slide 59 text

Successful MySQL Scalability Why 4 Friday, September 10, 2010

Slide 60

Slide 60 text

Successful MySQL Scalability One code path for business functionality Why 4 Friday, September 10, 2010

Slide 61

Slide 61 text

Successful MySQL Scalability One code path for business functionality Implied business documentation Why 4 Friday, September 10, 2010

Slide 62

Slide 62 text

Successful MySQL Scalability One code path for business functionality Implied business documentation Enforced data exchange standard Why 4 Friday, September 10, 2010

Slide 63

Slide 63 text

Successful MySQL Scalability One code path for business functionality Implied business documentation Enforced data exchange standard Testability Why 4 Friday, September 10, 2010

Slide 64

Slide 64 text

Successful MySQL Scalability Technology independence Business specification dependence (API) Stress testable GOAL 4 ✔ Friday, September 10, 2010

Slide 65

Slide 65 text

Successful MySQL Scalability Testing is not about what works, it’s about breaking your software SIDEBAR! 4 Friday, September 10, 2010

Slide 66

Slide 66 text

Successful MySQL Scalability 5 5 Friday, September 10, 2010

Slide 67

Slide 67 text

Successful MySQL Scalability Support different levels of data availability Action 5 Friday, September 10, 2010

Slide 68

Slide 68 text

Successful MySQL Scalability HOW 5 Friday, September 10, 2010

Slide 69

Slide 69 text

Successful MySQL Scalability Data can be: HOW 5 Friday, September 10, 2010

Slide 70

Slide 70 text

Successful MySQL Scalability Data can be: Read & Write HOW 5 Friday, September 10, 2010

Slide 71

Slide 71 text

Successful MySQL Scalability Data can be: Read & Write Read HOW 5 Friday, September 10, 2010

Slide 72

Slide 72 text

Successful MySQL Scalability Data can be: Read & Write Read No Access HOW 5 Friday, September 10, 2010

Slide 73

Slide 73 text

Successful MySQL Scalability Data can be: Read & Write Read No Access Cached HOW 5 Friday, September 10, 2010

Slide 74

Slide 74 text

Successful MySQL Scalability Development Team: We need a maintenance window for software upgrades and new releases. EXAMPLE 5 Friday, September 10, 2010

Slide 75

Slide 75 text

Successful MySQL Scalability Management Team: No Downtime EXAMPLE 5 Friday, September 10, 2010

Slide 76

Slide 76 text

Successful MySQL Scalability Development Team: But we need this to fix problems and improve performance. EXAMPLE 5 Friday, September 10, 2010

Slide 77

Slide 77 text

Successful MySQL Scalability Management Team: No Downtime EXAMPLE 5 Friday, September 10, 2010

Slide 78

Slide 78 text

Successful MySQL Scalability What is your definition of no downtime? EXAMPLE 5 Friday, September 10, 2010

Slide 79

Slide 79 text

Successful MySQL Scalability Support downtime Software upgrades Support disasters Reduce high load Support partial functionality WHY 5 Friday, September 10, 2010

Slide 80

Slide 80 text

Successful MySQL Scalability Meet the business needs of availability There are ways to give greater perception of availability GOAL 5 ✔ Friday, September 10, 2010

Slide 81

Slide 81 text

Successful MySQL Scalability 6 6 Friday, September 10, 2010

Slide 82

Slide 82 text

Successful MySQL Scalability Support different scalability principles Action 6 Friday, September 10, 2010

Slide 83

Slide 83 text

Successful MySQL Scalability Read Scalability Write Scalability Caching HOW 6 Friday, September 10, 2010

Slide 84

Slide 84 text

Successful MySQL Scalability Depends on your R/W ratio Depends on your business needs Depends on rate of data change (caching) HOW 6 Friday, September 10, 2010

Slide 85

Slide 85 text

Successful MySQL Scalability Support large scale growth with appropriate software architecture Minimize human interaction GOAL 6 ✔ Friday, September 10, 2010

Slide 86

Slide 86 text

Successful MySQL Scalability SIDEBAR! 6 Availability options & scalability principles = scalable datastore Friday, September 10, 2010

Slide 87

Slide 87 text

Successful MySQL Scalability 7 7 Friday, September 10, 2010

Slide 88

Slide 88 text

Successful MySQL Scalability I only had one hour! What’s NEXT 7 Friday, September 10, 2010

Slide 89

Slide 89 text

Successful MySQL Scalability What’s NEXT 7 Friday, September 10, 2010

Slide 90

Slide 90 text

Successful MySQL Scalability Reduce deployment time The right business metrics Team dynamics Managing risk Continual improvement Data interoperability ... What’s NEXT 7 Friday, September 10, 2010

Slide 91

Slide 91 text

Successful MySQL Scalability Reduce deployment time The right business metrics Team dynamics Managing risk Continual improvement Data interoperability ... What’s NEXT 7 Asynchronous Write once data Friday, September 10, 2010

Slide 92

Slide 92 text

Successful MySQL Scalability R R Friday, September 10, 2010

Slide 93

Slide 93 text

Successful MySQL Scalability Monitoring. Before, during and after NOW. You may not be able to predict the future but you can preempt the future. Operate below 90%. That 10% is your insurance policy. Invest in insurance. RECAP R Friday, September 10, 2010

Slide 94

Slide 94 text

Successful MySQL Scalability Does your business exist without your data? How long can your site be unavailable before customers go elsewhere? RECAP R Friday, September 10, 2010

Slide 95

Slide 95 text

Successful MySQL Scalability Support 3 levels of real time data access Read/Write, Read and no access Support 3 different aspects of scalability Read, Write and Caching RECAP R Friday, September 10, 2010

Slide 96

Slide 96 text

Successful MySQL Scalability If you can’t drive without a mouse, find somebody that can. RECAP R Friday, September 10, 2010

Slide 97

Slide 97 text

Successful MySQL Scalability The most common MySQL scalability mistakes, and how to avoid them. Reference @ http://omniti.com/surge Friday, September 10, 2010

Slide 98

Slide 98 text

Successful MySQL Scalability ‣ 1. My website is slow? ‣ 2. I want to add new H/W. How do I change my application to support this? ‣ 3. The database is slow. My webpage takes five seconds to load. ‣ 4. Why is my database executing so many qps? Reference @ Friday, September 10, 2010

Slide 99

Slide 99 text

Successful MySQL Scalability ‣ 5. My server has crashed with a hard drive failure ‣ 6. My replication slave can't keep up? ‣ 7. I can't access my website? ‣ 8. Why is my database so large? ‣ 9. My website seems to freeze or responds randomly? Reference @ Friday, September 10, 2010

Slide 100

Slide 100 text

Successful MySQL Scalability http://ronaldbradford.com [email protected] Successful MySQL Scalability CONTACT C Friday, September 10, 2010