Google Summer of Code 2010 - Project Proposal

Project

Derby-4587- Add tools for improved analysis and understanding of query plans and execution statistics

Student Name

Nirmal Fernando

E-mail

nirmal070125@gmail.com

IM

nirmal070125 (Google Talk)

Abstract

Apache Derby, an Apache DB subproject, is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0, and is a very interesting piece of software. Derby is based on Java, JDBC and SQL. Quite frequently, users of Derby have troubles in comprehending

The objective of this project is to provide visual displays to help people understand the way their query is being run.

Description

Derby is known as a system which is easy to install, deploy and maintain in production environments. Latest official release of Derby is 10.5.3.0, and was released in year 2009. Apache Derby (here after mention as Derby) has an active developers’ community behind the wall, who are dedicating, hours of their time to improve/ fix bugs that are raised by the users. The Derby developers’ community is really helpful and they are easy to work with. Users, of an open source project like Derby are the most important group that will take the software an extra mile from what it is at a particular instance. That’s why the developers’ pay a great attention for the issues or improvements that are raised by users of Derby. This project addresses such an improvement that users of Derby are expecting. Quite frequently, they have troubles in comprehending the steps followed by the optimizer when it translates a query into a query plan, and details such as time to execute, resource utilization at each step of the query plan. There are low-level features in Derby which capture this information and record it, such as logQueryPlan, and the XPLAIN tables, so this project will lead to a development of a new tool which can process the query plan and execution statistics information and present it in a more comprehensible fashion i.e. a graphical interpretation of the query plan. After went through some possible ways of implementing this tool which were suggested by my mentor for this project Mr. Bryan Pendleton, we came across the following idea which will display the query plan in a web browser.

Figure1: Process View of the tool http://img682.imageshack.us/img682/9691/derbya.png

First I do a research on Derby to recognize all the possible instances that can be occur in a query such as name of the table, sort, hash-join etc. Next I planned to write the template, as it iteratively looks for the instance occurred at that particular step and displays the image + details (execution time, resource usage), for all the steps that were followed when executing the query. Here, image and details can be obtained through the XML file emitted by the query plan.

The XML document should contain a predefined set of tags for all possible instances of a query as mentioned above and each tag (Child) should contain sub-children which will describe the image + details. And this will create at the run time in order of the execution procedure.

Things Done So Far

Deliverables

Development Schedule

About Me

I am Nirmal Fernando, 3rd year undergraduate at Department of Computer Science and Engineering at University of Moratuwa, Sri Lanka. I am very competent at Java programming language, OOP, relational database schemas, SQL, XML, XSL and data structures and algorithms. I had gone through a course module in Database Management Systems, few months’ back that will help me to understand the underlying concepts fairly easily. I had created an extension for OpenOffice.org as my level-3-semester-I project [3].

Community Interaction

Apache Derby developers’ community is the main community behind this project and I highly appreciated comments/ ideas of the expert developers of Derby and consider those as a great opportunity to learn and contribute more and more to the improvement of Derby. I already got subscribed to the derby-dev mailing list and getting the continuous feed backs and ideas regarding the project as well as the other issues of Derby.

Resources/References

[1] Project Idea on Apache JIRA: https://issues.apache.org/jira/browse/DERBY-4587

[2] Derby-4406-Involvements: https://issues.apache.org/jira/browse/DERBY-4406

[3] Open Office.org extension: http://extensions.services.openoffice.org/en/project/MatrixManipulator

Thank you !!