db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Ondruška <peter.ondru...@yahoo.com>
Subject Re: Proper configuration for a very busy DB?
Date Wed, 02 Oct 2013 14:41:49 GMT
I mean transaction log, by default they are in log subdirectory of database, next to seg0 directory.
If you can do batch insertions.

Peter

> On 1 Oct 2013, at 17:53, Jerry Lampi <jal@sdsusa.com> wrote:
> 
> Peter:
> Each client has one connection.  It is used for the entire session (which can be days).
> The Derby log file are configured to have one log file per day.  Format names like: productName-stderr.2013-10-01.log
and productName-
> stdout.2013-10-01.log.
> 
> Brett:
> - A flurry of data has been as great as 4000 records per second.  That is the number
cached by the client(s) and each record is dumped to the DB one at a time.  Not all 30 clients
see 4000 per second, likely only 2 or three of them.  The DB has over 10 million records in
it at any given time and it is purged daily of older records.
> - We use prepared statements (PS).
> - Each client has one dedicated connection.
> 
> All:
> I appreciate your responses.  I will benchmark using JMeter and then follow the tuning
tips for derby 10.8 ( http://db.apache.org/derby/docs/10.8/tuning/index.html ).  I will start
by tweaking the derby.statementCache.size up from the 100 default.
> 
> Any other advice greatly appreciated.
> 
> Thanks,
> 
> Jerry
> 
> On 9/30/2013 2:55 PM, Peter wrote:
> Do you open new connection every time or do you have a pool? How often does Derby checkpoint/switch
log file?
> 
> 
> Peter
> 
> 
>> On 9/30/2013 2:47 PM, Bergquist, Brett wrote:
>> 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
> 
> 
> 
> ---
> avast! Antivirus: Outbound message clean.
> Virus Database (VPS): 131001-0, 10/01/2013
> Tested on: 10/1/2013 10:53:12 AM
> avast! - copyright (c) 1988-2013 AVAST Software.
> http://www.avast.com
> 
> 
> 

Mime
View raw message