How can I measure mysql time, the time and / or load of an sql query in php?

Question

I need to find some measure of how long my queries are taking and the load on the server if possible.

I doubt its possible, but I would like to gain the cpu usage too.

Any ideas?

Answer 1

PHP Syntax

$starttime = microtime(); 
$query = mysql_query("select * from table"); 
$endtime = microtime();

Then calculate the difference between $starttime and $endtime.

Answer 2

Have a look at the query profiler. This may do the query time bit of what you need.

https://www.digitalocean.com/community/tutorials/how-to-use-mysql-query-profiling

https://dev.mysql.com/doc/refman/5.5/en/show-profile.html

There are various tools for seeing the load on the server

Answer 3

Is almost impossible given your database and web server are located differently
plus you try to achieve it using PHP

the limitation

• you need to have access to both servers (such as ssh)
• if you embed CPU load checking such as a exec_shell ssh into database and return uptime upon every query execution, is overkill

workaround

embed a cronjob in your database server,
periodically sent email if the server load went high

Or

at the database,
periodically sent email on current running query using
show full processlist

Example to store SHOW FULL PROCESSLIST

$con = mysqli_connect(...) or die('unable to connect');
$sql = "show full processlist";
$res = mysqli_query($con, $sql) or die($sql);

/* optional path is /tmp */
$fp  = fopen('/tmp/'.date('YmdHis'), 'w+');
while ($row = $res->fetch_row())
{
  fputcsv($fp, $row);
}
$res->free_result();

The above should be sufficient to dump current mysql process-list into a file.
In linux box, there are lots of commands allow user to show CPU load.
But is windows, I guess you can figure out with some search on google of SO

Answer 4

There is quite a bit of information you can get about database using

SHOW STATUS;

query.

I would assume Last_query_cost variable could be quite useful for your purposes of measurements. As well as Slow_queries which will show the amount of quires which was running for longer then certain amount of time. The full list of this variables is here http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html There is also a php function mysql_stat() php function which returns some database usage data. For example Queries per second could be somewhat useful. But to take a hold of a more certain data will require ssh access to the database.

Answer 5

use microtime to measure the time required for a query.

For server load, see http://www.php.net/manual/en/function.sys-getloadavg.php . CPU usage is mostly irrelevant, because mysql will be disk-bound most of the time.

Answer 6

For more detailed stats, you should use mysqli::get_connection_stats (if you use php 5.3+)

http://www.php.net/manual/en/mysqli.get-connection-stats.php

Answer 7

PHP can really only time the mysql_query() execution time, which includes the whole round trip, latency and transfer times. To have a break down you will need to use the MySQL profiler as mentioned already. The following code should output the information you need to know. If you want to incorporate the profiling into your PHP process, you will have to use the profiler and select relevant fields from the information_schema.profiling table, but if it is just to check the performance the below should suffice.

<?php

$con = mysql_connect("localhost","root","my-password");
if (!$con)
{
 die('Could not connect: ' . mysql_error());
}

mysql_select_db("my-database", $con);

function microtime_float()
{
 list($usec, $sec) = explode(" ", microtime());
 return ((float)$usec + (float)$sec);
}

mysql_query("set profiling=1;");

$starttime = microtime_float();
$query = mysql_query("SELECT * FROM my-table"); 
$endtime = microtime_float();
$trans_result = mysql_query("select sum(duration) as transtime from information_schema.profiling where query_id=1");
$transtime = mysql_result($trans_result, 0, 'transtime');
$total_time = ($endtime - $starttime); 
$transtime = ($total_time - $transtime); 

echo 'Total time: '.$total_time.' secs<br />';
echo 'Transfer time: '.$transtime.' secs<br />';
echo 'Query time break-down;<br />';

$debug_result = mysql_query("show profile cpu for query 1;");
while ($row = mysql_fetch_assoc($debug_result)) {
     echo $row['Status'].' (Time: '.$row['Duration'].', CPU_User: '.$row['CPU_user'].', CPY_sys: '.$row['CPU_system'].')<br />';    
}
?>

Once you are happy with performance, remove all but the MySQL query you want.