I am trying to embed the HSQLDB on multithreaded environment in java for persistence. I have 2 select/update, 1 insert and i read thread. I am processing 1 KB message through the below cycle.
1)Insert the data to DB and send data to different app
2)get the reply and update interim status, sent confirmation back.
3)Check the using one thread for periodic updates.
4)Get the final status and remvove the data.
Code snippet is given below shows how I am embedding the HSQLDB and I am using version 2.2.9
The mainthread which creates or use if table is existing
connection = DriverManager.getConnection("jdbc:hsqldb:file:sample.db;ifxeists=true;hsqldb.result_max_memory_rows=1000;hsqldb.sqllog=0;hsqldb.applog=0;hsqldb.lob_file_scale=16;hsqldb.inc_backup=true;hsqldb.log_size=25;hsqldb.defrag_limit=25;");
statement = connection.createStatement();
// Create Table
statement.executeUpdate("create table if not exists TESTTAB (msgid VARCHAR(20), data CLOB(4M) , status CHARACTER(6), retry int ,TimeStamp BIGINT)");
rest of the threads will just use the table reference using basic commands and use separate conenctions for their operations.
SRConn = DriverManager.getConnection("jdbc:hsqldb:file:sample.db;ifxeists=true");
All are running inside the same JVM. When I run this on eclipse 5 files are getting created for DB.
In this sample1111.db.lobs & sample1111.db.log is growing indefenitely eventhough there is no data present. I tried setting checkpoint but not seems to be working. The .lob file has grown to 180MB for processing just 80K messages. I am not sure is this something expected in HSQLDB and I am totally new to this.
Can someone please help me.
Try the latest snapshot for 2.3.0 from here:
You also need to change the settings to increase the frequency of CHECKPOINT. Use something like hsqldb.log_size=5. Each time the size of the .log file reaches 5MB, a checkpoint is performed and this will also reduce the size of the .lobs file.
If your messages are generally smaller than 16KB each, then reduce the hsqldb.lob_file_scale to the average message size in KB.
Thanks Fred for the fix !!!
I have done the initial round of testing and it seems the issue is resolved. As soon as the .log file crossed the 5MB mark it shrunk back. The .lob file growth is stopped and remained constant at 42MB which is fine for me. I will continue my testing and will let you know the outcome. Thanks once again.
I have observed one more scenario, though the .log file is getting refreshed once it reaches the threashold the .lob file is showing a small growth. I am processing the messages with the following rate.
in every 500 milli seconds there will be one cycle of
And this is continously happening. One the first reset of .log filea total of 2600 such cycles happened successfully. On the fifth iteration I have noticed that .lob file is gradually growing though not in a larger rate. On the 6th iteration I have checked the difference between the current and last file size and it was of 24KB. On 7th no growth noticed. the test is on and I have processed a total of 15K such cycles and .lobs file size is 47,696 KB.