Transcript Self-tuning DB Technology & Info Services: from Wishful Thinking to Viable Engineering

Self-tuning DB Technology & Info Services:
from Wishful Thinking
to Viable Engineering
Gerhard Weikum
[email protected]
Teamwork is essential.
It allows you to blame someone else.
Acknowledgements to collaborators:
Surajit Chaudhuri, Christoff Hasse, Arnd Christian König, Achim Kraiss,
Axel Mönkeberg, Peter Muth, Guido Nerjes, Elizabeth O‘Neil,
Patrick O‘Neil, Peter Scheuermann, Markus Sinnwell, Peter Zabback1
Outline
 Auto-Tuning: What and Why?
 The COMFORT Experience
 The Feedback-Control Approach
 Example 1: Load Control
 Example 2: Workflow System Configuration
 Where Do We Stand Today?
- Myths and Facts  Where Do We Go From Here?
- Dreams and Directions -
2
Auto-Tuning: What and Why?
DBA manual (10 years ago):
• tuning experts are expensive
• system cost dominated and growth limited
by human care & feed
 automate sys admin and tuning!
Härder 1981: mission impossible
3
Intriguing and Treacherous Approaches
Instant tuning: rules of thumb
+ ok for page size, striping unit, min cache size
– insufficient for max cache size, MPL limit, etc.
KIWI principle: kill it with iron
An engineer is someone
+ ok if applied with care
who can do for a dime
– waste of money otherwise
what any fool can do for a dollar.
Columbus / Sisyphus approach: trial and error
+ ok with simulation tools
– risky with production system
DBA joystick method: feedback control loop
+ ok when it converges under stationary workload
– susceptible to instability
4
Outline
 Auto-tuning: What and Why?
 The COMFORT Experience
 The Feedback-Control Approach
 Example 1: Load Control
 Example 2: Workflow System Configuration
 Where Do We Stand Today?
- Myths and Facts  Where Do We Go From Here?
- Dreams and Directions -
5
Feedback Control Loop
for Automatic Tuning
• Observe
Need a
quantitative
model !
• Predict
• React
6
Performance Predictability is Key
”Our ability to analyze and predict the performance
of the enormously complex software systems ...
are painfully inadequate”
(Report of the US President’s
Technology Advisory Committee 1998)
ability to predict
workload  knobs  performance
!!!
!!!
???
is prerequisite for finding the right knob settings
workload  knobs  performance goal
!!!
???
!!!
7
Level, Scope, and Time Horizon
of Tuning Issues
level
scope
(workflow) system configuration
query opt.
& db stats mgt.
index selection
caching
load control
data placement
time
8
Level, Scope, and Time Horizon
of Tuning Issues
level
scope
(workflow) system configuration
query opt.
& db stats mgt.
index selection
caching
load control
data placement
time
9
Load Control for Locking (MPL Tuning)
uncontrolled memory or lock contention
can lead to performance catastrophe
Locking for Concurrent Transactions
Locking and Lock Contention
lock
conflict
lock
wait
lock
thrashing
t
How Difficult Can This Be?
arriving
transactions
response time [s]
1.0
trans.
queue
0.8
active
trans,
0.4
0.6
0.2
DBS
10
typical Sisyphus problem
20
30
40
50
MPL
Adaptive Load Control
conflict ratio =
# locksheld by all trans.
# locksheld by running trans.
backed up
by math
(Tay,
transaction
Thomasian)
critical
conflict ratio
 1.3
arriving trans.
restarted
trans.
admission
conflict ratio
transaction
execution
aborted
trans.
transaction cancellation
committed trans.
WFMS Architecture for E-Services
Clients
WF server
type 2
WF server
type 1
Comm server
...
...
App server type 1
App server type n
15
Workflow System Configuration Tool
Workflow
Repository
Operational Workflow System Config.
Mapping
Modeling
Monitoring
Calibration
Admin
Hypothetical
config
Evaluation
Recommendation
Max. Throughput
Avg. waiting time
Expected downtime
16
Workflow System Configuration Tool
Workflow
Repository
Mapping
Modeling
Operational Workflow System Config.
Monitoring
Calibration
Long-term feedback control
• aims at global, userEvaluation
perceived metrics
and
• uses more advanced math
for prediction Recommendation
Admin
Goals:
min(throughput)
max(waiting time)
max(downtime)
+ constraints
Min-cost
re-config.
17
Outline
 The Problem – 10 Years Ago and Now
 The COMFORT Experience
 The Feedback-Control Approach
Example 1: Load Control

 Example 2: Workflow System Configuration
 Where Do We Stand Today?
- Myths and Facts  Where Do We Go From Here?
- Dreams and Directions -
18
Where Do We Stand Today? - Good News
Advances in Engineering:
• Eliminate second-order knobs
• Robust rules of thumb for some knobs
• KIWI method where applicable
Scientific Progress:
+ Feedback control approach
+ Storage systems have become self-managing
+ Index selection wizards hard to beat
+ Materialized view wizards
+ Synopses selection and space allocation
for DB statistics well understood
19
Index Selection: Live or Let Die
Orders
Parts
ONo ODate ...
PNo PName ...
100 x / min
Select ... From ...
Where City = ... And State = ...
20 x / min
Select ... From ...
LineItems
ONo LNo Qty Price ODate ShipDate ...
Customers
CNo CName City State Discount ...
Where ShipDate – ODate > ...
15 x / min
Select ... From ...
Where L.Ono = O.ONo
And Price < ...
300 x / min
Update ... Set ShipDate = ...
300 x / min
Insert LineItems ...
Workload
Data
Create Index ...
ONo
NP-hard combinatorial problem
with complex cost formulas
ONo
Price
ShipDate
City
State
Where Do We Stand Today? - Good News
Advances in Engineering:
• Eliminate second-order knobs
• Robust rules of thumb for some knobs
• KIWI method where applicable
Scientific Progress:
+ Feedback control approach
+ Storage systems have become self-managing
+ Index selection wizards hard to beat
+ Materialized view wizards
+ Synopses selection and space allocation
for DB statistics well understood
21
Where Do We Stand Today? - Bad News
– Automatic system tuning based on few principles:
Complex problems have
simple, easy-to-understand , wrong answers
– Interactions across components and
interference among different workload classes
can make entire system unpredictable
22
Outline
 The Problem – 10 Years Ago and Now
 The COMFORT Experience
 The Feedback-Control Approach
 Example 1: Load Control
 Example 2: Workflow System Configuration
 Where Do We Stand Today?
- Myths and Facts -
 Where Do We Go From Here?
- Dreams and Directions -
23
Autonomic Computing: Path to Nirvana ?
Vision:
all computer systems must be
self-managed, self-organizing, and self-healing
Motivation:
• ambient intelligence
(sensors in every room, your body etc.)
• reducing complexity and improving manageability
of very large systems
Role model:
biological, self-regulating systems (really ???)
My interpretation:
need component design for predictability:
self-inspection, self-analysis, self-tuning
aka. observation, prediction, reaction
24
Summary & Concluding Remarks
Major advances towards automatic tuning
during last decade:
• workload-aware feedback control approach fruitful
• math models and online stats are vital assets
• „low-hanging fruit“ engineering successful
• important contributions from research community
(AutoRAID, AutoAdmin, LEO, Härder/Rahm book, etc.)
Problem is long-standing but very difficult
and requiresSuccess
good research
stamina
is a lousy
teacher. (Bill Gates)
Major challenges remain:
path towards „autonomic“ systems requires
rethinking & simplifying component architectures
with design-for-predictability paradigm
25