MySQL Performance: MySQL => Monitoring MySQL Queries

Members

Help

Home

Home » Performance » MySQL » Monitoring MySQL Queries

Show: Today's Messages :: Show Polls :: Message Navigator

Monitoring MySQL Queries [message #1709]

Fri, 31 August 2007 09:23

srynonick
Messages: 6
Registered: August 2007

Junior Member

hello,

are there any tools to monitor which queries have been executed on the mysql server and especially how long they took?

thank you very much!

Report message to a moderator

Re: Monitoring MySQL Queries [message #1712 is a reply to message #1709 ]

Sun, 02 September 2007 11:49

jcn50
Messages: 44
Registered: September 2007

Member

Do you have phpMyAdmin?... I'm using it when I want to check the load, SQL command is:
SHOW PROCESSLIST

Hope this helps~.

Enjoy the Net!

Report message to a moderator

Re: Monitoring MySQL Queries [message #1714 is a reply to message #1709 ]

Sun, 02 September 2007 12:15

srynonick
Messages: 6
Registered: August 2007

Junior Member

hello jcn50,

thank you for your reply.
unfortunately this doesn't help me.

let me make it a little bit more clear:
i have a php script with several mysql queries. until i can use show processlist, they are already done. i need a tool that logs all ever executed queries and how long they took. i'm wondering if this isn't maybe already included in mysql as some kind of detailed logging?

Report message to a moderator

Re: Monitoring MySQL Queries [message #1715 is a reply to message #1709 ]

Sun, 02 September 2007 12:22

jcn50
Messages: 44
Registered: September 2007

Member

You need to turn the LOG feature ON in the mySQL config file (my.ini).

Is your mySQL server on your computer? Or shared hosting?

Enjoy the Net!

Report message to a moderator

Re: Monitoring MySQL Queries [message #1716 is a reply to message #1709 ]

Sun, 02 September 2007 12:28

jcn50
Messages: 44
Registered: September 2007

Member

I have a better idea for you: you can measure the time your query took, within your script... just take the time before the query, and after.... and you're done~.

Enjoy the Net!

Report message to a moderator

Re: Monitoring MySQL Queries [message #1717 is a reply to message #1709 ]

Sun, 02 September 2007 12:31

jcn50
Messages: 44
Registered: September 2007

Member

Since you code in PHP, it could look like this:

list($usec, $sec) = explode(' ',microtime());
$querytime_before = ((float)$usec + (float)$sec);

$result = YOUR QUERY/QUERIES ;

list($usec, $sec) = explode(' ',microtime());
$querytime_after = ((float)$usec + (float)$sec);

Your time, in seconds, is in $querytime = $querytime_after - $querytime_before.

This is not a PHP forum Laughing

[Updated on: Sun, 02 September 2007 12:37]

Enjoy the Net!

Report message to a moderator

Re: Monitoring MySQL Queries [message #1718 is a reply to message #1709 ]

Sun, 02 September 2007 12:36

srynonick
Messages: 6
Registered: August 2007

Junior Member

hello,

thank you again for your reply. yes, mysql is running on my local pc, where i'm developing and need the execution times to do some optimations.
i've googled a little bit and activated logging (mysql -l). a log looks like this:

mysqld, Version: 5.0.45-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 0, Named Pipe: (null)
Time                 Id Command    Argument
070902 18:35:01	      1 Connect     root@localhost on testdb
		      1 Query       select * from testtab

well, starting time is included and the query, but not how long the query took ):
i also couldn't find something about an additional option for this?

[Updated on: Sun, 02 September 2007 12:36]

Report message to a moderator

Re: Monitoring MySQL Queries [message #1719 is a reply to message #1709 ]

Sun, 02 September 2007 12:39

jcn50
Messages: 44
Registered: September 2007

Member

Tell me if the upper code was OK for you.

Enjoy the Net!

Report message to a moderator

Re: Monitoring MySQL Queries [message #1720 is a reply to message #1709 ]

Sun, 02 September 2007 15:57

srynonick
Messages: 6
Registered: August 2007

Junior Member

sorry, was writing while you posted and didn't see your code!
i know this can be done in php, but using mysql i wouldn't have to change anything in my php script, could also easily analyze other scripts, which aren't written by me.

[Updated on: Sun, 02 September 2007 15:57]

Report message to a moderator

Previous Topic:	What is "normal" performance of the MySQL database?
Next Topic:	How MySql optimises LIMIT 1 with ORDER BY by expression

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 8 17:38:05 EST 2009

Total time taken to generate the page: 0.01608 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.