MYSQL profiling

@PaPPyApr 14.2010

I know its possible but using php code i forget, ive searched the forums but dont see anything

i need to enable mysql profiling and show the profiles

(using these mysql commands set profiling=1; show profiles?

but how do i pull it off?

thanks

to post a comment

PHP

@NogDogApr 14.2010 — #I haven't used it, but it looks like all you'd need to do is:

[code=php]
 mysql_query("set profiling=1");
 mysql_query("whatever you actually want to do with the DB");
 $result = mysql_query("show profile");
 echo "<table>n";
 while($row = mysql_fetch_assoc($result))
 {
 echo "<tr>";
 foreach($row as $key => $val)
 {
 echo "<td><b>$key:</b></td><td>$val</td>n";
 }
 echo "</tr>n";
 }
 [/code]

You could do something similar with the [b]show profile for query 1[/b] syntax to get details for a given query.

@PaPPyauthorApr 14.2010 — #i get a warning mysql_fetch_assoc is not a valid MYSQL command

i am using a live cd and running mysql 4.0.18

is this not a supported feature?

@NogDogApr 14.2010 — #If the mysql_query() worked, then I don't know why the fetch_assoc would have failed unless you are using a really old version of PHP (< 4.0.3). You could try mysql_fetch_array() with the MYSQL_ASSOC constant for the 2nd parameter.

@PaPPyauthorApr 14.2010 — #

[code=php]mysql_query("SET profiling = 1");
 mysql_query("SET profiling_history_size = 50");
 
 echo "<table border=1><tr><td>#</td><td>Duration</td><td>Query</td></tr>";
 $profiling = mysql_query("show profiles");
 while($r = mysql_fetch_array($profiling)){
 echo "<tr><td>{$r['Query_ID']}</td><td>{$r['Duration']}</td><td>{$r['Query']}</td></tr>";
 }
 echo "</table>";
 //show some details for 2 common queries
 echo "<table border=1><tr><td>Status (Query 2)</td><td>Duration</td><td>CPU User</td><td>CPU Sys</td><td>Source</td></tr>";
 $p1 = mysql_query("show profile cpu, source for query 2");
 while($r = mysql_fetch_array($p1)){
 echo "<tr><td>{$r['Status']}</td><td>{$r['Duration']}</td><td>{$r['CPU_user']}</td><td>{$r['CPU_system']}</td><td>{$r['Source_function']}</td></tr>";
 }
 echo "</table>";
 echo "<table border=1><tr><td>Status (Query 3)</td><td>Duration</td><td>CPU User</td><td>CPU Sys</td><td>Source</td></tr>";
 $p1 = mysql_query("show profile cpu, source for query 3");
 while($r = mysql_fetch_array($p1)){
 echo "<tr><td>{$r['Status']}</td><td>{$r['Duration']}</td><td>{$r['CPU_user']}</td><td>{$r['CPU_system']}</td><td>{$r['Source_function']}</td></tr>";
 }
 echo "</table>";
 [/code]

found what i was looking for thanks

MYSQL profiling

4 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

MYSQL profiling

4 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

4 Comments(s) _↴

Also in #PHP _↴