Monitoring MySQL Queries

MySQL & MariaDB Other MySQL® Questions
1

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!

2

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

Hope this helps~.

3

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?

4

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?

5

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

6

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

[I] 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);[/I]

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

This is not a PHP forum D ;).

7

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 Argument070902 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?

8

Tell me if the upper code was OK for you.

9

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.