db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Steve Pannier" <steve.pann...@qlogic.com>
Subject Performance question
Date Thu, 12 Jul 2007 15:50:51 GMT
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 per-second 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 Java-based.  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  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!

View raw message