Database Management and Performance Tuning
| Lecturer: | Pei Li |
| Teaching language: | English |
| Term: | Fall 2014 |
Objectives. This course will cover basic principles of how to tune database applications. Such knowledge helps to tune applications on database management systems, operation system, and hardware. Specifically, performance criteria for choosing a database management system will be addressed, including sets of experimental data and scripts that help to test particular aspects of systems under consideration.
Literature. The textbook of the course is Database Tuning - Principles, Experiments, and Troubleshooting Techniques. Dennis Shasha and Philippe Bonnet.
The lecture notes and assignments for this course will become available as we progress through the semester.
Lectures
- 2014.09.17 General Introduction: Introduction, Tuning Principles, Course Organization. Download slides.
- 2014.09.24 Query Tuning: Query Processing, Tuning Examples. Download slides (PDF, 164 KB).
- 2014.10.01 Query Tuning: Minimizing DISTINCTs, Unnesting Queries; Index Tuning: Query Types. Download slides.
- 2014.10.08 Index Tuning: Index Types. Download slides (PDF, 324 KB).
- 2014.10.15 Index Tuning: Data Structures, Composite Indexes. Download slides (PDF, 214 KB).
- 2014.10.22 Index Tuning: Indexes and Joins. Download slides (PDF, 625 KB).
- 2014.10.29 Index Tuning: Tuning Examples. Download exercises (PDF, 86 KB).
- 2014.11.05 Concurrency Tuning: Introduction to Transactions. Download slides (PDF, 142 KB).
- 2014.11.12 Concurrency Tuning: Lock Tuning. Download slides (PDF, 718 KB).
- 2014.11.19 Concurrency Tuning: Weaken Isolation Guarantees. Download slides (PDF, 178 KB).
- 2014.11.26 Concurrency Tuning: Transaction Chopping. Download slides (PDF, 117 KB).
- 2014.12.03 Recovery Tuning: Log, WAL Buffer, Checkpoints. Download slides (PDF, 569 KB).
- 2014.12.10 Exercises. Download slides.
- 2014.12.17 Evaluation and Exam. Download slides.
Assignments
A PostgreSQL account will be created for every registered
student (instruction). You can connect to PostgreSQL via pgAdmin3
(tutorial).
| Assignment | Due Date | Max Grade | Downloads | Template | |
| 1 | [PDF] | 2014-09-24,14:00 | 0.25 | DBLP data (ZIP, 66 MB) | [TEX] (ZIP, 1 KB) |
| 2 | [PDF] | 2014-10-08,14:00 | 0.25 | [TEX] (ZIP, 1 KB) | |
| 3 | [PDF] | 2014-10-22,14:00 | 0.25 | [TEX] (ZIP, 1 KB) | |
| 4 | [PDF] | 2014-11-05,14:00 | 0.25 | DBLP data (ZIP, 66 MB) | [TEX] (ZIP, 1 KB) |
| 5 | [PDF] | 2014-11-26,14:00 | 0.25 | Java demo | [TEX] |
| 6 | [PDF] | 2014-12-10,14:00 | 0.25 | [TEX] (ZIP, 1 KB) |
Instructions: Please follow these instructions for preparing and submitting the assignments.
- To prepare the report, use the Latex template that comes with each assignment.
- Name the template with your lastname, firstname, and the assignment number (e.g., turing-alan_02.tex).
- You only need to submit the PDF of the report. No need to submit your Java source code or Latex file.
- I might explicitly ask some students to also send me their source code when I have doubts about the report.
Examination
Final exam takes place on Wednesday-Thursday January 14-15, 2015 in BIN-2.E.09.
The assessment is either based on the assignments and the final oral exam or on the final oral exam only. The final oral exam follows the standard grading system, i.e., 6 is the highest grade and 1 is lowest passing grade. The grading points for the assignments (max 1.5 points) are added as a bonus to the oral grade to form the final grade. There is no midterm.
| Student | Time Slot |
| Martin Spielmann | 10:00 (Jan. 08) |
| Jean-Paul Van Brakel | 10:30 (Jan. 08) |
| Liliana Leonor Barrios Aparicio | 10:00 (Jan. 14) |
| Thomas Walter Brenner | 10:30 (Jan. 14) |
| Atif Colo | 11:00 (Jan. 14) |
| Alessandro De Carli | 11:30 (Jan. 14) |
| Pascal Forny | 14:00 (Jan. 14) |
| Quentin Willimann | 14:30 (Jan. 14) |
| Thilo Haas | 15:00 (Jan. 14) |
| András Heé | 15:30 (Jan. 14) |
| Fabian Hegglin | 16:00 (Jan. 14) |
| Oliver Zihler | 16:30 (Jan. 14) |
| Livio Hobi | 14:00 (Jan. 15) |
| Samuel Liniger | 14:30 (Jan. 15) |
| Oliver Leumann | 15:00 (Jan. 15) |
| Jan Meier | 15:30 (Jan. 15) |
| Mathias Molnar | 16:00 (Jan. 15) |
| Poornigha Santhana Kumar | 16:30 (Jan. 15) |
| Sebastian Stephan | 17:00 (Jan. 15) |
| Taya Goubran | 17:30 (Jan. 15) |