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

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