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
me@ronaldbradford.com
Successful MySQL
Scalability
CONTACT
C
Friday, September 10, 2010