homepage Welcome to WebmasterWorld Guest from 54.205.144.231
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4042657
 10:32 am on Dec 14, 2009 (gmt 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

 

phranque




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

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




msg:4042743
 2:13 pm on Dec 14, 2009 (gmt 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

rocknbil




msg:4042948
 6:45 pm on Dec 14, 2009 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved