From: Hironori Sato
Date: March 16 1999 4:05am
Subject: Response time measurement
List-Archive: http://lists.mysql.com/mysql/298
Message-Id: <199903160359.TAA16491@tracer.jpnnet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

Hi ya'all.

I've been running mysql server for a while and wanted to measure the
performance somehow.  First, I wipped out a perl script to do uptime to
measure the load.  The result is graphed by MRTG.  But, since I read
somewhere that "load" is not the best way to measure the actual system
performance (correct me if I'm wrong), I decided to measure the mysql's
response time by sending some random queries.  Then, it got very
complicated...  I would appretiate any comment on this.

Basicaly, my aproach to this problem was same as getting the uptime.  I get
the number I want, then pass it to MRTG.  My co-worker wipped out very
simple perl script which creates few random user_id and do something like
"select field_name from user_table where user_id = $random_user_id"  It
measures the time like this:

---------------
$start_time = (times)[0];

# connect
# for loop doing prepare, execute, fetchrow()
# (the user table has 60K entries)
# disconnect

$end_time = (times)[0];

$total_time = $end_time - $start_time;
$time_per_query = $total_time/$num_query;
---------------

What I found out was that, for queries up to 20 or so, total_time would
result in either 0, 17, or 33ms.  


This is what confused me:

1) It's more likely the perl issue, but 'times' is supposed to measure in
milli-second, but it looks more like 100th of second.  Or, in order of
16.666ms.  Is this correct?  I guess, at this level of time difference, the
system can't realy measure the time accurately...  Is there any
alternatives to this?

2) When the system is loaded (load > 1.0), the above measurement shows
slight bump, but not much.  It's usually under 20ms per query, it might go
up as high as 32ms.  Is it safe to say that the system load might be high,
but mysql is running just fine?  It sure feels like it too.

3) Is it even worth trying to do this cuz mysql is just too damn fast?
Maybe, at current load, my server has plenty of hourse power to crank out
so that I don't see the delay in the system?  

4) Something related to response time...  Is there any way to measure the
each individual query's reponse time?  (Other than, doing it myself in PHP)
 This way, I could analyze which query is taking long time to process then
I can tweak each query that's running too slow.  Would commercial support
cover this much?


BTW, I'm running mysql 3.22.14b on RH 4.2.  Majority of queries come from
the webserver running apache & php.

Thanks in advance.

Hiro

//--- Hironori Sato --------------------------------------- KB9HAD ---
//  satoh@stripped                  http://staff.jpnnet.com/satoh/
//  Japanese Network                  http://www.jpnnet.com/