Database encryption turned out to be the performance bottleneck of my application. Running the same queries on the same data gives very different figures when the database is encrypted. Since I must keep the data encrypted on disk, I thought I could avoid the decryption overhead by increasing the page cache and warming up the cache by loading tables and indices at application startup. Virtually, I want to run queries against in-memory tables and indices. I run the Derby network server in its own dedicated VM process, gave the JVM 1GB of heap, and configured 400MB of page cache (4KB page size * 100K pages), database backup being 200MB.
The performance gain is really impressive (as expected) once the cache has been warmed. Unfortunately, a quick test showed there must be lots of cache misses with real usage patterns. I wonder if it's simply a matter of sizing the cache (but how can 200MB of db not fit into 400MB of memory? Are record compressed on disk?), or if it doesn't work the way I think, and I should use a cache at the application level (that maybe I'll do anyhow, but if it workd, it'd be a big win for us) Raffaele
