Welcome to WebmasterWorld Guest from 54.162.164.247

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: 324
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:10888
votes: 72


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: 324
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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members