How to see which script and queries is taking much mysql resource?

Mysql nomalization

[tabish]

Hi all,

When I see my server stat (CPU/MySql Memory usage) I see some of the sites using too much mysql and CPU.

Now, My problem is, I am not able to know what script or query is taking much time, when I ask my server people they give me a stat like this:

16597 27 18 0 185m 95m 4100 S 3.9 0.3 2815:34 mysqld
(2815 minutes on the same process)

Now I don't know what script is executing this sql and why it is taking so long?

Can you guys suggest any PHP tool by which i can see mysql process lists?

Regards

[phranque]

there is a MySQL SHOW PROCESSLIST [dev.mysql.com] command that may give you the information you are looking for.

you might also look into Using the New MySQL Query Profiler:
[dev.mysql.com...]

[tabish]

Thank you Phranque

But I was looking for some PHP script or something like that which can monitor mysql process and slow queries?

Do you know any open source class or script for that?

Regards

[rocknbil]

Drop a global variable (temporarily!) in your script somewhere. Call it $debug or something.

At the top of your script define two variables, $start_time and $current_time. Then do

$debug .= "start: $start_time ";

At various points in your script, append $debug with $current_time. This will define what's taking longest.

Supplement this with get_memory_usage() [us3.php.net], adding the values from it to your $debug.

As you do this, you can use explain [dev.mysql.com] on your selects (also explain syntax [dev.mysql.com],) add these to the $debug variable.

At the end of the script,

echo '<!-- ' . $debug . ' -->';

And view source.

Most likely it's not mysql, it's probably an excessive select or loop in your program hanging it up.