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