dbgroup-at-ucsc / dbtune

This research project aims to develop tools in order to make index tuning easier and more effective.

DBTune Read Me

DBTune is an open-source library for Automatic Database Tuning. It provides an abstraction of the DBMS, utilities for doing common database-tuning-activities, as well as facilities to execute what-if optimization on a RDBMS. It is part of the DBTune project.

See the project web site located at https://github.com/dbgroup-at-ucsc/dbtune for the latest info.

Documentation can be found in https://github.com/dbgroup-at-ucsc/dbtune/wiki

License information regarding DBTune can be found in the LICENSE file.

dbtune's Issues

Create non-generic core.metadata classes

create non-generic classes for Catalog, Schema, Table, Column and Index classes (in core.metadata package)

Wiki "Work Summaries" and "Meeting Minutes" pages are broken.

The latest edit seems to have erased the contents of these pages. Any ideas?

Add Wiki entry to describe our catalog/schema terminology

Add an entry that describes what's the convention used in dbtune with regards to the terms Catalog, Schema and Database. Also, describe how it relates to different DBMSs (DB2, PostgreSQL, MySQL and Oracle).

Merge DatabaseSession with DatabaseConnection

In order to simplify the API, this two classes could be merged

C++ traits or mixins like in Java (discussion)

Mixins or traits seems suitable for what we are trying to accomplish. However, I do have some concerns:

• Since they are not supported in Java (i.e., not built-in support), trying to emulate them, IMHO, looks to me as complex as the other proposals. Am I missing anything?
• Is Java the right language for this? I mean Java does not support traits (at the language level), and we are using Scala already, which it does supports traits (similar to C++ traits and Ruby Mixins), so my question is:
  can we consider the idea of building a layer of functionality using Scala's traits and mixins which will allow us to avoid the use of generics (core classes) and leave the core classes intact (with generic)?

should we discuss this on Monday's meeting?

Using Logging Framework

slf4j + logback
http://stackoverflow.com/questions/3042450/what-log4j-alternative-logging-libraries-are-available

Import GForge's wiki to GitHub's

Install Hudson

Install Hudson on aigaion

add an entries to benchmark and possibly replace classes in dbtune.util package

for each classes in util package, create a tracker item that corresponds to the
benchmarking of the class and based on the results and its comparison against
standard JDK classes, the class should be replaced or not.

Change the design of dbtune dbconnection related classes

• Remove the DBPortal class and replace it by Properties
• The DBService class will be merged with ConnectionManager class

Move fixCandidates and explainInfo out of DatabaseIndexExtractor

These metnods are related to what if optimization and are not part of index extraction. The index extractor should just recommend indexes for a given query and that's all.

Make connectivity properties optional (infer them from URL property)

Code dealing with connection establishment should be modified so that it doesn't require the user to provide connection parameters if they're already provided through the URL.

Affected files are:

• AbstractDatabaseConnectionManager
• JdbcConnectionFactoryImpl
• JdbcDatabaseConnectionManager
• Platform
• edu.ucsc.dbtune.core.DatabasePackageTest
• edu.ucsc.dbtune.spi.ibg.WorkloadHandlingTest
• build.properties
• This Wiki entry

Rename package from edu.ucsc.satuning to edu.ucsc.dbtune

Obtain diff between PG++ vs REL8_3_STABLE

Create core.metadata.extraction package

will contain metadata extractors for distinct DBMSs

Move WhatIf classes to optimizers package

Classes:

• WhatIfOptimizer
• WhatIfOptimizerFactory

and their implementations would be better placed in core.optimizers.

Mirroring of the generics hierarchy

Okay, just mirroring the entire hierarchy will be a pain, which it will increase code maintainability cost. So the idea is to find a solution that will scale, i.e., support new dbms.

Some of the techniques that I have evaluated are

1. A Facade plus a new set of interfaces. This technique is the one used in the java.util.concurrent API.
2. Checked typing. Here we will move the type declaration from the class to the methods. Additionally, we will add a parameter Class<?> resolver to the signature of the affected methods.
3. A set of interfaces (only the main ones) that will be transformed to their appropriate generic classes by using the Acyclic Visitor Pattern.

... and more

Create metadata CLI high-level classes

