I have a question regarding Derby performance:
We will be developing a Java application to collect and store statistics
and counters from devices, so that we can then graph the performance of
these devices. We need to store statistics on a persecond basis for up
to several months at a time, and every second we need to be able to
store stats for up to 400 devices. Plus, each device can have up to 50
statistics/counters ... every second. So you can see there will be
large amounts of data being stored.
We are hoping Derby will meet our needs as it is free, has a small
footprint, and is Javabased. I have been testing Derby to see how it
performs storing large amounts of data in this environment, and I've
seen a slight degradation of performance over time. I'm using Derby
10.2.2.0. I track the amount of time it takes for each iteration, with
one "iteration" being an insert of 400 records. My last test was 1
million iterations, and the average time for each iteration slowly
increased over the life of the test. (I've attached a file showing
average times.)
I did read the "Tuning Derby" manual, and applied the following tuning
changes while running my tests:
 Set autocommit to false (I programmatically do a commit
every 2000 inserts)
 Changed derby.storage.pageSize to 16384
 Changed derby.storage.pageCacheSize to 5000
 Use a PreparedStatement for my inserts
My table is called "stats", and is defined as follows:
"create table STATS (" +
"query_id int not null," +
"create_time bigint not null," +
"switch char(20), port int, " +
"intProp1 int, intProp2 int," +
"intProp3 int, intProp4 int," +
"intProp5 int, intProp6 int," +
"intProp7 int, intProp8 int," +
"strProp1 char(20), strProp2 char(20)," +
"strProp3 char(20), strProp4 char(20)," +
"strProp5 char(20), strProp6 char(20)," +
"strProp7 char(20), strProp8 char(20)," +
"longProp1 bigint, longProp2 bigint," +
"longProp3 bigint, longProp4 bigint," +
"longProp5 bigint, longProp6 bigint," +
"longProp7 bigint, longProp8 bigint," +
"longProp9 bigint, longProp10 bigint," +
"longProp11 bigint, longProp12 bigint," +
"longProp13 bigint, longProp14 bigint," +
"longProp15 bigint, longProp16 bigint," +
"longProp17 bigint, longProp18 bigint," +
"longProp19 bigint, longProp20 bigint," +
"longProp21 bigint, longProp22 bigint," +
"longProp23 bigint, longProp24 bigint," +
"longProp25 bigint, longProp26 bigint," +
"longProp27 bigint, longProp28 bigint," +
"longProp29 bigint, longProp30 bigint," +
"longProp31 bigint, longProp32 bigint," +
"PRIMARY KEY ( create_time, switch, port ))"
Does anyone have any suggestions or ideas as to why I'm seeing the
performance degrade over time? Is there a better way of defining my
"stats" table? Any other tuning parameters I can apply?
Thanks in advance!
Steve
