Transcript Presentation Name - Computer Measurement Group

Database Performance
What Really Matters?
Peter Zaitsev,
CEO, Percona
SCMG Meeting
Chester,VA
April 17,2014
About Me & Percona
• Founder and CEO of Percona
• Products and Services for MySQL
Ecosystem
• Support, Consulting, RemoteDBA etc
• Important Focus: Have been helping
customers with Performance Problems
• MySQL and Beyond
2
www.percona.com
About the Presentation
• Share some valuable background and
Theory behind Performance Optimization
• Look at Load Testing and Benchmarking
• How to apply them in practice
• How to look for right things
• “What really matters”
3
www.percona.com
Group Notes
• This presentation is focused on MySQL
Users
• And other users of “New Generation”
technologies
• They often have less theoretical
background
• Forgive me some simplifications and
talking about basics
4
www.percona.com
Some background and
Theory
5
www.percona.com
Databases do not matter
• Users, Business does not care about
databases
• Really!
6
www.percona.com
What Matters ?
• Application Performance
• As seen by your Users, Business
• Response time for their transactions with
the system
7
www.percona.com
What Impacts Performance?
• Changes to the transaction volume
• More users
• Changes to transaction mix/type
• Do not forget non-business
transactions
• Data Volume
• Changes to application features
• Environment Changes
• RAID running slow to degraded mode
8
www.percona.com
Database Performance – Part
of The Picture
• Database Performance – Part of the
Picture
• Look at complete Application not
Database alone
• Database may be the bottleneck
• Or it might be something else
9
www.percona.com
Bottleneck Solutions
• Even when Database is the Bottleneck
solutions might lay elsewhere
10
www.percona.com
Application Architecture
•
•
•
•
•
•
11
Caching
Queuing
Concurrency Management
Data Distribution (sometimes)
Use of Data Replicas (sometimes)
Async and Parallel Processing
(sometimes)
www.percona.com
Application Defines
• Schema and Queries
• Same Application Operation can be done
using different Schema and Queries
• Some Approaches
• Normalization/Denormalization
• Summary Tables
• Indexing
• Using Special Purpose Systems
• Column Store; Full Text Search;
Massively Parallel Processing; Graph etc
12
www.percona.com
1
2
The Rule for Query
Optimization
Do not look just optimizing
the query, look at the larger
picture how to optimize the
whole operation
13
www.percona.com
Learning from the Industry
• Technology has a lot to learn from different
industries
• Application Request processing is quite similar
to manufacturing
• Request processing takes multiple stages
• Having input and output data
• Utilizing Resources in the meanwhile
•
•
14
Resources on Service Level
Resources on Physical Level
www.percona.com
Lessons from Manufacturing
• “The Goal” by Dr. Eliyahu M. Goldratt
• “Identify And Protect Constraint”
• Bottleneck
• Minimize Work in Progress
15
www.percona.com
Lessons from Call Center
• Queuing Theory
• Response Time = Wait Time + Service
Time
• Requests come as “Random Arrivals”
• The high utilization means long Wait
time
• And poor response time
16
www.percona.com
The Hockey Stick
• High Utilization yields disproportional
increase in response time
17
www.percona.com
Throughput
• Theoretical possible Throughput assumes
uniform arrivals
• Or unlimited queue size
• Will not be reached; resource will stay idle
some time
• Do not drive for complete resource usage
• Ie 100% CPU usage
• Even at what you consider peak load
18
www.percona.com
Database Performance
Testing
19
www.percona.com
Benchmarks
• Type of Benchmark
• Pure Database
• Easy to setup. Might be misleading
• Complete System
• Much Harder to set up. More valuable
results
• Workload and Data
• Simulated (are we getting it right?)
• Replaying real world workload
• How close we can emulate it ?
20
www.percona.com
Synthetic Benchmarks
• Most of published benchmark results are
synthetic
• Sysbench, TPC-C, LinkBench etc
• This is what gives you comparable
result
• Beware: These results are highly
irrelevant
• Your application is different
21
www.percona.com
How are Benchmarks set up
• Most typical
• Fixed concurrency “number of
connections”
• Send requests in as fast as possible
• In Real World
• “Injection Rate” – amount users on the
system
• Concurrency is dynamic
•
More connections may be created as
needed
• “Think Time” between issuing queries
22
www.percona.com
Results from Benchmarks
• Throughput is reported
• Transactions per minute, per second
• What users care ?
• Response time, stable response time
• You can’t measure response time when
you try to saturate your system fully
• Issuing requests as fast as possible
23
www.percona.com
Response Time
• Saturated Load – Higher Concurrency –
Higher Response Time
24
www.percona.com
Time Stability
• Those graphs correspond to about same
average throughput
• Which is better ?
• How would you do capacity planning ?
25
www.percona.com
SSDs – New Pain in the…
• Conventionally – no history
• You reboot the system and get
repeatable workload all the time
• Exceptions: SAN/NAS, Virtualization,
Cloud
• Flash is complicated Black Box
• Performance is not repeatable – depends
on unknown state
• Peak Writes different from Sustained Writes
• Like Write cache but different scale
26
www.percona.com
Helpful Tools
• Sysbench has “Injection type benchmark”
• http://bit.ly/x4mZ0d
• Percona Playback
• http://www.percona.com/doc/perconaplayback/
• Look at the log file, reissue queries at
the intervals and threads as it
happened in real run
27
www.percona.com
Concurrency
• There is always optimal concurrency from
“throughput” point of view
• WIP is expensive
28
www.percona.com
Concurrency and Response
time
• Though Experiment
• 1CPU; 100 tasks requiring 1 sec of
CPU time each
• Run them concurrently ?
• Queue them and run one after
another?
29
www.percona.com
Restricting Concurrency
• Sysbench “Injection Benchmark”
• Setting the cap on amount of threads
30
www.percona.com
Side Load
• Almost no one does Benchmrks/Load
Testing with side load
• Can cause serve issues
• Your Batch Jobs ?
• Backups ?
• RAID background consistency check ?
31
www.percona.com
Real Performance Issues
• Persistent Problems
• Are easy. Well caught by conventional
load testing
• Not enough resources; bad query
• Transient Problems
• Something happens; System performs
poorly for 10 seconds; few minutes
• Very typical hard to catch
32
www.percona.com
Concurrency is often the
cause
33
www.percona.com
Real World
• You will not catch all Real World issues
even with best benchmark/load testing
• Proper monitoring is a must
• Look at the Application Response time
over time
• Gather related system metrics for
analyses
• Database included but not just
database
34
www.percona.com
Do not focus on average
• Look at 1%, 5% of worst transactions
• Some of your users will be impacted
before the problem is there for average
user
• What causes these to be slow ?
• The answer can be different
• Disk IO; Database locks etc;
35
www.percona.com
Drill Down
• Can you Drill down from your Application
Transaction to the other subsystem
• Why specific request was slow and
what made it slow?
• NewRelic, AppDynamics are some of
such tools available
36
www.percona.com
Summary
• Take a view of the full system
• Understand your Bottleneck
• Solution might be in the different place
than the problem
• Do Right benchmarks and Load Testing
• But do not expect them to catch all
performance situations
37
www.percona.com
Thank You!
Peter Zaitsev
[email protected]
38
www.percona.com
www.percona.com