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 <j...@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:j...@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 > > >
