Re: High Volume Inserts

<srinivas.somayajula_at_gmail.com> wrote in message news:1127745052.504391.291360_at_g44g2000cwa.googlegroups.com...
> All,
>
> We are building an application using Websphere MQ coupled with
> Websphere Application Server on 2 solaris 5.9 servers (Sunfire 6800 &
> 4500)
>
> The intent of the application is to pull messages from the queue,
> perform some validation, and insert to the database. During our
> performance testing this is what I encountered:
>
> 1. With 1 instance of the application on the 6800 and 1 instance of
> the application on the 4500, we were able to pull ~150 messages per
> minute and insert to the Oracle 9i db at a rate of 16,000 inserts/sec.
>
>
> 2. With 2 instances of the application on the 6800 and 2 instances of
> the application on the 4500, we were able to pull ~200 messages per
> minute and still only insert to the Oracle 9i db at a rate of 16,000
> inserts/sec.
>
> 3. With 2 instances of the application on the 6800 and 1 instances of
> the application on the 4500, we were able to pull ~260 messages per
> minute and still only insert to the Oracle 9i db at a rate of 16,000
> inserts/sec.
>
> First question I have is, would this experiment present the hypothesis
> that the database is the bottleneck here? Also, if so, what are some
> immediate optimizations that can be made to improve this (I am not a
> DBA of any sort)? Thanks in advance.
>

Well, who knows? You are going to have to look at the system and find where the bottlenecks are. Here are my guesses: 1. Not using bind variables. This is a real killer and very common. 2. Not using the array interface for inserts. Use the array interface. You will have to experiment with a good array size but 100 or 1000 rows at a time are not unusual.
3. Over parsing. Parse once and then bind and execute. 4. Potentially not enough disk spindles. Maybe you have saturated the disk drives.
5. Too many indexes on the underlying objects. Indexes will slow inserts.

We don't have enough information to tell you what the problem is. Jim Received on Mon Sep 26 2005 - 09:47:34 CDT