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

Ronald Bradford

August 21, 2012
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

Transcript

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

    Practices Being proactive OUTLINE Friday, September 10, 2010
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. Successful MySQL Scalability Preemptive Analysis Fail early Faster user response

    Less physical resource impact WHY 1 Friday, September 10, 2010
  8. Successful MySQL Scalability Have a status website allow for comments

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

    (e.g. blog) Have a public dashboard website TIP 1 Friday, September 10, 2010
  10. 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
  11. 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
  12. Successful MySQL Scalability Basics Version Control Automated Build & Release

    Integrated Monitoring How 2 Friday, September 10, 2010
  13. Successful MySQL Scalability Consistent and timely provisioning Unattended installation with

    correct and reproducible configuration Automated discovery GOAL 2 ✔ Friday, September 10, 2010
  14. Successful MySQL Scalability Question: Have you ever performed a database

    recovery? Answer: No, why? Example 3 ✘ Friday, September 10, 2010
  15. 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
  16. 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
  17. Successful MySQL Scalability What does your website look like when

    it’s down? SIDEBAR! 3 Friday, September 10, 2010
  18. 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
  19. Successful MySQL Scalability One code path for business functionality Implied

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

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

    business documentation Enforced data exchange standard Testability Why 4 Friday, September 10, 2010
  22. Successful MySQL Scalability Testing is not about what works, it’s

    about breaking your software SIDEBAR! 4 Friday, September 10, 2010
  23. Successful MySQL Scalability Data can be: Read & Write Read

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

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

    for software upgrades and new releases. EXAMPLE 5 Friday, September 10, 2010
  26. Successful MySQL Scalability Development Team: But we need this to

    fix problems and improve performance. EXAMPLE 5 Friday, September 10, 2010
  27. Successful MySQL Scalability Support downtime Software upgrades Support disasters Reduce

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

    are ways to give greater perception of availability GOAL 5 ✔ Friday, September 10, 2010
  29. 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
  30. Successful MySQL Scalability Support large scale growth with appropriate software

    architecture Minimize human interaction GOAL 6 ✔ Friday, September 10, 2010
  31. 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
  32. 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
  33. 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
  34. 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
  35. 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
  36. Successful MySQL Scalability If you can’t drive without a mouse,

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

    how to avoid them. Reference @ http://omniti.com/surge Friday, September 10, 2010
  38. 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
  39. 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