|
|
Database Tuning (SDBT)/Advanced Data Management (SDVM) Course, S2010
The overall purpose of this course is to enable students to understand and improve the performance of data management systems (perform "tuning"). Actually, this home page is shared by two courses: SDBT Database Tuning for ITU students (15 ECTS, 14 weeks course), and SDVM Advanced Data Management for DIKU and eScience students (7.5 ECTS, 7 weeks course). There is very much in common between both courses in the first 8 weeks, this is why they share the same home page. It should be very clear from the contents of this page what is common to both courses, and what is specific to each one.
Admin
- Teachers: Philippe Bonnet, and Rasmus Pagh
- Kursusbasen:SDBT, SDVN
- Forum: Send questions/comments to it-c.courses.SDBT on the news server news.itu.dk (use thunderbird or your favorite usenet reader - Remember to turn on SSL by default otherwise you will not be able to connect to the news.itu.dk server).
Textbook
|
|
Database Tuning: Principles, Experiments and Troubleshooting Techniques by D.Shasha and Ph.Bonnet.
We will use most of the book, so you can go ahead and read it. The course plan links to the chapters we will cover at each lecture. The preface makes it clear what you will learn and how to read the book.
|
Course Plan
| 26/1 |
Intro: Course Introduction |
1 |
|
| 28/1 |
Intro:Cloud, AWS and DB2 |
|
DB2 on AWS, What can you get for $100 on AWS |
| 2/2 |
Architecture: RDBMS and Beyond |
|
|
| 4/2 |
Architecture: Troubleshooting |
7 |
The End of Architectural Era |
| 9/2 |
Data Models: Relational Model and Beyond |
BigTable ( Paper, Video) |
BigTable Questions |
| 11/2 |
Lab |
|
Tuning Preparation; |
| 16/2 |
Tuning the guts: Log Tuning |
2 |
|
| 18/2 |
Tuning the guts: Lock Tuning |
2 |
|
| 23/2 |
no class |
|
|
| 25/2 |
Tuning the guts: Transaction Chopping
OS Tuning |
2 |
Assignment 1 |
| 2/3 |
Index Tuning: Basics (slides, video, DB2-indexing,DB2-partitioning) |
3, [JP08, sec. 1, 2.0, 2.1] |
exercise |
| 4/3 |
Index Tuning: Special-purpose indexes 1 (slides,DB2-dataaccess) |
[WOS04, sec. 1+2], [Graefe06, sec. 1-5]
|
exercise |
| 9/3 |
Index Tuning: Special-purpose indexes 2 (slides, DB2-text, DB2-spatial) |
[Fer02, sec. 2.0-2.1, 3.0-3.1] |
exercise |
| 11/3 |
Guest lecture: Kaare Kristoffersen, Lector (slides)
Lab |
|
|
| 16/3 |
Query Tuning (slides,DB2-explain,DB2-hints) |
4, Appendix D |
|
| 18/3 |
Exercises (starting 9.30), Lab (after exercises) |
4 |
|
| 23/3 |
Tuning the Application Interface |
5 |
|
| 25/3 |
Tuning the Application Interface |
5 |
|
| 30/3 |
Easter |
|
|
| 1/4 |
Easter |
|
|
AWS Environment
All experiments will be conducted on the AWS cloud infrastructure.
In addition, to the general introduction that we cover in class, I have prepared a couple of flash animations that should make it easier for you to set up your environment:
We will be using DB2 express 9.7 for the experiments. IBM has prepared an Amazon Machine Image (AMI) with DB2 9.7 preinstalled:
Here are some flash animations that illustrate the setup process:
- Retrieving AWS credentials
- Launching an EC2 instance based on the DB2 9.7 AMI
- Setting up the EC2 instance
- Getting started with DB2
DB2 Environment
Here is a practical guide that will allow to get started. It relies on two sql files: airline-schema.sqland insert-airline.sql. You should take the time to go through this guide and make sure that you complete it. You will have problems throughout the experiments if you don't set up your environment correctly. You should use the first two weeks of the course to do this.
Here is the IBM guide for getting started on DB2 v9.7. I suggest you read through it as a reference.
Assignments
You should use the gentable python script to generate data (e.g., with the hotelspec example).
- Assignment#1: You should use the DB2 directory (DB2.zip or DB2.tar.gz) to install the experiment scripts. You will need gentable referenced above to generate data (NOTE: YOU SHOULD GENERATE 1000000 tuples for the accounts table). If you are using AWS make sure that you are launching an instance with the custom AMI databasetuning-db2v9.7 (it is a public AMI that should show up in your AWS console when you launch an instance and search for tuning (ami-4b4ea222) - it contains python 2.5 with the ibm_db module already installed).
- Assignment#2: You should use the DB2 directory (DB2.zip or DB2.tar.gz) to install the experiment scripts. Again, if you use AWS, you should used the custom AMI databasetuning-db2v9.7 . THE ASSIGNMENT DESCRIPTION IS PRETTY LONG, but you should read it carefully before you get started.
- Assignment#3: You will tune the following query referenced in the assignment (the query might displayed in a bizarre way on your browser, do save the file - do not display it) .
Project
Your grade in this class will be based on a written report and an oral exam. The oral exam will be based on your written report and will establish your ability to (a) analyze the performance impact of design choices within a database system and (b) design and execute experiments to evaluate a data management system performance.
The written report is due in three printed copies at ITUs studieadministration on May 25th no later than 3PM. Note that this is a hard deadline. No extension can or will be accepted by the study administration.
The project is based on a case study: either ReserveWithUs that is a sanitized case study designed for this course, or a case study of your own based on a case from your work experience.
|
|
