homepage Welcome to WebmasterWorld Guest from
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, Moderator: open

Databases Forum

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

 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?




 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:


 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?



 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