Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Successful MySQL Scalability

Successful MySQL Scalability

This presentation talks about a set of principles to ensure appropriate system architecture, data availability, and best practices to build an ideal solution for your business.

Youtube presentation at SF MySQL Meetup - https://www.youtube.com/watch?v=tR1kvYFM7Rk

Ef8a4161c1e7ce34ea50c491ad99a67e?s=128

Ronald Bradford

August 21, 2012
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

Transcript

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

    2010
  2. The one hour version Friday, September 10, 2010

  3. Successful MySQL Scalability Necessary Principles System Architecture Data Availability Best

    Practices Being proactive OUTLINE Friday, September 10, 2010
  4. Successful MySQL Scalability 1 1 Friday, September 10, 2010

  5. Successful MySQL Scalability Integrated monitoring and instrumentation Action 1 Friday,

    September 10, 2010
  6. 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
  7. Successful MySQL Scalability How 1 Friday, September 10, 2010

  8. Successful MySQL Scalability Monitoring/Alerting Graphical Historical Necessary Generally missing/incomplete Useless

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

    for real-time analysis How 1 REACTIVE Friday, September 10, 2010
  10. Successful MySQL Scalability How 1 Friday, September 10, 2010

  11. 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
  12. 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
  13. Successful MySQL Scalability How 1 Friday, September 10, 2010

  14. 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
  15. 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
  16. Successful MySQL Scalability Monitoring + Instrumentation = Preemptive Analysis How

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

  18. Successful MySQL Scalability How 1 ✔ < 1 second Friday,

    September 10, 2010
  19. Successful MySQL Scalability Preemptive Analysis Fail early Faster user response

    Less physical resource impact WHY 1 Friday, September 10, 2010
  20. Successful MySQL Scalability TIP 1 Friday, September 10, 2010

  21. Successful MySQL Scalability Have a status website allow for comments

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

    (e.g. blog) Have a public dashboard website TIP 1 Friday, September 10, 2010
  23. 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
  24. Successful MySQL Scalability 2 2 Friday, September 10, 2010

  25. Successful MySQL Scalability Seamless automated server deployment Action 2 Friday,

    September 10, 2010
  26. 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
  27. Successful MySQL Scalability Basics Version Control Automated Build & Release

    Integrated Monitoring How 2 Friday, September 10, 2010
  28. Successful MySQL Scalability Intermediate Health Checks Runtime config file management

    Benchmarking How 2 Friday, September 10, 2010
  29. Successful MySQL Scalability Advanced Server request queue Automated discovery Proactive

    scaling (up & down) How 2 Friday, September 10, 2010
  30. Successful MySQL Scalability Consistent and timely provisioning Unattended installation with

    correct and reproducible configuration Automated discovery GOAL 2 ✔ Friday, September 10, 2010
  31. Successful MySQL Scalability 3 3 Friday, September 10, 2010

  32. Successful MySQL Scalability Disaster is inevitable Action 3 Friday, September

    10, 2010
  33. Successful MySQL Scalability Question: Have you ever performed a database

    recovery? Answer: No, why? Example 3 ✘ Friday, September 10, 2010
  34. 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
  35. Successful MySQL Scalability HOW 3 Friday, September 10, 2010

  36. Successful MySQL Scalability Have a DR plan HOW 3 Friday,

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

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

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

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

    Verified HOW 3 Friday, September 10, 2010
  41. Successful MySQL Scalability HOW 3 Friday, September 10, 2010

  42. Successful MySQL Scalability Test under production conditions HOW 3 Friday,

    September 10, 2010
  43. Successful MySQL Scalability Test under production conditions System Load HOW

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

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

    Size End to End HOW 3 Friday, September 10, 2010
  46. 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
  47. Successful MySQL Scalability What does your website look like when

    it’s down? SIDEBAR! 3 Friday, September 10, 2010
  48. Successful MySQL Scalability SIDEBAR! 3 Google Picasa ✘ Friday, September

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

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

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

  52. Successful MySQL Scalability 4 4 Friday, September 10, 2010

  53. Successful MySQL Scalability Application Programming Interface Action 4 Friday, September

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

  55. Successful MySQL Scalability Public API Example 4 ✘ Friday, September

    10, 2010
  56. Successful MySQL Scalability Public API Application web site Example 4

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

    Example 4 ✘ Friday, September 10, 2010
  58. 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
  59. Successful MySQL Scalability Why 4 Friday, September 10, 2010

  60. Successful MySQL Scalability One code path for business functionality Why

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

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

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

    business documentation Enforced data exchange standard Testability Why 4 Friday, September 10, 2010
  64. Successful MySQL Scalability Technology independence Business specification dependence (API) Stress

    testable GOAL 4 ✔ Friday, September 10, 2010
  65. Successful MySQL Scalability Testing is not about what works, it’s

    about breaking your software SIDEBAR! 4 Friday, September 10, 2010
  66. Successful MySQL Scalability 5 5 Friday, September 10, 2010

  67. Successful MySQL Scalability Support different levels of data availability Action

    5 Friday, September 10, 2010
  68. Successful MySQL Scalability HOW 5 Friday, September 10, 2010

  69. Successful MySQL Scalability Data can be: HOW 5 Friday, September

    10, 2010
  70. Successful MySQL Scalability Data can be: Read & Write HOW

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

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

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

    No Access Cached HOW 5 Friday, September 10, 2010
  74. Successful MySQL Scalability Development Team: We need a maintenance window

    for software upgrades and new releases. EXAMPLE 5 Friday, September 10, 2010
  75. Successful MySQL Scalability Management Team: No Downtime EXAMPLE 5 Friday,

    September 10, 2010
  76. Successful MySQL Scalability Development Team: But we need this to

    fix problems and improve performance. EXAMPLE 5 Friday, September 10, 2010
  77. Successful MySQL Scalability Management Team: No Downtime EXAMPLE 5 Friday,

    September 10, 2010
  78. Successful MySQL Scalability What is your definition of no downtime?

    EXAMPLE 5 Friday, September 10, 2010
  79. Successful MySQL Scalability Support downtime Software upgrades Support disasters Reduce

    high load Support partial functionality WHY 5 Friday, September 10, 2010
  80. Successful MySQL Scalability Meet the business needs of availability There

    are ways to give greater perception of availability GOAL 5 ✔ Friday, September 10, 2010
  81. Successful MySQL Scalability 6 6 Friday, September 10, 2010

  82. Successful MySQL Scalability Support different scalability principles Action 6 Friday,

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

    Friday, September 10, 2010
  84. 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
  85. Successful MySQL Scalability Support large scale growth with appropriate software

    architecture Minimize human interaction GOAL 6 ✔ Friday, September 10, 2010
  86. Successful MySQL Scalability SIDEBAR! 6 Availability options & scalability principles

    = scalable datastore Friday, September 10, 2010
  87. Successful MySQL Scalability 7 7 Friday, September 10, 2010

  88. Successful MySQL Scalability I only had one hour! What’s NEXT

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

  90. 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
  91. 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
  92. Successful MySQL Scalability R R Friday, September 10, 2010

  93. 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
  94. 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
  95. 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
  96. Successful MySQL Scalability If you can’t drive without a mouse,

    find somebody that can. RECAP R Friday, September 10, 2010
  97. Successful MySQL Scalability The most common MySQL scalability mistakes, and

    how to avoid them. Reference @ http://omniti.com/surge Friday, September 10, 2010
  98. 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
  99. 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
  100. Successful MySQL Scalability http://ronaldbradford.com me@ronaldbradford.com Successful MySQL Scalability CONTACT C

    Friday, September 10, 2010