Re: How to Determine Database Performance Limits

Ethan Post wrote:
>
> Hey Ronald, good to hear from you. GnuMetrics has gone by the wayside for
> the moment. I still use a greatly modified version of it for all of this
> testing. You track is the way I am going but I am finding it will be pretty
> hard to duplicate the application load because it is so poorly designed.
> Perhaps if I have 100 processes randomly select from 5 tables, perform
> inserts, updates, deletes and full table scans that return no rows I might
> get close to it!
>
> - Ethan
>
> "Ronald" <devnull_at_ronr.nl> wrote in message
> news:67ce88e7.0110022259.460585ac_at_posting.google.com...
> > "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.
> > -----------------------
> > http://ronr.nl/unix-dba

Do you have the target application ready to go ? You could always capture the application code in trace files - them build some Pro*C programs to duplicate this fire up as many of them as you like...

Not a perfect benchmark by any means, but a possible starting point...

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."