[SOLVED] INFO3404/INFO3504: Database Systems 2

School of Information Technologies Uwe Roehm & Bryn Jeffries

INFO3404/INFO3504: Database Systems 2 2.Sem./2017

Database Tuning Assignment

Group Assignment (20% INFO3404 / 10% INFO3504) 05.10.2017 Introduction

This is the practical assignment in which you have to tune the performance of a relational database for a given workload. We provide you with the database schema, example data, and a workload that consists of both queries and updates. Your task is to decide on which physical design is best suited to improve the given database on PostgreSQL.

This assignment is based on the RUBiS on-line transaction processing (OLTP) bench- mark. This benchmark models the principle activities (transactions) of an online auction system similar to eBay. These transactions include browsing for items, registering items, bidding and checking the bid history. We added a few more analysis queries, for example to check for top auctions, soon-to-close auctions and an analysis of auction users.

You find links to online documentation and hints on tools and schema needed for this assignment in the Assignments section in Blackboard.

Submission Details

You must submit your final attempt no later than Friday Week 12 (11:59pm, Friday 27 October 2017), with late submissions receiving a penalty of -20% of marks per day late. There will be an opportunity to show a draft to your tutor, and you should take the op- portunity to do this and make use of their feedback. Your submission should contain two separate items:

1. via PASTA a single SQL script with all SQL commands, such as index creation com- mands, to be executed on a freshly created and populated database, to implement all your optimisations;

2. via elearning a report in which you justify your tuning decisions.

Your answer will be tested against the standard installation of PostgreSQL 9.5.1 on our SIT lab server.

Please retain copies of your submitted assignment files and databases, as the unit coor- dinator may request to inspect these files before marking of an assignment is completed. This is a group assignment. You should form teams of 2-3 people in your Week 09 Tutorial

1

after the Mid-semester Quiz. If you are unable to form a group at this time, your tutor will assign you to a group. The mark awarded for your assignment is conditional on you being able to explain any of your answers to your tutor or the subject coordinator if asked.

All the best!

Database Preparation

On the course eLearning website, we have provided a schema creation script and a data loading script. Use both scripts the create your benchmark schema and to load an example database. About 150 MB of example data will be created. In order to minimize the loads on the SIT systems, please try to create only one database per group.

1. SIT PostgreSQL account installation

1. (a) log into your local postgres installation or the SIT account at: Address: soit-db-pro-2.ucc.usyd.edu.au
   Port: 5432
   Username: y17i3x04 unikey

   Password: Your Student ID number

2. (b) Download the auctiondb tables.sql script from eLearning and execute it with PGAdminIII.

   Important: You must change the user name in the last line of the script to your own postgresql login name!

3. (c) Download auctiondb.backup.zip from the course website and unpack it. You should get a auctiondb-pgdump.tar file.
4. (d) Load your database with example data using pgAdmin3: Login to your database,
   select the info3404 auctiondb schema, and execute the context menu Restore.
   It will prompt you for a data file where you should select the unpacked auctiondb-pgdump.tar file you unpacked in the previous step from eLearning.

2. PASTA

(a) Load your SQL tuning file via PASTA and it will be added to the queue in PASTA and tested when available. PASTA testing capability will appear from Week 10 onwards. An announcement on EdStem will be posted when it goes live.

Assignment Tasks
Question 1: Database Tuning [10 points]

The AuctionDB workload consists of several transactions, each comprised of several SQL statements. We have provided an example trace on the course website which you should

2

use. You can assume, that each transaction occurs with the same probability. Note, that the SQL statements are typically parameterised. See the corresponding comments in the SQL script of the transaction-pseudocode for details.

1. a) Create the appropriate indexes, do clustering of tables and tune the table storage such that the performance of the given workload trace improves. Do not change the database server configuration! The only allowed SQL commands are:
   CREATE INDEX, ALTER TABLE, CLUSTER, EXPLAIN and ANALYSE.

   Submit one single SQL script that performs all your physical design optimisations. To achieve full marks for this question ([10 pts]), your solution needs to

   • create all necessary primary key indexes and create appropriate secondary in- dexes that improve query performance (we will check versus the estimated query costs by the query optimiser of PostgreSQL 9.5.1),
   • ensure that the given set of indexes is minimal (no index is given that is not used),
   • be mindful of storage costs and the effect on updates of your set of indexes, and
   • make use of at least two special optimisations such as multi-attribute indexes, a covering index for an index-only plan, or some appropriate CLUSTER command.
2. b) On our eLearning website, we have provided an example workload trace script, as well as supporting stored procedures and shell scripts to automatically gather cost in- formation from PostgreSQL. You can run these scripts once on the initial, unchanged database and then, after you have finished all tuning operations to see your improve- ments that result from the optimisations you have run. Your final submission will be tested using this sample workload via PASTA.

   Note: Parts of the marks will be assigned based on the quality of your tuning efforts. We will rank the cost savings of all submissions of the whole class.

Question 2: Documentation of Tuning Decisions [10 points] (INFO3404 ONLY)

Write a text document (plain text or Word document or PDF file) in which you

1. analyse the workload (which queries/updates and how they relate), and
2. classify each non-primary-key index that you have created, and
3. briefly justify each tuning decision.
4. You should also briefly discuss the effect of your tuning decisions on updates.

For example, if you created an index on attribute A of a table R(A,B,C) you should state exactly which kind of index it is (B+ Tree or Hash or Bitmap or inverted Index, secondary or main index, clustered or unclustered) and why you have chosen to create it. This typically has to refer back to one or several queries in the given workload.

3