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.
>

There seems to be a hole in the statistics:

1. You pull 150 messages / min and do insert 16,000 inserts per second. Then you go up to 200 messages per minute, but still only do 16,000 inserts/sec. Where has the data from the extra 50 messages got to if Oracle has not increased its consumption rate ? Is there a queue between the message pulling and the Oracle inserting ? Is that where the bottleneck is?
2. You have 2 apps on the 6800 and 2 on the 4500 and can pull 200 messages per minute. You stop one app on the 4500 and the rate at which you can pull messages goes UP. Doesn't this suggest that something is happening a long way from Oracle that is a limiting factor ? (And it may be something completely different from the hypothetical problem I suggested in (1)

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005