Matt Doran <[EMAIL PROTECTED]> 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
