db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Derby 10.3.1 performance
Date Fri, 17 Aug 2007 13:56:08 GMT
Matt Doran <matt.doran@papercut.biz> writes:

> Thanks for all the info.  The performance gains look impressive.
> We have generally been very impressed with Derby's robustness and performance.
> We use Derby in an embedded mode.  We have found some performance issues with
> some more complex queries on large datasets.  We usually recommendlarger
> customers run our product (PaperCut NG) on a native DB (like SQL Server,
> Postgres, etc), because they seem to perform better on bigger datasets (maybe
> due to having less memory constraints).

We do get reports from time to time about performance problems with
complex queries. These problems are normally caused by the optimizer
choosing a non-optimal execution plan. If you have a reproducible case
(that is, the database schema and the queries), please report it as a
bug or enhancement request (see
http://db.apache.org/derby/DerbyBugGuidelines.html). Then the problem
might get attention from one of our optimizer experts.

> Admittedly we have not spent very much time analysing derby performance and
> understanding why these queries are slow.  Given the impressive performance
> shown in those presentations, our performance issues are probably an indication
> that we have not tuned the Derby configuration or queries adequately.  When I
> get some time I'll analyse these in more detail.
> We do have some constraints when tuning Derby configuration.  Our product is
> designed to be easy to install, and maintain ... and *must* work out of the box
> on all systems.  This means we cannot allocate too much memory to the page cache
> (which has a big impact on performance) by default ... because it could cause
> out of memory on memory limited systems.  So our performance suffers on more
> powerful systems in order to support the smaller ones.
> It would be great if we could have a more dynamic way to define the page cache
> size.  For example as a percentage of the maximum memory allocated to the JVM
> (e.g. Runtime.getRuntime().maxMemory()).  This would allow us to say use 10% of
> JVM memory for the page cache, which would provide some basic auto-tuning
> depending on the available memory.   We configure the JVM itself to use a
> percentage of available system memory (which works well at the application 
> level).
> What do you think?

That sounds like a very good idea. Unfortunately, it is hard to
calculate exactly how much memory Derby's page cache actually needs
since it holds a fixed number of pages, and the size of the pages may
vary between 4 KB and 32 KB (chosen dynamically based on the schema for
each table/index). Also, each cached page contains an array with handles
to each row on the page, so pages with a large number of small rows may
take more memory than an identically sized page with larger rows. But it
should of course be possible to make a qualified guess (or conservative
estimate) on the space needed. It would be great if you could log an
enhancement request so that the idea isn't forgotten.

Knut Anders

View raw message