db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "PerformanceDiagnosisTips" by Army
Date Tue, 18 Jul 2006 18:57:15 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by Army:

New page:
This page describes some initial steps that a user can take to help track down the cause of
performance problems that might occur between Derby releases.  For example, if an application
executes a query against Derby 10.1 and it finishes in some amount of time, but the same query
in the same application against Derby 10.2 takes longer, then this document describes some
steps a user can take to help developers determine the cause of the slow-down.

'''Step 1:''' Compilation or Execution?

If possible, try to separate the time taken for query compilation and the time taken for query
execution.  One way to do this is to use a Prepared``Statement for the query.  Measure how
long it takes for the "prepareStatement(...)" call to finish, and then measure how long it
takes for the "execute" or "executeQuery" call to finish.  The first measurement is the compilation
time; the second is the execution time.

'''Step 2:''' Query plans

Tell Derby to log the query plans for its queries by setting the "derby.language.logQueryPlan"
system property to true.  For details on how to do that, see [http://db.apache.org/derby/docs/dev/tuning/ctunsetprop34818.html
"Working with Derby properties"] in the Derby tuning guide.

Once that property is set, re-run the query.  Then open derby.log and find the query plan
that was chosen for the query.  Copy that query plan and hold on to it.

Now, in addition to the logQueryPlan property, also set the "derby.optimizer.noTimeout" property
to true.  This tells the optimizer to try out ALL join orders until it finds what it thinks
is truly the best one.   NOTE: depending on the complexity of your query, it could potentially
take the optimizer a long time to finish optimizing.  If you don't have the time to wait,
then feel free to skip this step; but this can be useful info so if you're able, this is a
good thing to do.

Once the noTimeout property is set, re-run the query again, and again open derby.log to find
out what query plan the optimizer chose.

'''Step 3:''' Numbers and query plans for older release

If it's possible for you to use or recreate the database and to access it with the earlier
Derby release (the one showing better performance), then repeat steps 1 and 2 for that earlier

'''Step 4:''' Database and/or DDL

It is often much easier to figure out why the optimizer makes the choices it makes when the
person investigating knows what database objects exist.  If you have a database that shows
the performance problem and that you can share with the community, this can help a LOT in
tracking down the problem.  If that's the case, mention this is in your emails and attach
the database to a Jira issue for the problem.

Otherwise, if you do not want to (or are unable to) provide the full database, a secondary
option is to generate the DDL for the database using Derby's [http://db.apache.org/derby/docs/dev/tools/ctoolsdblook.html
dblook] utility.  The full DDL is easiest to generate and is preferred, but if that's too
much information, you could also just try to generate the DDL for the specific tables that
are referenced by the query in question.

The more information about the database you can provide, the better.

'''Step 5:''' Bring it to the Derby community

At this point you should have rough estimates for the amount of time taken to compile and
execute the query against the later Derby release, and you should have two query plans for
that release: one for when the query is run as "normal", and one for when it is run with "derby.optimizer.noTimeout=true".

If that's as far as you were able to go (i.e. you were unable to do step 3), then send these
numbers and the query plans to the derby-user mailing list and explain the performance problem
that you are seeing.  Hopefully the community can then use the info you've provided to help
isolate and resolve the issue.  If you have a database and/or DDL and can attach that to a
Jira issue, so much the better.

If you completed step 3, as well, then please send that info to derby-user, too.  If you're
so inclined you can take a look at the query plans to see if anything obvious has changed
from one release to the other--for example, use of indexes or different join strategies--and
describe your findings on the list.  But if that's not your itch, feel free to just post the
info to the Derby list and to ask the community for help in figuring out what the problem

This is, of course, just the first step in diagnosing the problem.  But this data--executions
times, query plans and DDL--can go a long ways toward successfully diagnosing performance
issues that have come up from one Derby release to another.  So start with these steps and
then see what happens...

View raw message