Welcome to WebmasterWorld Guest from 107.20.75.63

Forum Moderators: open

Message Too Old, No Replies

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

Mysql nomalization

     
10:32 am on Dec 14, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 22, 2005
posts: 323
votes: 0


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

11:47 am on Dec 14, 2009 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10542
votes: 8


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...]

2:13 pm on Dec 14, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 22, 2005
posts: 323
votes: 0


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

6:45 pm on Dec 14, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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.