Self-tuning database

basesprocketΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 3 χρόνια και 9 μήνες)

84 εμφανίσεις


Self
-
tuning
Database

Leili Farzinvash

Nazanin Dehghani


Electrical and Computer Engineering Department

2

Introduction

Self
-
tuning Database

2

3

Outline



Introduction


Characteristics of Autonomic DBMS


Self
-
Tuning Architecture


Alternative Tuning Models


Monitoring Infrastructure


Case studies

Self
-
tuning Database

3

4

Characteristics of Autonomic DBMS



Self
-
optimizing


Self
-
configuring


Self
-
healing


Self
-
protecting


Self
-
organizing


Self
-
inspecting


Self
-
tuning Database

4

5

Phases of Self
-
tuning Loop

Self
-
tuning Database

5

Diagnose

Resole

Observe

6

Self
-
Tuning Architecture

Self
-
tuning Database

6

7

Alternative Tuning Models


Alerter (When to Tune)

Workload as a
Sequence

Dynamic (Online)
Tuning

Self
-
tuning Database

7

8

Monitoring Infrastructure


Query Progress Estimation

Ad
-
hoc Monitoring and Diagnostics

Self
-
tuning Database

8

9

Case Studies

Self
-
tuning Database

9

10

SQL Server



Creating “what
-
if ” physical structures


Search Architecture


Alerter


Self tuning histogram


SQLCM




Self
-
tuning Database

10

11

Database Engine Tuning Advisor (DTA)

Self
-
tuning Database

11

12

Self tuning histogram

Self
-
tuning Database

12

13

DB2



Key Ideas and Themes


Low
-
impact collection of accurate system data


Feedback


Re
-
using Optimizer as a “What if?” tool


Heuristics, new models


Automating batch operation


Self
-
tuning Database

13

14

Future Works



Comparison the quality of automated physical design
solutions


Migration of “heavyweight” to “lightweight”


Distributed tuning and monitoring


Machine learning techniques, control theory, and online
algorithms

Self
-
tuning Database

15

References

1.
Elnaffar, S.; Powley, W.; Benoit, D.; Martin, P. Today's DBMSs: how autonomic are they. Proceedings of
the
14
th International Workshop on Database and Expert Systems Applications (DEXA’
03
),
1
-
5
Sept.
2003
IEEE, Pages:
651

655

2.
Surajit Chaudhuri, Vivek Narasayya : Self
-
Tuning Database Systems: A Decade of Progress. VLDB

07
, September
23
-
28
,
2007
, Vienna, Austria.

3.
Benoit Dageville ,Karl Dias. Oracle’s Self
-
Tuning Architecture and Solutions : Bulletin of the IEEE
Computer Society Technical Committee on Data Engineering,
2006
.

4.
B. Dageville, M. Zait: SQL Memory Management in Oracle
9
i. VLDB
2002
,
962
-
973
.

5.
Agrawal, S., Chaudhuri, S. and Narasayya, V. Automated Selection of Materialized Views and Indexes
for SQL Databases.
In Proceedings of the VLDB,
Cairo, Egypt,
2000
.
.

6.
Zilio et al. Recommending Materialized Views and Indexes with IBM DB
2
Design Advisor. In
Proceedings of ICAC
2004
.

7.
S.Parekh, K.R.Rose, J.L. Heller stein, S.Lightstone, M.Huras, and V.Chang. Throttling utilities in the
IBM DBS universal database server. In Amer. Control Conf. (ACC),
2004

8.
Eugene Krayzman, Development of self
-
tuning and autonomic databases and latest achievements:
21
st Computer Science Seminar SE
2
-
T
4
-
1

9.
Sanjay Agrawal, Nicolas Bruno, Surajit Chaudhuri, Vivek Narasayya. AutoAdmin: Self
-
Tuning
Database Systems Technology: Bulletin of the IEEE Computer Society Technical Committee on
Data Engineering,
2006
.

10.
Berni Schiefer, Gary Valentin. DB
2
Universal Database Performance Tuning : Bulletin of the IEEE
Computer Society Technical Committee on Data Engineering,
1999
.