Profiling stored procedures

I’m trying to take some performance measurements of our stored procedures. I’ve already setup a debug table to log messages but is there any way to get milli- or microsecond precision? The timestamp field doesn’t offer this and I don’t see a way to get a time with either unix_timestamp() or now() or other functions with microseconds.

Any suggestions?

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date-format

You could try building a function to return microseconds using the modifier:

%f - Microseconds (000000…999999)

However I’m not sure if DATE_FORMAT(NOW(), ‘you_format’) has microsecond precision. On my machine “SELECT DATE_FORMAT(NOW(), ‘%s %f’)” doesn’t give ms accuracy.

Figured it out. Was able to make it work using the UDF function here:

[URL]MySQL Bugs: #8523: Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP fields

It gives millisecond precision which is good enough for now. It’s simple enough to modify for microsecond precision as well.

I’m surprised why MySQL for years can’t make accurate time counting implemented which is needed for a lot of things starting from internal counting to values in SHOW PROCESSLIST and slow query log.