Phase 2: Study on the Performance of a DBMS

Requirements

  1. Run the following type of queries on your DB instance.
    1. simple query: SELECT * FROM table
    2. single table, complex conditions: SELECT * FROM table WHERE condition
      condition must have at least 5 clauses related with AND, OR, and NOT.
    3. join query and complex conditions: use at least 3 tables.
    4. sorting (ORDER BY): SELECT attr1, attr2, attr3 FROM table ORDER BY some attributes.
    5. aggregate functions: SELECT SUM(), AVG(), MAX(), COUNT() FROM table(s) WHERE some condition GROUP BY attr(s). You should use all of them.
    6. add HAVING to the previous query
  2. Record the following information in a table per query: Execution Time, CPU Utilization, Memory Utilization, Threads Used.
  3. Execute the queries in the folloing manners.
    1. same query multiple time in a row (say 5 times). Record each time the information about and give the averages.
    2. execute the queries in a random order. Repeat several times, at least 5 times.
    3. Record each time the information about a query. Give the averages.

Deliverables

  • Record the state of DB Serve before you execute any of the querie.
  • Give the queries.
  • Give a detailed tables of results.
  • Give averages and standard deviations.
  • Analyze the results. Comment on the observed behaviour for the queries.
    • Start early!
    Collaborate! Compare and discuss your approaches. The end product is expected to be an individual effort!