Monday, April 27, 2009

Wither Derby?

Over lunch last week with co-workers, we were discussing the Oracle buy out of Sun and how that would affect MySQL and what other options there would be. Several people had some experience with PostgreSQL (including myself), but then I brought up Apache Derby ... and no one had used it or event downloaded it, and only a few had even heard of it. I just checked their web site and the last update was in November 2008. When I last played around with it, it seemed to have all the major features of a real database and the advantage of being pure Java (I can't speak to performance).

How many people are using Derby and if not, why not?

9 comments:

  1. I used it a bit in embedded mode (same JVM as my app). It was reasonably well behaved with regards to memory usage under light load. But full table scans would cause it to suck up so much memory that you'd see a lot of disk paging. Adding indexes solved part of the problem, but over time (5 or 6 days) of 50/50 read/write, Derby would just stop using the indexes and go back to table scans. Shutting down the app, running some Derby cleanup command, and starting back up would usually fix it.

    Once we hit about 2GB of data, performance also fell off. It seemed to be trying to keep a lot of data in memory even when it wasn't in use.

    You could fiddle with most of the knobs and buttons for controlling memory usage, etc. But a lot of times, our tuning of Derby would counter act our tuning of the VM.

    It is a good DB. It handled all the SQL we threw at it (albeit not that much) and kept on running. Given enough time, I think we could have made it work.

    But we didn't. We eventually moved the code over to Berkeley DB because we had years of experience tuning it. Plus using the C version with Java bindings allowed us to tune both the VM and BDB separately (used different heaps).

    We never did try the pure Java version of BDB.

    ReplyDelete
  2. I tried using it in embedded mode, and yeah, like Justin, I was very disappointed with the performance...

    I ended up switching to h2db (http://www.h2database.com/html/main.html), which was smaller, and way quicker....

    I do hear people mention Derby at java meetups... so, seems like it's still being used/considered...

    ReplyDelete
  3. Apache Derby AKA Java DB is distributed with Java SE 1.6 JDK.

    http://developers.sun.com/javadb/

    The apache website hasn't been updated for a while but there seems to be some activity on the sun site with Derby 10.5
    http://blogs.sun.com/kah/entry/derby_10_5_preview_sql

    ReplyDelete
  4. We're using H2, which is both lighter weight and faster than Derby aka JavaDB. The latter was the first though that had first complete JDBC-4.0 support.

    ReplyDelete
  5. I used the predecessor to Derby back in the Cloudscape days. Its performance never impressed me in the least. Back then, it was less than 100% stable as well (close, but not nearly as close as Postgres, or really any other popular DB).

    H2 is a better in basically every way, IMO (esp. if your willing to take a little more risk and enable MVCC).

    ReplyDelete
  6. We use Derby as the primary/internal database for our print tracking application, PaperCut. We've found it to be a very reliable and pretty good performing DB. We too use it in embedded mode.

    It performs quite well under transaction-based load ... inserting records, etc. However we find that it does have issues with the queries that hit large data-sets (like reports). Derby's optimizer doesn't appear to be any match for the big DBs, and it also tries hard to limit it's memory usage (often causing a huge performance hit). As an example, if it thinks a join is going to take to much memory it chooses a "loop join" instead of a "hash join" resulting in massive perf degradation. There are knobs to change these limits, but they're not always obvious.

    There are also some deadlocking issues when full table scans occur when insertions are occurring. I believe this one has been fixed for the upcoming 10.5 release. See issue DERBY-2991 for the details.

    For our larger customers, we usually recommend they upsize to SQL Server, Postgres, MySQL, etc.

    I gave a presentation on Derby at the Victorian (Australia) Java Users Group. For those interested, my slides are here.

    ReplyDelete
  7. I used Derby a little bit in network server mode when I was working on some small internal apps for my company before we pushed things to DB2 (I didn't have DB2 Express-C installed at the time). It worked quite well for my purposes, but now I use DB2 Express-C for everything (both at work and at home) and deploy to DB2 for z/OS at work.

    ReplyDelete
  8. The Derby mailing list is still relatively busy. I didn't have performance issues when using it for a greylisting antispam app, but H2DB didn't exist at the time (the reasoning for using Derby was it had a good SQL processing engine and the code could be ported to DB2 since IBM was making Derby SQL compatible)...

    ReplyDelete
  9. Apache Derby 10.5.1.1 just got released last week.
    http://tinyurl.com/d3hqaz

    Apache Derby is a multi-threaded database engine that can support concurrent users w/ row-level locking. Of course it can also be used as a singe-user database engine on the client side. Derby is stress-tested with its nightly builds and can support fairly big tables (300GB+).

    For those who don't know, Derby's core engine first incarnation was issued back in 98' (as Cloudscape). This is a robust engine and does fully support database ACID transaction properties.

    Of course, one database (size) does not fit all requirements and obviously some other databases might be faster with certain operations (types of queries) or might be slower. Derby is not an Enterprise database but it can sustain a pretty high load of concurrent users...It is free and well supported.

    Apache Derby has a very active users and devs community and is backed by Sun and IBM.
    http://www.ohloh.net/p/derby

    Java DB is Sun's supported version of Apache Derby which you can find bundled in Sun's JDK 6+.

    You can find more information about Java DB and Apache Derby below:
    http://developers.sun.com/javadb/
    http://db.apache.org/derby/
    http://www.ohloh.net/p/derby
    http://www.nabble.com/Derby-f356.html
    http://blogs.sun.com/kah/category/Java+DB
    http://blogs.sun.com/FrancoisOrsini/

    If you see a feature that you would like to get implemented or a bug fixed, please feel free to vote on the corresponding JIRA entry at:
    http://issues.apache.org/jira/browse/DERBY

    Thanks

    ReplyDelete

Please note that this is not a support forum for Tapestry. Requests for help will be deleted. Please subscribe to the Tapestry user mailing list if you are in need of support, or contact me directly for professional (for pay) support.

Spammers: Don't bother. I delete your comments and it's a waste of time for both of us. 垃圾邮件发送者:不要打扰。我删除您的评论和它的时间对我们双方的浪费