Profile Stored procedures in MySQL

Question

I am working with MySQL and using stored procedures. I have a profiling tool that I am using to profile the code that communicates 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.

Answer 1

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])