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.

Thanks,
-- 
Knut Anders

Mime
View raw message