Profile Stored procedures in MySQL

I am working with MySQL and using stored procedures. I have a profiling tool that I am using to profile the code that com开发者_开发技巧municates with MySQL through the stored procedures and I was wondering if there was a tool or capability within MySQL client to profile stored procedure executions. What I have in mind is something that's similar to running queries with profiling turned on. I am using MySQL 5.0.41 on Windows XP.

Thanks in advance.

There is a wonderfully detailed article about such profiling: http://mablomy.blogspot.com/2015/03/profiling-stored-procedures-in-mysql-57.html

As of MySQL 5.7, you can use performance_schema to get informations about the duration of every statement in a stored procedure. Simply:

1) Activate the profiling (use "NO" afterward if you want to disable it)

UPDATE performance_schema.setup_consumers SET ENABLED="YES" 
       WHERE NAME = "events_statements_history_long"; 

2) Run the procedure

CALL test('with parameters', '{"if": "needed"}');

3) Query the performance schema to get the overall event informations

SELECT event_id,sql_text, 
          CONCAT(TIMER_WAIT/1000000000,"ms") AS time 
             FROM performance_schema.events_statements_history_long 
   WHERE event_name="statement/sql/call_procedure";

| event_id | sql_text | time |

|2432 | CALL test(...) | 1726.4098ms |

4) Get the detailed informations of the event you want to profile

SELECT EVENT_NAME, SQL_TEXT,   
    CONCAT(TIMER_WAIT/1000000000,"ms") AS time   
 FROM performance_schema.events_statements_history_long   
 WHERE nesting_event_id=2432 ORDER BY event_id;   

| EVENT_NAME | SQL_TEXT | time |

| statement/sp/stmt | ... 1 query of the procedure ... | 4.6718ms |

| statement/sp/stmt | ... another query of the procedure ... | 4.6718ms |

| statement/sp/stmt | ... another etc ... | 4.6718ms |

This way, you can tell which query takes the longest time in your procedure call.

I don't know any tool that would turn this resultset into a KCachegrind friendly file or so.

Note that this should not be activated on production server (might be a performance issue, a data size bump, and since performance_schema.events_statements_history_long holds the procedure's parameters values, then it might be a security issue [if procedure's parameter is a final user email or password for instance])

You can turn on the slow query logging within MySQL.

Take a look at this other SO question:

MYSQL Slow Query

Depending on which version, you may actually be able to set the value to zero, so every single query in the DB is shown in the slow query log.

See here for additional details:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_long_query_time