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
    Ronald Bradford
    2010.08
    www.RonaldBradford.com
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  7. Successful MySQL Scalability
    How
    1
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  13. Successful MySQL Scalability
    How
    1
    Friday, September 10, 2010

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  17. Successful MySQL Scalability
    How
    1

    Friday, September 10, 2010

    View full-size slide

  18. Successful MySQL Scalability
    How
    1

    < 1 second
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

  20. Successful MySQL Scalability
    TIP
    1
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  24. Successful MySQL Scalability
    2
    2
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  30. Successful MySQL Scalability
    Consistent and timely provisioning
    Unattended installation with
    correct and reproducible
    configuration
    Automated discovery
    GOAL
    2

    Friday, September 10, 2010

    View full-size slide

  31. Successful MySQL Scalability
    3
    3
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

  33. Successful MySQL Scalability
    Question:
    Have you ever performed a database recovery?
    Answer:
    No, why?
    Example
    3

    Friday, September 10, 2010

    View full-size slide

  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

    View full-size slide

  35. Successful MySQL Scalability
    HOW
    3
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  48. Successful MySQL Scalability
    SIDEBAR!
    3
    Google Picasa

    Friday, September 10, 2010

    View full-size slide

  49. Successful MySQL Scalability
    SIDEBAR!
    3
    Mozilla

    Friday, September 10, 2010

    View full-size slide

  50. Successful MySQL Scalability
    SIDEBAR!
    3

    Friday, September 10, 2010

    View full-size slide

  51. Successful MySQL Scalability
    SIDEBAR!
    3

    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  54. Successful MySQL Scalability
    Example
    4

    Friday, September 10, 2010

    View full-size slide

  55. Successful MySQL Scalability
    Public API
    Example
    4

    Friday, September 10, 2010

    View full-size slide

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

    Friday, September 10, 2010

    View full-size slide

  57. Successful MySQL Scalability
    Public API
    Application web site
    Batch processes
    Example
    4

    Friday, September 10, 2010

    View full-size slide

  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

    View full-size slide

  59. Successful MySQL Scalability
    Why
    4
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  64. Successful MySQL Scalability
    Technology independence
    Business specification
    dependence (API)
    Stress testable
    GOAL
    4

    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

  66. Successful MySQL Scalability
    5
    5
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  81. Successful MySQL Scalability
    6
    6
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  85. Successful MySQL Scalability
    Support large scale growth
    with appropriate software
    architecture
    Minimize human interaction
    GOAL
    6

    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

  87. Successful MySQL Scalability
    7
    7
    Friday, September 10, 2010

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  92. Successful MySQL Scalability
    R
    R
    Friday, September 10, 2010

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide