db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Proper configuration for a very busy DB?
Date Mon, 30 Sep 2013 19:47:06 GMT
Jerry, can you provide a bit more background which might be helpful:

- what is your definition of a flurry of data?   What sort of transaction rate do you estimate
this is?
- are you using prepared statements for your inserts, updates, etc? If not, then do so and
also change the derby.statementCache.size to something quite a bit larger.  This will allow
the statements to be compiled once and cached instead of being prepared each time you execute
them.
- are you using a connection pool or are you opening/closing connections frequently?

I have a system with a busy database and it took some tuning to get to this point.  Right
now it is doing about 100 inserts/second continuous 24x7 and it has peaked up to 200 inserts/second.
 Granted my application is different than what you are doing but it is possible to get derby
to run when busy.


-----Original Message-----
From: Jerry Lampi [mailto:jal@sdsusa.com] 
Sent: Monday, September 30, 2013 3:29 PM
To: Derby User Group
Subject: Proper configuration for a very busy DB?

We have about 30 clients that connect to our version 10.8.2.2 Derby DB.

The clients are programs that gather data from the operating system of their host and then
store that data in the DB, including FTP activity.  
Sometimes, the clients get huge flurries of data all at once and Derby is unable to handle
the influx of requests; inserts, updates, etc.  In addition, the clients are written so that
if they are unable to talk to the DB, they queue up as much data as possible and then write
it to the DB when the DB becomes available.

This client queuing is a poor design, and places greater stress on the DB, as when the 30
clients finally do talk to the DB, they all dump data at once.  The clients do not know about
one another and therefore do not attempt any throttling or cooperation when dumping on the
DB.

The net effect of all this is that the DB is too slow to keep up with the clients.  As clients
try to feed data to the DB, it cannot accept it as fast as desired and this results in the
clients queueing more data, exacerbating the issue.

So the DB is very busy.  The only significant thing we have done thus far is change the derby.storage.pageCacheSize=5000
and increase Java heap to 1536m.

Is there a configuration considered optimal for a VERY busy Derby DB?

Thanks,

Jerry


---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 130930-0, 09/30/2013 Tested on: 9/30/2013 2:28:40 PM avast! - copyright
(c) 1988-2013 AVAST Software.
http://www.avast.com






Mime
View raw message