From: Qian S. <qi...@gm...> - 2006-02-22 21:57:59
|
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 |