[Hsqldb-user] Query Performance and Memory Footprint of Cached Tables

[Qian S. <qi...@gm...>]

Hi All,

I am developing a client-side application using in-process hsql database (
1.8.0.2).  I'm using cached tables for certain tables that may potentially
grow to tens of thousands of entries.  The expected .data size for cached
tables is between 30MB to 50MB.  My target is to limit memory footprint to
~60 MB for the application overall (i would assume that I can afford up to
30MB for the database) while providing decent query performance. One furthe=
r
requirement of my database usage pattern is that it needs to support in-tex=
t
search using 'like' operator on text of length over 1000 bytes in average.


Right now, I'm experimenting with a data size of 16MB, it seems that no
matter how I tune the configuration knobs in .properities file, I failed to
achieve decent query performance together with less than 60MB memory
footprint.  Below are my few observations:

1. it appears that reducing the cache size impacts query performance
significantly. A query that takes ~2 seconds with one configuration can tak=
e
up to ~1 min. with cache_scale reduced from 11 to 8.

2. search using 'like' operator over long text seems to be very memory
consuming even though the resultset returned is small and the consumed
memory can't be released after the resulset is closed

3. if a large resultset is returned, then database memory footprints can
grow up to 100MB, way above the specified limit of cache size, even after
the resultset is closed

I would really appreciate if you may assist me in evaluating if my target i=
s
realistic and if so, how to tune the query and database configuration
towards the target.

Thanks!
Qian