JBoss / HSQLDB 1.8.0 Integration

[freddt]

Some of the issues previously reported with HSQLDB (v 1.71) were resolved in 1.7.2/1.7.3 releases. For 1.8.0 we have looked at all the persistence issues and introduced measures to prevent them.

For best performance and reliability, we need to draw up a list of startup parameters for HSQLDB and introduce them to the JBoss configuration files.

The latest 1.8.0 RC7 is available at http://hsqldb.sf.net (Note this is a Release Candidate and should be used only for testing)

Would JBoss developers please comment on the issues below:

BACKGROUND

1. HSQLDB stores the commands that modify data or database structures in the .log file. The only purpose of this file is to replay the commands from the last known state (CHECKPOINT) in case of abnormal termination. By default, when the .log file reaches 200MB a CHECKPOINT will occur and clear the .log file.

2. A CHECKPOINT will not reduce the size of the .data file. It will back it up to disk, which will take a few seconds. So it is a good idea to prevent the size of the .data file from growing too large. The .data file contains the persisted data plus some free space left over from deleted rows.

3. HSQLDB 1.8.0 keeps track of up to 500 recently deleted rows and uses the empty space in the .data file for new rows fairly efficiently. This means little empty space will be left unless suddently more than a few hundered messages are cleared, but no new ones are queued.

4. The CHECKPOINT DEFRAG command is available to reduce the size of the .data file by clearing the empty spaces. 1.8.0 introduces a database setting for the maximum allowed empty space. If empty spaces exceed this threshold, a DEFRAG is also performed with a normal CHECKPOINT.

5. The amount of memory used by HSQLDB can be reduced by setting the cache_scale property to a smaller value. The current defaults allow up to 50MB of binary data to stay in memory. Reducing the cache_scale to 12 will take this down to 12.5 MB (these values will be inflated by a factor of about 3 when measuring memory object usage in bytes).

REQUEST FOR COMMENT

1. By default, how much memory should be allocated to HSQLDB use (as a maximum)?

2. After how many messages should a CHECKPOINT occur? Each message translates to a few KB of logged information, so this can be translated into the maximum allowed .log file size.

3. How often should a DEFRAG occur at the time of checkpoint? This can be set with the maximum allowed empty space size.

Fred Toussi
[e-mail]
Maintainer, HSQLDB Project

[starksm64]

In general the memory usage should be minimized as the jms layer already has a memory cache to avoid writing msgs to the persistent store. Only non-persistent msgs that overflow from the cache and persistent msgs hit the db. What needs to be understood is the interaction between the jms cache and persistence layer under different scenarios. How msgs go into and out of the db are a function of the msg producer and consumer behavior. It can be anything from individual addition/delete, massive addition followed by massive deletion, to anything in between.

[freddt]

Update. HSQLDB 1.9 is now available as Release Candidate from http://hsqldb.org. This is supposed to be a drop-in replacement for the hsqldb.jar. It improves many areas, including reliablility. If your messages are large, increase the hsqldb.cache_size URL property. Refer to the hsqldb Guide for details and ask any questions via hsqldb.org.

Fred Toussi