db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Faster create database
Date Wed, 06 Oct 2004 03:07:28 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I looked at the create database performance today and found an issue. In
the set of changes from Cloudscape 5.1 to Derby, we removed a
configuration property that allowed control of when/if stored prepared
statements (SPSs) were created for java.sql.DatabaseMetaData queries.
The change meant that these SPSs are always created at create database
time. This was an ease-of-use decision, the application developer
shouldn't care about how DatabaseMetaData is implemented internally.
It also reduced the code footprint.

Due to the way the code was laid out, these SPSs are created after the
full creation of the database, just before the Connection object is
return to the user. By moving their creation earlier Derby can create
databases faster, for two reasons:

 1) The SPSs would be created when the database is in "no-log" mode,
thus reducing the amount of i/o at create database time. All the initial
work of a create database is perfomed in no-log mode, and then all the
modified files are flushed to disk before the database is made
available. If a crash happens during this "no-log" mode then the
database is never visible to anyone.

2) The SPSs would be created in "no-compile" mode. This means the
catalog entries are created, but the SQL statement is never compiled and
hence the generated class is not written into the catalogs. Currently
these SPSs are compiled at create database time. The statement will now
be compiled on its first use, the first call to the matching
DatabaseMetaData method. This is how the SPSs for the network server are
created.


I made some quick changes to implement this, and see, on my machine,
database create drop from around 15 seconds to around 9 seconds.
The amount of log written during create database time drops to ~5k from
~650k.

The majority of the remaining time seems to be in spent in

  - (~5.5s) creating the system tables
DataDictionaryImpl.createDictionaryTables()

  - (~3s) Flushing the pages to disk completing the create
   AccessFactory.createFinished()

Assuming there are no objections, I'll work on cleaning up the code and
submitting it, hopefully before the release.
Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBY2FwIv0S4qsbfuQRAqQuAKCJcovwaObCw8hejLDqMF9vvdqz7wCeL18H
wC3dZSGzwXa8pUhGm1fB70A=
=CCn6
-----END PGP SIGNATURE-----


Mime
View raw message