db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Derby performance issues
Date Thu, 23 Jul 2009 12:38:20 GMT
Dan Armbrust wrote:
> I have a fairly complex application which I currently support on
> PostgreSQL and SQL Server.  Most of the SQL that is run is created by
> Hibernate.
> I'm trying out Derby as a backend for the application - and well, I'm
> hoping I have something configured wrong - because the performance of
> Derby is dismal in my use case.
> Maybe someone can give me a pointer.

Hello Dan,

The following are just some questions and general pieces of advice. 
Hopefully they can help guide us in the right direction...

 - Which version of Derby and Java are you running?
 - A problem many users have run into, is outdated cardinality 
statistics for the indexes. Depending on your Derby version and 
preference,  does it help to run SYSCS_UTIL.SYSCS_UPDATE_STATISTICS 
(version 10.5), SYSCS_UTIL.SYSCS_COMPRESS_TABLE or recreate the indexes?
   If that doesn't help either, you could experiment with optimizer 
overrides. If the performance is still bad, something else is wrong 
(could be a Derby bug).
 - Note that the page size must be set before you create the tables.
 - You have allocated 10'000 pages for the cache, ~80 MB (with 8K pages) 
+ overhead. Is this size comparable to the cache used by the other 
database systems?
 - For testing, can you locate the transaction log on a separate device, 
and then have another look at the disk IO?
   (see the connection attribute 'logDevice' in the manual)
 - What's the size of the database (compared to the page cache)?

Based on the information you have given, it sounds to me as if Derby is 
constantly reading in pages from disk as fast as it can.
> Typically, my application is driving 40 connections to the DB - all
> simultaneously.  With Derby, I backed that down to 8 parallel
> connections, which seemed to help a bit... but it is still far to slow
> -
> Derby is at least 30 times slower than PostgreSQL on the same
> hardware, and completely pegs both CPU usage and disk IO.
> I've bumped the page size to 8192, and pushed the pageCacheSize to 10000.
> One problem I suspect I have is tuning how Derby handles fsync.  Are
> there settings for this?  I don't need commits to be written to disk
> at commit time - buffered is ok, so long as the database can recover
> from an unexpected shutdown.

What do you mean by recover from an unexpected shutdown?
If you enable the write cache on the disk, Derby would be able to start 
up again, but you will most likely loose some of the most recent 
transactions in case of power failures etc.


[ snip - query details ]

View raw message