We are developing a function to store session information, for use in a HA environment.
However we are not reaching the throughput we want.
Since its for persistance, we need to do autocommit on all operations.
The Table has two indexes and a blob of data.
The first index is a char(20) and the second is a timestamp.
I put transaction log and data on separate disks.
The first issue is that on a desktop machine ( running vista ) with two 7.2k rpm sata disks I get over 900 tps, while on a server ( running RHEL 5 ) and two 15k rpm sas disks, I get around 250 tps.
I realise this might not have anything to do with derby, but running iozone tells me that the server has _a lot_ faster IO.
Im of course very interested in performance tweaking ideas regarding a HP smart array p400i aswell.
We need to get it to work properly in the server enviroment.
The other issue, and perhaps more related to derby, is that the timestamp index wont stop growing.
After 24 hours it had grownfrom around 100mb to over 1gb, and the performance obviously dropped massively due to this.
I have tried running in-place compression on the table, which didn’t get me any space back.
What tests do you need me to run to be able to say what might be wrong ?