[Asterisk-Users] OT : For the SQL gurus - performance testing

[Chris Albertson]

Testing a querry by doing 2000 identical querries and then
deviding the total by 2000 is not a valid way to
measure the time to do one querry.  The result will appear
to be as much as 100X or even more to fast.

The reason is:

1) Operating system will have cached the exact disk sectors
required resulting in zero disk access time to 1999 of the
querries.

2) If not the above the DBMS will have it's own cache

3) Some DBMSes will cache the "querry  plan" so even the
internal time to process the SQL into a list of lower level
actions will be reduced for 1999 of the querries.

A more realistic test would use multiple processes to
  1) Do normal "server stuff" to keep the OS-level caches flushed
  2) Do background writes to the DBMS, say loggig CDR data
     at a realistic rate
  3) The test program that does 2000 __random__ test querries with
     a small, realistic delay between each.

Next you'd devide the total time by 2000 and then subtract the
one or two second delay you introduced out.   I'll bet a beer
the result is longer than 2ms.

An even more realistic test would run four or five copies of
step #3 above concurently.  In the real world with MySQL the
biggest constraint of performance and scaleability is due to
table locking and a very simple test will ignore this single
largest factor.




--- Peer Oliver schmidt <posde at theinternet.de> wrote:
> Hi David,
> 
> >>>>For those who are interested I have done some speed tests on
> these
> >>>>Test script of 1000 quieries..
> >>>>Query2 ("code" field indexed) = 2.302s
> >>>OUCH! those times are loooooooooooong!
> >>I agree the first three are long, but the last one works out to
> just
> >>over 26000 queries per min.. I didn't think that was bad for a PII
> [..]
> > picture.  We are providing dial tone.  I don't want to have to wait
> an
> > extra 2.302 seconds for my call to be set up.  Also, think of the
> big
> > organizations:  if you have 200 phone calls, and you have each one
> take
> > even a couple of seconds extra, you are going to have to add more
> > lines...
> 
> you might want to re-read the results:
> 
> 1000 queries = 2.302s
> 
> For me this looks like 2ms per query.
> 
> Maybe WipeOut can confirm the information (one way or another)
> --
> Best regards
> 
> Peer Oliver Schmidt
> the internet company
> 
> _______________________________________________
> Asterisk-Users mailing list
> Asterisk-Users at lists.digium.com
> http://lists.digium.com/mailman/listinfo/asterisk-users


=====
Chris Albertson
  Home:   310-376-1029  chrisalbertson90278 at yahoo.com
  Cell:   310-990-7550
  Office: 310-336-5189  Christopher.J.Albertson at aero.org
  KG6OMK

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree