Re: How to Determine Database Performance Limits

"Ethan Post" <Blah_at_Blah.com> wrote in message news:<AAsu7.19228$Xk4.1286159_at_news1.rdc1.sdca.home.com>...
> One of my tasks at hand is ensuring our 12 CPU AIX box can process 150,000
> lines of sales orders per hour via J.D. Edwards. We have performed
> extensive testing and determine we will have to move the application logic
> to other machines and leave the DB where it is. My job now is to ensure the
> current box running 8.1.6 is capable of the task at hand. I am currently
> focusing on redo because I think that could be a potential bottleneck.
> Based on the numbers we already have during testing and extrapolating them
> we will need to be capable of the following:
>
> 120-160 MB of redo generated per minute
> 300-400 MB of traffic per minute sent from client (middle tier) via SQL*Net
> 600-800 MB of traffic per minute sent to client (middle tier)
> 1.6-2 million SQL*Net roundtrips per minute.
> 45-60 thousand commits per minute.
> 1.4-1.8 million user calls per minute.
>
> What will this achieve? An amazing 41 records per second! This gives you
> some idea of the swell coding going on in a modern day ERP system.
>
> Anyway, my question is how the heck do I determine if the box can actually
> do this? What is the best way to determine if the current box will handle
> the load. I can tell you simulating a load this heavy is hard to do!
>
> Your advice?
>
> Thanks,
> Ethan Post

Hi Ethan, how about gnumetrics ?
I would try to emulate the load. Just write a few pl/sql routines that insert records in a few tables. Fetch from sequences, update a few and commit. Run this from the client using sqlplus, run this from 10 sessions concurrently and see what happens.

I would use arbitrary tables for this. What is important is that you have a reasonable row length and a reasonable mix of tables. Maybe 5 or 10 tables will do. Give them a sequence, a sysdate and a varchar2 with random contents, a few indexes. If there are triggers is JDE, also make them in the test. Don't forget to also do some lookups. This will tell you if it is possible or not. If you cannot get the numbers you need in this test you will never be able to get them with the real app. If you do manage to get the numbers, you MIGHT be able to get them in JDE.

What disks do you have ?
What network do you have ?

Ronald.