db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Question on whether to use multiple databases/multiple network servers or not
Date Fri, 23 Mar 2012 12:41:45 GMT
An update, it turns out that so far the performance problem is not with Derby but with the
queries that JPA is generating.  I am still going to finish separating out the performance
from the provisioning and see if there is any performance gains to be had and will report
back then.

I just wanted to not let this hang around with the implication that Derby is not performing
when in fact it is the application that is not performing.  You know how these emails are
archived and live forever ;)


-----Original Message-----
From: Bergquist, Brett [mailto:BBergquist@canoga.com] 
Sent: Wednesday, March 21, 2012 10:12 PM
To: derby-dev@db.apache.org
Subject: RE: Question on whether to use multiple databases/multiple network servers or not

Thanks for the response Mike.

The JVM is Java 1.6.0

Currently configured, there are 16 worker threads that are processing performance data from
the network devices.  These threads are not busy waiting for data but rather are pooled and
dispatched when there is processing to perform.  Playing around with the number of processing
threads led to 16 being a good number for the current architecture.  I do realize that possibly
batching the updates to the disk and maybe using one thread to perform the database insertions
might be better but there is a wrinkle in that before the software can respond to the network
device that it has received the data, it must be ensured that the data is written to stable
storage.   So batching up the inserts does provide some problems.

A separate thread pool managed by the application server is used to process HTTP requests
which in turn hit EJB's that perform the provisioning processing.  This thread pool is configured
for 128 threads right now, but from monitoring I have not seen more than 20 in use at one
instant.  The request processing is synchronous.

The provisioning requests use completely separate tables that the performance data.  Only
some custom reports actually do some cross table queries and these are only run once in a
great while.   So there should not be any provisioning/performance data table contention.

The database is stored on 2 300Gb small form factor serial drives on a ZFS pool that are mirrored.
 Right now the log is on the same disk.  So moving the log to a separate ZFS pool on the other
two mirrored drives is possible and I will investigate that.  

There are query/update/delete operations being done by the provisioning requests.   Basically
read the current data, apply changes, and update is usually the processing.

The performance data does two indexed lookups and one insert per performance data record.
 The indexed lookups are in totally separate tables from the provisioning data.   I have implemented
a poor man's partitioning by having a table per week of the year for the performance data
and purging of data is done once a week by truncating the tables (where the data is no longer
needed).  This is working great in not trying to insert and delete from the same table.

I am in the process of trying to separate the provisioning and performance into two separate
databases.  This looks to be pretty straight forward and with surprisingly just a few changes.
  Simulating this environment is a little more challenging as there are about 45K network
devices being managed with about 1600 providing the performance data.   So the provisioning
portion of the database has records for about 45K devices times multiple copies of the this
configuration data (backup data to go to a previous know state).   Having available 45K real
devices is not possible so simulation firmware is run on the devices to make 1 device look
like 1000.   Similarly, having 1600 real devices providing data is done through hardware that
simulates multiple devices.

Over the next couple of days I should be able to get this up and running in a test environment.

From: Mike Matrigali [mikem_app@sbcglobal.net]
Sent: Wednesday, March 21, 2012 8:25 PM
To: derby-dev@db.apache.org
Subject: Re: Question on whether to use multiple databases/multiple network servers or not

Bergquist, Brett wrote:
> We have a system in production that has the following characteristics:
> *         Provisioning data for various network devices.  There is a
> large number of tables and rows for each device by the data changes 
> infrequently but changes have to be responsive as this data is 
> accessed by a human with a user interface
> *         Performance data that is being inserted into the database at
> about 6.5M records per day.  Queries are also done on this data for 15 
> minute intervals and also every 4 hours.  The inserts are non-stop and 
> the queries are periodic.  The inserts need to be responsive as this 
> data is being generated by network devices and needs to keep up.
> *         There is one database that contains both kinds of data.
> We are running into a performance problem particularly with provisioning
> data.   Without the performance data being inserted, the provisioning
> changes are performing okay but these are affected a great deal when 
> the performance data is being inserted at such a high rate.

is the provisioning problem with read only transactions, or are they write also?  My interpretation
is that all tables used by provisioning are different than those being inserted by performance.
> There are enough connections to the database engine.
> The system is an Oracle M5000 with 32 processors and 32Gb of memory.
> Looking at CPU utilization and the system is about 10% utilized.    It
> appears that the system is not I/O bound as of yet.
can you talk to number of threads provisioning and performance are using.  Derby will not
do much to break up a single connections work across multiple threads.  So a single inserter
may be cpu bound but looking at the machine it will only be 1/32 utilized, and derby will
not go faster for that connection.  In general derby does a good job of making each incoming
connection a different thread and running as many of them in parallel as possible as long
as there is not database lock contention.

What is the disk situation?  One disk, multiple disks, maybe multiple disks presented as a
single disk?  When talking about moving to multiple dbs, it would likely make a lot of sense
if you could spread the i/o to multiple disks putting one db on each.  Better is if the OS
just handles this by presenting multiple disks as one.
> I was wondering if it would make sense to separate out the performance 
> data into its own database and potentially its own JVM though a second
> Network Service running.    This will lead to some complexities when
> trying to correlate the performance data back with the provisioning data
> when needed (now to separate databases).    I was wondering if there is
> any thoughts on if this might help separate any contention in the 
> single database that might exist and allow better performance for the 
> provisioning information
Any chance you can prototype on a test machine easily first to verify it removes the bottleneck.

some bottlenecks in a single database include:
1 log file, so writes are somewhat blocked by other writes, but there is
      software to do "group" commit to optimize this.
1 background processing thread, so background work can become bottle neck, this is mostly
an issue if you are doing lots of deletes or updates of key fields in indexes.

1 disk for data per database, and optional second disk for log.  This is an obvious bottleneck
if you config has multiple disks that derby is not using.

There are other shared caches per database that are shared but i would be surprised if throughput
would be affected with such available cpu:
query cache, database cache, open file cache, jvm garbage collector, ...
> I almost would like it to be that the provisioning database access 
> have higher priority than the performance data since it is infrequent 
> but needs to be responsive.
> Any thought would be greatly appreciated.
> Brett

View raw message