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

1. Teamwork is essential. It allows you to blame someone else. Self-tuning DB Technology & Info Services:from Wishful Thinking to Viable Engineering Gerhard Weikum weikum@cs.uni-sb.de 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 Zabback

2. 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 -

3. tuning experts are expensive • system cost dominated and growth limited • by human care & feed • automate sys admin and tuning! Auto-Tuning: What and Why? DBA manual (10 years ago): Härder 1981: mission impossible

4. An engineer is someone who can do for a dime what any fool can do for a dollar. 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 + ok if applied with care – waste of money otherwise 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

5. 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 -

6. Need a quantitative model ! Feedback Control Loop for Automatic Tuning • Observe • Predict • React

7. 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 !!! ??? !!!

8. Level, Scope, and Time Horizonof Tuning Issues level scope (workflow) system configuration query opt. & db stats mgt. index selection caching load control data placement time

9. Level, Scope, and Time Horizonof Tuning Issues level scope (workflow) system configuration query opt. & db stats mgt. index selection caching load control data placement time

10. uncontrolled memory or lock contention can lead to performance catastrophe Load Control for Locking (MPL Tuning)

11. Locking for Concurrent Transactions

12. lock conflict lock wait lock thrashing Locking and Lock Contention t

13. typical Sisyphus problem How Difficult Can This Be? arriving transactions response time [s] 1.0 0.8 trans. queue 0.6 0.4 active trans, 0.2 DBS 10 20 30 40 50 MPL

14. backed up by math (Tay, Thomasian) Adaptive Load Control conflict ratio = arriving trans. restarted trans. transaction admission critical conflict ratio  1.3 transaction execution conflict ratio aborted trans. transaction cancellation committed trans.

15. WFMS Architecture for E-Services Clients WF server type 2 WF server type 1 Comm server ... ... App server type 1 App server type n

16. Mapping Monitoring Hypothetical config Max. Throughput Avg. waiting time Expected downtime Workflow System Configuration Tool Workflow Repository Operational Workflow System Config. Admin Modeling Calibration Evaluation Recommendation

17. Mapping Monitoring Goals: min(throughput) max(waiting time) max(downtime) + constraints • Long-term feedback control • aims at global, user- • perceived metrics and • uses more advanced math • for prediction Min-cost re-config. Workflow System Configuration Tool Workflow Repository Operational Workflow System Config. Admin Modeling Calibration Evaluation Recommendation

18. 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 -

19. 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

20. 100 x / min Orders Parts Select ... From ... Where City = ... And State = ... ONo ODate ... PNo PName ... 20 x / min LineItems Select ... From ... Where ShipDate – ODate > ... ONo LNo Qty PriceODateShipDate ... 15 x / min Select ... From ... Where L.Ono= O.ONo And Price < ... Customers 300 x / min CNo CName CityState Discount ... Update ... Set ShipDate = ... 300 x / min Insert LineItems ... Workload Create Index ... City State ONo Price ONo NP-hard combinatorial problem with complex cost formulas ShipDate Index Selection: Live or Let Die Data

21. 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

22. Where Do We Stand Today? - Bad News –Automatic system tuning based on few principles: Complex problems have simple, easy-to-understand answers , wrong – Interactions across components and interference among different workload classes can make entire system unpredictable

23. 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 -

24. 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

25. Success is a lousy teacher. (Bill Gates) 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 requires good research stamina Major challenges remain: path towards „autonomic“ systems requires rethinking & simplifying component architectures with design-for-predictability paradigm