A profiler for MySql

Allright, one things that really bugs me when I do random development in PHP/MySql is the lack of the fantastic SQL Server profiler which lets you monitor all queries coming in to the database.
I haven’t been looking too hard for one in MySql but after being stuck on some seriously annoying bugs in a project I decided to go find one.
And I did.
And it was costly…

If you have money, I’m sure the Jet Profiler for MySql is great, but I don’t and I’m not paying $499 for monitoring some queries…

There is a command line profiler that works great and that arrived in MySql version 5.0.37 and it’s quite easy to use, here’s an example:

mysql> SET profiling = 1;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SELECT * FROM USER;
Empty SET (0.00 sec)
 
mysql> INSERT INTO USER (Username, Password) VALUES ('Superuser', 'Secret');
Query OK, 1 ROW affected (0.03 sec)
 
mysql> SELECT * FROM USER;
+----+-----------+----------+
| Id | Username  | Password |
+----+-----------+----------+
|  2 | Superuser | Secret   |
+----+-----------+----------+
1 ROW IN SET (0.00 sec)
 
mysql> SHOW profiles;
+----------+------------+-------------------------------------------------------
---------------+
| Query_ID | Duration   | Query
               |
+----------+------------+-------------------------------------------------------
---------------+
|        1 | 0.00032425 | SELECT * FROM USER
               |
|        2 | 0.03341050 | INSERT INTO USER (Username, Password) VALUES ('Superus
er', 'Secret') |
|        3 | 0.00022775 | SELECT * FROM USER
               |
+----------+------------+-------------------------------------------------------
---------------+
3 ROWS IN SET (0.00 sec)
 
mysql>

Very nice, this can help a lot when your running dynamic queries and want to see why they’re not working :)!
One thing though, the profiling works on a session basis, so different sessions cannot listen to the others queries, but this can be used inside whatever application you’re running in tests as well.

One thought on “A profiler for MySql

Leave a Reply