• Index(name:String, columns:List, pages:#, sec:#, types:List, table:String). Note: sec = the number of entry keys in the index.
• Schema(tables:List, base_configuration:List). Note: base_configuration = set of indexes.
• Column(unique_entry_count:#, type:String, name:String, schema:Schema)
• Table(columns:List, indexes:List, pages:#, cardinality:#, name:String). Note: columns = attributes

improper use of serialization in edu.ucsc.dbtune.core.metadata.DatabaseObject

classes designed for inheritance (e.g., interfaces and abstract classes) should rarely implement Serializable, since this would force a significant and (often unwanted) task on their implementors and subclasses. And in most cases (if implemented), it will lead to serious and very tricky to find bugs in your code.

Write a proposal for refactoring the WhatIfOptimizer and IndexExtractor

See [2010-11-29 minutes][https://github.com/dbgroup-at-ucsc/dbtune/wiki/minutes-2010-11-29]

Team settings and permissions

done items

• created three more teams: external collaborators, phd students, and ms students.
• moved Jeff, Ivo, and myself to the Phd Students team

need to be done

• ask Alkis to remove us from the owners team; he will be the only owner there.

create_tests_for_baseline_divergent_index_configs

Discuss how the replacement of `DBIndex` by `DBSystem`

How can this be achieved?

Move docs/ folder to wiki repository

the wiki repository will contain all the documentation for the project, this means that meeting minutes, reading club overview's, etc. will have to be moved to it

Rename DatabaseIndexColumn to DatabaseColumn

A column is not just related to an Index but it's a database object on its own, so we should name it DatabaseIndex in the same way that we have a DatabaseTable class.

Update documentation for what-if optimizer

The documentation should be updated using a simple example query, with step-by-step instructions on how to run the what-if optimizer.

Change naming convention for releases

Switch to YYYY.MM.DD naming convention, similar to the core package.

Rename IndexExtractor to CandidateIndexExtractor

The name IndexExtractor is a little bit ambiguous. The interface of this class resembles more to a recommendation one. It could be thought as a "naive" or SQL-based (in a syntactical sense, not a DBMS one) advisor.

Even though we don't have an interface for an Advisor (an Advisor class exists but it's calling the DB2 command line tool db2advis; see issue #48) yet, we could come up with an interim Advisor interface based on this class and make IndexExtractor a child of it. Then we could rename IndexExtractor to SyntaxBasedAdvisor or something more ad-hoc.

Move metadata classes from core to core.metadata

Makes more sense to have the following classes (and their implementations) in core.metadata rather than in core

• AbstractDatabaseTable
• DBIndex
• DBIndexSet

Integrate slf4j + logback

http://stackoverflow.com/questions/3042450/what-log4j-alternative-logging-libraries-are-available

Configure a build on Jenkins

Create a build that will be continuously monitoring our Git repo and through Ant will build and run all the tests.

This item requires:

• install and configure the Git plugin
• configure the Ant plugin
• install an SMTP server (if we want e-mails to be sent)

Remove or rename Advisor class

Class core.Advisor contains calls to the db2advis command-line tool. Could be renamed to something more meaningful, like DB2AdvisorCommandLine.

Replace TODOException by UnsupportedMethodException

Pick a continuous integration tool

for build automation, TeamCity looks like a good alternative. If we can set it up easily on aigaion's, we should use it

Merge DatabaseIndexSchema into DatabaseIndex

There's no reason why to have them as two separate entities.

Replace custom classes in dbtune.util package with JDK classes

Add PostgreSQL support in core.metadata.extraction

Add PGExtractor class for extracting metadata information.

Remove Database prefix from classes in edu.ucsc.dbtune.core package

It's redundant (the project is about DB tunning)

Create a 'Git guide for SVN users' Wiki entry

Move CLI classes (created by issue 41) to CLI package.

These classes are not core classes. They are CLI specific classes

Rename each class from util package to avoid name clashes

package edu.ucsc.satuning.util (or edu.ucsc.dbtune.util) contains many classes
whose name clashes with JDK's ones

Remove IntelliJ's ipr file

It is causing problems when pulling changes from the master. There is not point of having that ipr file in GitHub.

Remove Exception swallowing

Code swallows exceptions in a lot files where catch() statements are used. This makes the code really hard to debug.

jeff_testing

testing for divergent index designs.

Remove generics from core package.

add entry in Wiki for explaining how aigaion's pg++ works

Explain how to start and shut it down. Also, how to access the log and where are the conf files stored.

Implement INUM

We must integrate INUM at some point. INUM operates on top of the PG optimizer, so the integration should be easy. However, we need to implement a new WhatIfOptimizer class that talks to INUM instead of the normal PG++ what-if optimizer.

Properly install postgres++ in aigaion

Create package core.connection or core.connectivity

The API would be more easily "browsed" if a connectivity package exists for placing all connection-related classes, namely:

• Connection
• ConnectionManager
• JdbcConnectionFactory

and all their implementations.

Create a Wiki entry to describe the change-pull-fix-push Workflow

We need to clearly specify what is the workflow that everybody should follow when submitting patches to the master branch. In short:

• git pull
• git branch -b master issue<#>
• commit many times
• git rebase
• ant test.all
• git push

After this, then the CI tool will double check that we haven't broken anything.