Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
From: "Bergquist, Brett" <BBergquist@canoga.com>
To: Derby Discussion <derby-user@db.apache.org>
Date: Tue, 1 Oct 2013 10:09:43 -0700
Subject: RE: Proper configuration for a very busy DB?
Thread-Topic: Proper configuration for a very busy DB?
Thread-Index: Ac6+vkErsmKktstvRF+oEIx2oAAomgACh3Mg
Message-ID: <97EB699F861AD841B5908C7CA9C9565602827710C5D7@VSERVER1.canoga.com>
References: <5249D0E7.5040606@sdsusa.com>
 <97EB699F861AD841B5908C7CA9C9565602827710C54F@VSERVER1.canoga.com>
 <524AEFE7.2080704@sdsusa.com>
In-Reply-To: <524AEFE7.2080704@sdsusa.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Jerry, I have a similar database size, about +10,000,000 records a day bein=
g stored and needing purging.  I found that purging the database had a sign=
ificant impact on the insertion rate.  I originally had one table in the da=
tabase for these (in my case performance measurements) records.   I ultimat=
ely went to a "poor man's partitioning".   I created separate tables for ea=
ch week of the year (53 tables), inserted records into the correct week, us=
ed a database view to join these tables back into one logical table (a unio=
n across the tables) and then purging was done by week.  This was nearly in=
stantaneous using a TRUNCATE TABLE.

Just something to be aware of.

-----Original Message-----
From: Jerry Lampi [mailto:jal@sdsusa.com]=20
Sent: Tuesday, October 01, 2013 11:53 AM
To: Derby Discussion
Subject: Re: Proper configuration for a very busy DB?

Peter:
Each client has one connection.  It is used for the entire session (which c=
an be days).
The Derby log file are configured to have one log file per day.  Format nam=
es 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 t=
he number cached by the client(s) and each record is dumped to the DB one a=
t a time.  Not all 30 clients see 4000 per second, likely only 2 or three o=
f 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/tuni=
ng/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 no=
t, then do so and also change the derby.statementCache.size to something qu=
ite a bit larger.  This will allow the statements to be compiled once and c=
ached 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 th=
is point.  Right now it is doing about 100 inserts/second continuous 24x7 a=
nd it has peaked up to 200 inserts/second.  Granted my application is diffe=
rent than what you are doing but it is possible to get derby to run when bu=
sy.
>
>
> -----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 th=
eir 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 additi=
on, the clients are written so that if they are unable to talk to the DB, t=
hey 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 o=
nce.  The clients do not know about one another and therefore do not attemp=
t 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 fa=
st as desired and this results in the clients queueing more data, exacerbat=
ing the issue.
>
> So the DB is very busy.  The only significant thing we have done thus far=
 is change the derby.storage.pageCacheSize=3D5000 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 P=
M 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