Database Management and Performance Tuning
Please send your preferred exam time slot to peili@ifi.uzh.ch.
| Lecturer: | Pei Li |
| Teaching language: | English |
| Term: | Fall 2013 |
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
- 2013.09.18 General Introduction: Introduction, Tuning Principles, Course Organization. Download slides (PDF, 153 KB).
- 2013.09.25 Query Tuning: Query Processing, Tuning Examples. Download slides (PDF, 164 KB).
- 2013.10.02 Query Tuning: Minimizing DISTINCTs, Unnesting Queries; Index Tuning: Query Types. Download slides (PDF, 140 KB).
- 2013.10.09 Index Tuning: Index Types. Download slides (PDF, 324 KB).
- 2013.10.16 Index Tuning: Data Structures, Composite Indexes. Download slides (PDF, 214 KB).
- 2013.10.23 Index Tuning: Indexes and Joins. Download slides (PDF, 625 KB).
- 2013.10.30 Index Tuning: Tuning Examples. Download exercises (PDF, 86 KB).
- 2013.11.06 Concurrency Tuning: Introduction to Transactions. Download slides (PDF, 142 KB).
- 2013.11.13 Concurrency Tuning: Lock Tuning. Download slides (PDF, 718 KB).
- 2013.11.20 Concurrency Tuning: Weaken Isolation Guarantees. Download slides (PDF, 178 KB).
- 2013.11.27 Concurrency Tuning: Transaction Chopping. Download slides (PDF, 117 KB).
- 2013.12.04 Recovery Tuning: Log, WAL Buffer, Checkpoints. Download slides (PDF, 569 KB).
- 2013.12.11 Tuning the Storage Subsystem. Download slides (PDF, 198 KB).
- 2013.12.18 Exercises. Download slides (PDF, 85 KB).
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] (PDF, 117 KB) | 2013-09-25, 14:00 | 0.25 | DBLP data (ZIP, 66 MB) | [TEX] (ZIP, 1 KB) |
| 2 | [PDF] (PDF, 99 KB) | 2013-10-09, 14:00 | 0.25 | [TEX] (ZIP, 1 KB) | |
| 3 | [PDF] (PDF, 110 KB) | 2013-10-30, 14:00 | 0.25 | [TEX] (ZIP, 1 KB) | |
| 4 | [PDF] (PDF, 106 KB) | 2013-11-20, 14:00 | 0.25 | DBLP data (ZIP, 66 MB) | [TEX] (ZIP, 1 KB) |
| 5 | [PDF] (PDF, 91 KB) | 2013-12-04, 14:00 | 0.25 | Java demo (ZIP, 1 KB) | [TEX] (ZIP, 1 KB) |
| 6 | [PDF] (PDF, 76 KB) | 2013-12-18, 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 Friday January 17, 2014 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 |
| Tomas Ludrovan | 9:00 (Jan.-15) |
| Martin Noack | 9:30 (Jan.-15) |
| Michael Studer | 10:00 (Jan.-15) |
| Teodor Cristian Anastasiu | 9:00 |
| Andreas Flückiger Kim | 9:30 |
| Manuel Gugger | 10:00 |
| Aikaterini Papaioannou | 10:30 |
| Alessandro Peduzzi | 11:00 |
| Daniel Reber | 11:30 |
| Sébastien Goldberg | 14:00 |
| Robert Sharp II | 14:30 |
| Silvan Troxler | 15:00 |
| Louis-Marie Loe | 15:30 |
| Alexander Mülli | 16:00 |